Skip to main content
Question

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

  • January 31, 2020
  • 0 replies
  • 94 views
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 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
------------------------------
This topic has been closed for replies.

Forum|alt.badge.img+15
  • DonLarson
  • Registered
  • January 31, 2020
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
------------------------------
    Terms & ConditionsAccessibility statement