How to Add Specific Field into Form with SUBSTITUTE Formula
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.
Answers

@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).

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!

Was the recommendation in the other post not correct?

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.

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

Thank you for the help Paul, those code numbers are helpful. I tried rewriting 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!

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.

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?

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

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.

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")

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!

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

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

If you look at the new formula, you will see that the field for Work Order ID is not specified.
Work%20Order%20ID
Help Article Resources
Categories
Check out the Formula Handbook template!