4 Replies Latest reply on Apr 8, 2011 4:11 PM by FredrikWallenberg5579

    Storing "snapshots" of summary data

    FredrikWallenberg5579

      Title

      Storing "snapshots" of summary data

      Post

      I have worked around this problem since FM 2.x where I would be managing a portfolio with current prices in a (these days fully relational) database. However, I want to save a history file with the total value each day. 

      The only solution I can find is the one I've used since the beginning of time ... export the summarized data from one table and then import that file into your history table. Is there no way to go directly from the portfolio to the history?

        • 1. Re: Storing "snapshots" of summary data
          philmodjunk

          Import records can be used to import data directly from one FileMaker table to another. No need to export the records and then import them. (You could do that even way back in the 2.5 and 3 days.)

          You can also use a loop or replace field contents to perform such an update without any use of Import Records. Some developers prefer this approach--especially if the two tables do not have identical field names due to a very ancient bug that can screw up scripted imports if you add a new field to your table and forget to also update the import records script step.

          • 2. Re: Storing "snapshots" of summary data
            FredrikWallenberg5579

            Thank Phil ... turns out the first solution doesn't work. I forgot to specify my problem in more detail. I have a portfolio with assets in multiple currencies where I want to store the total value by currency (sub-summary). As far as I can tell, that is not an available import choice. Let's say I have ten assets in each of of currencies A and B. What I want to import is two rows into the history table, once for currency A and one for currency B.

            I'll try to see if I can use the loop (guess that will be together with summarize to create a stored value). In pseudo code:

            Go to layout "portfolio"
            set variable "currency" ="null"
            sort by currency
            loop over all records {
                 if field "currency" ≠ $currency {
                      copy TotalValue to Value in new record in table "History"
                 }
                 set variable "currency" = field "currency"
            }

            • 3. Re: Storing "snapshots" of summary data
              philmodjunk

              What problem do you solve by doing this? You don't actually have to physically copy the data to generate your summary data. A summary report on your current table can produce the same results.

              We do summarize some data here where I work, but that's so that a summary report spanning millions of line items entries in some Invoices pops up quickly without encoutering significant delays. This denomalizes the data and can be a challenge to keep correctly updated so I don't recommend doing so unless there is a reason that makes this worth the extra effort to maintain and verify.

              I use a looping script scheduled to run late after closing when it won't interfere with other users:

              Loop
                 Find all records not summarized
                 exit loop if no records found
                 Find all records the same as the first record in the found set
                 Create record in summary table using totals in summary fields of current table to compute the totals and averages for this group of records
                 Use replace field contents to "mark" the records as summarized
              End Loop

              • 4. Re: Storing "snapshots" of summary data
                FredrikWallenberg5579

                This is not about generating summary data, it is about saving it. The main database is summarizing based CURRENT prices and I do not want to store price history for each asset. What I do want to store is a historical record of the values at today's prices. Anyway, your loop should work for me. Thanks!