6 Replies Latest reply on Apr 21, 2016 5:10 AM by ianmanning

    Copying data from a loosely related SQL table.

    ianmanning

        FM Pro Advanced 14 , Server 12 (can upgrade)

       

       

      I have to transfer data from an SQL data table (which filemaker has, as a related table) to a remote database via a text/xml like file.

      This is part of a large project putting data into a CRM database.

      I have a line items table containing the detail lines of all orders.

      The problem is that the line items do not always include a product many lines have notes in them.

       

      For example

       

      Record 1

      <Order number>  abc

      <Line Number> 1

      <Product> 1ABC2345

      <Quantity> 6

       

      Record 2

      <Order number>  abc

      <Line Number> 4

      <Product> 1 piece is ex-stock

      <Quantity>

       

      Record 3

      <Order number>  abc

      <Line Number> 5

      <Product> balance of 5 in 2-3 weeks

      <Quantity>

       

      Record 4

      <Order number>  abc

      <Line Number> 7

      <Product> 1DEF9876

      <Quantity> 2

       

      I need to be able to create a record

      Record 1

      <Order number>  abc

      <Line Number> 1

      <Product> 1ABC2345

      <Quantity> 6

      <Notes> 1 piece is ex-stock ¶  balance of 5 in 2-3 weeks

       

      Record 2

      <Order number>  abc

      <Line Number> 7

      <Product> 1DEF9876

      <Quantity> 2

      <Notes> Please send when complete

       

      The line numbers are always increasing between record lines with products on them and the quantity will be blank on the “Notes” type records. There are often gaps in the line numbers and there does not have to be a note style record after a product style record.

       

      I think I can do this using a script but would like to know if anyone has a better idea using a recursive calculation or something.

      My script idea would be

       

      Find all line items modified today (via a date field) (a note live item my change without its product line item changing

      Sort by <Order Number> and <Line Number> (this is actually the order they should be in when found as it is the creation order)

       

      Loop 1 start

      Find all line items for the first /next <Order Number> ( I think I have to open a new window (2) so that I don’t lose the Line items modified today)

      Sort by <Line Number>

       

      Loop 2 Start

      Check it is a product line by the quantity field >0 or not “” 

      If it is a note line go to the next record (loop 2)

      If it is a product line record the line/record number

      Check if the Product order line exists in the Transfer database

      Set $notes variable =””

       

      Loop 3 start

      Go to the next record, exit after last (the product record has no notes unless it has already been through this)

      Check if it is a product or note record by the quantity field.

      If it is a product record and $Notes is empty, (no notes for the previous product)  Exit loop 3 (go to loop 2 and start again with this record.)

      If it is a note record add the note to a $notes variable.

      If it is a product record and $Notes is not empty (reached the next product record)

      Put $Notes into the Notes field of the record in the Transfer Database

      End loop 3

       

      Close window 2

       

      Go to next record Loop 1 exit after last.

       

      I think this should recreate the notes in a product record if either the product or notes record has changed.

      Can this be done in a better way?

       

        • 1. Re: Copying data from a loosely related SQL table.
          macwombat

          Have you considered importing the records to the table you want and using the Import Action:  "Update matching records in found set"?

           

          If you are cautious about that process you could import to a temp table first - run some checks and them import to the destination table.

          • 2. Re: Copying data from a loosely related SQL table.
            ianmanning

            Hi Chris,

            Thanks for the suggestion, I hadn't thought of importing. This is going to be an hourly occurrence to keep the CRM up-to-date. so it has to be automated. (It would be much easier if we could simply let the databases talk to each other but our parent company will not do that!)

            How would I combine the data from the multiple Notes records into the Product record? I thought that the update matching records would overwrite the existing data.

            Would I need to export the data first so that only the records modified today were imported, there are currently 1,634,537 records in the SQL table.

            Thanks

            Ian

            • 3. Re: Copying data from a loosely related SQL table.
              macwombat

              Hi Ian.  From you original post I go the impression that you were wanting to get the information from the records from one table into a new table.  I understood that records in the original table were of two types - some with products and some with notes.  Your latest post has confused me with this statement - "how would I combine the data from multiple Notes records into the product record?"

               

              If you have multiple notes for the one product I would advise the following:

              1.  From your total set of data find the records that are products - import to a products table.

              2.  From your total set of data find the records that are notes - import to a notes table.

              3.  Create a relationship between your new products table and your new notes table that is based on the productID.

              4.  From a layout based on products, display the notes in a portal - or if you need them all in one field - create a calculated field in the product table that concatenates the text from the related notes records into one field.

               

              HTH.  Chris

              • 4. Re: Copying data from a loosely related SQL table.
                ianmanning

                Hi Chris,

                Sorry for the confusion.

                The original table is the line items from our sales system the end table is line items in our parent companies CRM I have to extract the data on a daily or possibly hourly basis and send the new or updated information to our parent company.

                The field in the line items table that contains product references will be the same field that contains the notes.

                There isn't a relationship between notes and products except that the records with notes in them will follow a record with product. They all have the same order number.

                i.e.

                if the original database was filemaker and  I were to place a line items portal on the orders layout the portal would look like this as long as I sorted the portal by line number

                Line Number     Item                                  this column is just so you can see which are products it doesn't exist in the table

                ------------------------------------

                1                       Widget                                         (Product)

                3                       1 piece available from stock

                4                       balance in 2-3 weeks

                5                       Big Widget                                  (Product)

                8                       Little Widget                                (Product)

                11                    special colour green for BP

                 

                I hope this makes things a bit clearer

                • 5. Re: Copying data from a loosely related SQL table.
                  macwombat

                  Hi Ian. OK - I think I see the problem clearly now.

                   

                  You have records that have notes but do not have a product number.

                   

                  The relationship between those notes records and their related product is that both the product record and the notes record have the same order number - and the notes records have line numbers that are subsequent to the product records.

                   

                  Is that right?

                   

                  Your original question was regarding a better way (faster way) to  do it than the script working through record by record.  There might be a better way but I can't think of one off the top of my head. 

                   

                  But I think a more important question is this:

                   

                  Can you restructure your original sql database so that it has a better structure?  Ideally you should have a notes field that is separate from the product field so that you can enter both product number and notes in the one line items record.  Or if you have to have separate records for the notes then they should be in a separate table where the two tables are related by an ID field that uniquely identifies both the order number and the product.

                   

                  Chris

                  • 6. Re: Copying data from a loosely related SQL table.
                    ianmanning

                    Hi Chris,

                    Unfortunately the original SQL database is our ERP system a commercial program that I have no control or influence over.

                    I have a script that is working now, it's not very pretty but it is doing the job.

                    Thank you for all your support and suggestions.

                    Best regards

                    Ian