When using BBCRM as a “source” system, Omatic leverages BBCRM’s Ad-hoc query tool to pull records that should be sent to your “destination” system. The Ad-hoc Query tool allows users to target specific records by defining various criteria (filter fields). Currently, Omatic requires at least one “date” type filter be present on your Ad-hoc query. The options you may use for the date filter are: Before, On or before, After, On or after, Between. 

When executing a “Run once” job for an Omatic formula, the dates you choose for “Starts on” and “Ends on” are applied to the date filters of your Ad-hoc query. For example, if you select a “Starts on” value of “11/01/2020”, that date will be applied to the date filters in your query that are of the types: “After”, “On or after”, and the start date of “Between”. The “Ends on” date is applied to: “Before”, “On or before”, and the “through” date of “Between”. 

You may have any number of date filters on your Ad-hoc query with the above-mentioned types. Omatic will apply the “Starts on” and “Ends on” value to each filter accordingly. 

This helps limit, or box-in, the records which should be pulled and sent to your destination system. For example, pulling constituents with an updated email address within a certain timeframe or getting transactions that occurred after a specific date. 

The concept is similar for “Scheduled” jobs in Omatic. The previous run-date of the query is used as the “Starts on” value and the next run-date is set as the “Ends on” value. This affects the date filter-fields in BBCRM as described above. 

For continuously running jobs, it is suggested that you also include one date-type field in the output of your query. The BBCRM Ad-hoc query tool does not allow timestamps to be included on date filters. This means that each time the Ad-hoc query is run, the date filter can only be as specific as “today”, and the query will repeatedly return all qualifying records for “today”. Often, attempting to send the same record to a destination system is seen as a duplicate and results in exceptions. 

However, date fields used as an output of an Ad-hoc query do include timestamps and can help circumvent the “duplicate” problem. For continuously running formulas, Omatic uses the timestamp to exclude records which have been pulled since the last run. This reduces the chances that the same records are sent to your destination system.  

Regardless of formula type (“Run once”, “Scheduled”, or “Continuously running”), all date fields used in the output of an Ad-hoc query are evaluated as described above (by timestamp). If the date and timestamp happen to fall outside the range of the formula dates (Starts on and Ends on), those records are excluded from the results. 

It is important to test how your Ad-hoc query is affected by the formula type chosen in Omatic and to ensure that subsequent executions of the formula will contain expected records.