Query

lmed80

Hello,

I am trying to link a form from another sheet and have it autopopulate a few fields in the form. The form is an Exit Survey and I want to have this on our offboarding sheet so when a new separation is entered, it will automate sending the Exit Survey form link to the employee separating. I have reviewed some of the other discussions and Use a URL query string article but am not finding how to pull in the column data from each row as it will be different for each employee (i.e., in the article it only discusses how to do for creating a specific link for Rep = Sally). Any help would be greatly appreciated! Here's what I started but it's not working: https://app.smartsheet.com/b/form/c279351265744f96b3f9b554a476976e?[Employee ID]=[Employee ID]@row%20Smart&[Employee Name]=[Employee Name]@row%20Smart&[Position Title]=[Position Title]@row%20Smart&Location=Location@row

Andrée Starå

Hi @lmed80

I hope you're well and safe!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

lmed80

Hi @Andrée Starå ,

Amazing, thank you so much! These are new sheets, so no confidential info yet. I've shared both with you via email. Please let me know if you can help. The Offboarding Tracker has one line of test data with my info ;)

THANK YOU!

Andrée Starå

@lmed80

Excellent!

Happy to help!

I'll take a look and get back to you!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

lmed80

Hi everyone - I had a pro desk session on this question and while we were able to mostly complete the formula for this to work, the rep got stuck on the ability to allow for cell contents with more than two words. For example, I have job titles that are 3 or 4 words, so as the formula is now, it will only pull in the first 2 words of their title until I get this fixed. Below is the current formula. Does anyone have a fix for this? The rep was supposed to do some research and get back to me but I never heard back from him...Thanks!

="https://app.smartsheet.com/b/form/c279351265744f96b3f9b554a476976e?Employee%20ID=" + [Employee ID]@row + "&Location=" + IF(FIND(" ", Location@row) = 0, Location@row, REPLACE(Location@row, FIND(" ", Location@row), 1, "%20")) + "&Employee%20Name=" + IF(FIND(" ", [Employee Name]@row) = 0, [Employee Name]@row, REPLACE([Employee Name]@row, FIND(" ", [Employee Name]@row), 1, "%20")) + "&Position%20Title=" + IF(FIND(" ", [Position Title]@row) = 0, [Position Title]@row, REPLACE([Position Title]@row, FIND(" ", [Position Title]@row), 1, "%20"))

bsikes

@lmed80

Looks like all those replaces are just replacing the 1 space in the cell with a %20. If you're wanting to replace all spaces, maybe tray swapping them for SUBSTITUTE instead?

="https://app.smartsheet.com/b/form/c279351265744f96b3f9b554a476976e?Employee%20ID=" + [Employee ID]@row + "&Location=" + SUBSTITUTE(Location@row, " ", "%20") + "&Employee%20Name=" + SUBSTITUTE([Employee Name]@row, " ", "%20") + "&Position%20Title=" + SUBSTITUTE([Position Title]@row, " ", "%20")

lmed80

@bsikes - Thanks for the info. I'm not wanting to replace the spaces. My understanding is that the %20 allows for the space, meaning it should bring in the data as written (with a space) if there is a %20. However, it's only bringing in the first two words (with 1 space) versus the third and/or fourth words (with 2 or 3 spaces). I'm not sure how to account for that, and neither was the SMAR rep. SUBSTITUTE does not seem to work either but thank you for reaching out!

bsikes

@lmed80

Usually %20 is used to represent spaces in a URL, since often the space character itself isn't supported. Below is an example using the formula I sent above. Is this not what you're looking for?

Example Sheet:

Clicked URL:

Paul Newcome

I agree that the SUBSTITUTE function should be working. Have you been able to test it yet to see if it is working for you as it seems to be working for @bsikes in the above screenshot.

lmed80

Thank you both - this is working now! I must've pasted the updated formula incorrectly. It makes much more sense with the SUBSTITUTE function. THANK YOU!

bsikes

@lmed80

Glad to hear it! If you wanted to save a few more characters, you could also make URL with the spaces, and wrap that whole value in the SUBSTITUTE function. Same URL, but potentially a bit more readable formula.

Single Substitute:

=SUBSTITUTE("https://app.smartsheet.com/b/form/c279351265744f96b3f9b554a476976e?Employee%20ID=" + [Employee ID]@row + "&Location=" + Location@row + "&Employee%20Name=" + [Employee Name]@row + "&Position%20Title=" + [Position Title]@row, " ", "%20")

Substitute for each property:

="https://app.smartsheet.com/b/form/c279351265744f96b3f9b554a476976e?Employee%20ID=" + [Employee ID]@row + "&Location=" + SUBSTITUTE(Location@row, " ", "%20") + "&Employee%20Name=" + SUBSTITUTE([Employee Name]@row, " ", "%20") + "&Position%20Title=" + SUBSTITUTE([Position Title]@row, " ", "%20")

lmed80

Excellent, thank you for this learning opportunity!