Erika,
In this use case I don't feel that all the fancy tools like Pipelines, jinja, webhooks, Automations Actions, or Formula Queries and all that jazzy stuff is the way to go. The right way to go here is old school Relationships and in this case 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.
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.. 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.
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]. 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
------------------------------
