Match Offers/Sales/Purchases of each item to a customer
I am a relatively new user of FileMaker Pro 11 (on Mac OS X 10.6.2) and I am creating a database related to the printing industry. The database has two layouts namely Machinery and Contact. The main function of the database with which I am having trouble involves linking the purchase, or sale of a machine in the database to a contact in the database. For example, Machine X has been bought from Contact A and then sold to Contact B, so I want to create a relationship that allows me to see a table (portal) of machines bought and machines sold on each Contact layout (so on the Contact A layout I would see Machine X in the Machines Purchased From portal while on Contact B's page I would see Machine X in the Machines Sold To portal). Similarly, on the layout for each machine, I would have portals for Purchased From and Sold To details...and I would be able to enter purchase/sale details (i.e. contact, price, date) from the portal itself.
The issue I am having is that I can only make the needed relationship between one field (i.e. Purchase OR Sale info) and not both (so I can choose to either link the Purchased From field in the Machinery layout to the Contact ID field in the Contact layout OR I can link Sold To in Machinery to Contact ID in Contacts). Perhaps I need to create extra tables in order for me to create the two relationships needed?
It would be greatly appreciated if someone can help me overcome this dilemma.
Thanks in advance.