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

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

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? :slight_smile:

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

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

1 Like

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?

@Steve @LeventK

Correct! Here was the reasoning explained above:

1 Like

Oh. Yikes! Yeah, that’s gonna be heavy no matter how you slice it, I’d think…

Thinking…

1 Like

Exactly! That’s why using the [Related Assigned Technicians] would nice because it already has the rows being selected by the inner SELECT().

1 Like

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.

2 Likes

@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…

1 Like

Right! And because I think they use C# on the back-end for UI, I know for a fact it performs short-circuit evaluation.

1 Like