Tap Forms – Organizer Database App for Mac, iPhone, and iPad › Forums › Script Talk › VLOOKUP Script
- This topic has 3 replies, 2 voices, and was last updated 4 years, 11 months ago by Ron Kline.
-
AuthorPosts
-
July 24, 2019 at 9:34 PM #36037
Sam MoffattParticipantOriginally 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 thevlookup
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.
November 12, 2019 at 5:16 PM #38017
Ron KlineParticipantHave you ever tried this concept using script to access the other forms data rather than a link to form field?
November 12, 2019 at 7:52 PM #38018
Sam MoffattParticipantSure! 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.
November 13, 2019 at 8:20 AM #38025
Ron KlineParticipantI did a little testing on this today and it works pretty well. Nice piece of code. Thanks for sharing!
-
AuthorPosts
You must be logged in to reply to this topic.