Lookup table and populating a M:M resolver table question
I'm trying to create a simple form where the user sees their customers and for each customer, they can pick, via a set of check boxes one or more web sites from the links table.
** See attached database diagram below **
What I want to do is populate the CUSTOMER_LINKS table with foreign keys from both the Customers table and the Links table for each link the client picks from the links table itself. So, ideally, the user would see a popup of available links. Then, if you were on customer 1's record and the user picked three Web site links via the chekbox set, the CUSTOMER_LINKS table might look like:
LNK_PK LNK_CUSFK LNK_LNKFK
1 1 1
2 1 2
3 1 3
(Currently the LINKS table has three records.)
The problem is that FMP won't let me give the user a list of all the fields in the LINKS table and then add a record to the CUSTOMER_LINKS table for each link selected. I've tried using the option where you use the CLK_CLNKFK for the first field and then "display only" the LNK_LINK field, but then I get the message that there are <no records defined>.
I've also experimented with using a portal, but that didn't make the difference.
The database relationship looks correct to me.
I've made sure that the relationship graph allows you to add new records to the CUSTOMER_LINKS table from both directions (from CUSTOMERS and from LINKS), but not to the LINKS table (since the LINKS table is just a lookup).
The only thing that actually works, but isn't want i want, is when FMP stores the actual link text picked into the CUSTOMER_LINKS::CLK_LNKFK field. To do this, I just create a regular value list lookup (check box set) based on the links::lnk_website field from the CUSTOMERS layout.
This is the only way I seem to be able to see all three links as a pop-up via this relationship.
This is basically a M:M relationship since a customer can have many links and a link can be used by many customers.
Thanks for any ideas how to get FMP to store actual keys and be able to see all the records from the links table in a popup.
Do I need to use a script to manually extract the LNK_PK and then add a record (again, in the script) to the CUSTOMER_LINKS table or am I missing something easy here?