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.
Iโm not 100% sure about your table structureโฆ you have tables like Equipments, Equipments Status and Operator Directory, correct?
@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.
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.
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.
I did open and save the record. but it doesnโt reflect the status. however, the virtual status does it.
Is the formula exactly the same?
@Aleksi_Alkio yesโฆ exactly same
You can access our Application if you require further information
SNMEOperations-575724 Please we need your help in this regard
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.
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])))
+Steve Coile thanks. By using this formulaโฆ can I get the status?
@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.
+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.
@Aleksi_Alkio True, but it produces the result intended by the LOOKUP() formula that produced the error.
+Steve Coile correct, the first row and that is causing the wrong status.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |