Extracting data typed into Harvest Forms

Products

• CA Harvest Software Change Manager
• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

I need to extract the data from all forms in Harvest to an outside file structure. Can i do this with hco or an sqlplus command?

Environment

Harvest Software Change Manager all versions

Resolution

In Harvest, users can use different types of forms to collect different kinds of information.  For each form associated with a package you can see the name of the "Form Type" by looking at the tab at the bottom of the form.

Each “Form Type” has a specific format and stores its data in a custom table within the Harvest database.  Each row in this table corresponds to a Form of that “Form Type” associated with a package.  You can see the format definition and the name of the table by looking on the “Forms” tab in the Administrator Tool.  Clicking on the name of the form type will present an example of the format or layout of the form.  

Right clicking on the name of the form type and selecting Properties will display the details about the form.  Of particular note, on the Form Name tab is the name of the custom database table for this form type.

Checking the Oracle or SQL Server database to see the contents of this table, a row for each “instance” of this type of form will be listed there.

In building a SQL query to extract this information, here are the other tables that might be needed:

HARFORM – contains form titles and form objids

HARFORMTYPE = contains the name of each form type and the name of the associated data table 

HARFORMTYPEDEFS = contains the list of fields on each of the form types

HARASSOCPKG = shows which specific forms are associated with which packages in the database

To retrieve attachments to forms in your Harvest database, since the attachments are stored as "blobs" in the database, it will be better to use a command line utility to get those.  The "hfatt" command will do this for you if you use the "get" option.  Here's where you can read more about the command:

Command Reference - hfatt Command-Form Attachment

The only thing you need is an easy way to get a list of the names of attachments for each form.  Here is a query that will do that:

SELECT HARFORM.FORMNAME,
  HARFORMTYPE.FORMTYPENAME,
  HARFORM.FORMOBJID,
  HARFORMATTACHMENT.ATTACHMENTNAME,
  HARFORMATTACHMENT.ATTACHMENTTYPE
FROM HARFORM
INNER JOIN HARFORMTYPE
ON HARFORMTYPE.FORMTYPEOBJID = HARFORM.FORMTYPEOBJID
INNER JOIN HARFORMATTACHMENT
ON HARFORM.FORMOBJID = HARFORMATTACHMENT.FORMOBJID

Once you have the list, you could set this up as a batch script to execute the "hfatt" command for each attachment in the list.