Foreign Key other than ID of Parent

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • In Progress
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)  Do you know if this is possible?  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.  The app has multiple existing child tables that I would like to leave in tact
Photo of Christine

Christine

  • 332 Points 250 badge 2x thumb

Posted 9 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 52,908 Points 50k badge 2x thumb
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.
Photo of Christine

Christine

  • 332 Points 250 badge 2x thumb
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.  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".   This table would act as a lookup to the child table maybe using a Quickbase Action to generate new SO.?  The Foreign key table would have the Sales Order Number as the Key and the Record ID as a look up field.  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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,908 Points 50k badge 2x thumb
I'm not really seeing the need to go though that extra effort.  In my experience if you have the correct field for [related parent] it should work just fine.
Photo of Christine

Christine

  • 332 Points 250 badge 2x thumb
This is an established app with 15+ related tables.  I want to add a few new tables and thought the Foreign Table could perform the VLOOKUP automatically.  I can then keep the Record ID as the key and prevent any orphan records if a SO# happened to be edited.
Photo of Christine

Christine

  • 332 Points 250 badge 2x thumb
I did run into an issue when creating summary fields using this method:  We are sorry -- at this time, you cannot create a summary field using a relationship where the reference field is a lookup field.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,908 Points 50k badge 2x thumb
OK, so that is a real issue.

A work around us to have the reference field be a field which is not a lookup field but is a data entry type field and has its value maintained by an Action.

If there are existing relationships that you don’t want to rebuild, then you can make the reference field a formula field and have the formula be just equal to a field maintained by an Action.

You will need to populate the field manually to get the values initialized, using a table to table copy or export import using excel.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,908 Points 50k badge 2x thumb
I’m not saying your method won’t work. I have not really fully thought it through. I’m assuming your method would need to also use Actions to stay in sync.