Why were these filters introduced?
First of all, Xero is rather limited in the way in which queries can be created. This limitation stems from a concept called Boolean logic. This is basically a form of algebra that is centered around three simple words known as Boolean Operators: “OR”, “AND”, and “NOT”. At the heart of Boolean Logic is the idea that all values are either true or false. Furthermore, some of these Boolean Operators such as “OR” are not actually supported by the Xero application. In this situation, we see a dilemma: one can either build additional filters on top of the broken Boolean logic in the Xero implementation or one can normalize the process by excluding the “OR” operator by using the variety of filters that G-Accon now offers.
This second approach involves making some changes in our users’ existing queries, but in the long run, it will increase efficiency and allow our customers to reach their desired results with ease.
We are now going to go over a couple of use cases that will help you better understand how to apply these new filters and resolve any problems you might face.
Scenario # 1.
In our first scenario, we need to create a report that shows our total due invoices and we need to sort them out by client name in ascending order.
In order to generate this type of report, you need to select G-Accon for Xero –> Reports Designer –> Design Accounting Reports. Then click the Invoices object from the drop-down menu. You can select all attributes or some attributes based on your preferences. Additionally, you can also expand attributes to see the complete structure with sub-attributes and collapse it back when needed. The next step is to set up the static or dynamic Date Range so that your data can be manually or dynamically refreshed. Then, you must select the desired options from the Change Pull Setting. Finally, you can apply your filters.
Select the field to filter by option and specify your filters based on your business needs. For example, if the field Type is selected and the ACCPAY option is chosen, the system will pull all of the account payable invoices or bills.
The first filter in our case is also Type and we will assign the ACCREC value to pull all receivables. The second filter is Status. In our case, the status of our invoices needs to either be “AUTHORIZED” or “SUBMITTED”, so we will need to exclude the following statuses from the query: “DRAFT”, “VOIDED”, DELETED”, and “PAID”. Our final filter is Due Date. For this, we are going to set up the reference to our Google Sheet cell that has the following formula in it: =today(). Next, we are going to set up the condition due date to “before” today’s day.
Finally, the last step is to actually order the report by ascending client names. To do this, you need to select Order Results By, choose the Contact as a field in Order By, and click “ASCENDING” values in Order Type.
This is how the filters and orders will be displayed in the boxes:
You can see the generated Invoice report below which displays all over due invoices and sorts them by the client name.
Scenario # 2.
For this second scenario, we are going to create a new query through the Design Accounting Reports menu option. With the existing features from before April 24th, 2021, you might have run into issues. But with our new update, creating a new query becomes effortless. For example, if your Invoice template has the statuses “AUTHORIZED”, “SUBMITTED”, and “PAID” selected, the updated option for the “Status” filter to pull the same invoices would be: “does not equal” DRAFT, VOIDED, and DELETED.
In order to make changes to your filters, you will need to
- Select Update/Modify/Delete Template
- Choose option # 3, “Select the field to filter by”
- Remove the filters: “Status” equals “AUTHORIZED”, “Status” equals “SUBMITTED”, and “Status” equals “PAID”
- Add new filters: “Status” does not equal “DRAFT”, “Status” does not equal “VOIDED” and “Status” does not equal “DELETED”.
- Click on the “Update And Execute” red button.
- Your query will now be fixed and will produce the expected result.
