I would like to write an SQL query to analyze data typed into my forms.  How can I find where this data is stored?

CA Harvest SCM all versions and platforms
Queries were written for an Oracle database but can be adapted for SQL Server

To get to the answer we need to first know the “type” of form that has the data you would like to analyze.  In Harvest you can have many different types of forms.  Each type of form has its own list of data fields in its own unique layout, and has its own unique data table in the Harvest database to store information entered into the forms of that type.

So, we start by finding the “Form Type” of the form that contains the data to be analyzed.  You can find this by opening a form and looking at the tab at the bottom of the form.  This will tell us the form type.  In this example on my test database I looked and found that the form type is “Defect Tracking”.

<Please see attached file for image>

Next, we will need to refer to the HARFORMTYPE table in the Harvest database to find the name of the table that contains the data for all the forms of this type.  Look in the "FORMTYPENAME" column for the name of the form type.  Then look across the same row to the FORMTABLENAME column and this will tell you the name of the table in the Harvest database containing the data for all forms of this type.

<Please see attached file for image>

The data you would like to analyze will most likely be found in a specific field on this type of form.  This field on the form will have a label (the word you see next to the field on your form) and a database column (the column in the form's database table where the data you type in is stored).  To find the name of the column in this table containing the data you need we can look at the properties for the form type in the Administrator Tool.  In this example, I want to locate information about the "Category" field on the "Defect Tracking" form:

<Please see attached file for image>

Or you can look in the XML file for your form type.  Look on your broker machine for a file with the name of your form type and a ".xml" extension.  On Windows you will find this in the C:\ProgramData\CA\SCM\Forms folder, and on Linux or Unix you will find this in the $CA_SCM_HOME/Forms folder.  Once you find the XML file for your form type, edit it and locate the entry that has "label=" followed by the lable for your field.  Then find the “dbfield=” entry on the same line – that will be the name of the column in the data table.

<Please see attached file for image>

Now we have all the information we need to build a query that will tell us the information that has been typed into the form for every package in your Harvest database.  In this example, we are looking for the data in the "Category" field on the Defect Tracking form type.

<Please see attached file for image>

SELECT HARENVIRONMENT.ENVIRONMENTNAME,
  HARPACKAGE.PACKAGENAME,
  HARDEFECT.DTCATEGORY
FROM HARENVIRONMENT
INNER JOIN HARPACKAGE ON HARENVIRONMENT.ENVOBJID = HARPACKAGE.ENVOBJID
INNER JOIN HARASSOCPKG ON HARPACKAGE.PACKAGEOBJID = HARASSOCPKG.ASSOCPKGID
INNER JOIN HARDEFECT ON HARASSOCPKG.FORMOBJID = HARDEFECT.FORMOBJID
 
The HARASSOCPKG table is the one that shows which forms are associated with which packages.