You could make the fields for the 6 values part of a join-table, removing them from the parent table and making them all choices in one field of the join-table, as links between the other two tables.
They could be used in one portal for establishing the links - creating the child records for these types of "persons", and have a separate portal for listing all transactions (grandchild-records) that are linked via the child records (buyers/sellers).
You end up with 3 tables, but you remove all of the 6 fields from the parent table into a sinlge related field for records of byers/sellers as the Child records, with transactions as the grandchild records.
I think i have an idea of what you are describing so i put together a test file. Are you saying i need a portal to the join table for each field? I've attached my test file so you can see where i was going with it.
test.fmp12.zip 11.9 K
You've got the 3-table idea, but the relationship and fields aren't ready yet.
You parent table should be the Company.
Your child table should be the buyers/sellers table. Should have a field for name, buyer-seller-type, Company_ForeignKey and it's own PrimaryKey field.
Your grandchild table is Transactions. It should have your transaction details and a ForeignKey field for the buyers/sellers table.
company and buyers/sellers link: Company::_pk_CoID = Buyers_Sellers::_fk_CoID
buyers/sellers link to transaction: Buyers_Sellers::_pl_BuyerSeller = Transaction::_fk_BuyerSeller
This will provide a direct link via portals to both buyers/sellers from company, and a link for the transactions from company (through the buyers/sellers table).
Why would the Company need to be the parent table?
The reason i ask is in this particular database the Transactions table is always the main table that the client does their data entry into, thus allowing them to select the company as the buyer / seller / broker.
I've edited my test database and it doesnt seem to be making sense, how do i select the company from the transactions screen?
test.fmp12.zip 12.3 K
Disregard, I understand now and have it working. Thanks for your help Stephen
Actually i have hit another snag trying to implement this. Currenly each Buyer and Seller field is a drop down field tied to a specific value list like so:
Buyer 1 and 2 = companies with a type of Investor
Buyer Broker = companies with a type of Arranger
Seller 1 and 2 = companies with a type of Investor
Seller Broker = companies with a type of Arranger
By setting up a join table with a company name field, i lose the ability to have these separate value lists as i can only specify one list. Any suggestions on how to work around this? I can provide a sample file for more clarification if needed.
You could move all of these values into another table (Buyer_Class), add a field for the Type, and a field for the Type in the Buyer/Seller table, which, once entered would limit the values from the Buyer_Class field to be displayed.