giving access to multiple users to one record

[agiuliano8]

Basically, my database solution is a lead management system, designed to help my sales representatives manage and keep details on leads that my company receives. I don't want my sales representatives to have access to LEADS that they are not assigned to. My solution to this was to have a field in the LEADS table that contains a foreign key from a record in my SALESREPS table. Each LEAD has one SALESREP assigned, and each SALESREP has many LEADS that they are assigned to. My solution works just fine, but I have been trying to figure out the best way to assign a second SALESREP to a LEAD record.

Would you suggest adding a second field to the LEADS table containing a second foreign key from the SALESREPS table, or is there a way to do this using records in a join table between LEADS and SALESREPS? The first solution limits the number of sales reps I can assign to a record in the LEADS table (without creating a third and a fourth foreign key field), and the second solution would enable me to assign as many sales representatives to a LEAD as I need.

The second solution is preferable, but I wouldn't know where to start with the Filemaker security options to make this work as far as SALESREPS access is concerned. Any input would be appreciated. Thanks!

[bcooney]

Do the join table. Use the List ( ) function to gather the "allowed" SaleRepIDs and use patterncount ( list (salesrepIDs) ) as your RLA rule. Also, you'd be best to use tight nav scripts so that they are only taken to "their" leads.

[comment]

IMHO, a join table is not necessary for this* and you can do with a checkbox field using a value list of SalesRepIDs. This will contain a return-separated list of authorized IDs - same as List ( JoinTable::SalesRepID ) would.

In any case, do not use PatternCount() to determine the access, because then authorizing SalesRepID #1234 will also authorize #123, #12 and #1. Use the FilterValues() function instead, e.g.

not IsEmpty ( FilterValues ( $$myID ; Leads::SalesRepIDs ) )

---

(*) If you ever intend to produce a report of leads by sales representatives, you will need the join table, so you might just as well have it from the beginning.

Edited February 20, 2012 by comment

[bcooney]

Good catch, comment, thanks. I wrap patterncount with pilcrows. Of course, we could again debate join tables vs. multi-keys, but we've done that to death.