Cannot compare List with Number in ([Assigned...

[Sarens_Nass_Mid]

Cannot compare List with Number in ([Assigned Operators] = [Emp Id].[Emp Id]) - Any alternative solutions for this please??

Dear Experts, We have developed an application where

we can change the equipment status “Working”, “Available”, “Standby” etc., When the status is “Working”, we may assign more than one operator for that particular equipment.

I have Equipment Status Table, where we can add an equipment first time, then just change the status.

I have Operator Directory, where I want to connect the equipment status as Operator Status. So that i can bring both Equipment and Operator Status. The problem is If i create a virtual column “Status” in operator table by using this below formula… =LOOKUP([_THISROW].[Emp Id], Equipment Status, Assigned Operators, Status) … i get below error message.

Cannot compare List with Number in ([Assigned Operators] = [Emp Id].[Emp Id]). How do i take the assigned operators to operator Directory.

Please help us out from this big obstacle as we almost there to complete this project. Our Management is welcomed this Concept.

[Aleksi_Alkio]

I’m not 100% sure about your table structure… you have tables like Equipments, Equipments Status and Operator Directory, correct?

[Sarens_Nass_Mid]

@Aleksi_Alkio Yes… Equipment directory and Operator Directory I have Equipment Directory Table, “Status”, “Assigned Operators”, “Till Date” are columns.

The Assigned Operators values are taken from “Operator Directory” as Enumlist (in order to select more than one Operator)

Now, in my Operator Directory, I want to link the equipment status as Operator Status. So that i can bring both Equipment and Operator Status.

[Sarens_Nass_Mid]

Somehow I managed to assign multiple operators by having a separate table called “Assigned operators”. In this table “Equipment Code”, “Emp Id” and “Status”. Now I want to lookup this status and update in Operator Directory by Emp Id. The problem is i got the correct status by having virtual column and below formula. =LOOKUP([_THISROW].[Emp Id], Assigned Operators, Emp Id, Status)

But in some cases, i have to change operator status straightaway from Operator Directory. So i need to have a physical column of status, not a virtual column. So i created a physical column for status and update the same formula. BUT ITS NOT UPDATE THE STATUS.

What is wrong here? Please advise.

[Aleksi_Alkio]

When you use a normal column for this purpose, the status doesn’t change if you don’t open and save the record. That’s the main difference between normal and virtual column.

[Sarens_Nass_Mid]

I did open and save the record. but it doesn’t reflect the status. however, the virtual status does it.

[Aleksi_Alkio]

Is the formula exactly the same?

[Sarens_Nass_Mid]

@Aleksi_Alkio yes… exactly same

[Sarens_Nass_Mid]

You can access our Application if you require further information

SNMEOperations-575724 Please we need your help in this regard

[Aleksi_Alkio]

I believe in your case you can’t read the status with the EnumList field. You could evaluate the “Emp ID” with the list, but it won’t give you correct result because it’s possible the assigned operator is chosed more than once. You should find a relation between these two tables so you could identify the status. The reason is not the formula itself, it’s the column structure.

[Steven_Coile]

Because [Assigned Operators] is an EnumList, you need to use the IN() function to see if [Emp Id] occurs in it. You cannot use LOOKUP() to do that; you’ll need to use ANY(SELECT()) instead.

Change this:

=LOOKUP([_THISROW].[Emp Id], Equipment Status, Assigned Operators, Status)

to this:

=ANY(SELECT(Equipment Status[Status], IN([_THISROW].[Emp Id], [Assigned Operators])))

[Sarens_Nass_Mid]

+Steve Coile thanks. By using this formula… can I get the status?

[Steven_Coile]

@Sarens_Nass_Middle_E Yes, the formula will return the content of the [Status] column of the first row found in the Equipment Status table in which [EmpId] occurs in the row’s [Assigned Operators] list.

[Aleksi_Alkio]

+Steve Coile This formula will give an answer, but it won’t work in this case because there are more records than just one where the expression evaluation is true. It will give a “status”, but the status is not the correct one.

[Steven_Coile]

@Aleksi_Alkio True, but it produces the result intended by the LOOKUP() formula that produced the error.

[Aleksi_Alkio]

+Steve Coile correct, the first row and that is causing the wrong status.