De-refs inside a lookup don't work. For inst...

(Multi Tech Visions) #1

De-refs inside a lookup don’t work.

For instance:

LOOKUP([Column2].[Column5], Table1, Column1, Column3)

I’m looking up a record from table 1, while editing a different record on table 1 - basically I’m trying to look up a value from a previous record.

But the lookup isn’t accepting the deref.

The de-ref works, if it’s by it’self.

But if you try and use it in the lookup, it doesn’t work.

I didn’t save any version with this setup, this is a bug I’m familiar with and the work around is to import the de-ref into a column and then use that new column in the lookup.

But this means I have to have an additional column in my table.

Could the de-ref work in the lookup?

(Multi Tech Visions) #2 - Image 2018-11-07 at 10.24.34 AM.png Here’s the de-ref working…

But this is only importing the KEY for the row that I need to grab information from… So I need to take the output from this, and use it in a lookup to get another value from that row. Image 2018-11-07 at 10.24.34 AM.png

(Multi Tech Visions) #3 - Image 2018-11-07 at 10.25.39 AM.png Here’s what happens when you try that.

The De-Ref doesn’t work.

I’ve had issues with this before, it’s a matter of context/perspective (from the app’s point of view).

When doing a lookup, you’re in the context of that table - so if you need to get something from the table that the lookup is running on, you have to use [_thisrow] to tell the system “okay, let’s leave the context of the table we’re looking up from and go back to the table where we’re editing/creating a row… what did they input there”

So the problem here is that when we’re in the lookup, we can’t then do something that’s another lookup-like function: aka a De-Ref.

But could they? Image 2018-11-07 at 10.25.39 AM.png

(Multi Tech Visions) #4

Some info for repro:

App: StudyTracker-96620-629466 Version: 1.001172 Table: Answer_Data


lookup([Measure_Item_Link].[Piping_Variable_To_Import], Measure_Items, Variable_Name, Item_Contents)

(Aleksi Alkio) #5

The reason is because we can’t read dereferences over one table. If you write the LOOKUP with ANY, your case would be like… ANY(SELECT(Table1[Column3],[Column1]=[_THISROW].[Column2].[Column5])) and for this reason, it would not work either.

(Multi Tech Visions) #6

But could they… :thinking::thinking::face_with_raised_eyebrow::face_with_raised_eyebrow:

(Aleksi Alkio) #7

You can always request :wink:

(Praveen Seshadri (AppSheet)) #8

@MultiTech_Visions, not only could they, they should! This is a silly excuse, but the reason they don’t is because the expression parser doesn’t handle them. The parser reads the expression and converts its into an internal structure that is then evaluated. The evaluation logic can handle it easily. The parser however only accepts syntax that works in Excel formulas. Actually, it is code used to parse Excel formulas. That supports [a].[b]

but it doens’t support [a].[b].[c]

— so that’s why we haven’t every supported this.