Filter and query data
This topic describes how allows you to limit the scope of the returned data using filters or queries.
You can limit the scope of items retrieved from the data source for synchronization by defining filters or adding criteria to the data source.
The most efficient way to limit retrieved items is through filtering, via the Filter tab. The filter performs the filtering on the data source tool itself, resulting in a smaller amount of transferred data. For details, see Filter incoming data per connection.
Note: Creating a new filter requires expert knowledge of the filter syntax, such as those described in ALM Octane cross filtering.
Filtering the source through the Filter tab lets you improve performance by reducing the amount of network traffic. If there are items that result in warnings or alerts that you want to ignore, you can filter them out from the data source tool. For example, if there are invalid parent fields, you can filter them out.
If you encounter issues when using an ALM/QC filter with a date field, use the format YYYY-MM-DD in the filter. For example:
You can further limit the data by the opposite data source by setting synchronization criteria in the form of a query. The query is performed on the Micro Focus Connect client side and is applied to the data included by the filter.
This capability is especially useful when working with large data sets, where you need to determine which subsets to synchronize. You can set criteria for both the master and target. <![CDATA[ ]]>
You set up the criteria by choosing an operator, field name and field value.
In this example, all defects created after 12/31/2020, whose status is either In Progress or To Do, are included in the synchronization:
You set the synchronization criteria for your connections using logical statements. For details, see Free form filters.
Filter incoming data per connection
Available in versions: 4.4.2 and higher for the following connectors: ServiceNow, ALM Octane, ALM/QC
Filters let you limit the scope of items retrieved from the data source through synchronization, by applying a filter to the server.
Many of the data sources supported by Micro Focus Connect allow you to create and save data filters. If you want to limit the items that are synchronized between projects, you can create a data filter in the project that defines the items to be synchronized. In Micro Focus Connect, you can select the filter name.
When choosing the Use Named Filter option, in each iteration, Micro Focus Connect retrieves the items that match the filter, for data sources and projects.
Select the Use Free Form Filter option to manually define the filter conditions. For details, see Free form filters.
To filter incoming data for a connection:
In your tool, define a data filter and save it.
In Micro Focus Connect, open a connection for editing. Proceed to the Projects and Rules section of the wizard using the Next button.
Add a new rule or scroll down to the Type rule that you want to configure. Click the Edit button to open the Edit dialog box.
Select a project for both the Master and Target data sources.
Click the Filter tab for the Master or Target.
Select the Use Named Filter option.
Expand the Selected Filter dropdown and choose one of the project's filters. The Filter Details section displays the conditions.
At the bottom right, select a field to show in the test results. Click Test Filter in the bottom right, to preview the data that the filter includes. The preview lists up to ten items, and indicates the total number of items in the filter.
Free form filters
In addition to selecting a named filter, you can also manually enter filter conditions. This is useful if your tool doesn't support named filters. You can also copy the filter conditions of a named filter, and modify them in the free form window.
Note: This method requires advanced knowledge of filter syntax.
To use a free from filter, select the Use Free Form Filter option in the Edit Connection Type Rule dialog box and specify a query in the Filter Details area.
Use the Test Filter button to ensure that your filter conditions are correct.
Filter a ServiceNow data source
Data queried from a ServiceNow data source can be filtered in one of three ways:
- Per type: For each data source type, you can apply a unique server filter. This filter is applied to the ServiceNow server and only returns data that meets the filter criteria, for all connections associated with that data source. For an example, see Data source settings.
- Per connection: For each ServiceNow connection, you can apply a server filter for each type (and associated project, if relevant) to limit the data returned from the server. For details, see above.
- Client filters. A client filter can be applied for each type in the Sync Criteria area. For details, see Build a query.
These methods allow you to apply different levels of filtering. You can first constrain the data by filtering at the data source level, further constrain it at the connection level, and then tighten the constraint even further at the synchronization criteria level.
In most cases, you do not need to use all three levels. A best practice is to use server side filters on the connection, as described above. For ALM/QC and ServiceNow, server side filters are very effective in fetching limited quantities of server data at optimal throughput rates.
We recommend applying connection level filters for entities such as a ServiceNow Incident and an ALM/QC Requirement, where projects may contain thousands of artifacts.
To create and use a server filter for ServiceNow:
In the ServiceNow dashboard, navigate to All > System Definition > Filters.
Create a new filter in ServiceNow. For example,
Product is MFConnect AND Short description is not empty.
In Micro Focus Connect, open your connection for editing. Click Next to navigate to Projects and Rules. Click the Filter (Target) tab.
Make sure the Use Named Filter option is selected and choose the filter that you created above.
Build a query
This section describes how to build a query for synchronization criteria or on the When and From statements of a calculated value:
To build a query:
Click Add Child Criteria<![CDATA[ ]]>.
The Add Term dialog box opens.
- Select a data source property from the Property Name list, for example: Blocked.
Select an operator from the Operator list. For a list of operators, see Criteria operators.
- In the Field Value column, enter the value that the field must contain in order to be synchronized, for example: True. If you select the Is New or Is Modified operator, the Field Value is not specified.
- Optional - Double-click another row to specify synchronization criteria for another field.
Optional - Use one of the below query options to further manage your query.
Option Description Adds child criteria. Adds a child And/Or/Not node. Opens the edit mode to update criteria. Deletes the selected criteria.
The following table lists the operators for building a query for the synchronization criteria:
|Equals||The property equals the value.|
|Contained in String||
The property contains a part of the value.
For example, assume you have a field which can be 'SAM', 'BOB', or 'GREG'. If you specify Contained in String: 'SAMBOB', the result for 'SAM' is TRUE. The result for 'GREG' is false. If you leave this field empty, it returns TRUE.
|Starts With||The property starts with a specified letter or number.|
|Ends With||The property name ends with a specified letter or number.|
|Contained in Set||
The property is contained within a set. Contained in Set is an OR operation.
The property includes the value.
Includes is an AND operation, and is useful for determining if a field contains a particular value. It is often used when the field is a multiple value property, such as the Tags field. For example, assume you have a field which can be 'SAM', 'BOB', or 'GREG'. To determine if the Tags property contains 'GREG', use the Includes operator, specify the Tags property as the field, with the value 'GREG'. A value of true indicates that 'GREG' appears.
|Greater Than||The property is larger in number than the value.|
|Less Than||The property is smaller than the value.|
|Greater Than Or Equals||The property is larger or equal to the value.|
|Less Than Or Equals||The property is smaller than or equal to the value.|
|Is Descendant Of<![CDATA[ ]]>||
Use for items contained in an hierarchical tree, such as stories and requirements. For example:
|Instance Of||Use with Property or Type Name. For example:
|Is Empty||The property value is empty.|
|Is New||The asset is being created for the first time. This operator is not available when setting up a Synchronization Criteria query.|
|Is Modified||The asset has been modified. This operator is not available when setting up a Synchronization Criteria query.|