Technical Function of Tracking Queries

Tracking queries, also known as queries where "Retrieve all records and save result history" or "Retrieve only the new records since query was last run" are set, are designed to enable Slate to track which particular records pulled into a query on a specific date and time.

Consider the following scenario:

You build a tracking query that pulls everyone that doesn't have a particular interaction code. In the filters (which goes into the "where" clause), you instruct it to find records without this code. You run it and we save the key (e.g., p.[id] for the person ID) to the [query.run.id] table, which tracks each unique record that has been returned for a particular query run (also known as an execution). You look at the results, are happy, and use interaction management to give them that code. Two weeks later, you want to see who came into that query.

If we've preserved the original where clause (to say find records without this interaction code), we'll get 0 records back, because everyone who met the criteria originally no longer meets the criteria. For this reason, as a default policy, we omit the where clause when retrieving the results, as preserving it would break any tracking query where the criteria might cause data not to be included if you go back to look at it later.  Instead of the existing where clause, we substitute in a where clause that limits the records to the unique keys that were saved.

This is generally fine, except in circumstances where the information in the where clause is very significant for the purposes of the select statement. Consider this query:

select p.[name], f.[date] as [date of campus visit]
from [person] p
inner join [form] f on (f.[record] = p.[id])
where (f.[category] = 'Campus Visit')

If we dump the where clause and are keying only off p.[id], the [form] join will bring in *every* event, not just the campus visits. In these instances, if this were a custom SQL query,  we would need to do one of the following.

In option 1, we would add the following to the XML tab:

<query>
<key>p.[id]</key>
<key>f.[id]</key>
</query>

This would cause the query to key off two separate IDs, so that we can at least route back to the right person and to the right form.

In option 2, we would add the following to the XML tab:

<query>
<key>p.[id]</key>
<preserve />
</query>

This will signal for us to include the entire where clause when we fetch the results again later. Using the second key is typically the better approach, since re-including the where clause might still cause certain filters not to evaluate if they have date-specific items in there or the record no longer meets the criteria for whatever reason.

In the query builder (for queries that are not using custom SQL), we don't know what the second key would be (since query builder queries only have a single primary key), so we have a "Preserve Where Clause" option in the Admin Tool for filters that will force it to include all where clauses when pulling up a tracking query if at least one of them has this flag set. We cannot just include the where clause marked as such, since there could be parentheticals and "or" operators that could be significant.

Was this article helpful?
2 out of 4 found this helpful