Slicing work orders based on "Assigned To:" column

[Wallace_Service]

I’m building a work order app that is working well so far. One of the features I’d like to incorporate is for each app user to only see work orders assigned to them. Right now we have an “Assigned To” column in our service ticket table. How would we incorporate behavior such that the “Assigned To” user only sees their tickets? Thank you in advance for any suggestions!

[LeventK]

@Wallace_Service
There are several ways to accomplish this. For example:
If you have a Users table in your app containing the user’s email address, than you can create a Security Filter with an expression like:

LOOKUP(USEREMAIL(),"Users","Email","_ComputedName") = [Assigned To]

[Wallace_Service]

Thanks Levent, I appreciate the feedback. Where does the “_ComputedName” portion of the expression come from?

[LeventK]

@Wallace_Service
It’s just an example. I’m not aware about the value of your [Assigned To] column and how you populate data for this column. The syntax for LOOKUP is like this:

LOOKUP("SearchString","TableName","LookupColumnName","ReturnColumnName")

You can fine-tune the expression as per your structure.

[Wallace_Service]

Hi Levent, so I’m trying to work through this. Right now the Assigned to is in a Service Tickets table.

I’m pulling the employee email from a Parts table.

Do I need to have the employee email in the Service Tickets table to make this work?

I don’t totally understand how the “LookupColumnName” and “ReturnColumnName” work in the context of two separate tables.

Thanks!

[LeventK]

Do you have a relationship between Parts and Service Tickets table? If not, do you have any column in common between these 2 tables?

[Wallace_Service]

Hi Levent, I have 3 columns in the Service Tickets table that reference the Parts table.

First part used
Second part used
Third part used

[Phil]

Can I ask why you have three columns in the Service Tickets table that reference the Parts table?

Typically you would have a third table that keeps track of which parts are used for each Service Ticket. You might call this table “ServiceTicketParts”. The “ServiceTicketParts” table would contain one row for each Part used for each Service Ticket.

For example, if a Service Ticket used one part, the “ServiceTicketParts” table would contain one row. If another Service Ticket used five parts, the “ServiceTicketParts” table would contain five rows.

The “ServiceTicketParts” table would contain two fields. One is a Ref to the Service Ticket table. The other is a Ref to the Parts table. You might also include a count field in the table if you want to track how many parts of a particular type the Service Ticket consumed.

[Wallace_Service]

I reread all of this and realized I confused things. I have a Service Tickets table and and Employee table (Parts doesn’t matter here). They are reference and the Employee table contains an employee name and employee email address. The Assigned to of the Service Tickets table contains the name of the Employee table, employee name column. I want each row on the Employee table to only be able to see service tickets Assigned To themselves.

[Wallace_Service · Accepted solution]

Thanks for the help all, I ended up getting this working by changing the value of Assigned To in the Service Tickets table to the user email instead of the name. Then I was able to set a security filter of [Assigned to]=USEREMAIL(). Seems to work. I do have another related question. We also have a Customers table that shows a list of all customers. Based on the user email I would also like a user to only be able to see the customer files they’ve used in the past. Say for a user in Ontario, Canada, we wouldn’t necessarily want them to see customer files for Manitoba, Canada. Is there a way to tie the same security filter we are using in the Service Tickets table to the Customers table? They do have referenced rows between them. Thanks in advance!