VLOOKUP Script

Originally noted in the general forum, this is a method equivalent to Excel’s VLOOKUP. It’s actually mostly templated from the snippets, the child records loop snippet looks mostly like the vlookup function and the prompter snippet was in the other form script POC.

This will work, it requires using script field instead of calc field but it’s not that bad. You need to tell it the value you want to lookup with, the “join” field (table or link to form), the search field to match the lookup value on and then the field to return.

Create a new form script called “vlookup” with the following contents:

function vlookup(lookup, join_field, search_field, return_field) {
	var entries = record.getFieldValue(join_field);

	for (var index = 0, count = entries.length; index < count; index++){
     	var target = entries[index].getFieldValue(search_field);
		if (target && target == lookup) {
			return entries[index].getFieldValue(return_field);
		}
	}
	return "";
}

Then create a script field to map the values across:

form.runScriptNamed('vlookup');
var addresses_id = 'fld-34e22de8a7cf438fb4a83146108f0511';
var address_name_id = 'fld-f05929829d674141aaed98efe11e29f1';
var street_id = 'fld-04ec2a23e3554770b3e1f1d771157dd6';
var primary_address = record.getFieldValue('fld-9b2865aa57b74b70bd4421b27081d65b');

vlookup(primary_address, addresses_id, address_name_id, street_id);

In the script editor, select the fields from the linked form and use the “ID” button instead of double clicking them to get the var syntax. You’ll want to change the last field to match your fields across.

I’ve attached a sample archive which should demonstrate what I’m talking about. It also has another form script using a prompter to handle the address change but for some reason the script fields aren’t updating afterwards, you have to manually press refresh on the record.

Sample archive link.