Filtering OpenAir Records for Export

Note:

The Filtering functionality is not available when importing information from a CSV file into your OpenAir account.

You can use filters to export only the OpenAir records that meet specific criteria to the CSV file.

The following filter options are available as standard:

Only new items since the last export — Use this filter option to export only records that were not exported previously. You can choose to take into account either:
- Recorded exports to CSV file done using Integration Manager or an Integration Manager shortcut, or since the last export done using any application.
- Recorded exports done by any application integrating with OpenAir.
Note:
By default, Integration Manager and other applications and integrations provided and supported by OpenAir add an exported date and time stamp on a record when this record, and a row in the OpenAir import_export table when the record is exported. The applications can then use this information to exclude previously exported records from the export.

You can configure filter options in Integration Manager so that it does not mark records as exported in OpenAir. This may be useful for testing, or when you want to export the same set of records several times, if you want to be able to export the same records again while using the Only new items since last export filter option.
Only deleted records — Use this filter option to export only deleted records.

Note:
When a user deletes a record in OpenAir, the record is flagged as deleted. It is not removed immediately from the OpenAir database. Deleted records are retained in the OpenAir database for a minimum of 180 days. Older deleted records, that is, records marked as deleted and last updated 180 or more days ago, are removed permanently from the database according to a routine schedule.

For more information, see Data Deletion.

You can define a custom filter and add multiple filter conditions to it. Each filter condition compares the value of a field for the selected OpenAir record type (first operand) with a static value, a range of values, or another field value, depending on the comparison operator and the data type of the field you select as the first operand.

All fields available for mapping are also available as operands in your filter condition. This includes not only standard OpenAir fields, custom fields, but also calculated fields and field value lookups defined in Integration Manager.

You can compare datetime fields with a custom date range or metavalue relative to the current date. Available date metavalues include last month, this month, <N> days ago, <N> hours ago (where <N> is an integer), today, or next month.

The following comparison operators are available: equal to, not equal to, is empty, is filled, contains, not contains, before, after and between.

The following logical operators are available:

All conditions must be true — Records must match all the filter conditions to be exported (AND logical operator).
At least one condition must be true — Records must match one or more filter conditions to be exported (OR logical operator).

After you add a filter condition, you can edit or delete it at any time.

To filter OpenAir records for export:

In Integration Manager, locate the row corresponding to the OpenAir record type available for export to a CSV file.
Do one of the following:
- Click to select the row, then go to Options > Filtering.
- Right-click the row, then click Filtering from the context menu.
The Filters window appears.
(Optional) Check the Only new items since last export box to export only records that were not exported previously. Choose one of the following options:
- By this application (or shortcut) — to export only records that were not exported previously to a CSV file using either Integration Manager or an Integration Manager shortcut.
- By all applications — to export only records that are not marked as exported in OpenAir.
(Optional) Check the Do not mark items as exported box to export records without marking them as exported in OpenAir.
(Optional) Check the Only deleted records box to export only deleted records.
(Optional) Create a custom filter. To do so::
1. Click Add to add a filter condition.
  
  The Create Filter window appears.
2. Enter a Name for the filter condition.
3. Select one of the fields for the selected record type from the Select a field to filter against dropdown options.
  
  Depending on the field you select, the Value box and Select a field to compare with dropdown field appear. If you select a datetime field, the current date appears in the Value box.
4. Do one of the following:
  - Select a comparison operator from the Condition dropdown options, then enter a static Value, enter two static values defining a range, or select a field from the Select a field to filter against dropdown options. When comparing numbers, check the Numeric comparison box, otherwise clear the box.
    
    Note:
    Available comparison operators in the Condition dropdown options depend on the datatype of the field you select as first operand. The ability to enter a static Value, enter two static values defining a range, or select a field as the second operand depends on the comparison operator you select.
  - If you want to compare a datetime field to a custom date range relative to the current date, click Custom Date Range.
    
    The Advanced Filtering window appears. Select the day of the month and the month relative to the current date for the start date and for end date to define an inclusive date range, and click OK. Relative month options include this month, last month and each previous month, up to 12 months ago.
    
    Note:
    You can also compare datetime field values to a relative date range using the comparison operator between and supported date metavalues.
5. Click OK.
  
  The Filters window shows the filter condition you added. Remember that dates are inclusive and you should use date filters to limit the amount of data OpenAir needs to process.
6. Repeat the steps to add other filter conditions as required.
  
  You can also edit or deleted filter conditions at any time, to do so click the filter condition name in the Custom filters box and click Edit or Delete.
7. If you have two or more filter conditions, choose whether all conditions must be true (equivalent to an AND logical operator between all filter conditions) or At least one condition must be true (equivalent to an OR logical operator between all filter conditions).
Click OK to save the filter settings and return to the main Integration Manager window.

Filtering Best Practice

Integration Manager uses server-side or client-side filtering depending on the situation. Server-side filtering can reduce export time significantly.

Server-side filtering is used for filter conditions in each of the following cases:

The selected field is native to the exported record type, the Condition is set to “Equal to” or “Not equal to”, all conditions must be true is selected, and both the Only new items since last export and Only deleted records filtering options are disabled. Note that if you use both “Equal to” and “Not equal to” filter conditions, server-side filtering is done based on the first of these filter conditions.
The selected field is the timesheet or envelope status field, the exported field, and date fields native to the exported record type.

Note:

The distinction between native fields and non-native fields is best illustrated by examples:

slip.date and slip.updated on a TimeBill export are native fields.
slip.invoice_date and slip.invoice_updated on a TimeBill export are not native fields as they relate to the invoice this time bill is associated to. Client-side filtering is used in this case.

Client-side filtering is used in all other situations. To reduce the time it takes to run regular exports for a specific subset of records, you run a cleanup export before you run the production export.

You can use the cleanup export to mark all records that are not in the required subset as exported.
You can then exclude all records that are already exported in your production export.

For example, if you want to create a TimeBill export and include only TimeBill records that are not yet exported and that are associated with a specific project stage (for example, the project stage with the internal ID 2):

Create a cleanup export shortcut to export all TimeBill records that are associated with all project stages other than the project stage — for example, slip.project_project_stage_id not equal to “2”.
Run the cleanup export and discard the output CSV file.
Create a production export shortcut to export TimeBills records, excluding records already exported, and including only the records for which slip.project_project_stage_id is equal to “2”.

Known Limitations

Filter conditions on Boolean or numeric fields with the Condition is set to “Equal to” may not work as expected when OpenAir API returns an empty field value.

Workaround: Specify a conditional override and check the Numeric comparison box and try the export again. With the Numeric comparison option enabled, the empty field value will be interpreted as 0 in the filter condition.
Filter conditions on Boolean fields may not work as expected when the Boolean value is 0 (False). OpenAir API returns an empty field value in some cases.

Workaround: As above.
Filter conditions on numeric fields may not work as expected. The value returned by OpenAir API may include a decimal part (for example, .00) with a specific number of decimal places, depending on the field type and definition, and may not match exactly the value specified in the filter condition.

Workaround: Use the same number of decimal places for the value specified in the filter condition, as in the values returned by OpenAir API. For custom fields, decimal places are determined by the decimal positions setting on the custom field entity form in OpenAir. For built-in field types and definitions, refer to the OpenAir data dictionary. See OpenAir Data Dictionary.