Auto-populating a Form

Sam Lugiano

Hi,

I am trying to add more data to my form by auto-populating with the below formula:

="https://app.smartsheet.com/b/form/648c11b98a0847d187a13ed81133bba7" + "?BARCODE=" + BARCODE@row + "&PATIENT%20NAME=" + SUBSTITUTE(SUBSTITUTE([PATIENT NAME]@row, " ", "%20"), ",", "%2C")

This formula populates the form with the data in the first two columns (BARCODE, PATIENT NAME) but I am having trouble adding to this to include additional columns like DOI, IV, CASE TYPE and PATIENT STATUS. I continue to get an INVALID OPERATION result.

Any help will be greatly appreciated.

Thank you,

Sam

Paul Newcome

You need the SUBSTITUTE functions as previously mentioned. Your URL that is output by the formula has...

Space between "PATIENT" and "NAME"

Comma after "EDWARDS"

Space after above mentioned comma

And I believe the forward slashes in the date also need to be percent encoded using the SUBSTITUTE method.

Paul Newcome

My suggestion would be to write out the whole thing without accounting for percent encoding of special characters. Once you see that it is outputting the correct string based on the additional fields, you can wrap the whole thing in SUBSTITUTE functions to accommodate the special characters.

="form URL?" + "BARCODE=" + BARCODE@row + "&PATIENT NAME=" + [Patient Name]@row + "&Next Field=" + [Next Field]@row

="form URL?" + SUBSTITUTE(SUBSTITUTE("BARCODE=" + BARCODE@row + "&PATIENT NAME=" + [Patient Name]@row + "&Next Field=" + [Next Field]@row, " ", "%20"), ",", "%2C")

Sam Lugiano

Thanks Paul. I think I'm a little closer but I keep running into a couple of issues. I think the are being caused by the hyperlink not continuing through the whole string as seen below.

This is the formula:

Leibel S

@Sam Lugiano

If there is a space or comma it wont bring it into the URI

As Paul pointed out, first write the formula to bring the text together and then wrap SUBSTITUTE functions around it to replace the spaces, commas, and any other character you may need...

Sam Lugiano

Thank you. I don't think there are any commas or spaces in the formula. It does look like there are spaces on the screen shot but there are not.

Paul Newcome

You need the SUBSTITUTE functions as previously mentioned. Your URL that is output by the formula has...

Space between "PATIENT" and "NAME"

Comma after "EDWARDS"

Space after above mentioned comma

And I believe the forward slashes in the date also need to be percent encoded using the SUBSTITUTE method.

Sam Lugiano

Great! Thank you for your help!

Paul Newcome

Happy to help. 👍️