Question

Forms: How best to use alternate lookup rules to populate fields?

Forms Version 10

Updated January 22, 2018

asked on January 21, 2018

Hi guys,

We have several hundred items in a SQL table that have two main references: the Item ID, and/or the Item Name.

We want to use a Form to be able to fill the other item detail fields from a lookup against the SQL table using either the Item ID, or the Item Name.

Normally, I would set up a Lookup Rule for the lookup against the reference field and then fill the remaining (matching) fields on the form. In this case however, sometimes only the Item ID is known, and sometimes only the Item Name is known. If a user knows the Item ID then I need to be able to fill the Item Name field from the lookup rule as well as all the other fields based on the Item ID lookup, however if they only know the Item Name, then I need the lookup to fill the Item ID field as well as the other fields.

I don't want to create a lookup loop between the ID and Name fields, is there a recommended approach to this type of scenario?

Thanks,

Mike

0 0

Replies

replied on January 21, 2018

Hi Mike,

You can use Ajax; for example:

$(document).ready(function(){
	$("#Field1").change(function(){
      	var itemID = $("#Field1").val();
		data ={itemID:itemID};
          $.ajax({
            type        : "POST",
            url         : "http://webapp/ajax.php?func=getItemByID",
            data        : data,
            dataType    : "json",
            success     :function (result){
				$("#Field2").val(result.itemName);
              	$("#Field3").val(result.itemDesc);	
            }
          });
	});
	$("#Field2").change(function(){
      	var itemName = $("#Field2").val();
		data ={itemID:itemID};
          $.ajax({
            type        : "POST",
            url         : "http://webapp/ajax.php?func=getItemByName",
            data        : data,
            dataType    : "json",
            success     :function (result){
				$("#Field1").val(result.itemID);
              	$("#Field3").val(result.itemDesc);	
            }
          });
	});  
});

Good luck!

2 0

replied on January 22, 2018

Mike,

Generally what we do in this case is make a SQL view that combines the name + item ID into a single result column. Then make the dropdown searchable with something like https://select2.org/ as it is only a few lines of code. This way they can type in either entry and it will populate.

1 0

replied on January 22, 2018

Hi John,

How did you add a css and js files to a form?

0 0

replied on January 22, 2018

Thank you guys, I'll check both those options out.

0 0

You are not allowed to follow up in this post.

Question

Question

Forms: How best to use alternate lookup rules to populate fields?

Replies

Sign in to reply to this post.