This article provides a consolidated list of TQL Tips & Tricks. Prior to reviewing this document, we recommend learning the basics of TQL Transaction search here and TQL Syntax here.

As an overview, the OR operator is helpful to return two or more unique slices of transaction results with a single query.

For example, this TQL Statement: BAI Description CONTAINS ach OR Currency IS JPY

Using the same example, we can add additional nested parameters on the left and right side of the OR operator by clicking the “+” sign.

In the animation above, you can see we’ve added nested search parameters to both sides of the OR statement, establishing an explicit order of operations that can be broken down into the following peices:

In the example above, you can see that we cleanly established order of operations on either side of the OR operator, resulting in a list of uniquely different transactions, derived from a single TQL query.

Takeaway

As an overview, string-based searches are extremely useful in order to precisely tag unique transaction results that match a specific pattern within a supported string-based property.

After selecting a string-based property, our users are able to choose from the following operators that define how to search the values:

For example: the statement in the query below returns 343 transactions that contain the unique code “FCS00020” anywhere in the Description Detail property.

But wait, there’s more..

TQL also offers wildcard values that are helpful to return specific patterns of characters.

Now, let’s take a look at how to use each wildcard in practice.

If we want to return all transaction that contain a single character between the last two zero’s from the first example (Description Detail CONTAINS FCS00020), we would replace the “2” with an underscore “FCS000_0” in the value.

In the screenshot above, you can see Trovata returned significantly more results and include transactions with variations that match the FCS000_0 pattern (like FCS00030).

If we want to return all transactions that contain two phrases with any amount of characters between them, we can leverage the % wildcard.

Lets update the original example (Description Detail CONTAINS FCS00020) to only return transactions with both “165” followed “FCS00020” divided by any other characters or phrases. Using the % wildcard, the new statement would be: Description Detail CONTAINS 165%FCS00020

In the screenshot above, you can see that the results include transactions that contain “165”, followed by any amount of characters (highlighted) and FCS00020.

Takeaway

Sometimes, when the customer has millions of transactions, building an optimized TQL search makes the difference between fast results and timeouts. See below a few recommendations on how to make your TQL query faster.

1) Use the correct statement order.

In order to build an optimized search, the TQL statements should be written in the same order as the Trovata objects from top to bottom (when available):

For example, let’s search for transactions that contain the transfer word in the description and are from the 0001 account.

If we build the search exactly as written above it would look like this:

However, to make the same search faster, we would need to change the statements order according to the list above. Hence we should search first for the account parameter and then for the one with the transaction description free text.

2) Use as little free text search as possible.

The search performance will suffer the more free text the search contains and less fixed-value parameters are used.

3) Make use of the list when available.

For example, let’s search for all debit transactions from 0002 and 0003 accounts. One way to do it the following:

To optimize this search we should use the account list instead of writing multiple OR statements:

4) Use date ranges.

When possible, apply a date range to your search. Especially if your account contains millions of transactions, applying a date range will make a big difference in search performance.

Trovata also offers a few date range shortcuts for easier use, like current month, current year, previous 30 days, etc.

Tagging is the most efficient way to automate the cash flow categorization of new transactions that are received into your instance each day.

With the new TQL functionality, all users can now create tags using a combinations of specific metadata properties that will return extremely accurate tagged results.

This is significantly different than our legacy search (now represented by "Text Search IS") which provides a way to execute a broad search across multiple properties.

Takeaway

When thinking through your tagging strategy, it's important to visualize and jot down what your ideal cash flow categories and report would look like.

For example, If my ideal cash flow report looks like the structure below, I'd want to make sure my transaction are tagged with one of the 8 child level cash flows in bold:

After I've tagged my transaction activity to a child tag (in bold), I can create group them into the three parent tags listed above (INFLOWS, OUTFLOWS, OTHER)

To create a parent-child group of tags using TQL, use the "Tag IS" property-operator combo and select the child lags that were created in the previous step. For example to

This is how the parent and child tags for INFLOWS looks like from the tags page after creation.

Another major benefit to TQL is now you can use an OR operator and edit one of your 9 cashflow categories to include a new transaction variation. This creates a more concise tag structure that is easier to understand and manage going forward when a new transaction variation is introduced.

Takeaway