Automating a single select change

Hi all, I’m building a table to track items availability (musical instruments) for students to sign out and borrow for a limited time.

My base has two tables, one with the instrument catalogue (containing general information on the instrument, brand, etc. Also has a field of whether the instrument is in stock or unavailable). The second table is the home to the form for instrument request, these requests come in as a name, instrument requested, and date needed. The email autopopulates from a student directory and the return date autopopulates with the dateadd formula.

I already automated emails to the students based on these dates and it works fine. What I am not able to do is to automate a change in status of the instrument. I would like for the “Status” field in Table 1 to change to “unavailable” when the next sign out date is “tomorrow” (as automation allows you to set) in table 2.

The problem I’m having is when I’m setting up the configuration for the “update record” step, it asks for a Record ID. The idea is I don’t want to update a specific record, but essentially any record in the field depending on which linked item is being requested in Table 2. Additionally, when I open up the dropdown items for Record ID, it shows fields from Table 2, in essence, not recognizing that I want to update Table 1 even when I selected Table 1 as the table I want updated.

I’d be happy to send screenshots or upload my base for viewing, just not sur ehow to do that on here.

Thanks for all your help! You’ve all been amazing so far on getting me out of the Airtable noob territory!

I should add, thanks to Kamille_Parks’ help, I do have a feeling that this could also be done via a formula, but I’m not sure how to get it to happen. In words, I guess it would be:

If [signout date] is tomorrow, mark instrument status as unavailable [from single select field].

and then

If [return date] is today, mark instrument status as available [from single select field].

In order to do this, I have added the max(values) rollup field, so now I can see the most recent signout date of the instrument in the same table, from here I could use this date to affect the above formulas.

Either formula or automation works for me! Just need to figure out how to make this step unmanned!

Hi @Edward_Gavitt, I’m assuming that each instrument request record is linked to the appropriate record in Instrument Catalogue?

If so, you could potentially set up a view in Instrument Requests that only displays records where the next sign out date is “tomorrow” and have your automation trigger off of that

From there, you would use the Update Record action, select the Instrument Catalogue table, and for the Record ID field, you would put in the record ID linked record from the Instrument Catalogue of the record that triggered the automation and update that single select to “Unavailable” like you wanted, does that make sense?

Hey Adam, thanks for the suggestion, though I’m not entirely clear on it unfortunately. I’m also realizing I need a bit more functionality than I specified above, as I would also like instruments to not be double booked in the future, ie, someone books a saxophone for Oct 15-29, I want the saxophone to be unavailable during that time. I might have had luck with a formula that at least marks instruments as available/unavailable in relation to the current date, but now the future date thing is throwing me off.

Hmm, yeah, I don’t think that’s doable via Airtable alone. You would need a dynamic form of some sort that would, based on the dates selected by the user, show only the instruments available. miniExtensions has a dynamic form thing that might work I think, so you may wanna check that out

If you want to keep this free and are willing to accept some errors, you could potentially use Softr or something and throw up a basic website that would show your users a calendar view of instrument availability and have a form on the same page, and trust that they won’t double book things?

Thanks @Adam_TheTimeSavingCo, I actually discovered a script from @Kamille_Parks that I posted about in another thread as I’m investigating the possibility of using calendar view for this, but her no-conflict script works perfectly. I’m just looking for a way to see if I can make the script page a client-facing page so I don’t have to show students a base view.