5 Replies Latest reply on Dec 8, 2010 1:42 PM by philmodjunk

    Report From Multiple Like Tables

    donjuan1498

      Title

      Report From Multiple Like Tables

      Post

      I am in-charge of creating a DB for an insurance company. We have commission statements that come in for several different carriers. Each statement needs to be imported into the database each month but into separate tables, (so each company gets it's own commission table) due to the fact that each companies statement is different. There are however 6 records from each statement that are similar (but often have different names). 

      Example of similar records from multiple commission statements -
      Commissions Amount
      Premium Amount
      Agent Number 
      Client
      Policy Number
      Transaction Date

      In my DB I also have the following Tables

      Insurance AgentTB
      - AgentID
      - Name 
      - Address

      Insurance Agent NumbersTB
      -agent_number_recordID
      -Agent Number
      - Company
      - AgentID

      I am trying to generate a report that will take all the policies written by an agent from each commission statement table underneath them. Then I need to summarize and total them. So for example

      Agent John Smith
      Wrote several policies this month from 3 different companies;

      policy1 CompanyA
      policy2 CompanyA
      policy3 CompanyB
      policy4 CompanyB
      policy5 CompanyC

      How do I get filemaker to run me a report based off the separate commission statements and summarize them into one report. I hope I am being clear enough. Please let me know if more explanation is needed. 

        • 1. Re: Report From Multiple Like Tables
          philmodjunk

          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.

          • 2. Re: Report From Multiple Like Tables
            donjuan1498

            "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? 

            • 3. Re: Report From Multiple Like Tables
              philmodjunk

              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.

              • 4. Re: Report From Multiple Like Tables
                donjuan1498

                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. 

                • 5. Re: Report From Multiple Like Tables
                  philmodjunk

                  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:

                  Commissions Amount
                  Premium Amount
                  Agent Number 
                  Client
                  Policy Number
                  Transaction Date

                  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...