Solved: Is there a trick to make this expression work? - Page 2

WillowMobileSys · 01-16-2020 11:03 AM

So I can do this to get a list of Employee ID’s that are attached to the current record:

[Related Assigned Technicians][Employee ID]

I can do this to verify that the specified Employee ID is in the list above:

IN("N4dCeqZo", [Related Assigned Technicians][Employee ID])

However, if I do the blow, the result is empty and I assume it has to do with a clash in the [Employee ID] column name. Is there some trick I can apply to make it work?

And yes I do know that I can take out the IN clause and replace with something like [Key] = [_THISROW].[Key] to make this expression work, but this is not the final expression I am aiming for.

I am just curious, before I find an alternate expression, if there is something simple that makes the below expression work.

 SELECT(Assigned Technicians[Employee ID], 
        IN([Employee ID], [Related Assigned Technicians][Employee ID]))

WillowMobileSys

@Gregory_Diana Your post inspired me to find a solution that, honestly, was right in front of me all along and I should have know better.

The main issue with the original expression I posted was a scoping and context rule issue. AppSheet isn’t identifying that usage of [Employee ID] like this [Related Assigned Technicians][Employee ID] is different than in the rest of the expression. The system should make that distinction based on context.

Your suggestion of using LIST(), while it doesn’t quite work, inspired me to try SELECT(), which didn’t work either. But then it dawned on me, I can use a Virtual Column for the [Related Assigned Technicians][Employee ID] part of the expression. This separates the ambiguity of using [Employee ID].

So…the solution is this:

Create Virtual Column, let’s just call it “Test” and set the App Formula = [Related Assigned Technicians][Employee ID]

Then in my original expression I can simply do this:

SELECT(Assigned Technicians[Assigned ID], 
        IN([Employee ID], [Test]))

Viola! I now have a more efficient expression.

Thanks for your reply. I had already punted and gone back to the inefficient solution. Now I can improve it!

View solution in original post