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
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.
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.
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.
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โ?
Youโre using LOOKUP() wrong.
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?
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.
See also:
thanks just 20 columns