5 Replies Latest reply on Jun 23, 2010 9:50 AM by m10muhammad

    Match Offers/Sales/Purchases of each item to a customer

    m10muhammad

      Title

      Match Offers/Sales/Purchases of each item to a customer

      Post

      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.

       

      Muhammad.

        • 1. Re: Match Offers/Sales/Purchases of each item to a customer
          philmodjunk

          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.

          • 2. Re: Match Offers/Sales/Purchases of each item to a customer
            m10muhammad

            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.

             

            Muhammad.

            • 3. Re: Match Offers/Sales/Purchases of each item to a customer
              philmodjunk

              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.

               

              Make sense?

              • 4. Re: Match Offers/Sales/Purchases of each item to a customer
                m10muhammad

                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.