Journal Query
Overview
The Journals screen, available in the Subledger tab, allows you to search for Journal Lines comprising a Journal. A Journal query comprises of a set of attributes and filtering criteria.
The Journals screen includes:
- An editable section on the left, where you can configure and run queries
- A navigation tab that allows you to open the configuration section at any time
- A results grid on the right, where you can view results returned by the query.
Tutorials
How to Create a Query?
- Go to Subledger > Journals and choose the attributes that will be used to search for Journal Lines:
To be able to run a query, you have to choose at least one attribute or set the Core Date. Otherwise, the Run button will remain disabled.
- Open the Criteria section. Click the Journal attributes field. A list with the available attributes will appear. Scroll down the list to find the desired attribute.
- Click the attribute to select it. The attribute will appear below the Journal attributes field with a selection of filtering criteria and the Delete icon. Also, an item count will appear in the Journal attributes field, the count will go up with each selected attribute. If you click the x on the item count, all selected attributes will be deleted.
- Select a filtering criterion for the selected attribute from the list. The available criteria types and the Attribute data types are listed in the table below in the Query Criteria and Criteria Types section.
You have to select at least one criterion and provide a value. If you leave the value field of a criterion blank, the system will throw an Incorrect value error. However, if you choose to search using the Core Date and set a criterion for it, you do not have to select an attribute.
- Additionally, you can combine multiple criteria for one attribute. Select another criterion by clicking the Add row button. Another list will appear. Choose either AND or OR operators to combine or not combine multiple criteria by selecting either Match All (for AND) or Match Any (for OR) from the list next to the attribute name. Only one of the operators can be used for the given attribute.
You have to choose a value for each of the selected criteria. If you leave a value field blank, the system will throw an Incorrect value error.
The more attributes you select, the more granular the search results.
- (Optional) In the Aggregations section you can enter the Journal Summary Id from a Journal aggregation extract that will allow you to query Journals from this extract.
Unposted Journals will not be visible in the query results.
- Configure the Core Date for which you want to view Journal Lines. You can configure either a specific date or a date range. Set the criteria:
- Open the Date section.
- Select a filtering criterion for the Core Date from the list. The available criteria types are listed in the table below in the Query Criteria and Criteria Types section.
You have to select at least one criterion and provide a value. If you leave the value field of a criterion blank, the system will throw an Incorrect value error. However, if you have already selected an attribute and set a criterion for it, you can leave the Core Date criterion field blank.
- Additionally, you can combine multiple criteria for the Core Date. Select another criterion by clicking the Add row button. Another drop-down will appear. Choose either AND or OR operators to combine or not combine multiple criteria by selecting either Match All (for AND) or Match Any (for OR) from the drop-down list next to the Core Date name. Only one of the operators can be used for the given attribute.
You have to choose a value for each of the selected criteria. If you leave a value field blank, the system will throw an Incorrect value error.
- Once you have defined all required attributes, you can run the query by clicking the Run button. The query will send a request to the database and return all Journal Lines for the specified date or date range based on the defined attributes.
- After you run a query, you may want to change the query criteria to run a different query. However, if during editing the criteria you want to return to the query you have already run, click the Revert button.
The Revert button becomes active only when you start editing a query that has been run.
Query Criteria and Criteria Types
Criteria can only be set for the attributes selected in the query. You can only select a criterion type that is valid to the given attribute data type, e.g., for an Attribute with Date data type you cannot select a “begins with” criterion type.
Table listing query criteria
Combination of Criteria
You can combine multiple criteria for one attribute with the Match All (AND) and Match Any (OR) operators. Only one of the operators can be used for the given attribute.
The criteria across multiple attributes are by default, combined as AND.
Examples:
a) Entity = 'Entity A' OR Entity = 'Entity X' OR Entity = 'Entity 123'
b) Account > '1000000' AND Account <= '2300000'
Example of an incorrect query:
a) Account > '1000000' AND Account <= '2300000' OR Account > '3600000'
Navigating the Query Results
The results grid on the right-hand side of the screen shows the records returned by the query.
The maximum number of returned results is 50,000. If your query returns more than 50,000 results, the system will throw an error. Only 50,000 records will be visible in the grid.
Initially, the query results are grouped in the table in the order of the attributes defined for the query. Each column allows you to perform alpha-numeric sorting on the records. Moreover, each column in the grid has a filter icon that allows you to further filter the query results.
Table listing filtering criteria
All filtering in the results grid is performed on the results displayed in the grid. Enabling a filter does not re-send queries to the database.
You can drag a column header to the top section of the grid to group the Query results using this attribute.
The results grid also has two icons:
