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.
(1) Always use precise metadata properties for Tags
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
"Text Search IS" is a quick way to perform a broad search, but is not recommended for creating new tql statements for tags
Leveraging a combination of precise metadata properties, now enabled by TQL, is the most accurate way to design tags and tag structures.
(2) Tag smarter, not harder
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.
Each Cash Flow line item in your report show be represented by a Tag
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:
INFLOWS
Customer Receipts
Lockbox
Returns
OUTFLOWS
Operational Expenses
Payroll
Taxes
OTHER
Intracompany Activity
Funding
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
Tag structures should be based on a list of the companies core cash flow categories / reporting goals
Parent-Child tag relationships can be created by using the Tag IS property-operator combo and saving as the parent tag.
When a new untagged transaction arrives in your instance, Try updating an existing tag by using the OR operator after the last statement and inputting an additional TQL statement to capture that unique transaction.
(3) Nesting statements with an OR operator
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
Returns two unique slices of transactions in the same list: All transactions that have “ach” in the description detail mixed in with all transactions that are in Japanese Yen (JPY). This breaks down to the following operations:
(a) BAI Description CONTAINS ach ----> All transactions that have “ach” in the description detail
(b) Currency IS JPY ----> All Transactions that are in Japanese Yen (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:
(a2) BAI Description CONTAINS ach AND Description CONTAINS eft ----> All transactions that have “ach” in the description detail and also contain “eft” in the transaction description
(b2) Currency IS JPY AND Description CONTAINS book transfer ----> All Transactions that are in Japanese Yen (JPY) and also contain “book transfer” in the transaction description
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
The Nesting feature is recommended to establish the order of operations on either side of the OR operator.
Users can build a nested query by clicking the “+” sign on the right side of a statement .
By using the nesting feature on either side of the OR operator, users are able to cleanly establish the order of operations, to easily return uniquely different transaction results derived from a single query.
(4) Using wildcards in string-based searches
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.
Common string-based properties include Description, Description Detail, BAI Description, and BAI Code. Check out our TQL Syntax article here, to find a list of all available string-based properties.
After selecting a string-based property, our users are able to choose from the following operators that define how to search the values:
CONTAINS - looks for matching values anywhere in the property
STARTS WITH - looks for matching values at the beginning of the property
ENDS WITH - looks for matching values at the end of the property
and the negation of those operators, DOES NOT CONTAIN, DOES NOT START WITH, DOES NOT END WITH.
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.
“_” the underscore wildcard can be added inside a value to look for a specific amount of characters between two or more phrases or characters.
“%” the percent wildcard can be added inside a value to look for any amount of characters between two or more phrases or characters
Now, let’s take a look at how to use each wildcard in practice.
Example 1: Using _ wildcard for pattern matching
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).
Example 2: Using % wildcard for pattern matching
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
Wildcards can be added to values for all string-based searches
“_” the underscore wildcard can be added inside a value to look for a specific amount of characters between two or more phrases or characters.
“%” the percent wildcard can be added inside a value to look for any amount of characters between two or more phrases or characters.