Forum Discussion

RusselNdip's avatar
RusselNdip
Qrew Trainee
6 years ago

Create Automation that adds value to multi select field if it doesn't exist

I have a product table and a certification report table. They have a one to many relationship where products can have many certification reports. In my product table there is a field "Product certifications" that is a multi-select field. This field is populated by a list that is identical to a multiple choice field in certification report table called "certification name". So essentially "Product certifications" should be an aggregate for all the certification names in the certification report table for that specific product. I want to automate this process

I want a way to automate that for every record I add or modify in the certification reports table, I want the certification name from the record to the "Product certifications" field to add (not replace) to that field if it doesn't already exist.
Formulas and functions
Quickbase Automations
Relationships
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    A far better way is simply to create a Combined text summary field on the relationship to summarize up all the certification names from the child tale up to the parent.
    • RusselNdip's avatar
      RusselNdip
      Qrew Trainee
      6 years ago
      The reason I wanted to try and avoid this method is that I need that field to still be a dynamic filter, and I've noticed that summery text fields don't have that ability.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      You can make a new field  to use as a DF.
      ToText([my combined Summary text field])
  • DavidPontier1's avatar
    DavidPontier1
    Qrew Cadet
    6 years ago
    I found this thread when I ran into the same problem.  I found a solution.

    You need to create two temporary text fields.  The first is a multi-select text field field in your parent table that has the same choices as your certification list.  The second is just a regular text field.

    When you add a new report, set the first multi-select temporary field equal to the new certification.  You can do this through an automation or code it in the button you use to save the report or return to the parent table.  When you do this you over right the old value that was in that field instead of adding to the multi-select.  Which is why we had this problem in the first place.

    Then create an automation that triggers every time this first temporary field changes.  Also include in the trigger that Main multi-select field that you want to updated dose not contain the value in the temporary field.  Multi-select fields can only be compared to other multi-select fields which is why this first temporary field has to be multi-select.  This check will prevent the automation from running if the certification already exists in the main multi-select field.

    Then for the action choose to modify the second text field.  Choose the selection to both type and copy data.  This option is not available for Multi-select fields otherwise you wouldn't need this intermediate step.  First select your first temporary field, then type a semi colon and then select your main multi-select field.  This will create your new full list in the text field.

    Then you need a second automation that runs every time your second temp text field changes.  Basically the first automation will trigger the second one.  For the action of this automation you want to set your Main multi-select field equal to the second temp text field.  I tried to combine these two actions in the same automation, but because QB tracks the old and new values, it doesn't recognize the new value of the second text field until after the whole automation is completed.

    Hope this helps.

    ------------------------------
    David David
    ------------------------------