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!
Qrew LegendOK 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.
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.
