Using a Lookup Field to update another record in the same table

[jaybushman]

Hi. I'm trying to figure out how to use a Lookup Field to update another record in the same table. 

This is a simplified version of the workflow to illustrate what I'm trying to do:

I've got a number of records, called "Sequences." I need to define the relationship between them, and track which one comes next and which one comes before. In the image above, those relationship are defined manually. 

In the below image, I've replaced the manual versions with Lookups:

What I'm seeking to do is this: when I specify the record for the Next Sequence, have that Sequence's record automatically update it's Previous Sequence field with the corresponding record. For example in the below image, in record Sequence 4, when setting Next Sequence to 5 - I'd like for the record Sequence 5 to automatically update the Previous Sequence field with the record Sequence 4. 

 

Is this even remotely possible using some configuration of Lookup Fields? Or automation? I'm at a loss.  

Thanks a lot!

[TheTimeSavingCo]

Yeap, try this:

Link to base

[jaybushman]

Thanks!  I tried to replicate the Automation you built, but it's giving the error: "Received invalid inputs." I think I've copied everything in the automation and config correctly. 

Any idea what I'm missing?  

[TheTimeSavingCo]

Hm, could you try: in "Record ID", click on "Next", then "Display as" and select ID?

[jaybushman]

That seems to work! Gonna do some more testing to confirm. Thanks!

[jaybushman]

This gets me most of the way there. One setback - I have some cases where I need to have multiple records in the Next field. For example, if Record 1 needs to connect Next to both Records 2 and 3. But it looks like the automation can't handle multiple inputs? 

[TheTimeSavingCo]

Ah try using repeating groups for that, using the records in the "Next" field as the list

[jaybushman]

Thanks! I set up repeating groups and got it to work with multiple records in the Next field

But when I set two different records to link back to the same one....rather than having both records show up in the Previous field, the second one added overwrites the first one. Any thoughts?

Thanks again for all your fantastic help

[TheTimeSavingCo]

Hmm...try adding a "Find Record" action inside your repeating group that will look for the record that you're updating, and then add the current value of that record's "Previous" field along with the "Aritable record ID" value to the "Update Record" action

This will...probably work, but I think it might break if you have too many records linked to each other too; the repeating groups run sort of in parallel as far as I can tell, so they might just overlap with each other and give you the wrong data.  I'd highly recommend testing this out a couple of times

[jaybushman]

Thanks. I updated it with the Find Record action, but it keeps failing at the final step:

In that last step, I feel like the problem is with the field specified for "Record ID." In the screenshot, it's set for "Next | List of 'id'", and it doesn't work. But I can't figure out which way to configure that step.