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.
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.
Ok thanks for your advice phil.
Will test this out in work tomorrow!
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).
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.
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:
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.
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)
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.
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.
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.
Is there anyway around this?
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.
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.
Export_Stock.xlsx 8.4 K