How do I create custom filters?

Overview

This guide shows you how to create a custom filter in a window of your choice. Generally speaking, you can use general SQL operators, such as the LIKE operator, but you will also need to use the field separator (<^>) or the segment separator (<~>) to write your query.

Example:
If you want to create a user query that will filter for all invoice candidates with the invoice rule "After delivery (D)", the pure SQL code using the LIKE operator would be: InvoiceRule LIKE 'D'.

But if you write it like that, it will not work because the system will not know how to parse that SQL. So the correct way would be: <^>InvoiceRule<^> LIKE <^>D<^>.

Note the spaces before and after the LIKE operator. They are essential!

Also, note the <^> symbol. This is the field separator. It marks the search field, the operator and the value.
When querying several terms, the segment separator is used, which looks like this: <~>.

Example:
If you wanted to filter for invoice candidates with the invoice rule "After delivery (D)" as well as for a certain partner, the query would look like this:
AND<^>InvoiceRule<^> LIKE <^>D<^><~>AND<^>Bill_BPartner_ID<^>=<^>2156423<^>.

Steps

Let’s use a practical example. Assume you want to create a filter for the “Attribute Value” window. This filter shall include the following filtering options: Name, Attribute and IsActive flag.

1. Open “User Query” from the menu.
2. Create a new entry.
3. Give your custom filter a Name, e.g., AttributeValue (see example below).
4. Set a User/Contact if the filter usage should be limited to this user. To do so, enter part of the user name and click on the matching result in the drop-down list.
   If no user is specified, the filter can be used by anyone.
5. Set the Table for which you want to use the filter. To do so, enter part of the table ID and click on the matching result in the drop-down list.
6. Set the Tab where you want the filter results to display. To do so, enter part of the tab ID and click on the matching result in the drop-down list.

7. Enter the filtering Code into the text box provided, e.g.:

   AND<^>M_Attribute_ID<^> = <^><^><~>AND<^>IsActive<^> = <^>Y<^><~>AND<^>Name<^>ILIKE<^><^>
   

8. Reset the web API server cache.
9. Reset the front-end cache. To do so, press Ctrl/⌘ cmd + ⇧ Shift + R.
10. This will result in: