Help

Re: Prefill Linked field with multiple records not working

Solved
Jump to Solution
3484 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Philalethes
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello. I have found several posts that deal with this issue but it doesn't seem to work for me.

 

Problem: I have a form that I want prefilled, but for columns pulling in data from linked columns, it will only pull in the first data entry even if there are multiple selections ("Assigend to" column with multiple people selected from a linked "Name" field on the "Persons" sheet.

Suggested Solution: Was to created a Roll up column that would join the linked values and then get pulled in to the prefill easier. https://community.airtable.com/t5/formulas/prefill-form-with-multiple-linked-records/td-p/26213

Here is my URL Formula on button that links to form (Assigned%20to is the linked column in question):

Philalethes_0-1676515022938.png

Here is the formula in my roll up column "Assigned_to_Rollup". This is the prefill value for Assigned to.

Philalethes_1-1676515057308.png

Actual behavior: In my form, only the first name gets pulled in, though.

Philalethes_3-1676515314046.png

In the base, there are 3 names assigned that are joined in the rollup column. The prefill doesn't seem to pull in the additional names, even though this was the suggestion on several posts I found.

Philalethes_4-1676515326150.png

My linked Table is named People. It is pulling in just the primary filed, which is "Name".

Philalethes_5-1676515584099.png

Philalethes_6-1676515655788.png

I can't figure out what the problem is. Any help would be greatly appreciated. Thanks!

1 Solution

Accepted Solutions

Thanks for the base invite!  I've set up a new field in your base called "Prefilled Form Link" with the following formula:

"https://airtable.com/shrubY2x5RLiO7dIS?prefill_Assigned%20to=" & 
ENCODE_URL_COMPONENT(
  SUBSTITUTE(
    NameCalc,
    ", ", 
    ","
  )
)

There's two hurdles we have to cross in order to get this to work.  The first is that URLs don't like special characters like spaces, and so we use `ENCODE_URL_COMPONENT()` to help us deal with all the special characters.  For example, it converts all spaces to "%20".

The second is that the form prefill system expects to have comma separated values such as "value1,value2,value3", but with rollups, it's automatically "value1, value2, value3", and that's why I've got another `SUBSTITUTE()` in there to replace ", " with ","

I've tested the output of the formula field in the base and it seems to work fine for me; lemme know if you hit any issues!

See Solution in Thread

5 Replies 5

Hm I'm not sure how your base is set up but I put something together here

Screenshot 2023-02-16 at 4.05.00 PM.png

Screenshot 2023-02-16 at 4.03.34 PM.png

Screenshot 2023-02-16 at 4.03.32 PM.png

And here's an example link:

https://airtable.com/shrbtSFcUezxFXP0w?prefill_Name=A&prefill_Table%203=A%201%2CB%202%2CC%203


Link to base

---

If this doesn't solve it, happy to diagnose it further with you.  If you could create an empty duplicate base and provide a link to it that'd be best

Philalethes
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Adam. Thanks for taking the time to help me try and figure this out! I think I see how things are set up on your example. The question I have is what is the formula you have that builds the airtable prefill link? How is it pulling in all 3 names dynamically into your formula result?

I tried to build a formula that just mirrored the data in my roll up column, and then reference the formula field in my prefill link, but it still just pulls the first name in a list. 

 

Philalethes_0-1676642521457.png

'&prefill_Assigned%20to=', NameCalc,

 

 

CONCATENATE('https://airtable.com/shrIVnlG2h1I6qX8M','?prefill_Task=',Task,'&prefill_Status=',Status,'&prefill_Subtask=',Subtask,'&prefill_Assigned%20to=',NameCalc,'&prefill_Project%20lead=',{Project lead}, '&prefill_Kick%20off=',{Kick off}, '&prefill_Due%20date=',{Due date}, '&prefill_Projects=',{Projects},'&prefill_Original%20Record%20ID=',RECORD_ID(),'&hide_Original%20Record%20ID=true')

 

 

 

 
On this form, it should have Jordan Peretz, Paris Fotiou, Cameron Toth. It only has the first name still.
Philalethes_1-1676642629218.png

Lastly, I am new to Airtable so I am not sure if I am sharing this right. I think this is a link to the base so you can see it.

https://airtable.com/invite/l?inviteId=inva6N5cd5DUzdInu&inviteToken=7e95b968dbf1208a4cf04b887712603...

It just has info from one of the Airtable templates in it along with my attempt to create buttons that allow for people to edit entries in the base on forms.

Thanks again for your help!

 

Thanks for the base invite!  I've set up a new field in your base called "Prefilled Form Link" with the following formula:

"https://airtable.com/shrubY2x5RLiO7dIS?prefill_Assigned%20to=" & 
ENCODE_URL_COMPONENT(
  SUBSTITUTE(
    NameCalc,
    ", ", 
    ","
  )
)

There's two hurdles we have to cross in order to get this to work.  The first is that URLs don't like special characters like spaces, and so we use `ENCODE_URL_COMPONENT()` to help us deal with all the special characters.  For example, it converts all spaces to "%20".

The second is that the form prefill system expects to have comma separated values such as "value1,value2,value3", but with rollups, it's automatically "value1, value2, value3", and that's why I've got another `SUBSTITUTE()` in there to replace ", " with ","

I've tested the output of the formula field in the base and it seems to work fine for me; lemme know if you hit any issues!

Wow! This works perfectly. I was able to combine it with my previous code and button and now everything is working perfectly!

I appreciate your explanation too. It sounds like I needed a way to deal with the spaces between people's first and last name, plus the spaces between the commas in the rollup list. Your solution works great.

 

Thanks, again, for taking the time to sort that out for me!