Discussions

Expand all | Collapse all

Child tables with different foreign keys for the same parent

  • 1.  Child tables with different foreign keys for the same parent

    Posted 12-19-2020 12:54

    I have found a couple of discussion about this, but not exactly my situation.  The closest is THIS. I'll state my issue since it is a little different than the original, but the basic problem is the same, I think.

    I have an app that will have three connected tables, one parent and two children. The data cannot be modified or augmented as they come from three different systems. The parent has two fields that could serve as a key.  Each child only has one of those fields.  How do I make these relationships?

    I should stop there, but I have some ideas.

    For one of the child tables - no problem (Coach!), it can use the parent field selected as key.  For the other, it seems like something needs to be done to get the parent key on it. How do I do that in the easiest and most flexible way (e.g., I need to be able to create summary fields in that second child's relationship, which can be restricted if the relationship is based on a lookup field)?  Do I make an entirely separate connected parent table refreshing at the same time and make the other potential key field the key for that alternate parent table, then relate that to the second child on the alternate key filed, then create an automation to write the first key field to a text field on the second child through that relationship?

    That seems crazy, but I need records from both children tables to appear on one parent record and I'm not sure how else to do that and make sure I can create summary fields, etc.   Am I missing an easier way to do this?  This situation doesn't seem like it would be that unusual. Any help is appreciated.



    ------------------------------
    Tate Forgey
    ------------------------------


  • 2.  RE: Child tables with different foreign keys for the same parent

    Posted 12-19-2020 13:00

    If the data cannot be modified, I would suggest using Pipelines to search the Parent table for the key data that the Child record has access to. Once that record is found, you can then update the Child record with the appropriate "Related Parent" value.



    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------



  • 3.  RE: Child tables with different foreign keys for the same parent

    Posted 12-19-2020 16:28

    Thanks so much for the reply, Blake. 

    Admittedly, I'm late to the game with Pipelines, but I want to make sure I understand what you are saying here.  Would the Pipeline you are envisioning still require a relationship to an alternate parent table?  If not, that seems like an expensive query to look through every parent record looking for a value in a field that is not its key.  If it does, then what is the value of using an automation?  Is it just more reliable?  

    Again, sorry to be behind the times with Pipelines.  



    ------------------------------
    Tate Forgey
    ------------------------------



  • 4.  RE: Child tables with different foreign keys for the same parent

    Posted 12-20-2020 15:05
    Edited by Mark Shnier (YQC) 12-21-2020 19:46

    Tate,

    You plan is to have [Field A] on the parent record table to be the key field. But as you say, that only solves half of the problem.

    An option is to make a Connected Sync Table of the Parent table but then change the key field on the Connected Sync table to be [Field B]. 

    Make a relationship back to the Parent table with Connected Sync Table as the Parent with Field B as the Key field.  Now you have a new Parent Table with [Field B] as the Key to make that the Parent to other other child table that knows [Field B].

    Now you can lookup any field that you need down to the Child Tables.

    I am guessing that you need summary fields as well.  Post back if you get stuck there but you should be able to make your summary field for the second Child table up to the Connected Sync table and then get that summarize back up to the original parent table.  

    Now, there is one no one flaw in this plan. When you have a connected sync table and you sort of cheat by changing the key field there is a known bug that I don't think has been fixed yet that if the value in that field be in the original parent table changes it will fail to update the connected sync table.   It's a bug and has been there for a pretty long time.  What are you do is set up a safety net report of parent table records missing it's connected table parent and perhaps vice versa for connected  table records  is missing it's original parent child.   When that happens what you do is change the settings in the connected table to add records but not delete, then manually delete all the records, and then change the setting back to mirror parent table exactly and manually refresh. 

    The whole set up is really a big cheat workaround in order to change the key field of the original parent table.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Child tables with different foreign keys for the same parent

    Posted 12-21-2020 19:38

    Wow - that's some deep QB knowledge right there.  I think I will give that a shot.  Thanks!



    ------------------------------
    Tate Forgey
    ------------------------------



  • 6.  RE: Child tables with different foreign keys for the same parent

    Posted 12-21-2020 19:50

    OK let us know how you make out or if you get stuck.  I ran into the situation with the client where they essentially head two Key fields for the same table so this was our work around.

    I also seem to recall now that as far as the bug goes we ended up setting the table to add and update records but not delete (as opposed to mirror exactly) . I think that was a workaround to the bug because the only purpose of that mirror Sync table was to provide a way to get to the alternate key field. 



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Child tables with different foreign keys for the same parent

    Posted 12-22-2020 15:55

    Instead of using the alternate parent / lookup table as a bridge to move summary fields to the original parent, I'm thinking of just creating an automation to write the key to the original parent looked up through that relationship to a regular text field to use as the refence.

    It seems like doing that would mean I still won't have the summary field restriction with lookup fields used for a reference and that has the advantage of being able to see reports of the different children tables on the parent instead of just summary fields.  I think that will work, but are we afraid of missed automations or something?  It is another moving part, but it seems worth it to me. 



    ------------------------------
    Tate Forgey
    ------------------------------



  • 8.  RE: Child tables with different foreign keys for the same parent

    Posted 12-22-2020 16:03

    Typically with automations it is a good idea to build the Safety net process in case an automation does not fire for some reason.

    You can either do it via report or you may even be able to run a scheduled overnight (via Automations) saved table to table import each night to trigger an automation which would be the equivalent of the original automation but only run for records that are missing that field.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Child tables with different foreign keys for the same parent

    Posted 12-22-2020 16:12

    Awesome tips.  Thanks, as always.



    ------------------------------
    Tate Forgey
    ------------------------------