Block Properties Filter Tab

For the Query Block and the Fetch Block, you can select one or more filters to narrow the data you retrieve from your Source Connection. See the examples below in Filtering Notes.

When a datetime is configured on the Query block on the Block Properties Net Change Tab to query for new and updated records, that configuration is treated as an additional filter. The Net Change datetime filter is applied as an AND after any other filters specified on the Block Properties Filter Tab. TIBCO Cloud™ Integration - Connect builds a query combining both the Net Change filter and the filters on the Filter tab. See Net Change And Filters for an example.

Some Connectors for TIBCO Cloud™ Integration - Connect only support one filter. For those Connectors you can use either Net Change or one filter on the Filter tab, not both.

Create A Filter

1. Navigate to the Flows section on the App Details page.
2. On the flows tab, open an existing flow to edit it or select New Flow.
3. Select Integration Flow to display the flow designer.
4. Begin building your flow. When you add either a Query or a Fetch block, select the block and select General on the Properties panel. The Properties dialog displays with the General tab selected.
5. Select an Entity.
6. On the Filter tab, select Add. A filter row displays.
7. Select the blank cell under Field to display any fields in the entity that can be used for filtering.
8. Select the field to filter by from the drop-down list.
9. Under Operator, select an operation, such as equals or is greater than.
10. In the Value field type a value or a formula, or select the Formula icon in the Value field to open the Formula Editor Overview. Use the editor to enter a formula, which can include fields, functions, and results from previous operations. TIBCO Cloud™ Integration - Connect uses this value to reduce the records retrieved by this Block.
11. If needed, select Add again to create another filter.

    After you select Add a second time, the And/Or column displays. Select and or or from the drop-down list to determine how to combine filters.

12. When you are done, continue defining this block or select OK to close the Properties dialog and return to the workspace.

Filtering Notes

Quotation marks are required around any string data. For example:

City equals "Salem"

If the source field is a DateTime field instead of a Date field, entering a date as the filter value, such as "10/12/2012", is translated to include the time as midnight on the selected date. For example, the following filter:

Date equals "10/12/2012" 

only returns records for which the Date is equal to "10/12/2012 00:00:00".

You can use Null, True, and False as filtering values. Quotation marks are not required for these values.

Use the percent (%) sign with the Is Like and Is Not Like operators as a wildcard to match one or more characters within a value. For example:

City Like "Win%"

Returns: Windsor, Winnipeg, and Winthrop

City Like  "%ington"

Returns: Lexington, Barrington, and Washington

When specifying multiple criteria, filter criteria are evaluated from the top to bottom.  The criterion at the bottom of the list is evaluated against all of the other criteria before it.

The following example:

And/Or	Field	Operator	Value
	LastName	equals	"Smith"
or	LastName	equals	"Smythe"
and	State	equals	"NY"

The filter settings shown above return records where both of these are true:

• LastName = "Smith" or "Smythe"
• MailingState = "NY"

Using mathematical notation, this statement can be read as:

((LastName ="Smith" or LastName ="Smythe") and MailingState = "NY")

Net Change And Filters

The datetime selected on the Query block Net Change tab is a filter that is always evaluated at the end of the list of filters as an AND. Using the example above, if you added a datetime field of LastModifiedDate with a value of 06/05/2017 09:15:00:00 AM on the Net Change Tab, TIBCO Cloud™ Integration - Connect would process the filters as follows: 

And/Or	Field	Operator	Value
	LastName	equals	"Smith"
or	LastName	equals	"Smythe"
and	State	equals	"NY"
and	LastModifiedDate	is greater than	"06/05/2017 09:15:00:00 AM"

The filter settings shown above return records where all of these are true:

• LastName = "Smith" or "Smythe"
• MailingState = "NY"
• LastModifiedDate > "06/05/2017 09:15:00:00 AM"

Using mathematical notation, this statement can be read as:

((LastName ="Smith" or LastName ="Smythe") and MailingState = "NY" and LastModifiedDate > "06/05/2017 09:15:00:00 AM" )

Related Topics

Defining An Integration Flow

Most Recent Record Processed