Prefill Linked field with multiple records not working

[Philalethes]

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):

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

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

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.

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

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

[TheTimeSavingCo]

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

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]

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. 

'&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.

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!

[TheTimeSavingCo · Accepted solution]

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!

[Philalethes]

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!

[TheTimeSavingCo]

Glad I could help!