Updating Fields in table when new record is created in another table

Question

Hello,&nbsp;
I am looking to set up a trigger to update several fields in my Employee table when a new record is created in my Position History table. My Position History table holds records for current and previous positions for the related employee. The fields I'm looking to update in my Employee table are department, unit, location, and position. Essentially, I would like the most recent position record added to act as the employee's current position.&nbsp;
I have a relationship set between the two tables and have tried adding Quickbase actions to modify the employee record, but nothing seems to be working.&nbsp;

Please see screenshot of my table set-up.&nbsp;------------------------------Erika Brown------------------------------

markshnier__you · Answer

Erika,
In this use case I don't feel that all the fancy tools like Pipelines, jinja,&nbsp; webhooks,&nbsp; Automations Actions, or Formula Queries and all that jazzy stuff&nbsp; is the way to go. The right way to go here is old school Relationships and in this case&nbsp; building a Reverse Relationship. The advantage of this is that it will work on all your historical information and will be rock solid and they can never fail to fire.&nbsp;&nbsp;

OK here goes. We have a relationship where one employee has many Position Histories.
Summary field of the maximum of the Effective Date of the position history (I'm assuming Position History has such a field..&nbsp; Call it [Date of Most Recent Position].
Look that field up down to all the Position History records. Great, now each Position History record has the opportunity to know if they were the lucky one that is the most recent effective date.&nbsp;

Make a new summary field on the relationship for the maximum of the Record ID subject to the filter that the [Date of Most Recent Position] equals the [Effective Date].&nbsp; &nbsp;Call it [Record ID# of Most Recent Position History]
Great, now make it a new reverse relationship where one position history has many employees. Yes I know it sounds backwards and that's why we call it a reverse relationship. When building the relationship when you go to choose the reference Field choose the field [Record ID# of Most Recent Position History.
Now simply look up all the fields that you need from that most recect position history into your employee record, such as department, unit, location, and position.
------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

personal_taxtea · Answer

This should be possible with actions, you need to set an action from the Position History table so that when a new record is created it copies the value from the relevant fields in that table into the relevant fields in the parent record. Maybe if you can upload a screenshot of the action that's not working I can see if that's been set up incorrectly.

You could also try to use a Pipeline, but as long as you don't already have 10 actions connected to that table, they should work. Make sure the action is enabled as well, when you see the list of actions for a table, make sure it has a green tick next to it.&nbsp;------------------------------Personal Tax Team------------------------------

erikabrown · Answer

Mark, you are amazing! I followed your steps and the records are pulling exactly how I need them to. Big thank you for your guidance and quick response!! I was stuck on this for hours!

------------------------------
Erika Brown
------------------------------

miketamoush · Answer

Mark,&nbsp;
This is a fantastic idea. I had a similar situation and instead I ended up making an absurd amount of summary fields:
[Most Recent Field 1] = Summary field where [Date of most recent]=[effective date][Most Recent Field 2] = Summary field where [Date of most recent]=[effective date][Most Recent Field 3] = Summary field where [Date of most recent]=[effective date]
I continued this like 20 times.
If I had done your method, I would have 1 simple summary field, and everything else would be lookup fields, and lookup fields are a lot quicker and neater than the mess of summary fields.
Great tip!
------------------------------Mike Tamoush------------------------------

Forum Discussion

Updating Fields in table when new record is created in another table