Pre-populate a form

Pre-populate a form

StevefcStevefc
edited 12/09/19 in Using Smartsheet

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.

Previous1

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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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?

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • StevefcStevefc
    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?

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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.

  • 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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • edited 10/17/19

    Hi Andree,

    I have checked and things appear to be working.  The issue I run into is when making the reference using (for my purposes)           

    "?Column%20Name=SUBSTITUTE([InfoHere]@row#)"

    What shows up in my Column Name box is literally

    "SUBSTITUTE([InfoHere]@row#)"

    I hope that makes sense.  I'm happy to post the actual information if that's better.  I'm just not sure why the system isn't ingesting the call to substitute.  (There's a decent likelihood that I'm missing part of the formula somewhere.)

    Also, thank you to everyone helping on here.  It's been a life saver multiple times over.

  • Hi Michael,

    You need to be building up the url as a string. Smartsheet is not calculating the value because the substiture command is inside the quotes. Try:

    "?Column%20Name=" + SUBSTITUTE([InfoHere]@row," ","%20")

     

    In terms of troubleshooting, it can help to build up your calculation one step of the time, ie set the fomula to =SUBSTITUTE([InfoHere]@row," ","%20"), check it looks right, then add the column name

    ="?Column%20Name=" + SUBSTITUTE([InfoHere]@row," ","%20")

    then add any other columns

    ="?Column%20Name=" + SUBSTITUTE([InfoHere]@row," ","%20") +"&Column%20Name2=" + SUBSTITUTE([InfoThere]@row," ","%20")

     

    then add the URL to the form at the front....

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

    Happy to help!

    I saw that Andrew answered already!

    Let me know if I can help with anything else!

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • This all makes sense!

    I've figured out how to build the pre-populating URL without issue, but rather than having the not-so-user-friendly URL showing in the cell, I'd like them all to display something like "Form". I quickly learned that making this change in the first cell and propogating down doesn't work, since they would all share the same pre-populated fields as the first line.

    Is there a way to update the entire column's Display settings at once, or am I destined to do this line by line?


    Thanks!

  • HI! I stumbled across this having a similar issue. I am working with the URL query presented above, and keep getting 'Unparseable' coming back when I complete the URL. The purpose of pre populating my form is to pull in 'Person Number' (employee ID) and the 'Manager ID' into the form to capture performance data. I've worked on it with different variations and can't figure out why it's not working. Any insight would be greatly appreciated!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Heather Piel Try removing the quotes.

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 04/27/20

    @Heather Piel

    I think it's the quotes.

    Was that the reason?

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Hi - I have been following these comments as well. Can you post a picture of the form that is pre-populated with the working formula?

    I was able to enter a formula that opens a form, but it just enters a new row on my sheet and I'm still entering the information in manually.

  • Hi Again - So I was able to make the formula to work to pre-populate the form. Now I was wondering how you prompt people to use the form. Currently the cell is the hyperlink but it shows the whole http address and I would like it to describe the form they are going to enter information into.


    How do you lock down the pre-populated information so it can't be edited in the form. If I make a change to any of the prepopulated fields, i create another row in my sheet and it throws off my intake sheet.


    Thanks!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @liz.mayeux You would need to include a column next to the URL that describes the form, or you can use the help text/description fields in the form itself to be able to provide a description for it.


    To lock down specific fields, try making them "Hidden" on the form.

  • It's me again - pre-populating the form formula is working great! When I fill in the blank cells of the form and hit submit a new row is created. I would like the row to be back populated. I thought I had it working but now it doesn't work. This is my formula:


    ="https://app.smartsheet.com/b/form/1e382154fd574bc3926b76eb05ccac11?Project%20Number=" + SUBSTITUTE([Project Number]@row, " ", "%20") + "&Customer=" + SUBSTITUTE([email protected], " ", "%20")

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    What do you mean by "back populated"?


    It looks like it is dropping off before the first SUBSTITUTE function. I have found through testing that sometimes accounting for special characters/spaces can be a little tricky. For example...


    If I have a cell that contains the email address "[email protected]", I can't just drop the cell reference into the formula that generates my URL. I actually had to separate the email prefix from the domain into two separate columns ("JohnDoe" and "email.com" in their own column, then use the following to generate the URL that would pull the email:

    =................................................ + [Email Prefix]@row + "%40" + [Email Domain]@row


    The "%40" is read as the "@".


    THIS LINK has a table that explains what some of the special characters need to be entered as.


    You may need to create a separate column that houses the SUBSTITUTE functions as standalones and then reference that in your URL formula.

  • I apologize, but I have been working on this formula and output for 2 days so I'm a little frustrated lol

    I have a pic showing my "list" of entries w/ the formula for the pre-populated form in the Forms column

    When I open my pre-populated form, I would like to enter the location and have it back fill the row the form is attached to. Unfortunately, it is just creating a duplicate row with new info.

    Please ignore

    the #invalid operation - that was something else I was working on

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You really like to make thing complicated. Haha. To be able to enter data into a previously used row using a form type of structure, you would need to use an Update Request.


    or


    You would continue to use the form, but ensure you have some type of unique identifier (maybe a hidden field that replicates the custom URL) that we can use to match things up and then use formulas to pull the data. At that point though, I would recommend having the form populating a different sheet so that cross sheet formulas are used (and the formulas would actually end up being much more simple as well).

Sign In or Register to comment.