Forum Discussion

MichaelSantiago's avatar
MichaelSantiago
Qrew Trainee
6 years ago

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

I'm attempting to make a training app with the following ERD: Training Materials has a Multi-select Text field called "Required Departments" that gets passed to Training Sessions child records ...
Quickbase Automations
Relationships
DonLarson's avatar
DonLarson
Qrew Elite
6 years ago
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
------------------------------
MichaelSantiago's avatar
MichaelSantiago
Qrew Trainee
6 years ago
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
------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    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].= [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
    ------------------------------
    • MichaelSantiago's avatar
      MichaelSantiago
      Qrew Trainee
      6 years ago
      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
      ------------------------------