Discussions

Expand all | Collapse all

Automatically Add Records in Associative Table based on Matching Values in Non-Key Field

  • 1.  Automatically Add Records in Associative Table based on Matching Values in Non-Key Field

    Posted 01-31-2020 12:01
    I'm attempting to make a training app with the following ERD:
    Training ERD
    Training Materials has a Multi-select Text field called "Required Departments" that gets passed to Training Sessions child records via lookup field ("Training Materials- Required Departments").

    Meanwhile, an identical Multi-select Text field exists in the Employees table called "Department(s)".

    My goal is to automatically add multiple Training Attendance records when a Training Session record is created, for each employee that has one or more "Department(s)" that overlaps with any of the "Training Materials- Required Departments" from my Training Sessions record.

    That is, automatically assign the same Record ID# (from the newly created Training Session) in "Related Session" and associate each corresponding Employee in the "Related Employee" for each of the automatically created Training Attendance records.

    I've tried both the native "Quickbase Actions" and "Quickbase Automations" but neither was capable of querying my Employees table, comparing it to my Training Sessions table, and adding a Training Attendance record to connect the two, for each Employee that has a Department included in the Required Department fields.

    I think the solution may lie somewhere between a Webhook and Formula URL. Any thoughts are greatly appreciated!

    ------------------------------
    Michael Santiago
    ------------------------------


  • 2.  RE: Automatically Add Records in Associative Table based on Matching Values in Non-Key Field

    Posted 01-31-2020 12:43
    Edited by Don Larson 01-31-2020 13:14
    Michael,

    Two things: 

    1) There is rumor that the new "Pipelines" can do this.  However to solve it today, I highly recommend Juiced Triggers.

    https://www.juicedtech.com/triggers

    It has a great Search feature that will do this quickly, it is all form driven and really spectacular.  I literally end up with hundreds of their Triggers in applications.


    2) I urge you to change the architecture and kill the multi text fields.   They are prone to failure and user error particularly trying to match them in two tables.   "Operations"  is not "Operation " Here is a potential solution:


    You now have one source of truth for the Departments and can use it anywhere.

    If the Operations department becomes Operation Control, you edit one record and nothing breaks.  Just drive everything from the Related Department field whether it is directly the parent or a Look Up field.




    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 3.  RE: Automatically Add Records in Associative Table based on Matching Values in Non-Key Field

    Posted 02-03-2020 15:27
    Don,

    Thanks for the suggestions- I'm not familiar with Juiced and looking into their solutions now. I ditched the multi-select fields and normalized with a few more tables (also added Department Training Materials for potential material sharing among depts).

    Sadly, this still doesn't provide a way to natively add multiple attendance records and assign each employee with a corresponding "related department" automatically when a new session is added. The challenge is that even with the new architecture, Employees and Departments are not directly related, due to the M:M relationship (i.e. Department Employees) so I can't leverage a Related Department field. I registered for the pipelines webinar this Wednesday hoping that it will address this snag.

    If I could somehow run a API_DoQuery on the Department Employees table and then API_ImportFromCSV based on the query results, I think I could return related employees and add a new record in my attendance table for each. I just don't know exactly how to pull it off and if a Formula-URL or Webhook would be capable of the sequence of events. Any thoughts on that?

    Thanks again for your help!

    ------------------------------
    Michael Santiago
    ------------------------------



  • 4.  RE: Automatically Add Records in Associative Table based on Matching Values in Non-Key Field

    Posted 02-03-2020 16:57
    Michael,

    Do you write PHP Scripts?   I am not a proficient coder but I agree entirely with your idea.   The query is extremely simple from a logic standpoint

    GET
    [Related Employee].[Department Employees]  WHERE
    [TM Related Department].[Training Sessions]= [Related Department].[Department Employees]

    Your import is just the variables for 

    [Related Training Session], [Related Employee]

    If you can write this and have a place to host that script, I am suspicious that you will have less than 100 lines of script.

    The Juiced tool solves this because they host the tool and you only have to know the boolean logic and not any script or syntax.


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 5.  RE: Automatically Add Records in Associative Table based on Matching Values in Non-Key Field

    Posted 02-04-2020 08:29
    Interesting... I've dabbled in PHP but far from an expert. I'll hold out for the webinar tomorrow and explore Juiced if Pipelines doesn't cover it.

    Thanks again.

    Michael Santiago

    ------------------------------
    Michael Santiago
    ------------------------------