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?