Stop a lookup field being modified in the child table when it is modified in the parent table?

  • 39
  • 1
  • Question
  • Updated 3 weeks ago
  • Answered
  • (Edited)
How can I stop a lookup field being modified in the child table when it is modified in the parent table?

I have two tables, Talent and Assignments (a one-to-many relationship). One Talent can have many Assignments. So for example, I have a freelance photographer called Joel in Talent. This table includes all the information about Joel - his email, phone, agency and also his daily rate.

When I add Joel to a job in Assignments it looks up fields from Talent and adds them to fields in Assignments. Among these is his daily rate so I know how much we paid Joel on that job.

My problem is that if Joel decides he wants to charge more money going forward, and I modify Joel's rate in his Talent record, in Assignments it now changes his rate in every job he's ever done because it's just looking up his daily rate from Talent. I don't want it to do this because what we paid him in the past must obviously reflect what his rate was then, not what it is now.

What is a way to handle this? Not having any luck so far.
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb

Posted 1 month ago

  • 39
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Yep, snapshot fields to the rescue!  Native easy peasy feature.

https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html

Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb
I've never used snapshot before but have heard of it. Interesting. I realize there is something that complicates things though....

What if I have a field that is not a lookup but is just a regular field. How could I snapshot that as well?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Typically you limit editing if specific field in a form using Dynamic Form rules.

Alternatively you can use Role permissions of you want to prevent editing of the record entirely under certain conditions.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
np
Change the name of the [Day Rate] field to be called [Day Rate (lookup)]

Then make a new field called [Day Rate] and set that to be a snapshot field of the [Date Rate (lookup)] field.

ie the snapshot field is a separate field from the lookup field.