Introduction to Transactions & Tagging
Transaction metadata search capabilities have always been a core tenant of Trovata’s offering. The search functionality is simple to use and effective in pulling desired queries. Our search has become even more robust through the statement-building foundation queries, known as Trovata Query Language (TQL).
We’ve transitioned from supporting the search of one phrase or word at a time to multi-dimensional statement building queries that isolate data with ease. Query building, simply put, means that the user can determine which metadata field they are searching (e.g. BAI description, check number, bank reference number, etc.) and direct it with an operator (e.g. is, is not, contains, does not contain, etc.). Users can enjoy the same filtering options that we’ve always supported, such as filtering by the account number or by an entity label, in addition to the support of a date range query.
To make these search queries valuable to users, we take this offering one step further through the creation of Tags. Tags are transactional categories that leverage our search functionality to create a rule - meaning the user can instantly create a data repository linked through a common identifier. For example, if I search on the name of my payroll vendor, I can create a tag that isolates all payroll payments and generate a report off of it.
These categories are flexible and can be edited at any time. Many of our customers have automated their reporting through tags, which allows them to track key cash flow items such as vendor payments, lockbox receipts, taxes, payroll, debt paydown, rent, and more. Tags are used to effortlessly generate historical reports and become the cash flow line items within the forecasting module. Tagging helps to ultimately organize your transactional dataset, which is already normalized in Trovata, no matter the bank it's coming from.
Trovata Query Language (TQL) Overview
Introducing Trovata Query Language (TQL) - a significant advancement in our search and tagging capabilities. This upgrade enables our customers to execute highly accurate transaction searches, leveraging more than 20 indexed metadata properties. Additionally, we’ve enabled AND / OR logical operators, which opens up the playing field for our users to construct intricate and succinct tag statements.
TQL is more than just a tool; it’s a game-changing enhancement to search and tagging, specifically designed to boost efficiency, precision, and reliability.
Benefits
Increase search precision, with over 20 indexed metadata properties
Simplify tag structures with AND / OR logical operators
Reduce time and effort required to create and maintain in-depth tag structures
Fully backwards compatible with our legacy search and tagging system
Getting Started
The core updates to our transaction search experience can be found on the Transactions Page.
If you’re familiar with our legacy transaction search experience, you’ll notice that we’ve removed the icon to click filters and have added the config icon on the right side of the search bar.
Query Builder
Click the config icon to access our step-by-step Query Builder, and click the “Add Query” button to start your first statement.
All search statements can be broken down into the following syntax:
Property, Operator, Value
Ex: Description Detail CONTAINS amazon
A Property represents where you are searching within a transaction’s metadata. Examples of properties include but are not limited to Description, Description Detail, BAI Code, Currency, Account, Bank, Credit/Debit.
An Operator distinguishes how you are searching the selected property.
IS / IS NOT can be used when a property’s values are known and populated in a fixed list.
>, <, >=, <=, IS, and IS NOT can be used for the Amount property to filter on numeric values.
CONTAINS, DOES NOT CONTAIN, STARTS WITH, ENDS WITH are ideal for text-based properties like Description, Description Detail, BAI Code, and BAI Type
Values represent what you are specifically looking for within a property.
Depending on the property selection, value types include:
known values may be selected from a fixed list
text values can be inputted by the user
numeric values can be inputted by the user to search on Amount
Configure a Simple Query
First, Click the config icon to open the Query Builder
Click the “Add Query” button to configure a new statement
Click the property dropdown to review a menu of all transaction elements that can be searched on.
For this example, we’re going to choose BAI Description as the property.
Next, select your Operator. The available options in this section are completely dependent on the property selection.
For this example, we’re going to choose CONTAINS, which searches for a character or phrase anywhere in the selected property.
Then, input a value that you would like to search.
For this example, I want to return transactions with a BAI Description that contains “ach”.
Click “View Results” to execute the search.
Takeaway: In the animation above, you can see that:
1) the Query Builder statement is immediately converted to the equivalent TQL statement “BAI Description CONTAINS ach” in the search bar.
2) Trovata returned 41,289 transaction results that contain the word “ach” in the BAI description field.
Add Paramaters to a Simple Query
Building off the previous example, click the config icon again to view the simple query configuration
Select the “add query” button underneath it to add more parameters
Then choose one of the following operators to link the two statements:
AND - Used to return results that match all parameters in both statements
OR - Used to return results that match unique sets of parameters located on either side of the OR operator
AND NOT - Used to return results that do not match the following parameters
OR NOT - Used to return results that do not match unique sets of parameters located on the right side of the operator
For this example, we’re choosing “AND”
Follow the same steps in the above section for building a simple query
Input a property, select an operator, and input a value
For this example, we’re going to use Currency as the property, IS NOT as the operator, and USD as the value (selected from the list).
Input a second statement after the AND operator and click View Results
Takeaway: In the animation above, you can see that:
1) the Query Builder statement is immediately converted to the equivalent TQL statement “BAI Description CONTAINS ach AND Currency IS NOT USD” in the search bar.
2) Trovata returned 20,630 transaction results that
contain the word “ach” in the BAI description field
AND are not in USD currency
TQL Statements from the Search Bar
Once you've mastered assembling statements through the use of our Query Builder, we encourage you to elevate your skills by constructing them directly from the search bar.
Build a Simple TQL Statement in the Search Bar
Simply start typing to find the first property in your statement.
Use the up and down arrow keys to navigate the list of recommended properties
Hit the enter key to select the property
In the example above, BAI Description is the selected property of the first statement and Currency is the selected property of the second statement
After hitting the enter key to select your property, use the up and down arrow keys to review the list of available operators and hit the enter key to make a selection
In the example above, CONTAINS is the selected operator in the first statement, and IS NOT is the selected operator in the second statement
Next, type in your search value or select your search value from a fixed list
In the example above, “ach” was typed in by the user in the first statement and USD was selected from a fixed list in the second statement, since the Currency property has a list of known values
After each statement is complete add an AND / OR operator to link additional statements
In the example above, AND is the operator that links the two statements together
Finally, hit the enter key to execute your search.
Takeaway: In the animation above, you can see that:
1) Trovata returned 20,630 transaction results that
contain the word “ach” in the BAI description field
AND are not in USD currency
2) After a TQL statement is executed in the search bar, the user can easily reference how it’s broken down in the Query Builder by clicking the config icon.
Build a TQL Statement with the OR Operator
Leveraging the OR operator between two TQL statements allows users to cleanly return uniquely different sets of results from a single statement.
As an example, let’s review the previous statement and swap an OR operator for the AND operator:
Now the statement is displayed as: BAI Description CONTAINS ach OR Currency IS NOT USD
Takeaway: In the animation above, you can see that:
1) Trovata returned 75,230 transaction results, almost 3 times the amount of transactions than the same statement using the AND Operator. This is because the second statement is treated separately than the first statement and returns all transactions that are not in USD currency. These results include transactions that:
contain the word “ach” in the BAI description field
OR all transactions that are not in USD currency
2) This is a great way to return uniquely different slices of transactions in a single statement that can be consolidated into a single cash flow tag.
Build a Nested TQL Statement with an OR Operator
Creating a TQL statement with nested parameters is extremely useful to simplify the order of operations in a complex query.
Click the plus sign in the corner of a created statement to build out a nested statement.
After the plus sign is clicked, add another operator and statement to complete your nested query
In the example above, we added an AND operator followed by a statement that specifies a single bank to search for on each side of the OR statement.
Takeaway: In the animation above, you can see that:
1) Trovata returned 28,772 transaction results. These results include transactions that:
contain the word “ach” in the BAI description field AND are from JP Morgan
OR
transactions that are not in USD currency AND are from Wells Fargo
2) This is a great way to filter and return uniquely different slices of transactions in a single statement that can be consolidated into a single cash flow tag.
Save a TQL Statement as a Tag
As a best practice, you should spot check a few of the transactions in your search results so you ensure your parameters accurately capture what you’re about to tag since you are, in effect, generating a rule for future matching transactions to be tagged into this category. Once you’re satisfied with the TQL statement paramaters and resulting transactions:
Click the blue “Save Tag” button in the top right corner of the page.
Input a Name for the tag and a brief description to add context about the tagged transactions
Click “Save” to complete tag creation
Build a Parent-Child Tag Relationship
Establishing parent child relationships between tags is useful in order to capture different perspectives of cash flow streams that you plan to report on.
For example: If you’d like to build a report that shows ACH credits over $1m and below $1m, you should:
First, build a tag to capture ACH transactions with amounts >= 1,000,000
TQL Statement: BAI Description CONTAINS ach AND Amount >= 1000000
Next, build a tag to capture ACH transactions with amounts < 1,000,000
TQL Statement: BAI Description CONTAINS ach AND Amount < 1000000
Finally, build a parent tag that includes both child tags using the following statement
TQL Statement: Tag IS tagName1, tagName2
Takeaway: In the animation above, you can see that:
1) Creating a parent child relationship leverages Tag IS followed by selecting the child tags you’d like to include.
Tag IS ACH Credits - Above $1m, ACH Credits - Below $1m
2) After the parent tag is saved, the parent tag (All ACH Credits) appears on the Tags page, with a “2”, indicating the parent tag now has a relationship with two child tags.
