Calculation similar to VLOOKUP (MS Excel)

[Chris Ju]

Hello Brendan,

is it possible to add a calculation similar to Excels VLOOKUP?

Thanks!
Chris

[Brendan]

You could do something like that using a Script field instead of a Calculation field. You’d just have to fetch the values from the field from another form using the value from a field in your current form. I’m sure there’s more nuances to the VLOOKUP function. I’ve just never used it before.

[Sam Moffatt]

You could possibly do a Link to Form with a JOIN type to get matches that way and then you could iterate over the values of the Link to Form field. Not quite a 1:1 match to the functionality but you could tie this together with a script field to get the exact value you want out of the children.

[Chris Ju]

Thanks for your reply! The Link to form solution would not really help. Nevertheless i’ll give it a try.

A solution with a VLOOKUP calculation would be better to get data from a table inside a form.

After i tried different things, i’ll come back to this thread.

Thanks!
C.

[Sam Moffatt]

Out of interest, do you mind sharing a little more about your use case, what structures you’ve got already and what you’re trying to solve? Might help in providing lateral solutions to achieve your goal :)

[Chris Ju]

Thanks again!

That’s a very complex thing i’m trying to solve. In a silence minute, i’ll write it here!

C.

[Alan Woollard]

I think I am looking for the same thing – Hope your “silence moment” bears fruit.

[Chris Ju]

Quiet moments are unfortunately a shy species! ;-)

[Sam Moffatt]

@mythical if you can share your use case that might help as well :)

[Chris Ju]

Thanks for your interest and help!

There are several scenarios, but all are based on the same problem (which can be easily implemented with Excel’s VLOOKUP!):

1. I have a form (form1), eg clients.
2. In this form I have a table (tab1) or a child form (form2) with eg address data (street, postcode, city, …).
3. tab1 / form2 has several records linked to form1 (eg several addresses, such as business, private, branch, …)
4. Now I want to set a main address from the linked records (tab1 / form1) for a client or switch as needed. The reason for this is that form1 itself is linked to a form eg “files” (form0) and the main address is to be used in form0.

….

So far, I have entered the main address in form1 itself. But that is awkward!

…

The next scenario would be to be able to select in form0 from the records from form1 (from tab1/form2) by checkbox.

Thanks!

[Sam Moffatt]

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.

Attachments:

You must be logged in to view attached files.

[Chris Ju]

That is excellent! Thanks alot! Did not think that it is so “easy”!

Maybe I have other much more complex programming tasks. Are you interested in helping me? Of course I would pay for it!

[Sam Moffatt]

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.

I don’t have dedicated time to commit to extra work, I don’t mind helping ad hoc on the forums though. Something like this is also generically useful and interesting enough.

[Author]

Posts