Automating field permission updates

John_Mark · ‎Aug 16, 2021

I have a sheet that facilitates a document workflow between Creatives and Client. Creatives add draft copy to an attachment column, which triggers an automated notification to the Client to review it. If the Client has feedback, they put it into a 2nd attachment column (“Feedback”), which notifies the Creatives, who will add their revised draft to the 1st column, and so on. In addition to notifying the relevant party, the automation updates a Status column. What I’d like to do is prevent the Client from updating their attachment column while the Status indicates the ball is in the Creatives’ court; and vice versa. Can columns be disabled or permissions adjusted conditionally using the “Run a script” option in the automation? Any feedback on the best way to prevent one party from interrupting the other would be appreciated. Thanks!

kuovonne · ‎Aug 17, 2021

Welcome to the Airtable community!

Airtable does not have permissions that can be switched on and off automatically.

How are your creatives and clients interacting with the base? Are they all collaborators on the base? If not, the mechanism they use for editing the data will guide you on how to control the flow of updates.

You may also want to consider changing how you structure your data. Instead of having a single record with attachment fields that are overwritten back and forth, I recommend a single “project” record with updates in a linked record field. Each update can state if it is a client update or a creative update. This way, you will have a history of the back and forth if any issues come up. Also, a client might want to submit feedback again before getting a new draft—for example if they got new information or something changed on their end.

John_Mark · ‎Aug 18, 2021

Thanks, kuovonne. Creatives and Clients are collaborators in a sense, but not simultaneous ones. So the example you gave of submitting feedback before getting a new draft is precisely the action we want to prevent. We want a feedback delivery action to mark the formal end of a review phase, and trigger (not overlap with) the revision phase. To that end, can scripts be used to lock or disable certain columns for a given row, e.g. based on the value in that row’s Status column?

Re: attachment fields, it looks like they can contain multiple attachments. That is, uploading a new one into a given column seems to add to any existing attachments in that column, not overwrite them. However, I will look into your “single project record” idea—that might be be the solution we’re looking for.

Thanks again for your assistance!

kuovonne · ‎Aug 18, 2021

No. As I said in my previous post, scripts cannot do this. Scripts can include logic to change or not change data based on the value of a {Status} field, but they cannot disable columns. The only way to change permissions on columns is through Airtable’s user interface. And even then they are not robust enough permissions for this use case.

My question was more geared to seeing if they are collaborators on the base to see if they have direct edit access to the base. If they are not collaborators on the base, how are you getting the data entry? (Usually people do not have clients as direct collaborators on the base in order to provide privacy so that they do not see the work being done for other clients.)

Yes, attachment fields can contain multiple attachments, and adding a new attachment in the user interface will not overwrite existing attachments. However, this method also makes it a little more difficult to see the timeline of events. For example, you have to manually match up which attachment of comments goes with which attachment of a deliverable. This may not be a problem if there are only ever one or two revisions, but it can get confusing if there are multiple revisions. Plus, it is possible to re-arrange the order of attachments.