Double Dereference workaround without virtual colu...

Jessy_Mrozowski · 02-04-2020 08:55 AM

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

Bahbus

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.

Steve

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.

Fabian_Weller

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.

Jean-Charles_FO

Hello,
I block on LOOKUP (v, t, c, r) because I have an r depending on a ref column ([tarif]) in the input screen. the ref column gives the name of the column to look for in the table but unfortunately there was an error when entering the formula

(LOOKUP([_THISROW].[diametre],“dmm”,“diametre”,"[tarif]"))

Unable to find column ‘[tarif]’, did you mean ‘tarif’?

Steve

You’re using LOOKUP() wrong.

Jean-Charles_FO

So it is not possible to look for a value in an array whose return column depends on an entered value? where what function should I use?

Steve

The best method I’m aware of is to use SWITCH(), like this:

SWITCH(
  [tarif],
  "a", LOOKUP([_THISROW].[diametre],“dmm”,“diametre”,"a"),
  "b", LOOKUP([_THISROW].[diametre],“dmm”,“diametre”,"b"),
  "c", LOOKUP([_THISROW].[diametre],“dmm”,“diametre”,"c"),
  ...,
  ""
)

Not very pretty, I know.

Double Dereference workaround without virtual columns