Summarize a transactional table and import into a new table

[Goetch]

We have been using FileMaker for reporting for several years now.  We basically import invoice lines ( and several other KPI fields from related tables ) from our point of sale into our reporting program nightly.  Basically what we have is lots of historical data that is not summarized.  Most of my reports use summarized aggregate data, a few actually need the line details.  What I would like to do is create several summarized tables from my main data table so I can have several summarized data cubes basically.    What would be the best way to import the summarized data from the existing table?  Is it even possible?  

For example I would like to summarize sales by category by month and import that into a new table.  

Any insight would be greatly appreciated.  

 

[Steve Martino]

Why would you need to export this to a new table?  Seems incorrect to duplicate the data. I would think you could just create a report and a report layout.

Find the records by date range, go to report layout and sort.  The added advantage is if you ever have to correct some data, you don't have to find the report in the report table and delete, then create new report and save.

If you had to do more analysis outside of FM (Excel for example), you could then script an export to Excel.

You could also take the report, print to PDF and save it in a folder or save it in a container

[webko]

@Steve Martino - there are times when the number of line items becomes excessive, and the historical data really isn't going to move. I've certainly got a system where I do this to speed up the process for end users...

I would look to the Virtual List technique to generate the summarised data that can then be imported to a History table for reporting / archiving (or possibly just use the VL technique anyway, as it's much faster at creating summary reports on large data sets in my experience...)

[Steve Martino]

@webko  good to know

[Goetch]

@Steve Martino - The reason as webko stated is for performance.  The figures for the historical data do not change, at times an item's classification does get changed, or was entered incorrectly by the data entry team.  The program itself is really just a type of data cube, and summarizing and storing historical data will speed up many of the reports.  The users of the program expect the reports to be near instant. I rarely use the FileMaker's built in sub summaries, the users like to see aggregate data, so sub summaries do not always work for the style of reports they want.  I have tried to move to virtual list style reports, however, they took longer to generate than using multiple table occurrences, and the users were not happy waiting 5 to 10 seconds after a button click.  This was probably due to my lack of experience with virtual lists. 

I didn't think of trying to use a virtual list for the import, so I will give that a try.  I am not that experienced with the technique, but I think I can muddle through it.   

[Goetch]

@webko Thank you so much for the hint.  It actually seems too easy now that I did it!!!  I basically created a VL, populated the utility table, omitted blank records to get a record count then just looped through creating new records in the summary table, setting the fields just like one does for the VL utility table.