VLOOKUP Script

[Sam Moffatt]

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.

[Ron Kline]

Have you ever tried this concept using script to access the other forms data rather than a link to form field?

[Sam Moffatt]

Sure! A minor modification like this should work:

function vlookup_form(lookup, form_name, search_field, return_field) {
	var entries = document.getFormNamed(form_name).getRecords();

	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 "";
}

Haven’t tested it but it’s a small change to have the source be all the records in a form versus a link to form field. Obviously the field ID’s would be from the other form.

[Ron Kline]

I did a little testing on this today and it works pretty well. Nice piece of code. Thanks for sharing!

[Author]

Posts