Query

Options
lmed80
lmed80 ✭✭
edited 08/18/22 in Smartsheet Basics

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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.

  • lmed80
    lmed80 ✭✭
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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!

    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.

  • lmed80
    lmed80 ✭✭
    Options

    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
    bsikes ✭✭✭
    Options

    @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
    lmed80 ✭✭
    Options

    @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
    bsikes ✭✭✭
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    lmed80 ✭✭
    Options

    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
    bsikes ✭✭✭
    Options

    @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
    lmed80 ✭✭
    Options

    Excellent, thank you for this learning opportunity!