Prefilling forms

John Schell

I am prefilling a form designed using worksheet "B" with data from worksheet "A" using the Substitute function. Worksheet "B" is a test defect tracker, worksheet "A" is a test script tracker. If a test script fails. I want to take several fields from the test script tracker, prepopulate them on a form developed using the defect tracker, have the tester fill out some additional information and then have the entire form update the defect tracker with the combination of prefilled data and manually entered data. The following formula is working.

=IF(AND([Primary Test - Results Dropdown Menu]@row = "Fail", [Defect Number]@row = ""),

"https://app.smartsheet.com/b/form/e3828156241842278a19db84dd6cf1c9?Test%20Case%20ID="

+ SUBSTITUTE([Test Case ID]@row, " ", "%20") + "&" + "Tester%20Name=" + SUBSTITUTE([Primary Tester Name]@row, " ", "%20") + "&" + "Test%20Case%20Name=" + SUBSTITUTE([Test Case Name]@row, " ", "%20") + "&" + "Test%20Scenario=" + SUBSTITUTE([Test Scenario]@row, " ", "%20") + "&" + "Failed%20Step=" + SUBSTITUTE([If Fail Add Step #]@row, " ", "%20") + "&" + "Date%20Defect%20Identified=" + SUBSTITUTE([Date Defect Identified]@row, " ", "%20") + "&" + "HRC%20Module=" + SUBSTITUTE([HRC Module]@row, " ", "%20") + "&" + "Defect%20Description=" + SUBSTITUTE([Defect Description]@row, " ", "%20"), "")

Unfortunately, if the composer of the test script uses a comma in their test script text the formula will pre-fill up to the point of the comman and then will not pre-fill anything beyond that. This also appears to be happening intermittently meaning that I have seen comma's used successfully then others stop the pre-filling immediately. No idea what is going on here.

Paul Newcome

My first recommendation would be to simplify the formula by using only a single SUBSTITUTE function to replace the spaces.

Generate the string as is then wrap the whole thing in the SUBSTITUTE function.

=IF(AND(..........), "base_url" + SUBSTITUTE([Test Case ID]@row + "&" + "Tester%20Name=" + [Primary Tester Name]@row + ......................., " ", "%20"))

Then you can wrap that in a second SUBSTITUTE to replace the commas that need replacing.

=IF(AND(..........), "base_url" + SUBSTITUTE(SUBSTITUTE(..........), ",", "%2C"))

John Schell

Paul,

Thank you for the 'Substitute' suggestion. I will give that a try.

As to the commas, I wasn't trying to put them back in, the problem was that if the commas were there as originally written, i.e. "Invoice PDF attachment, Logo, and Table Validation: Unearned Discount Notification" the code would insert "Invoice PDF attachment" in the test case name field and it would stop there. Any downstream "Substitutions" would not take place and my prefilled form would be mostly blank. Test Scenario, If Fail Add Step, Date Defect Identified, HRC Module and Defect Description would not populate in the form. The code seemed to stop executing as soon as it hit the comma. Have you seen this before? Is something in my code causing this?

Paul Newcome

I'm pretty sure it has to do with the commas. Because it is not a valid character in a URL, it will stop there which is why you need to replace them all with the %2C.

John Schell

Paul,

With this code;

=IF(AND([Status1]@row = "Fail", [Defect Number]@row = ""),

"https://app.smartsheet.com/b/form/6c12d5fc96b8452f9ac2c1e1a4be5041",

+ SUBSTITUTE("Test%20Case%20ID2=" + [Test Case ID1]@row, " ", "%20"))

The form is coming up when status1=fail and defect number is blank but not with any prefilled data for Test Case ID. In every other situation I get an invalid operation error. I am trying to do what you suggested above by simplifying my 'Substitutes" so I figured I would start with one and the build off of it. Unfortunately, I can't get this one to work, i.e. prefill the form for Test Case ID2 with the Test Case ID1 data. Thoughts

Paul Newcome

It looks like you have a comma after the base URL that will need to be removed.