Unique IDs for Testing Documentation

WilliamPaschall

I may be overcomplicating this, but I wanted to see if you guys could help me.

My team is building out some testing documentation for our projects. Our current set up is that we have a sheet that is basically divided into two halves. The first half is the general information for the test scenario.

The second half is for the person to fill out any defect information if the test scenario fails.

Right now, I have it set up that if the test scenario fails, an automation will copy the row to another sheet I called Testing Defect ID Sheet. The sole purpose of this sheet is to assign the failed test scenario a Defect ID using an auto-number column. The Defect ID column on the original sheet has a column formula that does an INDEX/MATCH to that Defect ID Sheet to pull the Defect ID for that test scenario by matching the Test Activity ID. This is all working great except for the fact that the automation does not update when you provision a new project to point to the new Defect ID Sheet instead of the one that is in the blueprint. We are not needing all of these testing documentation sharing the same Defect ID Sheet. The Defect IDs need to be unique to each project.

I know a workaround for this is to just manually update the automation whenever we provision a new project, but does anyone else have a suggestion to where this can be done automatically or have a formula suggestion that I can use for the Defect ID that will only generate a number if the test scenario fails?

Austin Smith

Not sure if this is what you're looking for:

=IF(Result@row="Failed",[DefectID]@row+"_"+Left(testedby@row)+MID([testedby]@row, FIND(" ",testedby@row)+1,1)+"_"+[recorded open date]@row," ")

should be a Defect ID: 00001_AB_03/23/23

(defect ID, initials of operator, date)

if you have multiple failures per day per operator, you'd just throw another category in

to safeguard data permanency, you could have an automation fill out a secondary sheet with this data whenever this field is populated - it would just be a holding site

Deric

Instead of using the new sheet to create an auto id, can you have a composite id in your original sheet? For example, could you use your "Test Activity ID" and combine it with your failed description to create a unique composite id? This would have the added benefit of referencing the specific test. I am assuming that Test Activity ID is an auto number column and is always unique.

The formula would look something like this: IF(result@row = "Failed", "Defect ID - " + [Test Activity ID]@row, "")

WilliamPaschall

While both of those ideas would work, how would we be able to keep the defect id if the result changed to passed. The way it is set up, the tester fails the initial scenario. Then someone would go in behind them and fix the error and "pass" the defect. This would then trigger an automation that would change the initial test scenario to Retest. If we use these formulas, that Defect ID would then be lost as soon as the status changed to retest and then again to Passed if the fix worked. I was told that even if the defects gets fixed, we still need to keep that defect id as a record.

Deric

@WilliamPaschall

I hope you found an answer already! Sorry for not realizing you asked a follow up.

You can add an extra column that references the composite id column. It is always best for each record to have a unique id and then to create other ids off of that. =[key id]@row + [helper id]@row.