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.
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.
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?
Correction I meant import to sage*
We can create the file from filemaker in seconds, its just importing to sage thats the problemo.
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.
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.
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.