Auto create unique IDs for both Parent and Child Rows for Test Scripts


Help with logic for Test Script auto creation of the Test Case ID (Parent) and Step Number. Since this is Parent / Child the Column properties for auto / number system will not work.


Step Number:  

I created formula to auto generate step number ID by looking at the Step Details column and if the cell in row has any text/data it will look at the previous row number and add 1. 

=IF(ISBLANK([Step Details]@row), "End Script", [Step Number]1 + 1)

Question: Is there a way to automate the number when rows are inserted as we add steps to a script? I tried both @row and @cell but got errors. Work around is train users to copy and paste or drag down from previous row.


Test Case ID: 

Question: As this is the Parent row is there a way to have this number generated automatically when a new test case is created by looking at the other parent rows in this column and generating the next sequential number? Currently I am having to manually add this and hope I did not miss a row maybe it was hidden (not that we would want to but we could if a script is eliminated). 


Also in this column, as the child row, I used the formula =[Test Case ID]$1 + "." + [Test Case ID]@row The purpose of this is to create the unique Test Case ID with script number that will be used for defect tracking. 

Question: Since the number of rows will vary someone will have to manually enter update the formula since the Parent row number is dynamic based on the number of rows in particular script. I would like to eliminate need to lock the reference to specific cell by using logic. 

Part 2 of the question what could be added to logic that if the Step Number is End Script to leave this blank or fill End Script instead of the TC_03.End Script. (low priority if gets to complicated).

Kind Regards,  Bob

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!