Forum Discussion

AndreaBest's avatar
AndreaBest
Qrew Trainee
2 months ago

Snapshot fields and Importing records

I'm trying to streamline some manual processes, and running into inconsistent results.

I'm importing data into a child table. It has 4 snapshot fields that are set from the parent record.

The child data is coming from a 3rd party, cleaned up (ex. making sure parent record id is correct). The goal is to get the clean up out of Excel, and simplify the upload to the child table.

The fields on the parent record used for the snapshots are based on summary data from the child table. Specifically - based on the type of visit, if the Minimum visit date = Maximum visit date, then the child record being added must be new. It is the "new flag" that is used for the snapshot on the child record. FWIW - as weird as it sounds, yes the summary fields do appear to account for the new record as it is being added.

I've moved the clean up to a worktable in Quickbase, no problem.

I wanted to use pipeline to move the final data from the worktable to the Child Visit table.

I created the pipeline, (simple API_RunImport) ran it, but only 34 of the expected 245 records had the correct snapshot values. Bummer. Reviewing the lookup fields on the parent & child, the values were correct. Just the snapshot wasn't set correctly.

I cleared the data, and manually ran the defined TableToTable import. This time only 3 of the expected 245 records had the snapshot set correctly. Bummer again, going in the wrong direction.

I cleared the data again, downloaded the data to CSV, then manually did Import from CSV.

This time I got 234 out of 245 records set properly. The ones that were incorrect, I could possibly account for. The child table has 4 different snapshot fields, related to different types of visits. If a parent record client had 2 types of new visits, there would be 2 records in the dataset. Only 1 would have the new snap field properly set. The other would not. I'm guessing it probably has to do with QB caching parent record data. I could solve this by separating imports by visit type.

Does anyone have a suggestion on how to get the pipeline to add records with correct snapshot values? 

I would consider this a bug, but would like to know what the community experience is with snapshots and data imports.

For those wondering about repeat visits, I have those separated out, and they would be a different import, just to prevent issues with the New visit calculations.

thanks,



------------------------------
Andrea Best
------------------------------

2 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    2 months ago

    if I understand the challenge correctly, you are mass importing, children, and the value of the records and field in those children need to flow up to the parent record and then summarized and come down in the lookup and snapshot.

    I think that you have a condition where the children are still loading while look up some snapshots are being created.  

    You might want to consider having a process where you first do the import, and then you run a pipeline to manually copy the value of the look up field into a manual snapshot field. By that I mean not a real snapshot field but your home grown  snapshot field.  


    You say that you you are using API Run Import so maybe that means you have a formula button to kick off the process. Maybe the next step built into that button could trigger the pipeline to copy the lookup field into a scalar data entry field  



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • AndreaBest's avatar
      AndreaBest
      Qrew Trainee
      2 months ago

      Hi Mark,

      You have the data flow correct.

      Over the years, we started with manual data entry of the Child records, and the snapshots worked just fine, once I figured out the chicken& egg issue with the summary and look up fields.

      Then we were required to use a 3rd party to gather the data, and used excel to verify and create the CSV uploads. We had some issues when the same client (parent) had multiple visit records (children), so we split the upload so the first upload had all unique client IDs(parents). The CSV upload had no access to the parent & child tables to determine ahead of time which records might be "new".

      My current project to streamline the whole process within Quickbase, allowed me to have access to parent & child tables, so I know ahead of the import, which records should be new. I set up the Table to table import and Pipeline to run it with that in mind.

      Unfortunately, this last iteration is not reliable. 

      I'll probably throw an "override New" value on the child table, populate that from source worktable, and take the snapshot from a formula field that looks at both the parent look up field or the override field for the final value.  It isn't worth my time to create a pipeline to try to populate another field. 

      The child table does get the occasional, valid, manual entry record, so I need to keep that part working the same way.

      thanks for the feedback.



      ------------------------------
      Andrea Best
      ------------------------------