Is there a trick to make this expression work?

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 Solved
0 37 1,266
1 ACCEPTED 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!

View solution in original post

37 REPLIES 37

LeventK
Participant V

@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]
	)
)

@Steve @LeventK

Correct! Here was the reasoning explained above:

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().

Steve
Participant V

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!

@LeventK @WillowMobileSystems

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.

gregdiana1
Participant V

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).

gregdiana1
Participant V

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!

gregdiana1
Participant V

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.

Top Labels in this Space