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]))
Solved! Go to Solution.
@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!
@WillowMobileSystems
John,
Why donโt you use below expression or below expression doesnโt work as expected?
SELECT(
Assigned Technicians[Employee ID],
IN(
[_THISROW].[Employee ID],[Related Assigned Technicians][Employee ID]
)
)
I didnโt think of trying this. I will. But wouldnโt [_THISROW] refer to the record outside of the SELECT? That record doesnโt have an [Employee ID] column.
Just so it makes sense, what Iโm trying to do is identify Appointment clashes for scheduling. For the Technicians assigned on the current Work Order record (i.e. [Related Assigned Technicians]), I want to find all of the OTHER appointments they are assigned to and then check if those appointment times clash. Did I just make explanation worse?
I had this all working when selecting a single Technician. But now I want to assign multiple techs and using a Child table. Trying to shore up all of the related expressions.
@LeventK I tried with [_THISROW] and as expected it failed looking for [Employee ID] on the Work Order record, which is no longer there.
John, itโs hard to say what could be wrong without knowing your table schema really.
I belive I couldnโt be able to understand this part actually. May be @Steve can propose something better.
Yeah I know. I was hoping there was some simple trick to prevent ambiguity in the [Employee ID] column when using a REF_ROWS() list - [Related Assigned Technicians]
.
This works:
SELECT(
Assigned Technicians[Employee ID],
IN([Employee ID], SELECT(Assigned Technicians[Employee ID], [Order #] = [_THISROW].[Order #])
), true
)
But over time, as the Assigned Technicians table grows, itโll become slower.
[Related Assigned Technicians]
already has the desired rows so using it directly would theoretically cut out half of the table searching time required by the two SELECT()'s above.
John,
I believe the problem is the source table being the same table where the related records VC is. I havenโt tested it or donโt know if it may work this way but, can you try with this:
SELECT(
Assigned Technicians[Employee ID],
IN(
[_THISROW].[Related Assigned Technicians],[Employee ID]
)
)
No dice. IN() requires second parameter to be a List.
How about this way?
SELECT(
Assigned Technicians[Employee ID],
IN(
[_THISROW].[Related Assigned Technicians],Assigned Technicians[Employee ID]
)
)
Oh. Yikes! Yeah, thatโs gonna be heavy no matter how you slice it, Iโd thinkโฆ
Thinkingโฆ
Exactly! Thatโs why using the [Related Assigned Technicians] would nice because it already has the rows being selected by the inner SELECT().
Does the Assigned Technicians table have its own Related Assigned Technicians column?
@Steve
I believe itโs like that as far as I have understood from Johnโs explanations
No. [Related Assigned Technicians] is a REF_ROWS() column found on the Work Order record that the expression is operating within.
Sorry for the vagueness. I was trying to be super simple. I was simply hoping somebody had a trick to make the original expression work. I wasnโt trying to solve a query problem but rather a syntax problem. Alas, I donโt think itโs possible.
Iโll try another approach.
The expression below works, can it be simplified into a single SELECT()?? @Steve
SELECT(Assigned Technicians[Employee ID],
AND(
IN([Employee ID], SELECT(Assigned Technicians[Employee ID],
[Order #] = [_THISROW].[Order #]
)
),
[Order #] <> [_THISROW].[Order #]
)
)
This appears to be gathering a list of employees attached to this work order that are also attached to other work orders?
At the very least, put [Order #] <> [_THISROW].[Order #]
before IN() to skip the SELECT() altogether if this isnโt the work order you care about.
@Steve
Shall I understand from this that AND expression does not process its 2nd parameter provided the 1st one evaluated to FALSE as there is no need to check for it as the AND already will eval to FALSE?
I assume so. Most languages will behave that way. I donโt know that Iโve tested it specifically. Lemme askโฆ
Right! And because I think they use C# on the back-end for UI, I know for a fact it performs short-circuit evaluation.
Thatโs an awesome approach and information @Steve, thnx for letting us know bud!
My assumption was wrong: AppSheet does not perform short-circuit evaluation. Boo!
How did you arrive at that? It would be determined by the underlying code that ultimately executes the expressions. Right? It is my understanding they are using C# which I KNOW does short circuiting. Am I wrong in that understanding?
I asked a developer, who then reviewed the code.
Thnx for the info @Steve and I agree with youโฆBooo!
How about:
INTERSECT(
SELECT(
Assigned Technicians[Employee ID],
[Order #] = [_THISROW].[Order #]
),
SELECT(
Assigned Technicians[Employee ID],
[Order #] <> [_THISROW].[Order #]
)
)
O(2n) vs O(n^2)
Wait!! What?? Thereโs an INTERSECT() function?
This is totally wicked awesome!!
That just might be the ticket AND I may even be able to use the [Related Assigned Technicians] list. Wahoo!!
I saw that after your post.
Is there by chance, just a page with nothing but a list of function names on it - a glossary? Half the battle is sometimes just knowing whatโs available. I have yet to come across such a reference.
Sortaโฆ
Doesnโt include everything, but is as complete as we have outside of Expression Assistant itself.
I wonder who we could ask to create such as thing?
As it turns out, the INTERSECT() doesnโt work in the end. I was excited as I thought I could apply it.
I really need an EXCLUSION() function - rows in List 2 excluding rows from list 1. But the issue always comes down to selecting the rows in each List 1 and List 2 based on the [Employee ID] list found on the current record.
Since I canโt use my Child list of [Related Assigned Technicians] for the list Employee IDโs already chosen INSIDE of a SELECT(), I see no way around using a SELECT() within a SELECT(). I have that solution so will just have to use it and move.
After thinking more on this, I believe the initial problem expression I posted should work. And I believe that it doesnโt due to a flaw in the context rules. The rules need to be updated to recognize that [Column Name]
within the expression scope is different than [Ref rows list][Column Name]
- just as is done in dot notation [Ref Column].[Column Name]
.
[list 2] - [list 1]
would give you that.
Ah yes!! I need to remember that for future ref.
Hi John,
I am coming into the back end of this and maybe can add.
The original expression you had was
SELECT(Assigned Technicians[Employee ID],
IN([Employee ID], [Related Assigned Technicians][Employee ID]))
Here the IN() will return either true or false which results in
SELECT(Assigned Technicians[Employee ID], TRUE)
This will or should simply return the entire table of all distinct Assigned Technicians[Employee ID] and if FALSE return all the entire table of non-distinct (everything).
However, the SELECT has no idea of which Employee ID is being referred to. Also
IN(โN4dCeqZoโ, [Related Assigned Technicians][Employee ID])
works as โN4dCeqZoโ is a single defined value and whilst it correctly identifies the correct ID it returns only TRUE so that the select will return all DISTINCT values for the entire table and not just that for N4dCeqZo.
I am not sure that the IN() function can use a list as the VALUE to be found and which is causing the IN() to fail and hence the select to fail and return NOTHING.
So it appears you are wishing to first find and confirm whether any employee ID exists within Assigned Technicians and then use this same ID to then select that same Employee ID from the Assigned Technicians.
What I understand you are basically looking for is as shown below
SELECT(Assigned Technicians[Employee ID], โN4dCeqZoโ, FALSE)
So this would return all Assigned Technicians with that Employee ID = โN4dCeqZoโ
So you need to be able to return a list of all confirmed Related Assigned Technicians Employee IDโs and use these to SELECT these IDโs from the Assigned Technicians.
INTERSECT(Assigned Technicans[Employee ID], Related Assigned Technicians[Employee ID])
will return a list of all common IDโs which I understand is what you require
So INTERSECT(Assigned Technicians[Employee ID], List(โN4dCeqZoโ)
should do the same as
SELECT(Assigned Technicians[Employee ID], โN4dCeqZoโ, FALSE).
Also try
SELECT(Assigned Technicians[Employee ID],
IN(LIST([Employee ID]), LIST([Related Assigned Technicians][Employee ID])))
This might return a Y/N for each row of the IN()
IN() requires two arguments which are both of TYPE LIST.
@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!
Hi John,
Great work. I have found APPSHEET to be very sticky on TYPES and suffers from the ambuigities you noted. Sometimes and expression which works well standalone must be reworked when incorporated into other expressions, mainly due to TYPES.
As such I have learned to use a step by step incremental approach to expression dvelopment which requires a bit of head bashing.
User | Count |
---|---|
41 | |
31 | |
31 | |
16 | |
14 |