Forum Discussion

ChrisSipes's avatar
ChrisSipes
Qrew Cadet
7 years ago

Suggestions for how to ""snapshot"" the database

I have an app with several tables which keeps project information throughout the quoting, execution, and closing phases.  I want to get a "snapshot" when a project moves from quoting to execution and then again from execution to closing phases.

I can create an action to take the fields from the main table and put them into a "snapshot" table when this happens.  The problem is that important info like the parts and the material is in other tables.  These are the items that are likely to change and I want to track that.

Am I missing an easy solution?
Tables and fields

12 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    If you use an Automation rather than an Action you can do up to 50 separate steps in a row based on a trigger, so for example you could possibly capture some data from other tables as well.
    • ChrisSipes's avatar
      ChrisSipes
      Qrew Cadet
      7 years ago
      So if I use an automation, I can perform a snapshot of each of my child tables and my parent table into snapshot-version tables of each one?

      How do I maintain/create the relationships between the parent and child snapshot tables?  While I can keep the project number in each one to tie them back to the original, they will all get their own record IDs.  
  • JustinTorrence's avatar
    JustinTorrence
    Qrew Cadet
    7 years ago
    It sounds like you're headed in the right direction.
    1. Why can't you capture the "parts and material" fields in the "snapshot table".
    2. Do you need this information purely for logging purposes, or do you plan on doing some type of calculation with the data, like # of changes in a given time frame etc.?
    • ChrisSipes's avatar
      ChrisSipes
      Qrew Cadet
      7 years ago
      Justin,
      The project has one main table, where parts, equipment, and materials are all child tables.  The project may use zero to dozens of pieces of equipment to install one to hundreds of different parts, requiring some types and amounts of material.  It is logical to have a main Project table and children for all of the many-to-one relationships.  

      As you expect with any complex project, there will be changes along the way.  If we add or remove parts, we will bill for those in the end.  While I don't want to keep track of every change, when the project changes phase, I need a snapshot of what the scope was at that time.  Knowing what we first quoted, what the scope was at kickoff, and how it changed to billing are all vital.  I will look for number of things that change, which items change, along with the dollars that change, and the time frame between phases, among other things.  This will help improve our company processes.

      This is not a project management system - it has nothing to do with people planning, but it is the database of the parts that we sell to feed into accounting, so knowing these things is important.
  • QuickBase9's avatar
    QuickBase9
    Qrew Cadet
    7 years ago
    You may want to consider using one of the current child tables (or creating a separate child table) where you can pull down important fields from all the relationships related to a project. We take our daily snapshot from the lowest child record so that we can pull all related fields from the parents down (it is much easier than trying to pull all fields up the relationship list).
    • ChrisSipes's avatar
      ChrisSipes
      Qrew Cadet
      7 years ago
      Leanne,
      So you pull data from the parent table into one child, then you only have to snapshot that child?  That is an interesting idea.  While I would like parts and equipment (two children), I would settle for just the parts child table if I needed to.  This is not perfect, but I can see how it would work.
  • AdeebAlgadban's avatar
    AdeebAlgadban
    Qrew Member
    7 years ago
    Hi Chris
    I ran into the same issue when building a snapshot table for multiple tables (and ironically, when a user switches phases on a project). My approach was as follows:
     1) In the child table: create a formula field that is comma delimited that combines the columns (or the delimiter of your choice) with a newline character at the end
    2) In the Relationship screen->Add Summary: Use the new Combined Text summary field to summarize the formula field for all the Child records to the Parent record 
    3) In the Parent table: Convert the Data to Text in another formula using ToText()
    4) Use an Automation to snapshot the Value in a Text field in the Snapshot table.
    Now that you have a record or (metadata) of the related record of the child table, you can reconstruct them through string manipulations in a variety of ways using formula fields or Javascript.
    For my use case, I used javascript to reconstruct the data.
    Can you tell me more about the use case what you are trying to accomplish?
  • ChrisSipes's avatar
    ChrisSipes
    Qrew Cadet
    7 years ago
    So what I ended up with (so far) was an Automation.

    When a Project [status field] is modified, that record becomes the trigger.

    The automation then copies the child table Parts related to the Project into the Snapshot Parts table.
    It then copies the Project into the Snapshot Projects table.

    It has to occur in this order because I then needed a second automation to create the relationship between the Snapshot Parts child records and the Snapshot Project parent record.

    This trigger is the creation of a record in the Snapshot Projects table.  

    When the Snapshot Parts were created, the related Snapshot Project field was blank.  I am currently trying to use the trigger of the creation of a Snapshot Project to fill in the relationship between the two.

    If this works, it should be relatively easy to also handle any other child tables that I wish and I am not creating any additional fields.

    The potential problem could occur in a multiuser environment if two people change project status at the exact same time.  There would be a relationship error.  That is rare enough that I think that I will take the risk.


  • AdeebAlgadban's avatar
    AdeebAlgadban
    Qrew Member
    7 years ago
    I think if you snapshot the Project table first, it will be better.
    In the snapshot, copy the Original Project record id. Then trigger a Copy Record action when a Record is Added in the Project Snapshot by looking up each Record in the Part table based on the Project Record ID.

  • ChrisSipes's avatar
    ChrisSipes
    Qrew Cadet
    7 years ago
    I cannot access the Project>Record ID in the Filter.  I can only access the Trigger which is Project Snapshot>Record ID or Parts>Record ID
  • ChrisSipes's avatar
    ChrisSipes
    Qrew Cadet
    7 years ago
    And then, as usually happens with Quickbase, it stops working altogether.  

    I removed an extraneous field from the table, and then removed it from the automation and now the Automation fails to trigger (even though that field was never used). 

    Even though the trigger is on any add, change, or delete in the entire table, it now fails to trigger at all.  <sigh>    One step forward, two steps back.