Introduction
This time, I am writing about an extended setup for the query filter, using SunSystems as the product and selecting the Ledger Transactions table.
The three mandatory filters are automatically displayed as soon as the table is selected. I will use one of the filters, Account Code, to illustrate this post. While these filters are useful for narrowing down query results, there are additional filtering options available. For now, I will focus on just one of them: the Sub Query option.
Let's take a look.
Normal Use of the Filter
I use one of the filters, Account Code here.
Normally, I display a lookup list for the Account Code filter by clicking the lookup button or double-clicking in the filter fields, then selecting one or more account codes. This wouldn't be a problem if we didn’t have so many account codes; however, this product has a significant number of them.
Let’s explore how I can filter the account codes to reduce the number displayed in the lookup list.
The Use of Sub Query
Open the Filter Options dialog for the Account Code filter.
There is Filter based on Sub Query option on the dialog and it helps me to define a sub query to control the records to be shown in the lookup list.
Click Create Sub Query button to open Query Builder.
As you can see, the value for Business Unit filter is inherited from the parent query.
Choose Chart of Accounts table and 2 mandatory filters Account Type and Account Code are displayed.
We can manage the list of account codes with these.
If I leave those values as <ALL>, it behaves as the same as the normal use of the filter in the parent query.
I set D for Account Type this time so that the lookup list will show Debtor account codes only.
Set Account Code as the output item because this is for the lookup list for Account Code filter in the parent query.
Furthermore add Account Type and Description to help users understand the meaning of the account code.
Let's check what is displayed by clicking Preview button.
They will be displayed in the lookup list for the Account Code filter in the parent query.
Go back to query definition, and click OK to save the query.
Select Sub Query is used for Lookup value selection option.
Click OK to save the filter option dialog.
Now I am on the parent query.
Click Lookup button for the Account Code, it shows the same list as the one we saw in Preview of the sub query definition.
Conclusion
In this way, you can use subqueries to design lookup lists for filters.
I set a fixed value to the filter in the sub query. i.e. D for Account Type.
I think it works only a specific case, and we may need to use a dynamic value for the most of cases.
I will show you how I can do it with this post here.