Change a record in one table when another table's record updates

Question

Hello!
I have a table called "Topic Repository" that lists out a training topics and how often we need to train on that topic. For example:

Topic Being Trained
Training Cadence Required (Contract Requirement)
Date of Most Recent Training

HIPAA
Monthly
X/XX/XXXX

Hospice
Quarterly
X/XX/XXXX

Diversity, Equity &amp; Inclusion
Yearly
X/XX/XXXX

I have another table (in the same app) called&nbsp;"Training Records" that logs different trainings. For example:

Training Title
Date of Training

Hospice
12/14/2023

Hospice
2/3/2024

HIPAA
8/10/2023

HIPAA
10/10/2023

I would like to create an automation/webhook/formula field, so that when a new record is added in "Training Records", that record's&nbsp;Date of Training&nbsp;it will update Date of Most Recent Training in "Topic Repository". It needs to only change this field if it's the most recent date.

One training topic in the repository can have many training records.

Does anyone know I can do this? I've watched so many videos online and have tried Webhooks, but am not sure how to execute.

Thank you very much!
------------------------------Kim Nguyen------------------------------

markshnier__you · Answer

I am hoping that you have set up your application properly so that there is a relationship where one Topic Repository has many Training Records.&nbsp; If so, then easy, just go to that relationship&nbsp; and create a summary of the "Maximum" of the Training Date field called [Date of Most Recent Training].

If you do not have a relationship between those two tables but rather the training topic field in Training records is a shared multiple-choice field then you need to make a relationship. Post back if you want some help with that. It's quite easy actually.&nbsp;&nbsp;------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

kimnguyen · Answer

Hi Mark,

Thank you so much for your quick reply! I was not able to build a relationship because

One Topic in the "Topic Repository" can have multiple trainings in "Training Records"
and One Training in "Training Records" can have multiple Topics in "Topic Repository"

For example, a training done on 1/1/23 can cover Topic A, Topic B, and Topic C
While Topic A can have trainings that was done on 1/1/23, 2/1/23, and 3/1/23

I wasn't able to do a circular relationship (if that's even possible) because I could only reference one parent per child.

Currently, the Topics field in "Training Records" is a multi-select with values pulled from the other table.

Is there a way I can pull the Date of Most Recent Training as a Formula Date Field?

Thank you again for your reply------------------------------Kim Nguyen------------------------------

markshnier__you · Answer

Can you tell me how many topic repository records you have and how stable they are? I have a solution for you if they're fairly stable and you don't have a ridiculous number of them.&nbsp;
But right now I need to jump on a spin bike. Back at the computer later this evening.------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

markshnier__you · Answer

The right way to set this up from a database design point of view is to have a middle "join" table called perhaps Topic Trainings.&nbsp;

When you set that up, initially it has no fields other than the 5 built in fields.

Make a relationship or one Topic Repository has many Topic Trainings.&nbsp; Look up the Topic and set the Related Training Topic field to have a Proxy field of the Training Topic if the Key field of Training Topic Repository is [Record ID#].

Make another relationship where one Training Meeting has many Topic Trainings.&nbsp; Set the field for Related Training meeting to be some identification field (maybe the date or a concatenated field of the date and the dept being trained) from that meeting that you look up into the joint table.&nbsp;

If you set up your application this way you have endless possibilities for reporting, including a super easy way to get the date of the most recent training up to the topic repository.&nbsp; That would just be a look up of the meeting date down to the Join table (Topics Trainings) and then a summary maximum field up to the Topic Repository table of the maximum of that meeting date field.

It would be possible with some data manipulation to coax your existing data in your multi select field into that join table.

I thought about whether it would be possible to use a formula query but unfortunately at Formula Query has a function to SumValues for a set of records but unfortunately there is no function for&nbsp; MaxValues at this time.

The only other way I can think to do this would be to create a helper table with a single record with Record ID of 1. You would join that to all of the Training Meetings&nbsp; with a related helper field with a formula of 1. Then you would run a whole bunch of summary fields up to that helper record looking for the Maximum training date which contains "HIPPA". Then another summary field for the Maximum training date where the multi select field captains "Hospice", Etc. etc.&nbsp; for each Training Topic.

You could look up those dates down to your topic repository table and then have a long IF statement to check the Training Topic and return the date of the last training. The problem of course is that every time a new training topic is added, you have to do actual programming to create a new summary field and a new look up field and adjust your IF statement. That's why the route of a proper database design with a middle join table is preferred.&nbsp;
&nbsp;&nbsp;------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

kimnguyen · Answer

Wow Mark.... Thank you SO very much for taking the time to answer. I've read and re-read your instructions about 100 times and am stuck.&nbsp;I understand that I need to create a "middle man" table. I created the middle table:

Repository
Middle Table
Training Logs

Here is how I have it set up per your instructions. The only inputting that happens is from our trainers, who will input training logs into Table 3 (Training Logs). There are a bunch of topics already uploaded into Table 1 (Topic Repository). When a trainer inputs a training log, what do I want to happen? I am confused about the proxy fields.
Thank you again for your assistance.------------------------------Kim Nguyen------------------------------

Topic Being Trained	Training Cadence Required (Contract Requirement)	Date of Most Recent Training
HIPAA	Monthly	X/XX/XXXX
Hospice	Quarterly	X/XX/XXXX
Diversity, Equity & Inclusion	Yearly	X/XX/XXXX

Training Title	Date of Training
Hospice	12/14/2023
Hospice	2/3/2024
HIPAA	8/10/2023
HIPAA	10/10/2023

Forum Discussion

Change a record in one table when another table's record updates