Use a form to update an existing record in Airtable

[julianpost]

Hmm ok. First, make sure your markdown syntax looks exactly like I showed above, with no spaces in between the brackets and the parentheses. If it still doesn’t work, try using this substitute function in the base before sending it to the automation:

SUBSTITUTE({Tutor Link}, "_", "\_")

The backslash “escapes” the Markdown replacement for the underscores as described here

[Fi2]

I had no spaces, but this worked! In case someone else wants to use it, I had to create a second “Tutor link (updated)” field as a formula in my table. Then I copied and pasted Julian’s substitute function into it and changed the automation to point to this new field.

Thanks, Julian!

[julianpost]

I’m glad it worked !

[Fi2]

Hey Julian! I’ve tried to follow this but I might have missed something. After adding the Rollup, my Impact field disappeared.

I would like to try it all over, but if there is a thread with fuller description of what I am doing that you can share too, that would help a lot.

Thanks!

[julianpost]

@Fi2

I’ve tried to follow this but I might have missed something. After adding the Rollup, my Impact field disappeared.

Hi Fi2, here is an overview of Airtable Rollups that might be useful. As always, any before and after screenshots that you can provide are very helpful for me to see what is going on and help you troubleshoot. Here is a copy of the original template that I modified to do something similar to what I suggested: new template

And some basic instructions for how to get there:

1. In RSVPs table, add a linked record field that links to the Guests table. Field is called “Guest”
   

   

2. Add the new “Guest” field to the RSVP form
   

   

3. In Guests table, add another prefill snippet to the formula so that the “Guest” link is filled out automatically (if you’re using the customized form link)
   

   
   
   When you click the personalized link now, it opens the form linked to the guest (see bottom field)
   
   

You could also elect to hide that “Guest” field, since it will automatically be filled out by the link.

4. In the Guests table, create a new Rollup field type and roll up the info you want. In my example, I rolled up dinners. So if the same person RSVPs multiple times, and one time they selected Chicken and one time they selected Salmon, it will show both Chicken and Salmon. It’s a nonsensical example, but in your case you can roll up the number field and SUM(values) to get the total Impact
   
   

[frawidi]

Hi Julian
The RSVP works generally fine.
I’ve problems with 1:n relations, e.g. one person with experience in n industries.
I’ve a table Contacts and a table Industries.
In Contacts is a field Industries, defined as Link allowing multiple linking.

I can add different industries within Contacts. Also in the RSVP form.
When I select several industries in the RSVP, they are returned to Contacts.
BUT: When I call the RSVP, only one industry is shown, not all assigned to the contact.

I use prefill while generating the link to RSVP:
&“&prefill_Industries=”&ENCODE_URL_COMPONENT(Industries)

The URL to the RSVP is generated correctly, in this case the person has already 2 industries (Banking, Pharma) assigned:
&prefill_Industries=Banking%2C Pharma&

But In the RSVP only Banking is displayed. What can I do?

BR
Frank

[julianpost]

Hi Frank, welcome to the community!

@frawidi

&prefill_Industries=Banking%2C Pharma&

I noticed in your example here that you have an extra ‘&’ at the end. If that’s also in your formula, it could be the issue. However, there is also another likely reason. Airtable is a bit finnicky with multiple linked records like this. To get multiple values, it wants to see a comma in between each value but not a space. See this post:

Use a form to update an existing record in Airtable

Hello to the whole community! I had a question when generating the link. In the example you use Guest ID and Guest Name, but in my case I want to use the variables Project and Accounts, which are multi select. When I create the link I see all the options that are in these variables, but in the form I only see the first of them. Here you have some pictures of what I’m talking about: Fields: [image] Link: [image] Formula: [image] Form [image] Any idea how I can fix this? Than…

And my explanation of what’s happening:

Use a form to update an existing record in Airtable

You’re totally right! I tried prefilling a linked record field like you did, instead of a multiple select (which is what I was using before) and I got the same problem you did. After messing with it, I can provide a little more context to why it’s not working, and why your solution works. It appears that Airtable doesn’t like the space after the comma. So if the prefill options are “Booking Pronovias, Ladybird” (URL encoded) it only accepts the first value. But if they’re written “Booking Pron…

[frawidi]

Worked perfectly, thank you so much Julian!
I wasn’t aware of this separation needs, I’d have expected, AT would separate list fields in a way, it can process it.

But having 3 list fields each containing 100 entries, I think if some choose all, the generated URL could become too long. Any idea of a work around, is there an extension which could solve this problem?

[julianpost]

@frawidi

I wasn’t aware of this separation needs, I’d have expected, AT would separate list fields in a way, it can process it.

A reasonable assumption, but alas, it’s a little weird in this case!

@frawidi

But having 3 list fields each containing 100 entries, I think if some choose all, the generated URL could become too long. Any idea of a work around, is there an extension which could solve this problem?

