Forum Discussion

SolaceSonder1's avatar
SolaceSonder1
Qrew Member
3 years ago

Creating multiple records in Table X from a QB Sync Table

My QB Sync table has a patient primary key and a multi-select field (although QB imports it as a text field) with hundreds of possible symptom values. I would like to make this into a join table between patients and symptoms with each record being one instance of a patient with that specific symptom. Is there any way to do this natively with QB? 

I can script it, but the organization I work with is not very technical, so it would be difficult to maintain. Much easier to maintain in a low code platform like QB.

------------------------------
Solace Sonder
------------------------------
Quickbase Sync
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    Here is a low tech approach.

    I believe the choices for a multi select field come off a set of records in an other table.  Create that table and have it be the source of truth for the list of choices.

    Create the child table and lookup the value of the multi select field down to the children.

    Create a checkbox field that is called [Symptom has been selected?] which evaluates if the symptom on the child matches any of the multi selected choices, you would need to use a contains type IF statement, but  you would have to check if you can get away with that as some of your symptoms may have similar words like "High Blood Pressure" and "Very High Blood Pressure" - but if that is the case you can work with a long IF to get the parts of the multi select field.

    Create a pipeline that triggers on "new Event", so add edit or delete.

    First purge all the children - this is to ensure that if a record is edited, that the children will get updates.  There is channel for bulk delete that would be faster I assume than one by one.

    Then import all possible Symptoms.

    Then delete the ones that do not apply.

    I imagine that there is a way to do this more directly using Jinja (but someone else would need to chime in with that syntax), though to create the proper children, but it's still a good process to first delete the existing children and then recreate the required ones when a record is edited.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------