Syncing tables across bases--help needed

[Scott_Hoek]

Say I have a base with a table that has a parent/child relationship with itself.  Projects with parent/child projects, Tasks with subtasks, Contacts with links to a fiance or manager – there's a lot of use-cases for linking a record to another record in the same table.  The problem I'm running into is that two-way sync doesn't allow you to edit linked records to the same table.

Linked records in two way sync is so great, and it fixed soooooo many things in my workflow – but I can't figure this one out.  Links to the same table are not editable in the target base as far as I can tell, but I really really want to be able to make changes to parent/child relationships in a target base.

The only way around this I've been able to think of is to make a second table to handle the relationship, probably like this:

It's a little ugly in the data view, but it works well enough in interfaces in the target if you use a view and not a field:

This approach works fine, except for where it doesn't.  Here are the main issues:

• It only works if the relationship is hierarchical (parent/child) – if I wanted to have 2-way link that isn't a hierarchy, such as a "fiance" column in a contacts table, the way I'm handling the interface wouldn't work because there's effectively two fields and the Fiance could be in either one.  Bad UX.
• We lose the ability to drag items into their parents in list views like we can with a true link to the same table
• We lose the power of date dependancies which do not work with lookup fields, which makes my workaround pretty much a non-starter for tasks/subtasks or similar workflows.

Anyone have any thoughts for other workarounds that could handle these problems better?

[Eric_L]

Short of text fields editable by automation only that act as a relationship manager, I can't think of another solution. I think what you have is the most efficient and clear.

[chloe-elizabeth]

Yes this is the exact same issue I'm having, and I'm surprised I can't find other topics on it.

I would love to hear if there are any other workaround suggestions as I'm having the 'tasks/subtasks workflow' issue. I'm also trying not to overload my org with automations that are hard to fix after I'm gone, but I guess that's secondary to actually getting this working.
The thought of creating a second table just to handle the relationship links, and then having to sync both tables just to keep those links is very frustrating.

[Scott_Hoek]

The other way to do it that I can think of is to use a helper text field.  This is somewhat convoluted, but the benefit of it over the secondary table would be that you don't lose hierarchy.  Here's how I would set it up:

Source Table:

• Linked Record Field (to self)
• Text field

Target Table:

• Linked Record Field from target
• Text Field from target
• Local linked record field that is duplicate of field from target, but editable.

Setup:

In the target base:

1. Dupliate the linked record field from the source table in the target table to create an editable field in the target base
2. Make two "last modified time" fields; one for the synced linked record field, and one for the local linked record field
3. Make a "last modified by" field for the local linked record field
4. Set up an automation in the target base that triggers when either linked record field is updated
   1. If local linked record field modified time is after synced linked record field and last user to modify local linked record field is not automations, set the value of the helper text field to the value of the local linked record field
   2. If synced link record field modified time is greater than local linked record field, set the value of the local link record field to the value of the synced linked record field

Then, in the target base: 

1. Make an automation that runs when helper text field is not empty. 
   1. Set the value of the linked record field to the value of the helper text field
   2. Clear the value of the helper text field

Before I tell you to try this, two caveats: 1) I hate this, and 2) I haven't tried it this way, this is super theoretical and you may wind up with some issues that I didn't think of. 

I did try something similar but slightly different where I had an automation like I describe for the target base in both bases, but I had a few cases where if the value of the linked record was cleared and then set in the source base, the sync delay would case the target base to clear that value after it got set, so we lost the data.  This actually nearly caused us to double book an event on a date here, so proceed with caution, I guess. 

My feeling is that despite the downsides, using an extra table to manage the relationship is probably the best bet, especially in a parent-child relationship you're describing.  I hate relying on automations for this because there's just too many ways it could go wrong and cause you to lose data. 

That said, if you give this a go, please report back with how it works and any tweaks you make to fix it up!

Good luck, god speed ❤️