5 Replies Latest reply on Mar 19, 2009 11:19 AM by philmodjunk

    Consolidating Records



      Consolidating Records


      After importing records from an excel spreadsheet, I need to consolidate records which have the same unique ID.  In this case, "consolidate" means ending up with one record (from 2 or more with the same Unique ID) wherein the consolidated record's fields, where applicable, would be the sum of the same fields from the individual records.  


      For example, starting out with this record layout:


      Unique ID/Value1/Value2/Value3





      ...would be processed to ultimately end-up with a record set like this:





      How can this be done? 


        • 1. Re: Consolidating Records

          There are a lot of different approaches and I can't tell which approach is best. It depends on what you need.


          The first thing to decide is whether there is any utility in keeping the records in their current form and simply viewing/printing them in consolidated form. This is the easiest thing to do as you won't actually change any of your records and their data. Whether you want to keep your records as is, or want to physically consolidate them, start with this approach as it can be step one for physically combining your records.


          Define summary fields for each number field. In your example you show three number fields, I'll call them fld1, fld2, fld3. Define your three new fields as Summary and select Total of Fld1, Total of Fld2... etc.


          Create a new layout that refers to this same table. Make it a list report that contains the date field D1 and each of your summary fields. Place them in a sub summary part "when sorted by D1". Do not place the number fields on this report. While still in layout mode, delete the body report part. (You can click on the "Body" label and press delete.)


          Now enter browse mode and sort your records by D1. This step clusters your records and will make your subsummary part visible. If you are using FMP 10, you should now see your data in a consolidated format. If you are using 9 or older, preview or print your report to see the data.


          If you want to physically consolidate your data, the simplest method to describe is to use Export Records to export the data in its summarized format to another file. From here you can import the data back into a table in your data base.


          There are other options for physically consolidating the data in scripts that use Set Field, Get Summary or possibly Sum(). These approaches can work well especially if this is going to be something you need to do repeatedly, but they require a good understanding of how to write scripts and design relationships.

          • 2. Re: Consolidating Records

            Thanks Phil - a few comments to your speculation on certain things.


            I am using FM10.


            I need to perform this task monthly.  The data begins life as an Excel file, which is a rather complex spreadsheet whose mission in life is to allocate profits/losses to partners in a Hedge Fund.  As partners add money, it requires a separate line in the excel file since those new funds require different calculations due to new baselines that relate to stock market indexes at that time.  But ultimately, partners only want to see the totals of their investments and appreciation/depreciation, so I'm using FM to generate PDF reports, attached to e-mails.


            I don't really care whether or not I keep the records "as-is", since at the end of every month I delete all records, and dump-in another month of records.


            Now, based upon the above, what would be the best approach?  Though I have very little experience with databases, I have managed to figure out scripts which do generate PDFs, and attach them to e-mail addresses imported as a field into the database from Excel. 

            • 3. Re: Consolidating Records
                 Do the summary report, most definitely.
              • 4. Re: Consolidating Records

                When creating the Summary Field, there are several check-off boxes for further specs -


                Running Total

                Restart Summary for each sorted group


                Summarize repititons:  

                All together



                One of the latter two need to be checked.


                So, what selections should I make? 

                • 5. Re: Consolidating Records
                     Select "Total of". Leave all other settings "as is".