Understanding Custom and Extended Custom fields

In order to query custom and extended custom fields using subqueries, it is important to understand which fields serve as primary keys, as well as what the database names are for each custom field.

The custom tables and primary keys for the five Visual Intercept Document Types are as follows:

Below is a list of the standard custom fields and their corresponding database column names:

Extended Custom fields and their corresponding database column names are as follows:

Creating Custom Queries

Queries for data in custom fields require the use of subqueries. Subqueries are added to a query using the Where Clause box of the Visual Intercept Query Builder.

All Queries for custom data have a similar structure. Once you understand the structure of one query for custom data, others can be constructed using the same pattern.

In the following example query, the statement to the left of the IN operator "Incident.IncidentID", is structured as it would be in a standard incident query. In that statement the query asserts that incident records are to be returned from the Incident table based on the values contained in the IncidentID column.

In a standard query you would simply add incident IDs to the right hand side of the IN operator, and the query would be complete. However, in the case of a query for custom data we want to select the incident IDs based on their relationship to custom data contained in another table. To select the incident ID’s based on data from another table we will use a subquery.

In the following example query, the statement to the right of the IN operator is a subquery. In this statement "SELECT Incident_Custom.IncidentID FROM Incident_Custom" specifies the table and the column from which the incident IDs will be selected. In this case the table is Incident_Custom and the column is IncidentID.

Left as is this query would return every incident that had an ID that matched and entry in the IncidentID column of the Incident_Custom table. However, the purpose of this query is to return specific incidents based on the value(s) of a custom field. To return incidents based on a specific custom field value(s) we will add a WHERE clause to the subquery that will limit the result of the subquery to only those records that have the desired value. In this particular example the clause "WHERE Incident_Custom.Custom5='Requirement'" will limit the incident IDs returned by the subquery to only those records where the Custom5 column has a value equal to "Requirement".

By following this pattern you can create queries which use any custom incident field as well as queries which use custom fields for other items such as contacts or projects. You can also use subqueries with other operators such as "NOT IN" to further refine your query results.

The format of a subquery statement for data contained in either a Custom or an Extended Custom field is the same. Below is an example of a query to find all incidents where the Custom Parameter1 field has a value of "Requirement":

Incident.IncidentID IN(SELECT Incident_Custom.IncidentID FROM Incident_Custom WHERE Incident_Custom.Custom5='Requirement')

To find all incidents where the Incident Extended Custom Date 1 field has a value of "04/19/2005", you would type the following in the Where Clause box:

Incident.IncidentID IN(SELECT Incident_CustomEx.IncidentID FROM Incident_CustomEx WHERE Incident_CustomEx.DateTime1='04-19-2005')

Similarly, to find all incidents where the Incident Extended Custom Boolean 1 field is not checked (set to "Yes" or "True") you would run the following:

Incident.IncidentID IN(SELECT Incident_CustomEx.IncidentID FROM Incident_CustomEx WHERE Incident_CustomEx.Bool1 IS NULL)

Querying custom and extended custom fields can be a simple as searching for a parameter value in one field or further limiting the criteria by searching several custom and stock fields together. For example, using the three queries above, you could further search for all incidents that have a status of "Closed", a Custom Parameter1 field set to "Requirement", a CustomEx Boolean 1 field set to True, and a CustomEx Date1 field with a value of "04/19/2005". Your statement in the Query Builder’s Where Clause box would look like:

Incident.Status = 'Closed' AND Incident.IncidentID IN( SELECT Incident_Custom.IncidentID FROM Incident_Custom WHERE Incident_Custom.Custom5='Requirement') AND (Incident.IncidentID IN(SELECT Incident_CustomEx.IncidentID FROM Incident_CustomEx WHERE Incident_CustomEx.DateTime1='04-19-2005' AND Incident_CustomEx.Bool1=1))

For additional information on using the Query Builder, including several useful query examples, please