What you need is an extra table occurrence--not an extra table. Table Occurrences are the boxes you see in Manage | Database | relationships. Each such "box" refers to a table (or data source table) listed on the table tab. Filemaker confuses this a bit by referring to the table occurrence boxes as "tables" throughout the system and also in the help system.
To make a second table occurrence of contacts, you can click on the contact table occurrence to select it, then click the button with two green plus signs to make a duplicate table occurrence. You can rename this file and use different fields in its relationships.
This way you can have a "sold to" link to contacts and a "purchased from" link to the same contacts data-source table.
When adding fields and/or portals to your layout, just be careful to select the correct table occurrence you'll have what you need here.
Thanks for the quick response. I tried doing that earlier (I made two extra table occurrences of my Contact table and established a link from Contact ID in one table to the Purchased From field in the Machinery table and a link from Contact ID in the second table to the Sold To field in the Machinery table), however I was not able to create a portal on the page of each contact that would show me all the machines he has bought/sold. It says that the Machinery table is not related to the Contact table so no portal can be formed, but the Machinery table does have links to two different table occurrences of the Contact table.
Just to be clear, I have my Contact table (with fields such as Contact ID, Name, Address, Company, etc.) and the Machinery table (with fields such as Make, Model, Year, etc. also including fields Purchased From, Purchase Price/Date, Sold To, Sale Price/Date...) and I then created two table occurrences of Contact table (named contactSALE and contactPURCHASE) and I created a relationship between Contact ID in contactSALE to Sold To in Machinery table, and Contact ID in contactPURCHASE to Purchased From in Machinery table. Hence, there is no direct link between the original Contact table (upon which my Contact layout is based) and the Machinery table.
Please advise a suitable course of action to overcome this problem.
Thanks in advance.
First, with these table definitions, you won't be able to list bought and sold items in the same portal. You can list purchased machinery in one portal and sold machinery in another portal.
To make this work with two portals to machinery on your contacts layout, you use the same method, but make separate table occurrences of Machinery to link to contacts. Then You can set up a portal to the Sold machinery table occurrence and a portal to the Purchased machinery table occurrence.
So what I did is in addition to the new table occurrences of Contact table which I linked to the corresponding field in the Item table, I made two new table occurrences of Item table which then linked to corresponding field in Contact table. With this new setup I was able to create two portals in the Contact layout (one for Purchases and one for Sales) which displayed the necessary data.
Thank you very much for your help.
I would really appreciate it if you could help me with my latest quandary: http://forum-en.filemaker.com/t5/Using-FileMaker-Pro/Script-for-matching-data-in-one-layout-to-another/td-p/70749
Thanks and regards.