Calculated column - finding current row value in another query output

💬 App Building
1

Hello again!

I have a table with a list addresses. I would like to add a calculated column to this table, that looks up each address in the table - and checks if it can be found in another list of addresses, generated from a different query. if the address was found on that list, it marks it as "yes".

is there a way to do that?

thank you!

2

There is definitely a way to accomplish this. The most straight-forward way would be to search the results of the other query for the value in currentSourceRow.yourValueProperty

The calculated column would be setup with the Value property being the result of this search/lookup. If the value you are using is a key in the other query results, you could set this with something like:

{{otherAddressListQuery.data[currentSourceRow.yourValueProperty]}}

and check to see if there is a null value or a returned object. If you just want to know if it is there or not your column would be a Boolean and the value might be more like:

{{otherAddressListQuery.data.indexOf(currentSourceRow.yourValueProperty) != -1}}

As a side-note, your query data may not be formatted as an array or you may see the results of the .data appear as several arrays with the query fields as keys. Then it would look more like:

{{otherAddressListQuery.data.theLookupPropertyArray.indexOf(currentSourceRow.yourValueProperty) != -1}}

3

it worked perfectly!! thank you so much!!

1 Like
4

Can I ask please one more question -

How can I use this for something like "vlookup"? /

to be specific - look up currentSourceRow.myValueProperty in column X in otherAddressListQuery.data, but the output should be the corresponding value in column Y? (so rather than a yes/no question, actually pulling out the value in column Y).

thank you!

5

So, in this case you would be returning the index of the entry that is found (the value that isn't -1) with:
{{otherAddressListQuery.data.theLookupPropertyArray.indexOf(currentSourceRow.yourValueProperty)}}

... a quick and dirty way to use this would be to wrap it in index brackets and feed it back to the array data you need with:

{{otherAddressListQuery.data.thePropertyArrayValueYouWantToDisplay[otherAddressListQuery.data.theLookupPropertyArray.indexOf(currentSourceRow.yourValueProperty)]}}

... but this could be a bit costly in terms of performance so you might consider tinkering around with other array/object search methods.

1 Like
6

thank you! once again, worked perfectly. thank you so much.

1 Like