The question of the week comes one of my co-workers who asked:
“I'm looking to use the ALM11 API to update a field within an existing Defect. I know you are using the API to create Defects. Have you (or know how to) update a field on an existing Defect?”
OTA Code to Update a Defect in ALM/QC
The following example updates a defect that has the ID of 17 and changes the status field to Fixed. The code is pretty straight forward and uses the OTA's BugFactory object to accomplish our goal. Creating an instance of the BugFactory object allows us to access all the services needed to manage defect records.
'================================================
set tdc = createobject("TDApiOle80.TDConnection")
tdc.InitConnectionEx "http://yourURL/qcbin"
tdc.Login "yourName","yourPassword"
tdc.Connect "yourDomain","yourProject"
'============================================
DefectID = 17
Set BugFactory = tdc.BugFactory
Set BugFilter = BugFactory.Filter
BugFilter.Filter("BG_BUG_ID") = DefectID
Set BugList = BugFilter.NewList
Set myBug = BugList.Item(1)
'Just need to know the qc field name value
myBug.Field("BG_STATUS") = "Fixed"
myBug.Post
Set BugFilter = Nothing
Set myBug = Nothing
Set BugFactory = Nothing
Set BugFilter = Nothing
How to run the OTA code
To run the defect code you can either place it in QTP and run as a script or place the code in a text file and save as a .VBS vbscript file.
Of course you will be using the field names found in your ALM project. If you don't know what the actual field names are, you can easily find them by going into QC's Tools>Customize. In the Project Customization section and go into the “Project Entities” section. Under the Project Entities Tree view click expand Defect and click on your System Folder or User Fields. Clicking on a field will reveal the field name that you will need to use:
how to append value in the “Comments” field of the bug ? I want the existing comments to be retained and just want to add a line more before changing the status of bug.
The above mentioned code overwrites my entire “Comments” field with the single line that i’m trying to add.
Thanks,
preeti » Hello- Before doing the post you can get the existing value and append your new text to it then post the new comment value. For example:
nowComment = myBug.Field(“BG_DEV_COMMENTS”)
newComment = nowComment & ” this is the text I’d like to append”
myBug.Field(“BG_DEV_COMMENTS”) = newComment
Make sense?
Can we upload fields value through excel to test lab using OTA…?
sandhya » What values in the test lab are you trying to update?
Hi,
I am trying to get a list of defects and im using the release field as filter for the same. I’m facing 2 problems:
1) My release name say example month date contains space in between so its giving an error for space also
2) I tried with Field BG_TARGET_REL with .Name and .Value also but its erroring out
Please let me know how to go about this.
Hi,
I have a field in test set which is a list. I want to add more items to the list. how do I achieve it, please let me know. I am using perl script to connect to QC.
Thanks
Giridhar
Giridhar » Hi Giridhar – you should be able to do this by using the OTA Customization option. For an example check out QC List customization–Adding list items using OTA API
Jatinder Singh » Can you send me the code that is causing you this issue? For adding the BG_TARGET_REL did you try bug.Field(“BG_TARGET_REL”) = “yourRelease”? To remove unwanted spaces in a variable you can use the r = instr(myrelDate,” “,””)
Hi,
I am trying to submit a defect through QTP. I have provided all the values needed for posting the Defect.
Bug.Post logs the defect in QC. But the automatic mail notification that should happen when a defect is assigned to someone – doesn’t happen. The mail get triggered when I manually submit a defect.
Any thoughts on what I am missing ??
Thanks,
Rita
Hi great article… really useful.
Just wondering… Is there anyway to evaluate whether or not BugFilter.Filter has results before continuing. I am using an excel report, looping through it to update bugs in QC. I look for a particular project number in the excel file to match a field in QC. Only if there is a match I want it to update the field.
Hi.
I’m also having a problem accessing the bug field, BG_TARGET_REL. I keep getting “invalid property assignment”, is it because the field is not a string?
Code (with and without quotes “1029”) looks like:
and Bug.Field(“BG_TARGET_REL”) = 1029
And with this:
Sheet.Cells(Row, 9).Value = Bug.Field(“BG_TARGET_REL”)
I get an unknown error.
I’ve checked the OTA client DLL.
Thanks !
K.
Found the answer. First of all, need to check that the field has a value (i.e. IsEmpty = False); then, need to refer to the field as: bug.Field(“BG_TARGET_REL”).Name.
Kweeks » Cool – thanks for the update!!
Hi,
I want to get the defect COUNT from QC using a VB code with the following Filters combination:
/*1.BG_DETECTED_IN_REL=”December 2012 ”
2.BG_STATUS=”Closed”
3.BG_DETECTED_BY=”aa39645″ */
The following is the code that I have used.I tried to output the Count of defects satisfyingt the above filter conditions,but I am getting AUTOMATION ERROR when I ran the VB script.Please help
‘MY CODE
Sub DefectHistoryReport()
‘Give the ALM URL
strQCURL = “http://qtomavmpc025:8080/qcbin/”
strQCDomain = “DEFAULT”
‘Select the Project for which we need to pull the data
strQCProject = “My QC Project”
‘Enter the ALM User name and Password
User = InputBox(“Enter QC username”, “******”)
pass = InputBox(“Enter QC password”, “******”)
Dim com ‘As TDAPIOLELib.Command
Dim bugfac ‘As TDAPIOLELib.TestFactory
Dim buglist ‘As TDAPIOLELib.List
Dim bugfilter ‘As TDAPIOLELib.TDFilter
Dim hst ‘As TDAPIOLELib.History
Dim hstRec ‘As TDAPIOLELib.HistoryRecord
Dim hstList ‘As TDAPIOLELib.List
Set tdc = CreateObject(“TDApiOle80.TDConnection”)
tdc.InitConnectionEx (strQCURL)
tdc.Login User, pass
tdc.Connect strQCDomain, strQCProject
Dim Bug_Ct: Bug_Ct = 0
Set oBugFactory = tdc.BugFactory
Set oBugFilter1 = oBugFactory.Filter
oBugFilter1.Filter(“BG_DETECTED_IN_REL”) = “December 2012 ” ‘Name of the Release
oBugFilter1.Filter(“BG_STATUS”) = “Closed” ‘Defect status for which count is calculated
oBugFilter1.Filter(“BG_DETECTED_BY”) = “****” ‘QC Username of the testers for whom the count is calculated
Bug_Ct = Bug_Ct + oBugFactory.NewList(oBugFilter1.Text).Count
MsgBox Bug_Ct
Set oBugFilter1 = Nothing
tdc.DisconnectProject
tdc.ReleaseConnection
End Sub
Maharshi Gurrala » What line of code is it failing on? What happens if you try to simplify it (using just one filter on status) to see if it works. The following vbscript that worked for me:
‘================================================
set tdc = createobject(“TDApiOle80.TDConnection”)
tdc.InitConnectionEx “http://yourURL/qcbin”
tdc.Login “yourName”,”yourPassword”
tdc.Connect “yourDomain”,”yourProject”
‘============================================
Set BugFactory = tdc.BugFactory
Set BugFilter = BugFactory.Filter
BugFilter.Filter(“BG_STATUS”) = “Closed”
BugList = BugFilter.NewList.Count
msgbox BugList
Hi Joe,
While updating comments in QC , is it possible to get the username date format as reflecting in HP QC using vbs.
Hi Joe,
Just wondering, since ALM 11 provides REST API, have you tried that method? Can you share us your experience?
Regards,
Jimmy.
Jimmy » HI Jimmy – I do have a post planned on the ALM REST API – I just have not done it yet. I will try to get it posted soon.
I am new to QTP and I am using QTP 11.0 for functional testing.
I have a scenario where I have QTP is installed (and Scripts) are on one machine (Say Machine1) and AUT is in another machine (Say Machine 2)
and both Machine 1 & Machine 2 are in same network.
I want to run my qtp scripts such a way that Script must run on Machine 1 but Action must be performed on Machine 2.
I am using Win XP in both machines.
Please let me know the procedure.
Regards,
RAJ
Hi, i am trying to extract the values of Detected in Cycle field from QC defects section.
well i treid below code to extract value of Defect status field:
Set cust = QCConnection.customization
Set custlists = cust.Lists
Set custlist = cutslists.List
Set custlist = custlists.List(“Bug Status”) ‘it is listing complete bug status values not specific ‘to the project
Set listrootnode = custlist.RootNode
Set listchildren = listrootnode.Children
For Each listname In listchildren
MsgBox (listname.Name) ‘Assigned, deferred, cancel…… all are displayed
Next
But here i am not able to replace “Bug Status” for “Detected in Cycle”…So please help me by letting me know what is the custom list name for Detected in cycle field. Or please provide any solution if you got any.
Hi Joe
The actual fix time field in defect is not getting updated automatically. We need to click on cell to update it. Please let me know what can be done to make function properly.
Thanks
Nitin
Detected in Cycle = BG_DETECTED_IN_RCYC
This is excellent, Able to update the defect fields. Is there a way to get the inputs for defect ID and status from a excel sheet.
I want to get Planned prod Date and Detected in Env(BG_DETECTED_IN_RCYC) from QC 11.0
Could you please help me.
Thanks in advance
Hi, When i try to assign the value to the specific field in the release using the below code
val.Field(“Rel_REQ_Count”)=5, i am getting the below error
SyntaxError: can’t assign to function call
Can anyone help to resolve this issue ?
is it possible to assign the value to the functional call ?
How do i know the columns in the bug table?
I am able to find
Bug.Summary
bug.Priority
bug.status
bug.post
But I need other column names so that I can copy data from my excel and post a bug in QC using tdapiolelib.dll
How to use OTA to link a requirement to a defect
Hi I am trying to upload defects to QC fro Excel inputs. I have a field ‘BG_DETECTED_IN_RCYC’ which is mandatory in QC. I am unable to pass the value for that field from Excel. I found that this field is actually taking the value from release folder based on the ID. similarly there are other requirements fields which is not actually a field in Bug table but a reference from the requirement table.
now my question is how to assign the value to this field?
code goes here:
Set BugF = tdc.BugFactory
Set Bug1 = BugF.AddItem(Null)
‘for all the mandatory fields
Bug1.field(“BG_DETECTED_IN_RCYC”) = Sheets(“Defects”).Cells(6, 3).Value – its picking blank value.
Bug1.post
i tried getting the value and i am unable to assign it to this field. can anyone help me out with exact code that i need to use?
you can mail me @ prasanmgc@gmail.com
Does anyone else know how to do this?
Hi,
I want to update the Test: Comments column under executiong grid QC test lab.
Can some one help me with the code.
Thanks,
Ravi
This is awesome.
I am using the code suggested by you as below to update the defect comments section by adding line below the existing comments .
‘nowcomment = myBug.Field(“BG_DEV_COMMENTS”)
‘newComment = nowComment & ” this is the text I’d like to append”
‘myBug.Field(“BG_DEV_COMMENTS”) = newComment
But this code is not working as nowcomment = myBug.Field(“BG_DEV_COMMENTS”) has no value when running in debug mode.Its not showing the existing values in comments sections.
Can you please help its really very imp for me.
Nitin.
Here is the VBSCript I use for the information that is important to me….
Option Explicit
Dim qcServer, project, tdc
dim custfield, custfields, cust, msg, listname, bglist
Dim fileName, fso, f
project = “your project name”
fileName = “C:\Temp\” & project & “.csv”
Set fso = CreateObject (“Scripting.FileSystemObject”)
Set f = fso.CreateTextFile (fileName, True)
qcServer = “http://your server/qcbin”
Set tdc = CreateObject(“tdapiole80.tdconnection”)
tdc.InitConnectionEx qcServer
tdc.Login “your user name”, “your user passwd”
tdc.Connect “your domain name” , project
Set cust = tdc.Customization
Set custfields = cust.Fields
‘Column headers for the csv file
msg = “Field Name,Field Label,Type,Mask,List Name,Keep History,Required,” & _
“Verify,Multi Value,Searchable” & chr(13)
f.WriteLine(msg)
For Each custfield In custfields.Fields(“Bug”)
listname = “”
Set bglist = custfield.List
if Not bglist is Nothing then
listname = bglist.name
End If
If CustField.IsActive then
msg = custfield.ColumnName & “,” & _
custfield.UserLabel & “,” & _
custfield.UserColumnType & “,” & _
custfield.editMask & “,” & _
listname & “,” & _
custfield.IsHistory & “,” & _
custfield.IsRequired & “,” & _
custfield.IsVerify & “,” & _
custfield.IsMultiValue & “,” & _
custfield.IsSearchable
f.WriteLine(msg)
End If
Next
Msgbox “Done!”
f.Close ()
tdc.DisconnectProject()
tdc.ReleaseConnection()
Set f = Nothing
Set fso = Nothing
Set tdc = Nothing
Set cust = Nothing
Set custfields = nothing
Sorry, I missed the bug table part. My response wasn’t quite what you are looking for.
Can any one tell what is the field name for Assigned to in QTP
if i give BG_ASSIGNED_TO its through error message. Please help
Please help me, now I cannot reference the OTAClient.dll with VB or
C# in Visual Studio 2010. And I cannot run the script with VBS as below. What can I do next?
I am using HP ALM QC tool to manage my bugs. Actually we have a dot net Application and we are inserting the bugs by using API.
We are able to post the comments from the application to QC tool through API using
bug[“BG_DEV_COMMENTS”]
But I want to pass the history to store something in History on QC tool. I don’t have a way for this.
We want to pass the values changed in History field.
Please help me out.
Thanks,
Hi,
Is there a way in qc workflow to filter detected in cycle ( defect form) by date/other way so that it only displays cycles based on current release ?
Please help me
Thanks
I want to fetch output of Dashboard SQL query into Excel sheet using macro
example:
SQL Query : Select * from Bug
I tried below code but getting Compile error: User defined type not defined.Please help.
Function ConnectToQC()
On Error Resume Next
Dim QCConnection
‘Create QC Connection Object to connect to QC
Set QCConnection = CreateObject(“TDApiOle80.TDConnection”)
Dim sURL, sUserName, sPassword, sDomain, sProject
sUserName = “User”
sPassword = “Pass”
sDomain = “Doman”
sProject = “Project”
sURL = “QC Server/qcbin/”
QCConnection.InitConnectionEx sURL
‘Authenticate your user ID and Password
QCConnection.Login sUserName, sPassword
‘Quit if QC Authentication fails
If (QCConnection.LoggedIn True) Then
MsgBox “QC User Authentication Failed”
End
End If
‘Login to your Domain and Project
QCConnection.Connect sDomain, sProject
‘Quit if login fails to specified Domain and Project
If (QCConnection.AuthenticationToken = “”) Then
MsgBox “QC Project Failed to Connect to ” & sProject
QCConnection.Disconnect
End
Else
MsgBox “Welcome to ALM”
call runQuery
End If
End Function
Function runQuery()
Dim td, sQuery
td = CreateObject(“TDApiOle80.TDConnection”)
Dim com As TDAPIOLELib.Command ”’ Getting error at this line
Dim RecSet As TDAPIOLELib.Recordset
com = td.Command
sQuery = “SELECT * FROM DEFECT ”
com.CommandText = sQuery
RecSet = com.Execute
MsgBox “Total Records” + RecSet.RecordCount
For i = 1 To RecSet1.RecordCount
Sheet.Cells(Row, 1).Value = RecSet1.FieldValue(0)
Sheet.Cells(Row, 2).Value = RecSet1.FieldValue(1)
Sheet.Cells(Row, 3).Value = RecSet1.FieldValue(2)
Sheet.Cells(Row, 4).Value = RecSet1.FieldValue(3)
Next
End Function
I want to extract latest comment added by dev. to a defect using VBA. Can someone please help me?
Hi, thanks for the blog.
Does anyone has a solution for the following.
We want to update a defect field based on the users area. So if the user is from Procurement we want to populate the field with Procurement.
Would a workflow be possible were we crosscheck the assigned user in an excel where they all have their assigned area in to send back and populate the field?
what is the bug factory field name for “Root cause” in defect section of HP ALM ? i tried using BG_USER_6 ,but it is not working .
hey joe,
i my automation framework i have to integrate HP ALM with selenium via OTA,
The script has to be in preferably in java, python.
I have to integrate in such a way that it can upload the attachments, change test case status pass/fail and all major activities.
can you please help on this, is there any documentation on this or any reference.
Thanks
Vishrut