3 Replies Latest reply on Feb 7, 2016 5:51 PM by jasheldo

    Creating a Summary table from a Detailed Table


      I have a table with 750,000 records.  The table is detailed medical claims data.  For example, there may be 14 records (or pick your favorite positive integer greater than zero, it doesn't matter) that represent one single claim.  Each of these 14 records shows the claim number.


      What I want to do is create a second table which is more or less a summary of this table.  I want each record in the summary table to be a unique claim number.  This table needs to be a table in and of itself, not just a summary report of the existing table or a table occurrence of the existing detailed table.  For now, all I want to do is have one single element in this summary table:  the claim number.


      The way I'm doing now is performing a script that sorts all the records by claim number, goes to the first record, stores the claim number to a local variable, creates a new record in the summary table and puts the local variable there.  It then goes back to the detailed table, goes the next record and compares that record's claim number to the one stored in the variable.  If they're identical, go to the next record and make the comparison of the variable with this new record.  If they're different, store the new claim number in the variable, go to the summary table, create a new record and put the variable there.  Repeat this process until the end of the detailed file.


      It's logical enough and it does in fact work.  However, I had the script running all night and it still didn't finish.  In fact, when it get about half way through it slows to a crawl.


      Is there a better way accomplish what I'm trying to do?


      Thank you!

        • 1. Re: Creating a Summary table from a Detailed Table



          Not positive this will help but you never know.  This is mostly about minimizing the UI and layout swapping.


          0.  Sort data as you are doing.

          1.  Create a join to the summary table using the claim# as the key and allow creation of related records.

          2.  Perhaps a very lightly populated table of your medical claims data - just data fields.  Or maybe no fields at all on the layout.

          3.  Freeze window.

          4.  Commit every "n'' records.

          5.  Perform your script on the server.


          6.  Or maybe you could export your data summarized by claim# and just export claim#.  Then import results into your summary table.




          "Research is what I'm doing when I don't know what I'm doing." ~Wernher von Braun

          • 2. Re: Creating a Summary table from a Detailed Table

            You can take a similar approach to your method but change to simply omitting records with duplicate claim numbers and then import all of the remaining records into the summary table. This method would allow you to run it as two separate steps and give you a chance to check that your duplicate omit step worked as expected. From my experiences you can easily import in 50,000 records in around a minuted depending on your system. The looping through each record will take some time but all of it could be offloaded to the server to make it quicker.

            • 3. Re: Creating a Summary table from a Detailed Table

              Thank you both so much for the assist.  I found a super fast way to do it directly from FileMaker.  More or less, create a self join using the field that has the repeats.  Have a serial field in the table and a calculation field that uses the self join to identify the FIRST of any of the duplicates.  Then create a script that finds all the "Unique" values and copy all of them over to the table.  Worked like a charm and ran in seconds.