How to Add Specific Field into Form with SUBSTITUTE Formula

John- Michael Diedrich · 10/26/22

Hey Community,

I am trying to have my OPPTY # field prepopulated in my form using the SUBSTITUTE formula because a lot of our users tend to enter it incorrectly. Can you please help me figure out how to solve this? I will paste a screenshot of the format below.

Paul Newcome · 10/27/22

@John- Michael Diedrich Is it possibly the same issue in your other post regarding the URL? I believe there was an issue with the # and it needed to be swapped out with something (maybe %23 but could be remembering wrong).

John- Michael Diedrich · 10/27/22

https://community.smartsheet.com/discussion/comment/348953#Comment_348953

Thanks for the response Paul. I am assuming it is something along those line, however I am not sure what "%##" needs to be there for it to work. I tried playing with it but have had no luck.

Any help is appreciated!

Paul Newcome · 10/27/22

Was the recommendation in the other post not correct?

John- Michael Diedrich · 10/27/22

https://community.smartsheet.com/discussion/comment/348967#Comment_348967

It was not unfortunately. I think it will require a different "%##" number in order for it to work properly. That is where I struggle and need help, because there are unique codes for specific columns that I do not know about.

Paul Newcome · 10/28/22

%23 is correct for the #, but you also need to replace any spaces with %20.

John- Michael Diedrich · 10/28/22

https://community.smartsheet.com/discussion/comment/349104#Comment_349104

Thank you for the help Paul, those code numbers are helpful. I tried re-writing the Substitute formula but had no luck. Below is how I tried writing it if you want to see:

And here is the OPPTY # column I am trying to have prepopulated on the form:

Any help is greatly appreciated!

Paul Newcome · 10/28/22

It looks like you are not including the Oppty # field in the URL.

Notice how you have "Work%20Order%20ID" in the URL to indicate you want to populate that field and then the following bit is what you want to populate the field with (in this case it happens to be the cell contents).

You need to do the same for the Oppty # field. You need to list the field and then list what you want to populate it with. Right now you are basically combining the Work Order ID and the Oppty # as a single string and dropping it into the Work Order ID field of the form.

John- Michael Diedrich · 10/28/22

https://community.smartsheet.com/discussion/comment/349117#Comment_349117

Sorry Paul I am trying to follow and correct the formula but I am not fully understanding. I updated the formula to the below and now the Work Order ID doesn't populate. Do you know how the formula is supposed to be written?

Paul Newcome · 10/28/22

You need to string it together in the proper order. Field value field value. In the above you have field field value value.

John- Michael Diedrich · 11/01/22

https://community.smartsheet.com/discussion/comment/349154#Comment_349154

Understood, thank you Paul. Will I need to include the form link for each field value? Or should I use the below:

="FormLink?Work%20Order%20ID=" + SUBSTITUTE([Work Order ID]@row, "#", "%23") + ?Oppty%20# = SUBSTITUTE([Oppty #]@row, "OPPTY", "%20")

Sorry this formula is giving me a lot of trouble.

Paul Newcome · 11/01/22

I personally would write out the formula that puts everything together and then just wrap the entire thing in a series of SUBSTITUTE functions to make sure everything gets replaced as needed.

=SUBSTITUTE(SUBSTITUTE("FormLink?............" + [Work Order ID]@row + "?Oppty #=" + [Oppty #]@row, "#", "%23"), " ", "%20")

John- Michael Diedrich · 11/01/22

https://community.smartsheet.com/discussion/comment/349391#Comment_349391

Thank you for the help Paul, but unfortunately this did not return a value in the Work Order ID and Oppty # fields. I typed it out as the below:

=SUBSTITUTE(SUBSTITUTE("FormLink" + [Work Order ID]@row + "?Oppty #=" + [Oppty #]@row, "#", "%23"), " ", "%20")

Please let me know if you can spot the issue, any help is appreciated!

Paul Newcome · 11/01/22

Wha tis the final output of the above formula? Can you drop the URL in here?

John- Michael Diedrich · 11/01/22

https://community.smartsheet.com/discussion/comment/349435#Comment_349435

Here is the exact formula I used based on your input:

=SUBSTITUTE(SUBSTITUTE("FormLink" + [Work Order ID]@row + "?Oppty #=" + [Oppty #]@row, "#", "%23"), " ", "%20")

And when it is clicked the form shows the below:

Please let me know what you think when you get a chance, below is the old formula I had that would populate the Work Order ID but not the OPPTY #:

="FormLink" + SUBSTITUTE([Work Order ID]@row, "#", "%23") + SUBSTITUTE([Oppty #]@row, "OPPTY", "%20")

Thank you for the help

Paul Newcome · 11/01/22

If you look at the new formula, you will see that the field for Work Order ID is not specified.

Work%20Order%20ID

How to Add Specific Field into Form with SUBSTITUTE Formula

Answers

Help Article Resources

Categories