Why My Lookup Function is not working? Having...

(Sarens Nass Middle East) #1

Why My Lookup Function is not working? Having Two Tables -

“Assigned Operators” having Emp Id, Short Name, Status “Test Data” having Emp Id, Short Name, Status.

Assigned Operators is my transaction table. If i add one row in “Assigned Operators”, the status should be reflected in “Test Data”. I used this formula =ANY(SELECT(Assigned Operators [Status], [Short Name] =[_THISROW].[Short Name] ))

And tried with LOOKUP formula. =LOOKUP([_THISROW].[Short Name], “Assigned Operators”, “Short Name”, “Status”)

But, if i have virtual column for “Status” in Test Data, its working.

Please help me out with this.

(Sarens Nass Middle East) #2

@Aleksi_Alkio Yes Mr. Aleksi… The following hints will be helpful when you check the problem. Two Tables - Operator Directory and Assigned Operators In application, please click “Status” button,

choose any code in “Available” category, then select “Working” then you need to provide “Client”, “Certification type”, . and “Till date”, then click “New” in assigned operator. system will show the competent operators, choose any one name then save it. Now if you see in “Assigned Operators” table, the new row is added. If you see in “Operator Directory” table, the particular operator “Status” column doesn’t change.

Kindly check the problem. Thanks lot for your kind support.

(Aleksi Alkio) #3

It doesn’t recalculate the status if you don’t open the record from the Test Data table. And if you want to recalculate all records, you should open all those records.

(Sarens Nass Middle East) #4

@Aleksi_Alkio I did open and save the records. Even i tried with new row.

(Sarens Nass Middle East) #5

Sorry… I noticed the status change in “Assigned Operators” only… not in “Operator Directory” :frowning:

(Aleksi Alkio) #6

You won’t see it because I didn’t open/save that record from “Operator Directory”. Let me try to explain how the app formula works… If you want to read any value from another table (like this status), it won’t update the normal column’s value even if it has an app formula. If you want to update the value, you need to read it with the virtual column or with the spreadsheet formula.

(Aleksi Alkio) #7

Would you like me to open your app and check the reason?

(Sarens Nass Middle East) #8

@Aleksi_Alkio With pleasure… SNMEOperations-575724. But i changed the concept. Still you can check… instead of test data… please use “Operator Directory”

(Sarens Nass Middle East) #9

Mr. Aleksi, If you want, i will go back and set the things. so that you can check the reason

(Aleksi Alkio) #10

Yes please

(Aleksi Alkio) #11

I choosed > 1362 > then Sargunam > 93043. It won’t change the “Status” value because I didn’t open the record from “Operator Directory”.

If I open it, I’m able to see the “Status” and if I would save that record, I would see it in the detail view.

(Sarens Nass Middle East) #12

@Aleksi_Alkio Oh… really… i am sorry to say that i tried now. i dont see the changes in status. can you please add one record from your side. So that i can see the changes in Operator Directory in excel sheet.

(Sarens Nass Middle East) #13

@Aleksi_Alkio Yes. Mr. Aleksi… I noticed the status. I am wondering why i could not see the data in excel sheet?.. it means…even this normal column with appformula, i cant set the value manually you mean?