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.
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.
Qrew LegendTate,
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
------------------------------
Wow - that's some deep QB knowledge right there. I think I will give that a shot. Thanks!