The max URL length for prefills is 16,000 characters

Is it the case that these lists are bundled in some way so that the user is choosing one value that leads to many in the prefill? Or is the user choosing potentially hundreds individually?

[frawidi]

thx so much.
Weird - yes, as often but I can handle it.

16k chars - it’s OK Otherwise I would have the tables splitted into different per topics.
Thank you for your great support!
Frank

[eve_str]

Hey Julian
Thanks for the video. I’m new to your community and I was wondering, if there is any solution to update records through a form, without a specific link for each user, so that everybody has access to the whole table, with the same Link redirecting to the same form.
I have to build an Airtable with people having to chose their name (and perhaps other names) and also chose an event they would like to host. My goal is, that after submitting the form, the name of the host appears in the column behind the name of the event. I could only find solutions, where each host would need to get their own personalised link.

For more context: The name of the event is a multiple select field and the field for the name of the host is also a multiple select field, that links to another record/ table where all the hosts are listed.

[julianpost]

@eve_str

I was wondering, if there is any solution to update records through a form, without a specific link for each user, so that everybody has access to the whole table, with the same Link redirecting to the same form.

Hi @eve_str, welcome to the forum! So, the form is filled out to pick a Person and and Event. You want to have it update an existing Event record based on the Event that they picked. Is that accurate?

If yes, then absolutely you can do this. I also want to clarify when you say “Multiple Select”, do you mean the colorful multi-select field type or a linked record field type?

In order to update an existing record, you need to make an automation that finds that record once your form is submitted, and then updates it. In my video, we already know which record will be updated. In your scenario, you don’t know yet - it depends on what the user picks from the form. In that case, the best scenario is to have the field in the form be a Linked Record field, not multiple select. You can do it with a multiple select too, but it will be harder and more susceptible to errors.

1. In your Event table, create a “Record ID” field that’s a formula written as “Record_ID()”.
2. In the table that the form lives in, create a lookup field that looks up that record ID from the link to the Event that the user specified. Now you have the record ID, you can build the automation the same way as it is in the video:

• Trigger: when form is submitted
• Action: Update Record. Update the Event using the record ID that you have from the above steps

[eve_str]

Hey @julianpost !
Thank you so much for your fast reply! Unfortunately the field you have to choose your event from, a typical, ‘colourful’ multi-select field
Is there any solution for this?

[eve_str]

I found a solution! Thanks for your help keep up the great content and the great community engagement.

[julianpost]

@eve_str

I found a solution!

@eve_str that’s great! What did you end up doing?

One way to make the above system work with a multi-select is by using a ‘Find Records’ automation. But this has a more room for error, because it’s going to find any record in the table with that multi-select, and there’s nothing stopping you from having multiple records that match.

So I’d first want to ask - in the table where you hold these Event multi-selects, what does each row represent? Is each row a unique Event? If yes, then why not have the Event name as the primary (left-most) field rather than a single select. And if no, then further discussion may be needed to clarify what you are updating (if not a unique Event).

If each line is in fact a unique Event record and you still want the multi-select just because it’s colorful (totally reasonable, give us more color Airtable!), then I might recommend making the primary field a formula that mirrors the multi-select. Then you can still use my linked-record solution above.

And you may have found a completely different solution that works for your needs! But hopefully this helps clarify the risks with a multi-select

[JAJH]

New to the community… thanks for the insight and support…
Your example, of sending and seeking RSVPs from an existing list of possible wedding guests, is what I have been looking for… sorta!
I am writing from Canada which provides the context for my first ‘problem’ below.
I have an existing clean dataset of prospective guests. I want to poll them as to whether or not they will be attending our event and for a bunch more information beyond what they want for dinner. Those many additional fields give rise to my second ‘problem’.
I am imagining that the form that guests will receive will have two forks (I don’t know if that is the correct terminology?). The first will ask for the respondent preferred language - English or French. Depending on their choice, they would be directed to a survey form in their language. The second fork will be whether or not they will be attending. If they choose no, I want them to skip all of the event related questions and go directly to Submit.
Should I do this in one table/form (maybe with duplicates of each field) or should I have separate tables for each language? To be clear, the results from both sets of respondents (English and French) will be amalgamated into one dataset. So, for instance, both English and French respondents will be asked to choose a hotel from the same list of options and I am only interested in the cumulative totals for each hotel choice.
Any comments or suggestions would be most appreciated. Thank you.

[julianpost]

Hi @JAJH ! By far the easiest way to do this would be to include both languages in the form. For example, French in the heading of each question and English in the description. Otherwise, yes you could merge the data afterward, but it would be a LOT more work. With regard to the second fork, you can use conditional logic in forms. So, for example, you can have all event-related questions hidden unless the person marks “Yes” for attending. I cover conditional visibility in forms in this video.