Foreign Key other than ID of Parent

Question

I would like to create a foreign key on a child table using a field other than Record ID, but retain the Record ID for the Parent table (to not disable existing relationships)&nbsp; Do you know if this is possible?&nbsp; I am using Expense data from another application and would like to use the Sale Order number rather than the Record ID to eliminate the need for VLookups.&nbsp; The app has multiple existing child tables that I would like to leave in tact

quickbasecoachd · Answer

Quick Base only allows 1 Key field per table but it does not have to be the Record ID# field.

I suggest that you make a copy of your app and then practice changing the Key field. Quick Base will give you a couple of scary warnings but bin fact it will preserve the connections to the child table data.

Typically you just need to then check the child table form to ensure that the correct field and Proxy is set for the forms to set [Related Parent]. QuickBase typically automatically corrects the [Add Child] URLs formula fields as well as well.

Once you have confidence, then change the Key field on your live app. I don't do the practicing any more on a copy of the app because I know it works, but for the first time it's not a bad idea.

But it is always a good idea to make a copy of your app before you make dramatic changes like that to the app and then make that change "off hours" from your user base like a weekend or evening.

christinewhite · Answer

Thank you, I have changed the key field in other applications, but limited the user roles that had access to edit the key field as a safeguard.&nbsp; Since there is the danger of disconnecting child records when the key field is editable, I was thinking of creating a read only "Foreign Key Table".&nbsp; &nbsp;This table would act as a lookup to the child table maybe using a Quickbase Action to generate new SO.?&nbsp; The Foreign key table would have the Sales Order Number as the Key and the Record ID as a look up field.&nbsp; The child table could be related to the Foreign key to add the Record ID or the Sales Order in the main table...Haven't tried it yet.

quickbasecoachd · Answer

I'm not really seeing the need to go though that extra effort.&nbsp; In my experience if you have the correct field for [related parent] it should work just fine.

christinewhite · Answer

This is an established app with 15+ related tables.&nbsp; I want to add a few new tables and thought the Foreign Table could perform the VLOOKUP automatically.&nbsp; I can then keep the Record ID as the key and prevent any orphan records if a SO# happened to be edited.

christinewhite · Answer

I did run into an issue when creating summary fields using this method:&nbsp; We are sorry -- at this time, you cannot create a summary field using a relationship where the reference field is a lookup field.

Forum Discussion

Foreign Key other than ID of Parent

Related Content

Add child record through parent form and redirect back to parent form in edit mode

Totaling Child Records Live on Parent Form

Many parent records on a single child record

Pipeline to add Parent record from child import

Parent table with custom key from a formula