Match text across tables for automation to update record

[Gemma_Permanand]

I am creating a booking web app using Softr and Airtable. At the moment suppliers list vacancies using a Softr form and this form creates a vacancy record in the ‘Booking availability’ table in Airtable. Each vacancy has a book now button which automatically passes the record ID and logged in user name to Airtable and creates a record in the ‘bookings’ table. I need to link together the ‘booking availability’ and the ‘booking’ tables to enable the customer name to be populated in the ‘booking availability’ table. The ‘booking’ table pulls the record ID of the vacancy listed in the ‘booking availability’ table. Can I use an automation so that when a record is created in ‘bookings’ it automatically finds a match to the record ID and then updates the record in ‘booking availability’ to add the customer name?

[TheTimeSavingCo]

Hi Gemma, yeah that’s doable, but I would suggest that you link the records together and do a lookup of the username instead of updating the record with the customer name

I’ve put a working version together for you here and you should be able to view the automation.

So the idea is to create a link between the Bookings and Booking Availability tables, and trigger an automation whenever a new record gets created in the Bookings table.

You mentioned the record ID of the record in the Booking Availability table is passed in to the Bookings table, and so the automation pastes that record ID into the link field. We then have a lookup field that will display the Logged in username value in the Booking Availability table.

May I know if it’s possible to get Softr to update the link field with said record ID instead? That way you wouldn’t even have to make this automation

[Gemma_Permanand]

Thanks! That seems to work - I think I have to run it via an automation though as Softr gives me an error if I try and pass the record ID directly to the link field - I think because it can’t pass records to lookup formula fields.

[TheTimeSavingCo]

Hi Gemma, hmm that’s odd. I’m able to use Softr to update linked fields directly. Our setups are probably different in some way; let me know if you’re interested in investigating this

[Gemma_Permanand]

@Adam_TheTimeSavingCo - sorry for the delay, the reply went into my spam folder so only just seen it. Yes, would be interested in investigating - I just get this error when I try to pass the record ID into a linked field in my Airtable.

[TheTimeSavingCo]

Hi Gemma, no worries!

Assuming you’ve implemented the linked field solution above, we would need to get Softr to update the linked field in the Bookings table that that connects it to the Booking Availability table.

From the screenshot, you’re passing the record ID of the record from the Booking Availability table into a field called Record ID. Is the Record ID field a linked field in the Bookings table that connects it to the Booking Availability table?

[Gemma_Permanand]

Yes - the Record ID field is a direct look up field in the bookings table - so it pulls the record ID directly from the Booking Availability table. I think the problem may be that the original record in the booking availability table is a formula field as that’s how Airtable displays records - I think the issue Softr has is that it can’t write to a formula field.

[TheTimeSavingCo]

Gemma_Permanand:

the Record ID field is a direct look up field in the bookings table

Hi Gemma, we need to change the field Softr is updating to a linked field in the Bookings table that connects it to the Booking Availability table, not a lookup field. Could you confirm that that’s been done please?

---

In the following gif, the Softr form creates a record in Table 1 on submission, and updates the field Link to Table 2 with the text that’s submitted in the form.

I submitted the record ID of the record in Table 2, and you can see that the linked field Link to Table 2 gets populated on form submission

And so the idea would be to do the same thing to update the linked field in the Bookings table that connects it to the Booking Availability table

[Gemma_Permanand]

Hi Adam,

Yes, that works when it is a direct linked field not a lookup as long as the directly linked field is not a formula. The issue I need to work out is how to generate a unique non-formula based direct field. Because I might have numerous available slots for one date, I can’t use a date and I might have the same company listed more than once as well. So i have used a formula to generate a unique ID for each record in the initial field - but obviously this doesn’t work for the pass through of information from Softr. I will have a think about what unique reference I could auto generate for each vacancy and then see if I can pass the info between the tables that way.

[TheTimeSavingCo]

Gemma_Permanand:

Yes, that works when it is a direct linked field not a lookup as long as the directly linked field is not a formula.

Hmm, in the following gif, Table 2's primary field is a formula field, and the form is updating the field Link to Table 2 in Table 1 and is able to create the link

I’m assuming that when you say directly linked field, you’re referring to the primary field of the table we’re linking to though, apologies if I’ve misunderstood you