3 Replies Latest reply on Apr 29, 2013 1:07 AM by RBowering

    Copy data between tables IF

    RBowering

      Title

      Copy data between tables IF

      Post

      I have two similar tables. One is local to the FMP12 file and I use it for data entry, sales management, reports. There is a similar table I access via ODBC on a MySQL server which contains as much data as needed so it can be queried and used by php on my website

      When I create a new product, I enter all the data in the local table and it's linked child table

      Then I have to manually copy certain fields to the ODBC table plus add a few dummy values there to keep the MySQL validations happy. Part of the data entry includes copying the main ID field from table 1 to ODBC table

      I would like a script to:
      Check to see if an ID exists in both tables
      If it does, then end the script
      If it doesn't then 
      Create new record in ODBC table and copy fields A, B, C, D, E and enter certain data into the two mandatory 'other fields

      This would be a one record at a time activity - not a loop through the whole dataset. I would put a button on my main data entry table; enter my original data then click the button to fire the script to copy the data across. It'd be even cooler if the button was disabled when there was already a matching record

        • 1. Re: Copy data between tables IF
          GuyStevens

               Isn't it easier to add a field in your original table that marks a value when that record is imported in your ODBC table?

               That way you can at all times see which records have not yet been imported and import only them.

               The first time you could run a loop script that checks all the records and marks all records that match (have already been imported).

          • 2. Re: Copy data between tables IF
            RBowering

                 Thanks for the answer, but the real problem isn't really about checking whether the new record is in the ODBC table

                 This is about when I create a new product. I will know that that product is not in the ODBC table. This new product is the one I want copied across to a new ODBC table record. 

                 In reality I don't create new products very often. I just created 6 new ones last week, but it had been months since the previous new product

                 So, maybe the auto checking to see if ODBC record exists is a bit of a red herring and unnecessary - it's a nice one click button solution to create a new ODBC record and copy the appropriate data that I'm after

            • 3. Re: Copy data between tables IF
              RBowering

                   In case anyone has the same query as I, I got an elegant reply to this elsewhere:

              Set Variable [$FirstField; YourTable::FirstField]
              Set Variable [$NextField; YourTable::NextField]
              Set Variable [$AnotherField; "Dummy Value"]
              Set Variable [$Main ID; YourTable::MainID]
              Go to Layout [SQL Table View (showing records from your MySQL Server as an External Data Source)]
              Enter Find Mode []
              Set Field [SQL::MainIDField; $Main ID]
              Set Error Capture [On]
              Perform Find []
              If [Get (FoundCount) = 0]
              New Record/Request
              Set Field [SQL::FirstField; $FirstField]
              Set Field [SQL::NextField::$NextField]
              Set Field [SQL::AnotherField; "Dummy Value"]
              Set Field [SQL::MainIDField; $Main ID]
              End If
              Go to Layout [original layout]