1 2 3 Previous Next 32 Replies Latest reply on Aug 20, 2017 4:35 AM by fmpdude

    Copy data to a temp table

    globe11123

      On one of my scripts I use to copy over data to a temporary table is inefficient,

       

      Everything works fine BUT importing the data into our accountancy program is very slow.

      This is because of the inefficient way I'm copying over the data to the temporary table.

       

      An example output file looks like this :-

       

      EXCEL_2017-08-10_14-19-46.png

      As you can see there are plenty of the same product code going across.

       

      My method is basically searching against the order to find the related components. Then i loop through each component line and copy over certain information to the temporary table.

       

      How could I change it so that when I find the related components, it also does a find / loop on the product codes within the related list and only enters one line into the temporary table with the total quantity of the found product codes.

        • 1. Re: Copy data to a temp table
          philmodjunk

          Is this file produced from FileMaker or another program?

           

          If from FileMaker, you can export the data already summarized such that you get one line to a product with the needed sub total.

           

          If not from FileMaker, a summary field can give you the needed total so that you don't have to loop through the individual rows of a given product. I will elaborate on the second option if you confirm that's what you need. If you are exporting this data from FileMaker, I'll elaborate on the first option.

          • 2. Re: Copy data to a temp table
            globe11123

            Its generated in FileMaker.

             

            This is the method I've used :-

            ApplicationFrameHost_2017-08-10_16-42-19.png

            How would I go about using the method you described phil?

            • 3. Re: Copy data to a temp table
              philmodjunk

              Add a summary field, if you don't already have one, that totals the field for which you need this sub total.

               

              When exporting the data, sort the records by product so that each product is now in contiguous groups.

               

              Then use the "group by" option to get one row of data for each product and include the summary field in the export to show the sub total based on each product.

              • 4. Re: Copy data to a temp table
                globe11123

                Ok thanks for your advice phil.

                 

                Will test this out in work tomorrow!

                • 5. Re: Copy data to a temp table
                  globe11123

                  FileMaker Pro Advanced_2017-08-11_08-38-10.png

                  Grouping it by product but nothing happened, the data was the same amount as before. Is this because of the row headers?

                  Will I have to set it so that one of the export fields are grouped instead?

                  • 6. Re: Copy data to a temp table
                    taylorsharpe

                    FYI, writing to a temporary table is one of the slowest things FileMaker does because it writes, validates, builds index, etc.  All great things for a table with a lot of future use, but for a temporary one-use table, that is a lot of overhead.  When I need a temporary table, I write a SQL statement storing the data to a variable, insert it into a global field and export field contents.  Or if I need to see the data, I use the virtual list.  But I do not store the data into a normal FileMaker table for temporary use.  Also, doing this means you don't have to delete the data in the temporary table before repopulating it. And lastly, you don't have to worry about someone else running the same script and storing data on top of the data you already have in that temporary table (makes it more multi-user friendly). 

                    • 7. Re: Copy data to a temp table
                      philmodjunk

                      Grouping it by product but nothing happened, the data was the same amount as before

                      Does that mean that you only have one row of data for every different value in Product Code?

                       

                      My understanding was that you have multiple rows for each product, but only need one row of data for each. If you are getting exactly the same result, that would indicate that Product Code has a unique value for every record that you are exporting.

                      • 8. Re: Copy data to a temp table
                        fmpdude

                        Don't forget the incredibly slow loop performance in scripts!

                         

                        I would do this task, yes, using SQL, but OUTSIDE FMP, using JDBC, so I can:

                         

                        (using JDBC...)

                        1. fetch the data (SQL SELECT)

                        2. do any updates or loops in fast Java code, then, (ROWSET processing)

                        3. write the data back to the temp table. (UPDATE OR INSERT INTO SQL)

                         

                        Based on my tests over the years, this method is immensely faster.

                        • 9. Re: Copy data to a temp table
                          globe11123

                          I think I've figured out the problem.

                           

                          Rather than using the field names for table headers, I was setting custom headers on row one to match up with SAGE's field names. I've removed them now and sorted the data.

                           

                          Edit* - I've re added the headers as they was nothing for the software to point to, called the field where product code will be __Stock Code and sorted it to Descending. (Works)

                           

                          EXCEL_2017-08-15_11-58-48.png

                           

                          They're grouping now but the problem is that the qty's are wrong, Quantity is just the standard quantity field and Test is the summary field, it looks like its added every lines quantity up.

                          • 10. Re: Copy data to a temp table
                            philmodjunk

                            The method that I described works for me.

                            I found the records that I wanted to export.

                            Sorted them by Category (for the sample data shown here)

                            Used Export Records to export them to an excel workbook

                            Selected "group by". Selected the summary field and kept the version labeled "by category".

                            Exported the data.

                            GroupedExport1.pngGroupedExport2.png

                            • 11. Re: Copy data to a temp table
                              globe11123

                              Ah I see now.

                               

                              Tested with a summary field within the export table and it worked but...Not sure this will work for me due to having to set my own headers as I'm exporting to csv rather than xlsx. The summary field can't have a header set.

                              • 12. Re: Copy data to a temp table
                                globe11123

                                Is there anyway around this?

                                • 13. Re: Copy data to a temp table
                                  fmpdude

                                  Following up on my posting above, if you post some representative data (that I can import) and also post the exact expected output, I'll do this using Java and a FMP database in less than an hour. Done.

                                  • 14. Re: Copy data to a temp table
                                    globe11123

                                    will this help?


                                    row 1 is the field names (not needed, just from field accidentally exported as xlsx)

                                    row 2 is the alias names that match up with the import software for sage.

                                    row 3 and beyond. Data

                                     

                                    Expected output would be row 2 and data grouped up by product code and the qty total'd up. ( I can't get it to do this) also needs to be CSV.

                                    1 2 3 Previous Next