Filtering Defects using Defect ID (BG_BUG_ID) in ALM/QC

· by · in ALM/QC, Defects, Managing ALM Defects, OTA API. ·

Hello Friends,

In this post, we will discuss filtering ALM defect using its primary ID (BG_BUG_ID) field.

Filtering Defects (Bugs) from ALM/QC using Defect ID (BG_BUG_ID)

For filtering defects using defect ID, copy the below code and execute, function will return the ALM defect(Bug) object (TDAPIOLELib.Bug).

Details about how to connect to ALM using OTA API is here

public static TDAPIOLELib.Bug GetALMDefectUsingDefectID(String DefectID)
{
      TDAPIOLELib.BugFactory OBugFactory = OQConnection.BugFactory as TDAPIOLELib.BugFactory;
      TDAPIOLELib.TDFilter OTDFilter = OBugFactory.Filter as TDAPIOLELib.TDFilter;
      TDAPIOLELib.List OBugList;
      TDAPIOLELib.Bug OBug;
      try
      {
            OTDFilter["BG_BUG_ID"] = DefectID;
            OBugList = OBugFactory.NewList(OTDFilter.Text);
            if (OBugList != null && OBugList.Count == 1)
            {
                  OBug = OBugList[1];
                  return OBug;
            }
            else
            {
                  return null;
            }
      }
      catch (Exception ex)
      {
            Debug.WriteLine("Error occurred while fetching the Defect object using Defect ID : " + ex.Message.ToString());
            return null;
      }
}

How it works?

For finding the Defect with Bug id, we will use TDFilter object. First, we will have to cast the TDFilter object using

TDAPIOLELib.TDFilter OTDFilter = OBugFactory.Filter as TDAPIOLELib.TDFilter;

Then we will use this object for setting the filter for the Defect id field, which will create the filter text

OTDFilter["BG_BUG_ID"] = DefectID;

Once we have the TDFilter populated with the desired filter text, then we will used the BugFactory object to create the list of Defects with BG_BUG_ID = DefectID, which is passed to the function using

OBugList = OBugFactory.NewList(OTDFilter.Text);

Now, if the defect id passed to the function does not exist or there are any other errors, then OBugList will have null value stored in it otherwise it should have the list of defects having the criteria defined within the filter.

Now, because we are using the filter field as the primary key of the defect table, we will only get one defect in the results, so to make sure we can check if the count of defects inside the OBugList.Count is 1.

Then we can simply get our defect object by using

OBug = OBugList[1];

Now, we can use this object for updating any field value.
Let me know, if you need any help, I will try to provide the solutions.

Thanks

