Control logic for filtering records by user

[V8S]

A small number of international remote workers require access to specific customer records that they should work on. Each worker has a code number and customer records are tagged with their code number accordingly.

For years I have used a complex system of restricted privileges and scripting to hide the thousands of other records from these users. User experience is VERY slow.

As suggested on one of the Filemaker forums, I am trying to implement a new system using a CONTROL table of customer IDs linked via a relationship to the CUSTOMERS table.

CONTROL TABLE

CustomerID	Control_Code
123	UserA

CUSTOMERS TABLE

CustomerID	Control_Code	Name	Address
123	UserA	Mr Joe Bloggs	456 Main Street

 

A layout based on the CONTROL table shows fields from CUSTOMERS. The user can navigate their customer records without the performance overhead of loading thousands of customer records they shouldn't access. This is much faster.

When UserA logs in, a global list variable sets which code(s) they can access.

User	$$AccessibleControlCodes
UserA	X Y
UserB	Z

UserA should see only records in the CONTROL table records with a Control_Code of either "X" or "Y", but not "Z".

A privilege restriction on CONTROL::Control_Code works. If I show all records, UserA can see X and Y records. Z records show "No Access".

• not IsEmpty(FilterValues ( Control_Code ; $$AccessibleControlCodes ))

It would be annoying for UserA to have to flip through dozens of "No Access" records, so I still need to limit the visible records with a Find.

Sadly, Find does not work at all on the layout. Doing any Find on any field always returns no results. Even though I am logged in as UserA and can see the X and Y records, trying to do a find of those same records doesn't work.

I imagine this is a product of the privilege restriction but I can't figure out another route to take.

I feel it would be inelegant to build separate CONTROL tables for each user. This would probably work, but I have multiple portal relationships for each control table and would need multiple scripts, multiple layouts, etc If we added remote workers, it would be a nightmare.

Ideas please?

 

 

 

[MaxB]

If you haven't found a solution yet:
I don't see, why find wouldn't work, maybe something is wrong with your relationship between Control and Customers. You say, find doesn't work on any field - can you at least search by fields of the Control table? Can you find records as admin user? Is the privilege restriction defined for the Control table or the Customer table or both?