For best results, you'll need to find a way to merge at least the comission data required fror this report into a single table. This can be done in a temporary table just prior to printing your report or you can structure your initial import process to load a table of these common values at that time.
"This can be done in a temporary table just prior to printing your report or you can structure your initial import process to load a table of these common values at that time."
I hav never setup a temporary table before can you explain the process?
Perhaps a bad choice of words. The table isn't temporary--just the data that resides in it. You'd define a table for you report and just before you view your report, you kick off a script that deletes all the records in this table, then imports data from your separate tables into this common table.
I consider this approach second best to putting the "common" data into one table during the import process and then keeping it there.
I tried importing the data from each statement into each table. But as you can imagine a double import leaves room for a lot of errors.
I really like the idea of the temporary storage table. I am at best intermediate in filemaker, and I am clueless on how to accomplish populating this table. I have some ideas but not sure how to perform the task. Would it be possible to go a little more in depth on how to get records temporarily into a temp table.
The details depend on the exact structure of each of the separate company specific tables. The process of merging the data into this temp table for reporting processes is virtually identical to doing a "double import". It's just that the second "import" takes place just before you view your report and thus, that approach requires doing this stage two import over and over again instead of just one at the beginning.
Basically, you'd load your data into these fields:
Either once during initial impart or many times--each time just before you generate a report.
Don't see why a scripted import process that first captures the file path to the source file, then imports the data from it twice--once to the company dedicated table and once to the combined table would "leave room for a lot of errors". It is very likely that you can completely eliminate the company specific tables and just have a combined table of information--which can be a bit more flexible when your business adds new companies or existing ones change.
And each company can still use distinct commission data for calculating agent commissions...