3 Replies Latest reply on Apr 18, 2017 5:00 AM by wimdecorte

    Auto enter Primary key of parent table in child table filemaker

    muhammad.ikram@contedia.com

      Hello.

       

      I have 2 tables.

       

      CustomersQB - This table contain data retrieved from Quickbooks using ODBC.

      Customers: This is filmmaker table also contain customers data but all the fields in this table use "LOOK UP" from "CustomersQB" table. The reason I have done it this way so I can add global keys. I cannot add globel keys in ODBC connected CustomersQB table.

       

      So my layouts are based on Customers table. And because I'm using look up feature, I can see the actual quickbooks data and do all the filtering.

       

      My problem is that If a new customer is added in Quickbooks, that will also appear in CustomersQB table. Currently, I copy the key of newly added customer and paste in Customers table so with the relationship and loop up, I can get all the information about customers.

       

      It's done manually. I was looking to do it automatically. So when new customer is added in customerQB table, the key of that customer also gets entered in Customer table.

       

      What would be the best way to achieve this. So keys of parents table are auto entered in child table.

       

      Many Thanks.

        • 1. Re: Auto enter Primary key of parent table in child table filemaker
          wimdecorte

          Sounds like you are retrieving the ODBC data in a script?

           

          That script would then need to find the new records, create a list of their IDs, go to the Customers table and loop through that list of IDs, create  a new record for each one and set the ID in the field that will force the lookup.

           

          But... I have some doubts about the ODBC data, sounded from part of your description that you are using ESS and not an ODBC import?  Can you let us know which one it is?

           

          If you are using ESS then you don't have a good notification of what records are new in QB.  You'd have to create a server-side schedule to do that evaluation for you and then build that list of IDs and create the new records in Customers.

          • 2. Re: Auto enter Primary key of parent table in child table filemaker
            muhammad.ikram@contedia.com

            I'm not using script to retrieve data from Quickbooks.

             

            I'm using PHP and MYSQL to retrieve the data.

             

            So i have PHP which connect to quickbooks and gets the data and insert in MYSQL. Than using ODBC , I'm linking MYSQL and FileMaker.

             

            So FileMaker is using Mysql tables, which contain data from quickbooks.

             

            What I wanted is whenever new customer is added in CustomerQB table, the primary key of that customer also to be entered in customer table. 

            • 3. Re: Auto enter Primary key of parent table in child table filemaker
              wimdecorte

              muhammad.ikram@contedia.com wrote:

               

               

              What I wanted is whenever new customer is added in CustomerQB table, the primary key of that customer also to be entered in customer table.

               

              Ok, so you are using ESS.

               

              What you want to have happen is handle an event that happens outside of FM.  Then have that event trigger a script in FM that can do the processing of the new records.

               

              From your setup it seems like the PHP code could do it.  If your PHP code an identify the new records then you can use it to execute a web-compatible script on FMS, pass it the list of new IDs and have the script process it.

              You could skip passing it the list of IDs and just have the PHP code run a FMS script everytime the PHP code runs.  The FM script would then be responsible for checking if there are new records in the QB table and create new records in the Customers table.