Jump to content

One primary key referencing two foreign keys in same table


madslp

This topic is 2779 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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?

 

Skjermbilde 2016-09-12 kl. 16.00.35.png

Edited by madslp
Link to comment
Share on other sites

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?

 

 

Screen Shot 2016-09-12 at 15.03.31.png

Edited by rwoods
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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:

rg.png

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

Link to comment
Share on other sites

  • Newbies

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. 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2779 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.