I am building a field service app which allow...

[Abdemanaf_Dhari]

I am building a field service app which allows a particular sales agent to only view his/her customers based on a security filter. Each customer has an account number. Sometimes, a Sales Agent may encounter a customer from a different region and would want to record that customer’s order, however, due to the security filter, he cannot view that customer account number while making a new order. The sales agent can still process the new order, but he doesn’t know that the customer belongs to another Sales Agent.

Is it possible to check if that customer’s account number exists in the spreadsheet and alerts the sales agent that this customer belongs to another sales agent?

I tried using a Virtual Column that has an IF App Formula. However, the formula only looked at the filtered data from the security filter, and not at the entire spreadsheet.

How would I structure the formula so that it checks the entire spreadsheet for that account number?

[Aleksi_Alkio]

When you have a security filter already, you can’t read it. You could create a separate table in your spreadsheet with just 2…3 columns and then you could check the status from that table. You would not see any details but you could get the info for your warning. You could use for example QUERY expression (if you are using gSheet).