One primary key referencing two foreign keys in same table

[madslp]

Hi,

I'm new to both building databases and Filemaker, but am trying to set up a simple database for managing transactions. Currently I have two tables, 'Company' and 'Transaction'. To avoid duplicates I have tried to store information about both buyer and seller in the Company table, i.e. one primary key referencing two foreign keys in the same table. So I've used Company_ID as the primary key in the Company table and link this to two foreign keys (Buyer_ID and Seller_ID) in the Transaction table. This is where the problem arises. I am not able to access any information in the Company table from the Buyer_ID or Seller_ID. This only works if I use only one foreign key, but then I only get information about either buyer or seller. I have also tried to create a copy of the Company table, and then link from Company1 to Buyer_ID and Company2 to Seller_ID, but that didn't work for the layout showing details about each company (it would only show transactions where the company was buyer if associated with the original company table etc.). 

If my problem is at all comprehensible, any ideas on how to solve it?

 

Edited September 12, 2016 by madslp

[rwoods]

Hi there, thanks for entrusting your future success with your database to this community!

You need to have two occurrences of your 'Company' table in the graph. The transaction table then becomes a join table, effectively pulling together two companies into a single transaction.

Depending on the context of the layout that you then create, you will be able to access the transaction information, buyer details and seller details from any layout based on the 'Transactions' table. Layouts based on one or the other of the 'Customer' tables will be able to see all transactions that relate to them as a buyer, or as a seller.

In the image below, the two outer table occurrences are based on the 'Customers' table.

Does this give you enough info to take the next step?

 

 

Edited September 12, 2016 by rwoods

[comment]

If you want to define a relationship where a company can see all its transactions (i.e. the transactions where the company is the buyer OR the seller), define a calculation field (result is Text) in the Transactions table =

List ( BuyerID ; SellerID )

and use it as the matchfield opposite Companies::CompanyID.

 

What you have now would only show transaction where the company is both the buyer AND the seller.

[comment]

19 minutes ago, rwoods said:

Layouts based on one or the other of the 'Customer' tables will be able to see all transactions that relate to them as a buyer, or as a seller.

That is the weakness in your proposed solution. It necessitates switching the context in order to get each part of data - and does not allow user to see all the related data at once (which I think this question is about). And you would have to duplicate a lot of layout work. And what about the relationships of the Companies tables to other tables in the solution? They too would have to be duplicated in your approach.

The "idiomatic" solution to the described situation would look like this:

and there would be no layouts based on either the Seller or the Buyer TOs.

[madslp]

Thank you both for helping out! I think I agree with 'comment' that connecting only through a buyer and seller table will make it difficult to display all transactions for a company, both as seller and as buyer. It would also entail duplicating data, which I hope to avoid. 

I have therefore tried to use the solution with a calculated cCompanyID field in Transactions linking to Companies, as suggested by 'comment' and shown in the figure in the previous post. But there's something I'm not doing right. Say in a Company Detail layout, how could I get all transactions? Using the List(BuyerID;SellerID), I am currently only able to view transactions when the Company is buyer. 

[comment]

20 minutes ago, madslp said:

Say in a Company Detail layout, how could I get all transactions? Using the List(BuyerID;SellerID), I am currently only able to view transactions when the Company is buyer. 

See if the attached file works for you.

 

21 minutes ago, madslp said:

It would also entail duplicating data, which I hope to avoid. 

No, it wouldn't. Buyers and Sellers (in both suggestions) are occurrences of the same table and look at the same data.

Transactions.fp7

[madslp]

That clarified a great deal! Very helpful to access a file. 

Sorry, I hadn't understood that the Buyer and Seller were copies of the Company table (in both solutions).

Thanks again!