What is the correct url query to have value pre-populated in form ?

Vero

Hello,

I have created a form for weekly progress status, and all project leader ask me to add if I can pre-populated this form with there previous input, in order to be able to update it more quickly.

I find previous discussion

https://community.smartsheet.com/discussion/73355/pre-populate-forms#latest

https://community.smartsheet.com/discussion/73388/pre-populate-a-form#latest

https://community.smartsheet.com/discussion/55241/pre-populate-a-form#latest.

So I try to use URL Query feature, but I have an issue.

Context : In my grid I have a column “Tests”, and for the subject name “ Subject Véro Test 1” : I have this entry in “Tests” column : URL prepopulated.

I would like to have in my form this text.

I add formula  at the end of form url :

https://app.smartsheet.com/b/form/fe6f37be72d547d3b7c00a800a8cc06b?Tests=+SUBSTITUTE([Tests]@row,””,”20%”)

But in the form on field “Tests” I have: SUBSTITUTE([Tests]@row,””,” ”)

In this discussion   https://community.smartsheet.com/discussion/55241/pre-populate-a-form#latest, I see other users who have same issue, but I do not find how they have solved this? (I already try to add Space, quotes, suppress quotes….nothing work, I go mad !

Could you help me?

Brent Wilson

I Haven't tested but try with the quotes

="https://app.smartsheet.com/b/form/fe6f37be72d547d3b7c00a800a8cc06b?Tests="+SUBSTITUTE([Tests]@row,””,”20%”)

You are concatenating a value with a text string..

I think your formula is seeing it all as text and not evaluating it

Vero

@Brent Wilson thank you for your answer. It is not working (no text)

Brent Wilson

@Vero

This works

="https://app.smartsheet.com/b/form/fe6f37be72d547d3b7c00a800a8cc06b?Tests=" + Tests@row

There are two possible issues

• The issue is in your SUBSTITUTE Statement and I am not quite sure what you want it to do. It cannot search for "". The SUBSTITUTE function is like a Find and replace.. It cannot search for a NULL

So if I guess you are trying to say if the Test field is blank then put "20%"

="https://app.smartsheet.com/b/form/fe6f37be72d547d3b7c00a800a8cc06b?Tests=" + IF(ISNULL(Tests@row), "20%", Tests@Row)

Might be a better way

• The other issue is the "%" is a special character in HTML and can cause some confusion in the request. I have played around with some code and the only way I can get it to work is to remove the "%" or type "PerCent" which tells me that is the issue

Also, I used to do things with Transact-SQL and it is a bit sketchy at best if the logic becomes too big and is executed at runtime. You may want to add a helper column for your Substitute to keep the URL call simple.

Probably doesn't fix the problem but hopefully points you in the right direction