Pre-populate a form

Stevefc
Stevefc
edited 12/09/19 in Smartsheet Basics

I am building a change control log. Is it possible to pre-populate a form with cell data from a sheet? For example, project name, ID, customer, description and resource from the sheet to help the process of logging a change to the change control log.

«1345

Answers

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

    Hi Steve,

    If possible, I would recommend using an Update Request instead.

    Otherwise, you can pre-populate the form with fixed values, or you can create a link with the values included.

    Would any of those methods work/help?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi Andee, Thanks for your reply. How would I create a link with a value included from a cell in a sheet? Thank you, regards Steve 

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

    Happy to help!

    Look here for more info: https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

    Let me know if you have questions!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi, Andrée, 

    That’s a good solution and its works for manual import, however, I have a sheet with different project IDs and ideally, when the form loads I want to pick up the project ID from the relevant row.  Regards Steve 

     

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

    Ok.

    Can you describe your process in more detail?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Stevefc
    Stevefc
    edited 08/17/19

    Hi Andrée,

    I’ve created a sheet that shows progress on projects. If change control is required, I need to generate a form, link under Next Steps and pre-populate the form with the Project No, Customer and Project. I manually used the hyperlink https://app.smartsheet.com/b/form/45c2d28d1e544956b700bbb4b6904a7e?Project=90860 on line 1 for the form link and this populated the form with the project number.

     

    I would not like to do this manually as I have over 25 entries. Ideally, I would need to pick up the project number from the sheet in an automated workflow for different projects. Therefore, users would just click on the form link and the relevant data is populated in the form for the project. Hope this helps, thank you,  

    Regards

    Steve

     

     

    projects.PNG

  • Hi Steve,

    You have probably sorted this out by now, but if not try using a formula in the Next Steps column....

    ="https://app.smartsheet.com/b/form/45c2d28d1e544956b700bbb4b6904a7e?Project="+[Project no:]@row+"&Customer="+SUBSTITUTE([Customer]@row," ","%20")

    which should build a link that prepopulates the form with project and customer fields. You could add others as required (eg Current Date could be defaulted to TODAY). Note that spaces need to be replaced with %20, other special characters as per https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values.

    A useful trick for data integrity is to make the form field hidden, so the user can't change it to a non-existent one, for instance. The value is still passed through and collected when the user submits the form.

    I am having problems with drop down fields, these do not seem to prepopulate, text fields seem fine.

     

  • Actually drop down fields are fine, I found my issue was that the form had overridden the column name.

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

    Wouldn't it work with update requests instead? Does it have to be added to sheet as new lines?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • There will potentially be multiple (or none at all, in theory but never in practice) changes per project that need to be tracked. It would make sense to keep the main sheet as a master or summary, with one row per project, and have each change with its own line in a separate sheet. The main sheet could reference the change log to provide summaries, such as number of open changes, etc...

    Update requests would make good sense for ensuring the changes are kept up to date by whoever the change is assigned to.

  • Hi Andrew,

    Thanks for your comprehensive feedback. I’ve haven’t resolved this yet, so I will investigate your solutions and come back to you if I have any further questions, thank you.

    Regards Steve 

  • Hi Andrew,

    I tested the formula suggested and it works very well for pre-populating the form. Many thanks for your help with this. I will also look at update requests. 

    Regards

    Steve 

  • No problem, I'm glad to be able to share solutions to problems I've struggled with.

  • michael tomlinson
    michael tomlinson ✭✭✭✭✭✭

    Hi there, I've read through this multiple times as I think it would be a very useful capability but I'm still struggling a bit and wondering if you might be able to help me determine what I'm doing wrong.

    When I set my URL using the formula provided above, the substitute statement is populating into the field rather than being processed.  Any ideas on why that might be happening or tips on troubleshooting?

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

    Hi Michael,

    Have you checked so it's not the changes made to the query string structure?

    More info: https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.