Help

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

Topic Labels: Automations Base design
3454 9
cancel
Showing results for 
Search instead for 
Did you mean: 
jaybushman
6 - Interface Innovator
6 - Interface Innovator

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:

airtable - sequences manual.png

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:

airtable - sequences lookup.png

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. 

airtable - sequences lookup2.png 

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

Thanks a lot!

9 Replies 9

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. 

 

CleanShot 2023-03-22 at 15.13.44@2x.png

Any idea what I'm missing?  

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

Screenshot 2023-03-23 at 4.20.43 PM.png

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

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? 

jaybushman_0-1679598918536.png

 

 

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

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

jaybushman_0-1679693040903.png

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

 

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

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

jaybushman_0-1679791377899.png

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.