Help

Automated entry of rollup text into linked items not working w/synced tables

Topic Labels: Automations
2008 12
cancel
Showing results for 
Search instead for 
Did you mean: 
F-Law_IT
6 - Interface Innovator
6 - Interface Innovator

My understanding (drawn from this post: How can I make a lookup field show only unique results? - #23 by Mathias_Mayer) is that using automation to act on update of a rollup field (leveraging ARRAYUNIQUE) should work for selecting matching items in a linked items field, as though each string were entered by hand; but it does not work when operating on linked tables. It only works for choice fields in this context.

airtable4

Trying to operate on field with it set to lookup generates "insufficient permission" errors. The only hint on that I found was here:

For context, the error you are receiving can happen when the cell value for the linked record is not a valid primary cell value for the linked table (e.g. if the primary column in the linked table is a computed field).”
[ Creating the record failed due to insufficient permissions ]

Which…what? When I type the same string into the field, it finds the record. So why should this make a difference? But it is a calculated field in the source base, and a long text field in the sync’d version.

12 Replies 12
F-Law_IT
6 - Interface Innovator
6 - Interface Innovator

I could really use at least some indication whether this is expected when working with synced tables, before I waste more time on it…

If the automation functions to enter each string in the array as though typed in from the keyboard, it should be working. As that works… (as it does if I change the target field to a choice field rather than a link field).

Also, strangely, if I delete the automation and re-create it then it tests successfully – once (inside the automation builder). But then automated runs will fail, and so will subsequent tests.

From my experience when using an automation to create links in a linked record field, the most reliable method is to use the target record’s ID. When trying to link using the text from the linked record’s primary field, it’s hit or miss as you’ve seen, and I haven’t run sufficient tests to know for sure why it’s not consistent, or if it’s possible to force it to be more consistent.

One workaround would be to use a “Find records” action in your automation to look for the target record by matching its primary field contents against the output of your rollup field. That should return a single record, and you can use that result to create the link in an “Update record” action.

I think that is what I’m doing from a scripting perspective, maybe my understanding that it gets passed in as text via the rollup is a little simplistic.

This should work, should it not? Despite that the linked items are referencing a synced table.

airtable5

.

I cleared out the automation and re-created it as above, and I still get the permission error.

One workaround would be to use a “Find records” action in your automation to look for the target record by matching its primary field contents against the output of your rollup field. That should return a single record, and you can use that result to create the link in an “Update record” action.

This makes sense. I’ll give it a shot, thanks.

This doesn’t work, the rollup entries flow into the automation wrapped in quotation marks (despite that I’ve stripped them in the rollup) and so they aren’t matched in the target field and nothing is returned to the second step.

And to be clear, this needs to work for multiple entries. Although for now I’m just trying to get one to work.

If I can live with clicking a button in the interface, can this be more cleanly scripted than automated?

Is it significant that I can’t seem to paste into this target field in the UI?

I created a new base with a single table and record and reproduced the permission error. Upon inspection, the error seems to be complaining about insufficient permissions in the only table belonging to the new base, “Table 1” rather than the synced tables.

As far as I’m concerned, this is a bug. Perhaps stemming from the problem that Airtable can’t insert a reciprocal link in the sync’d table. Which is fine, don’t. (Although that doesn’t seem to be an issue when doing so manually so… :man_shrugging: :triumph: )

AirtableSyncLinkBug

F-Law_IT
6 - Interface Innovator
6 - Interface Innovator

Is there some way to do it by percolating the recordIDs up as text and then using them to insert the entries, so as not to bother with the Find step? So far I’m not finding a means to do it that way either.

Airtable6

The error message stems from the fact that synced tables cannot have new records added.

In the first set of screenshots, where you’re getting the record name, the name from the rollup is actually not the same as the linked record. In the lookup/rollup, you have (2016) after the parties, but it’s not in the linked record name under Associated Entries.

The (2016) version matches the target case and is being percolated up from the primary key column of the table I’m trying to link back to… The name on the left comes from an item tracking a point of law within that case. That’s why the “entry” name can’t just be cast as a string and matched against the “case name” in the first place.

The error message stems from the fact that synced tables cannot have new records added.

Except that it accepts manual input. Refer to the right side of the last pic. Those are linking to the same field in the synced table. And the recordIDs all match up – the one being pulled in on the left, and the one shown when I manually link to the target table on the right. The field just won’t accept automated input. So apparently while you can’t add new records, you can extend existing rows that are brought in.

And I’m guessing the check that is meant to prevent an automation from doing the first is somehow catching the second. Although you’d think this would bite more people and get fixed if so, so I’m still wondering if I’m doing something wrong or if it’s really a bug.

Airtable7

Here we see the manually entered version reflected reciprocally on the synced table.

So…? If I can do it manually that can’t be the reason the automation fails, can it?

If I were handier with JS I’d just try to do it in a code block, from a button, and get better logging – but that’s a whole project in and of itself for someone like me. That’s why I’m here in the first place…

F-Law_IT
6 - Interface Innovator
6 - Interface Innovator

I’ve filed a bug and a support request. For now I’m getting on with my life and just linking them manually, but that’s not a permanent solution.

Possibly, but in a later screenshot I noticed that you had a record ID field that was synced. Record IDs are unique to the record, even when using a synced table, meaning that the ID of the original record won’t carry through to its synced counterpart. With that in mind, you could add a new formula field to the synced table that outputs the new record ID. If you’re able to roll that up where it can be picked up by the automation, that should work.

Are you interested in hiring someone to write a script that could take care of this?

Are you interested in hiring someone to write a script that could take care of this?

Possibly, at some time in the future. If I decide to stick with the platform despite this limitation and other weird oversights I’m running into in various areas. And at that point I might sweat it out as a learning experience.

But that wasn’t really the idea in adopting Airtable…I’m a veteran of the times in which you had to have VB consultants to maintain Word macros via some eldritch Access database. I’m not trying to return to them. This ought to work, so far as I can tell. I’m not trying to add a row to the synched table. I’m trying to extend it, and that’s supported. I know I sound like a broken record, but if I can insert the link manually then I don’t understand why I can’t do so via the automation. Where’s the permission failure coming from?

Why should I need to get into paying thousands dollars in additional costs to create and maintain custom code (that will have to be run manually) because Airtable is doing more marketing than maintenance? Not an auspicious trial experience.

Record IDs are unique to the record, even when using a synced table, meaning that the ID of the original record won’t carry through to its synced counterpart

Interesting. I take from that then doing it this way would require me to lookup the IDs in the destination table rather than the source. That’s doable, but I still don’t see a way to leverage that record string via the automation controls to insert the link.

There may be a way to make this work, but it’s difficult to tell from a handful of screenshots and the descriptions so far. I’d be happy to meet with you directly to look this over and see if anything sticks out. If that sounds good, message me directly and we’ll set up a time to connect.