Is there a best practice for snapshotting records in time

Questions
1

I have a table which is a list of student data. How many days attended in the past 10, how many credits earned, etc.

I'd like to snapshot it monthly.

In the past for situations like these I've added a "snapshot date" to the table and then whenever it's time to "take snapshot" I:

  1. set the snapshot date to today minus 1
  2. duplicate the record and increment the date to today

Then I have a portal that lets users go "back in time" and view past data.

Is there anything dangerous or unwise about doing it this way? I've also considered having a duplicate table with all the "past snapshots" which could in theory have less data since I could streamline the data I've stored there and remove calculations and things, but having a duplicate table feels complicated and error prone.

2

The separation is beneficial. Having snapshot data mixed in with current data complicates summaries and relationships. For example… how would you count the total number of student records? A duplicate table is, in my opinion, the better approach.

Think of an ERD. You would not normally want to mix both types of data in a single entity because it would create a mess in the diagram.

1 Like
3

is there some way to automate that snapshot table? Is it just a matter of making a duplicate table and then creating a script which copies over every field.

4

Import is easier and less error prone. Monthly server-side scheduled script works unless you have container fields that need to be in snapshots.

5

By import you mean export it as csv (or something) and then import it?

6

If server-side, a temporary intermediary file is a must. I usually use csv as the intermediary file format. The server is limited to a subset of the formats available to a client for both export and import. FileMaker's documentation has all the details.

If client-side, then you can import directly from one table to another.

7

Gotcha. Thank you!