Query
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
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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"))
-
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")
-
@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!
-
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:
-
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.
-
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!
-
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")
-
Excellent, thank you for this learning opportunity!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives