Double Dereference workaround without virtual columns

Hey Guys,

Not sure if anyone has looked into this yet but I recently used a workaround for accessing a double dereference, without the use of a virtual column (saved computation time?)

I used a nested LOOKUP() expression that’s seemed to do the trick (without the repetitive check of the Virtual Column)

Example

Lookup( Lookup("Ref 1", "Table 1", "Check Col 1", "Return Col 1"), "Table 2", "Check Col 2", "Return Col 2")

The inner Lookup expression (Or more precisely the first return column) acting as the second dereference. Hopefully someone else see’s value in this.

In my case I used it to get a customers email based on the subscription ID of a job (Customers can have multiple Sub ID’s.

LOOKUP(LOOKUP([Subscription ID], "Subscriptions", "ID", "Customer ID"),
"Customers", "ID", "Email")

Cheers

2 Likes

I was recently actually using this exact method for something. But I wanted to cleanup some performance issues and found it was slightly faster to only use 1 lookup as a VC and then a single normal dereference. Went from 2.2 second sync to 1.7. Which I’m now realizing, I can probably remove the VCs too, and speed it up even more… let’s find out.

1 Like
LOOKUP(v, t, c, r)

is essentially a macro for:

ANY(SELECT(t[r], ([c] = v)))

so nested:

LOOKUP(LOOKUP(v, t1, c1, r1), t2, c2, r2))

is essentially:

ANY(SELECT(t2[r2], ([c2] = ANY(SELECT(t1[r1], ([c1] = v)))))

This expression examines each row in t1 for each row in t2. If each table has 100 rows, that’s 100 * 100 = 10,000 rows examined each time this expression is evaluated.

If the inner LOOKUP() is instead a virtual column (which is computed at sync time), the expression is:

LOOKUP([_THISROW].[v], t2, c2, r2)

which is equivalent to

ANY(SELECT(t2[r2], ([c2] = [_THISROW].[v]])))

This expression only examines each of the 100 rows in t2 once each time the expression is evaluated.

10 Likes

What an awesome explanation @Steve. Thank you.

What I’ve seen is: Even though we can speed up sync, we should not forget that expressions can slow down the App while working with it.
For example, if you use the Lookup() as an initial value in a form, every time you open the form, it has to calculate the Lookup(). On old phones the App will become veeeeeery slow.
That’s why using a VC to calculate the first Lookup() is a good solution.

4 Likes