Child tables with different foreign keys for the same parent

Question

I have found a couple of discussion about this, but not exactly my situation.&nbsp; 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.&nbsp; Each child only has one of those fields.&nbsp; 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.&nbsp; 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)?&nbsp; 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.&nbsp; &nbsp;Am I missing an easier way to do this?&nbsp; This situation doesn't seem like it would be that unusual. Any help is appreciated.------------------------------Tate Forgey------------------------------

blakeharrison · Answer

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/
------------------------------

tateforgey · Answer

Thanks so much for the reply, Blake.&nbsp;
Admittedly, I'm late to the game with Pipelines, but I want to make sure I understand what you are saying here.&nbsp; Would the Pipeline you are envisioning still require a relationship to an alternate parent table?&nbsp; 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.&nbsp; If it does, then what is the value of using an automation?&nbsp; Is it just more reliable?&nbsp;&nbsp;
Again, sorry to be behind the times with Pipelines.&nbsp;&nbsp;------------------------------Tate Forgey------------------------------

markshnier__you · Answer

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].&nbsp;
Make a relationship back to the Parent table with Connected Sync Table as the Parent with Field B as the Key field.&nbsp; 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.&nbsp; 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.&nbsp;&nbsp;

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.&nbsp; &nbsp;It's a bug and has been there for a pretty long time.&nbsp; 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&nbsp; table records&nbsp; is missing it's original parent child.&nbsp; &nbsp;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.&nbsp;

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 ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

tateforgey · Answer

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

markshnier__you · Answer

OK let us know how you make out or if you get stuck.&nbsp; 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.&nbsp;------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Child tables with different foreign keys for the same parent

Related Content

Using GetRecords to query a table in a different application?

Add button to create record in different app

Merging Two Completely Different Tables

Moving Fields from One Table to Another

Table is missing