Relationships break when key field is changed.

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Our Key Field in our Parent table is of course a field that will always be unique. However, it is also a field that is likely to change (still being unique). When our Key Field changes in our Parent Record, any relationships to child records are then broken.

For example:

"Table A" has a relationship to "Notes" table.

"Record 1" has a note attached.

When the key field of "Record 1 "changes after a note is already created, the relationship between "Record 1" and the note is broken. The note then shows (unknown record) in the reference field.

Is there a way that when updating the keyfield of the parent record, the reference field of the child record updates as well?
Photo of Lori

Lori

  • 50 Points
  • concerned.

Posted 2 years ago

  • 0
  • 1
Photo of Harrison Hersch2

Harrison Hersch2

  • 400 Points 250 badge 2x thumb
This is intended. Say that you have an employee record and the key is the employee's SSN (which is unique). You proceed to add performance reviews, pay history, etc. On each of these child records, the SSN is actually being written as a foreign key. If you subsequently realize the SSN was incorrect and you update it on the parent, the join no longer exists basically.

As far as getting around this, you could do something with a custom script in JavaScript to have a change process or perhaps QuickBase Actions can address this when it is production.

What is the reason though you are using an alternate key? Sticking with the RID is always recommended for maximum support and to avoid these types of things. There is a fairly limited subset of requirements that genuinely necessitate using a key such as a custom data field, composite, etc. Perhaps there is a way we can work around that?
Photo of Lori

Lori

  • 50 Points
We have this field in order to create automatic relationships when we upload an excel sheet in a separate table. This was the only way we could match up a field in our secondary table with our parent table. If we can't have any child records automatically update we'll have to scrap the project. If javascript could automatically update all child records in different table then that would solve our problem.
Photo of Harrison Hersch2

Harrison Hersch2

  • 400 Points 250 badge 2x thumb
Got it. Yea this is the most common reason of doing this. One route that is more scalable is a custom JavaScript importer that does these lookups for you.
Photo of Lori

Lori

  • 50 Points
Interesting. We'll look into that.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
If you really need to make the switch, here is a way you can do that.

Lets assume you are switching it back to [Record ID#].  And for this example your parent table is "Parents" and the child is "Children."

For any and all of the child tables, you will need to make a report. It will have 2 columns;
[Record ID#] (assuming this is the key field for that table)
and
[Related Parent Record ID#]  (You may need to pass this value, or your new key field, down in the relationship as a lookup field prior)

Then you will save all those records as a spreadsheet / csv files to your computer.

Then after you have saved all the "Child" spreadsheets with what your new planned Key Field will be, you can make the switch in the parent table of the key field.

Then rebuild the relationships as needed.

Then run and import that will update those records with the correct [Related Parent Record ID#]

The import line up will only need the 2 columns.
[Record ID#] > [Record ID#]      This action will prompt a warning, as you are about to update existing records.
[Related Parent Record ID#] > [Related Parent]

Note: you will want to do this when nobody is using the application as, data modifications, or new records added after you save the csv files will not be connected properly.


Good luck, and ask if you have other questions.
Photo of Harrison Hersch2

Harrison Hersch2

  • 400 Points 250 badge 2x thumb
Are you trying to account here for linking the children records back to the parent when the key is changed? QuickBase handles this for you and it is actually pretty good at it. You might need to address external influences manually (i.e., API calls) but native QuickBase stuff will convert. Still a good practice to make a copy of the app though prior.
Photo of Lori

Lori

  • 50 Points
The problem with this is we will need it to happen automatically. There are records in multiple different tables that are related. Harrison Hersch2 We're not changing our key field to another key field, but the content of our key field is updating.
Photo of Harrison Hersch2

Harrison Hersch2

  • 400 Points 250 badge 2x thumb
Right. This part of the thread was about changing your key field back to the Record ID# to alleviate this issue.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Lori Contacted me off line about this, so I took the time to think it trough and this was my response as to how i would do it.

Here is what I suggest.

I assume that the Key field is based on a calculated field and then there is a form rule to change the Key field to the value in the calculated field, as of course, an actual Key field may not be a formula.


I would have a form rule that says
if there are children records then make those key field component fields non editable.


Then when the user needs to edit those fields they would push a button and re-display the record a super mini form with just those Key field components (still non editable and three new field for the new values. The user would save and stick on the mini form.


Then the user would push a formula URL button and like Jean Luc Picard would say "Make it so"


The formula URL button would mass update  all the child records, in all tables for that parent record, and update the parent record's key field components, and then redisplay on the regular form which i assume shows the child records on the parent record form as embedded tables.  Then the user could observe that the children are still attached and know it worked..


I can do this with native QuickBase, not javascript,  and it depends on how many child tables there are, but it's probably a 3 hour project.


it is also possible that it can be done with webhooks, but even if it could, i would still use my method as its more "step wise" and deliberate and risk free and in a sense safety netted by the user observing that every looks OK..
(Edited)