【Query Builder】Filter : Preparing a lookup list by using a sub query

# 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eb948ffe-5fd8-d33a-7394-73d170076f57.png)

# Normal Use of the Filter #
I use one of the filters, **Account Code** here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/78d45dec-a0d4-cf0c-d62d-a418c78ddddc.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/febe9b22-47f0-f771-e1f3-ef95dbe8c596.png)

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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/532abefa-d175-09e6-35ed-b78d7b496cd9.png)
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. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7ea5d0ae-9161-e6ee-f0a8-fae5b5a65266.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4d3a30e1-5d9e-8f96-87f0-fab30c7d43bb.png)

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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6c18e829-7b5b-0221-3bdb-18a49d715444.png)

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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/14bce3c0-ad5c-96d1-7aac-3cb7f61af81f.png)

Go back to query definition, and click `OK` to save the query.
Select `Sub Query is used for Lookup value selection` option.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/81ee5073-c3c0-0617-559d-e5cd7cb24f18.png)

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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/082b1985-05b3-decf-bb5e-063d64e5ed2d.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/264b1d94-4f7d-ac9b-73c0-352a2016c741.png)

# 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](https://qiita.com/Miki_Yokohata/items/63cb9de0ed858bafdf38).