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

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

Advertisements

9 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

      Like

  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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s