8 Replies Latest reply on Oct 27, 2016 8:37 AM by philmodjunk

    Condense export - more efficient

    globe11123

      I create a stock transaction file export in file-maker so we can update our stock figures on sage.

       

      Everything works fine but the export file isn't is efficient is i want it to be.

       

      i.e there could be several of the same product codes with quantities. But really what I could do with is just the one product code with all the quantities combined like a summary of that product code?

       

      3.png

       

      how could I make it more efficient?

        • 1. Re: Condense export - more efficient
          IanJempson

          You could sort your export file then export the summarised data.

           

          but... are you really sure you want to do that?

           

          yes it may be a little bit more efficient in the sense that it's transferring a smaller amount of data. However it's also breaking any chance of following the audit trail between the two systems. If you're sending data to Sage at a granular level then you're able to trace the individual transactions from FileMaker through to Sage. More importantly you or your accountant can trace back from Sage to FileMaker.

           

          So for a small gain in efficiency you'd be breaking your audit trail by aggregating the data. If it were me, I'd check with my accountant that they're ok with this. If they are ok with it, then simply sort the data and export a summarised version. You might need to add summary fields for some of the data you want to aggregate. You also need to do some testing to ensure that the file format is still good for Sage.

          • 2. Re: Condense export - more efficient
            globe11123

            We exported a file with 256 seperate lines. It took from 11:14 to 11:50 to complete.

            (Mind that they're computer isn't the best)

             

            Within this file a product code could appear more than 10 times. And I'm guessing the way the software works which puts the data through sage it will just go line by line. Having to come back to the same place more than once.

             

            The audit trail will be fine as for a reference we are putting the Sales Order number.

            Also note that I only export a stock file per order.

            • 3. Re: Condense export - more efficient
              globe11123

              Here's what the example export file looks like.

               

              6.png

              Would I need to create a summary field for each field? Type, Quantity etc

              • 4. Re: Condense export - more efficient
                IanJempson

                are you saying that it took over half an hour to produce an export with 256 lines? I'd expect an export of that size to take a few seconds.

                 

                before you look at aggregating the data I think you should have a look at what the export script is doing. Could you post the script?

                • 5. Re: Condense export - more efficient
                  globe11123

                  Correction I meant import to sage*

                   

                  We can create the file from filemaker in seconds, its just importing to sage thats the problemo.

                  • 6. Re: Condense export - more efficient
                    IanJempson

                    if the import to Sage is taking that long, I'd be very suspicious that something is wrong there. That is an absurd length of time for such a small number of records.

                     

                    First place to start is with a call to Sage support to see if they can help.

                    • 7. Re: Condense export - more efficient
                      globe11123

                      It will probably be down to hardware as it took 6 minutes to reproduce that on my computer but then again my sage is local not based on the server.

                       

                      Either way the current file output isn't very efficient.

                      • 8. Re: Condense export - more efficient
                        philmodjunk

                        There is another method that does not require exporting data.

                         

                        Create a list view report with no body, but with a sub summary layout part "when sorted by" your Product Code field.

                         

                        Put summary fields in the sub summary part to provide any needed sub totals or counts along with the product code and any other fields needed for your report.

                         

                        Find the records you want for your report.

                         

                        Sort them in an order that includes the product code specified as the "sort field" for the sub summary part and you get one row for each group of records with a total shown in the sub summary part.