How to fix Key Field Issue in Table to Table Relationship

Question

Hi,&nbsp;My app has two tables: Colleague Calls, and Directory.&nbsp; I want to periodically update the Directory table but i can't use record ID as the Merge Field - and I'm not sure how to proceed. Background: We are managing covid leave of absences for colleagues.&nbsp; The colleague calls, we record their issue, then HR is able to input leave time into payroll based on a quickbase report. Often our colleague ID numbers were being incorrectly entered in the Colleague Calls table causing extra work. We fixed this by creating a Directory Table.&nbsp; We obtained and uploaded a complete list of all colleagues and their correct information (emp ID #, manager's name, etc. ) into a table (Directory) and we created the Table to Table Relationship: Directory -Parent, Colleague Calls-Child.&nbsp; The record number was the key field in the directory table when setting this up.&nbsp; It works great, when the colleague calls us, we can look them up in while in the intake form using a lookup field, and the directory fields are copied to our Colleague form fields for name, emp ID#,etc.&nbsp;​Now the problem -- I want to update the Directory Table on a routine basis to show changes to employment status, manager changes, job role changes, etc.&nbsp; I have an updated excel with directory information and wanted to merge the records in the Directory table based on the unique employee ID #, however, the key field for this table was initially set as Record ID number...and I don't want to loose the parent-child relationships that already exist.&nbsp;&nbsp;I copied the app just so I could test some options.&nbsp; I changed the Key Field in the Directory table from record ID to the unique Employee ID​ field.&nbsp; Now the lookup fields don't work.&nbsp;&nbsp;I am fairly new to quickbase and i don't want to accidently change or re-write the wrong information into existing records.&nbsp; Is there a way forward?&nbsp; How can I perform a merge/updates on the directory table? Do I have to delete the relationship or directory table and start over with emp ID as the key field?&nbsp; If I do, will I loose all of the related information already in the Colleague Table?&nbsp;&nbsp;I've searched the discussions for similar issues, but didn't find a solution.&nbsp;Thank you for your time.------------------------------Sheree Brown------------------------------

markshnier__you · Answer

The Directory is your list of Unique employees and they have a Unique Employee #. You can change the Key field of the Directory table to be the Employee number and Quickbase will retain the existing relationship and data connections.

It will give you two scary warnings, but just go ahead.

If you have never done this before, but I suggest you do is to make a copy of the app and make the change there just so you can get comfortable with what QuickBase is going to do. Then once you are comfortable make the change in your production app.

Then, when you go to upload a new Excel sheet with the updated employee list it will do e Merge for the existing employees which is exactly what you want and add new employees as required.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

shereebrown · Answer

Thanks Mark!&nbsp; &nbsp;The warnings were scary!I will change the key field in my copy app and see how it goes.------------------------------Sheree Brown------------------------------

shereebrown · Answer

Hi Mark,&nbsp;I changed my key field and completed the merge, all went well.&nbsp; However, with change in the key field, the reference proxy field in the child table form no longer works to look up the colleague in the Directory table.&nbsp; If I reset the key field back to record ID it seems to work.&nbsp;I assume i will need to change the key field before and after each merge?&nbsp; right? or is there a way to make this work and keep the key field as the unique employee id instead of record id?------------------------------Sheree Brown------------------------------

markshnier__you · Answer

No, you absolutely do not have to keep changing your key field back-and-forth!It might help me if you could post a picture of your relationship but if you look at the relationship between the employee directories and Absences, on the left side of the relationship will be the employee ID number as the Keyfield of the Director a table. So on the right side the corresponding field that anchors the relationship should be named the same thing something like employee ID number.That is the field that you should put on your absence form so that when entering absences manually the users can choose an employee. &nbsp; You will want to make sure that the fields you were offering to users to help the users pick the correct employee is listing useful fields the most important among them probably being a calculated field of full name, their first and last name.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

shereebrown · Answer

Ah ha,lightbulb!&nbsp; All is working now. Thank you again! You are always so helpful.​------------------------------Sheree Brown------------------------------

Forum Discussion

How to fix Key Field Issue in Table to Table Relationship

5 Replies