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!

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.

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, 201213 yr by comment

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.