12 responses to “Filtering Defects using Defect ID (BG_BUG_ID) in ALM/QC”

  1. Hi Sumeet,
    (sorry I put this comment in a different post)

    I’m facing an issue I think is about the filters, I tried to translate the next query to the OTA API with the bugFactory

    //The users have a query like the next
    SELECT
    BUG.BG_USER_72 as Application,
    BUG.BG_BUG_ID as CR_ID

    FROM bug
    Where
    BUG.BG_BUG_ID > 666666
    AND BUG.BG_USER_70 not like ‘BLABLA’
    AND BUG.BG_USER_70 not like ‘ETC’
    AND BUG.BG_USER_70 not like ‘OTHER STUFF’

    This is my c# translation of the query
    var bugs = this.ALMConnection.BugFactory;
    var bugFilter = bugs.Filter();

    //Is the next the right way to set multiple filters?
    bugFilter[“BG_BUG_ID”] = ” > 666666″;
    bugFilter[“BG_USER_70″] = ” not like ‘BLABLA’ AND not like ‘ETC’ AND not like ‘OTHER STUFF’”;

    What is the correct way to put multiple filters for one field?

    As you can see I put the same filters on c# OTA API but with the API I retrieve like 3000 more or less records I don’t know what is wrong if I put the same filters.

    PD: Send the same query to the API is not an option because limited access;
    PD2: More deeply analysis seems like only the first filter of user_70 is applied ===> not like ‘BLABLA’ but the others are not.

    Like

    • Hi,

      If you dont know, how to create the right filter condition in the code then there is a easy way to find it out

      1) Set the filter in the ALM defect module (For all the filter you want)
      2) Click on the Filter Icon again and Copy the Filter Settings
      3) Paste it in the notepad. It will look like this

      [Filter]{
      TableName:BUG,
      ColumnName:BG_SEVERITY,
      LogicalFilter:S1-Critical,
      VisualFilter:S1-Critical,
      NO_CASE:
      }
      {
      TableName:BUG,
      ColumnName:BG_STATUS,
      LogicalFilter:Closed,
      VisualFilter:Closed,
      NO_CASE:
      }
      {
      TableName:BUG,
      ColumnName:\00000013\BG_USER_TEMPLATE_03,
      LogicalFilter:Bug,
      VisualFilter:Bug,
      NO_CASE:
      }

      4) That will be your Filter Text.
      5) Create the Filter Object in the OTA Api and Set the Text with the Text you just copied in the ALM.

      TDAPIOLELib.TDFilter OTDFilter = OBugFactory.Filter as TDAPIOLELib.TDFilter;

      OTDFilter.Text = Text_From_ALM

      7) Change the Filter values in that text
      6) Apply the filter, you should see the same results.

      Another easy way is to directly use the same query in the OTA API. You can execute the queries using the TDCommand Object. If you want the code, I can send that to you.

      Let me know, If this works out for you.

      Thanks,
      Sumeet Singh Kushwah

      Liked by 1 person

  2. hi Sumeet,

    How to get count of defects from QC using excel vb script?
    I could write SQL in QC analysis tab but not able to put it in Excel VB script.

    Thanks,
    Tejaswitha.

    Like

    • Below is the code

      ” Add reference for the OTA ComType Library
      Sub getDefectCount()
      Dim QCConnection As New TDAPIOLELib.TDConnection
      QCConnection.InitConnectionEx “https://yourALMServer/qcbin”
      QCConnection.Login “UserName”, “Password”
      QCConnection.Connect “Domain”, “Project”

      Set com = QCConnection.Command
      com.CommandText = “SELECT Count(*) from bug”
      Set recset = com.Execute

      MsgBox recset.FieldValue(0)

      QCConnection.Disconnect
      QCConnection.Logout
      QCConnection.ReleaseConnection

      End Sub

      Like

  3. Thanks Sumeet for .commandText suggestion.
    Could you please suggest to pass parameters .. like below
    com.CommandText = “Select Count(*) from bug where BG_DETECTED_IN_RCYC in (‘@QCCyc’)”
    Set recSet = com.Execute
    QCCyc – is Global variable i have used in my script.

    But when i run the script “Run Time error – Failed to Run query” is returned.

    Like

      • hi Sumeet,
        Could you please help on filtering defects within date range required?

        I used below script:
        com.CommandText = “Select Count(*) from bug where BG_DETECTED_Date in (” + DtRange + “)”

        DtRange carries a value of “>=21-Sep-2017 and <=30-Sep-2017" in excel sheet cell value.
        I tried to declare this variable as string and Date but it is considering only last date to filter the defects.

        Thanks!!

        Like

      • try this one, this should work for Oracle Database if you have SQL Server then just search how to convert date string to date in oracle.

        SELECT * FROM Bug where BG_DETECTION_DATE >= TO_DATE(‘2017-09-21′,’YYYY-MM-DD’)

        Thanks,
        Sumeet

        Like

  4. I am getting script error , failed to run query at –> Set RecSet = com.Execute
    ———–CODE—————-
    If User.Checked Then
    If Group.Checked = False Then
    ‘- increment row
    intPos = intPos + 1
    End If
    intPos = intPos + 1
    objExcel.Cells(intPos, 1).Value = “User”
    objExcel.Cells(intPos, 1).Font.Size = 12
    objExcel.Cells(intPos, 1).Font.ColorIndex = 41 ‘- blue
    objExcel.Cells(intPos, 2).Value = “Group Assigned”
    objExcel.Cells(intPos, 2).Font.Size = 12
    objExcel.Cells(intPos, 2).Font.ColorIndex = 41 ‘- blue
    objExcel.Cells(intPos, 3).Value = “Full Name”
    objExcel.Cells(intPos, 3).Font.Size = 12
    objExcel.Cells(intPos, 3).Font.ColorIndex = 41 ‘- blue
    objExcel.Cells(intPos, 4).Value = “Email”
    objExcel.Cells(intPos, 4).Font.Size = 12
    objExcel.Cells(intPos, 4).Font.ColorIndex = 41 ‘- blue
    ‘- increment row
    intPos = intPos + 1

    sQuery = “SELECT USERS.US_USERNAME, ”
    sQuery = sQuery & “GROUPS.GR_GROUP_NAME ”
    sQuery = sQuery & “FROM USERS, GROUPS ”
    sQuery = sQuery & “WHERE(INSTR(US_GROUP,1,1,1)-1) = GROUPS.GR_GROUP_ID ”
    sQuery = sQuery & “ORDER BY GROUPS.GR_GROUP_NAME”

    com.CommandText = sQuery
    Set RecSet = com.Execute
    ‘- output row to Excel
    For i = 1 To RecSet.RecordCount
    ‘- name
    strValue = RecSet.FieldValue(0)
    objExcel.Cells(intPos, 1).Value = strValue
    ———-CODE——————

    Like

  5. Hi Sir,

    You had posted very good information above
    Thanks for it.i have one requirement to automate clicking on the generate button once all is logined.

    Step 1 – connect Alm with credentials
    Step 2- once it is logined select the date of release and click on the generate button to download all the information of the current release.

    Kindly help me to automate it.

    Like

Leave a comment