Discussions

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.

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.

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.

You can make a new field  to use as a DF.
ToText([my combined Summary text field])

Thanks you! this helps!

Having done this method, the dynamic filter lists all the possible values instead of each values separately. For example 

1. US ; Mexico
2. US
3. Mexico

unlike the filter for a regular multi-select field that would just list US and Mexico separately whether or not a record had both values selected. Is there a way to fix that?

no, you would need to use the filter box to search or else search at the detail level - the child table instead.

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
------------------------------

Original Message:
Sent: 07-12-2019 20:21
From: Russel Ndip
Subject: 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.