Discussions

 View Only
  • 1.  Foreign Key other than ID of Parent

    Posted 03-12-2018 00:21
    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


  • 2.  RE: Foreign Key other than ID of Parent

    Posted 03-12-2018 02:06
    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.


  • 3.  RE: Foreign Key other than ID of Parent

    Posted 03-12-2018 02:20
    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.


  • 4.  RE: Foreign Key other than ID of Parent

    Posted 03-12-2018 03:18
    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.


  • 5.  RE: Foreign Key other than ID of Parent

    Posted 03-12-2018 03:24
    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.


  • 6.  RE: Foreign Key other than ID of Parent

    Posted 03-12-2018 03:31
    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.


  • 7.  RE: Foreign Key other than ID of Parent

    Posted 03-12-2018 09:26
    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.


  • 8.  RE: Foreign Key other than ID of Parent

    Posted 03-12-2018 09:28
    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.