Scripting app - update a record in another base

[Josh_Cooper]

I have searched the community for an answer to this and I am beginning to think it isn’t possible. I am wanting to use the scripting app to generate unique lot numbers for products my company sells. To keep the lot number unique, I want to have a single field in a single record in a single table to simply keep track of the sequential number section of the lot number. I have had no issue accomplishing this using the scripting app and creating a new table in the same base. My boss really doesn’t like the idea of their being no security around that number, so I thought I might be able to “hide” it in another base and use the api to get and update it.

I have no trouble getting the value from another base, but I can’t figure out how to update the record with the new value after new lot number are created.

Here is the method I used to get the value in the other base using the scripting app.

let response = await fetch('https://api.airtable.com/v0/appXXXXXXXX/Lot/recXXXXXXXXX?api_key=keyXXXXXXX');
//console.log(await response.json());

Any ideas? Any other way to “hide” the number so it can be used but not unintentionally altered?

I appreciate the help in advance.

[kuovonne]

Josh_Cooper:

Any other way to “hide” the number so it can be used but not unintentionally altered?

There are two basic ways to update a record in another base (without using a 3rd party service).

• Use the REST API. I do not recommend doing this in Scripting app, because this would make your api key visible to any collaborators on the base.
• Use scripting to call an Airtable webhook automation in the other base. This method does not require revealing your api key. However, the webhook would be exposed. People are less likely to accidentally update the number.

However, it is possible to provide protection against accidental changes without storing the number in a different base, using table and field editing permissions. Store the number in a table in the same base. Change the table permissions so that no-one can add/delete records in the table. Change the field permissions so that users cannot directly edit the field, but automations can set the value. Then create an automation with a logical trigger to set the value of the number.

Out of curiosity, is there something about your base that prevents you from using an autonumber field to ensure uniqueness?

[Josh_Cooper]

Thanks for the quick response @kuovonne!

Do you know of an example using the webhook automation with another airtable base? That sounds like an interesting idea. I will also look into the permissions route. I was unaware you could set it up so that it could be used by an automation and not the person who fired the automation.

To answer your question about using the autonumber field to ensure uniqueness, I went down that road first. The problem is, first, even though it could be worked around by deleting or archiving, the autonumber would quickly start to rack up the records. The second problem is handling multiple lot numbers for one order. I could relate multiple lot numbers to a single order by keeping them in another table, but this seems an unwise use of resources, as it would mean records would be piling up and there is no useful information associated with the lot number.

Thanks again for the help!

[kuovonne]

Josh_Cooper:

Do you know of an example using the webhook automation with another airtable base?

Receiving webhooks is a very new feature, so there aren’t many examples. It does require sending a POST request, versus a GET request, and you cannot access nested data. If you are not familiar with how to call a webhook, you may want to research that first.

Josh_Cooper:

I was unaware you could set it up so that it could be used by an automation and not the person who fired the automation.

This is also a relatively new feature and isn’t in all the documentation yet. Airtable automations don’t know who triggered them, as they do not need to be triggered from human interaction.

[Josh_Cooper]

I was able to get the webhook working perfectly.

const data = { CurrentCount: Number(currentCount) + Number(multiple) };

let webhook = await remoteFetchAsync('https://hooks.airtable.com/workflows/v1/genericWebhook/appXXXXXXXXXXXX/wflJmpBH8mhqXXXXXX/wtrWFmZXmgXXXXXXX',
    {
    method: 'POST',
    body: JSON.stringify(data),
    headers: {
        'Content-Type': 'application/json',
    },
});

Thanks again @kuovonne!!