Dynamically update master list of multi-select values from all child records on parent

  • 1.  Dynamically update master list of multi-select values from all child records on parent

    Posted 07-10-2018 19:44
    I have two relevant tables here, Parent and Child. Each has a multi-select Tag field, each field pulling its values from the same field, on a separate table. 

    What the user wants is to have the tag field on the Parent reflect a combination of all tags from each of its Child records, with of course no duplicates allowed. 

    In essence, this means that: 
    1. When a new Child is linked each of its tags is added to the parent, skipping duplicates
    2. When a Child is deleted each of its tags is removed from the Parent, excepting tags that already are coming from another Child of that Parent. 
    We are thankfully ignoring the case of a Child being edited after being linked to the Parent. 

    I have set up case #1, taking advantage of the fact that in my app users will be clicking a button to link the Child to the Parent. Within that button, I have a formula that separates out all Child tags, and then one by one checks them against the Parent, using the List() function to merge any values that aren't contained within the Parent. I was able to set up this function with a lot of help from the community here in fact. 

    Is there any way to set up Case #2 here, perhaps taking advantage of my setup for Case #1?

    What I was thinking might work is a hidden field on Parent that contains all tags from all Children ever linked, including duplicates. Maybe when a child is deleted one occurrence of each of its tags could be removed from the master list, and then the master list, sans any remaining duplicates, could be set as the new Parent Tags field. I have no idea how I'd set this up however. There are quite a few problems, and I may be on the wrong track entirely. 

    Frankly, I don't think this is natively possible, though obviously I could have missed something. I'd welcome any suggestions at all, I recognize that this is a bit of a mess.