Advanced Filter

The Filter dialog also contains an Advanced Filter tab. If a standard filter will not provide the necessary results, it is possible to use an advanced filter to query for specific records.

Note: You should have a working knowledge of SQL Query Syntax before attempting to use Advanced Filters. For most users, the Build-A-Filter screen will be sufficient.

Note: Filters saved from the Advanced tab will be saved as Advanced Filters. In future releases, this designation may mean that only NON-Advanced Filters can be re-displayed in the Build-A-Filter screen.

1. Use the window provided to enter or modify the SQL statement
2. Reserved words can be used to add specific types of data to the filterset. These are provided in the dropdown list below the SELECT*FROM dialog. To add a Reserved Word, simply select one from the list. It will be added to the filter string. The available Reserved Words are described below:
   • '%FISCALYEAR%' - You must own Work to use this string. It uses the current fiscal year from the Planning and Budgeting, Fiscal Year module in the desktop application.
   • '%CURRENTYEAR%' - Inserts the current calendar year.
   • '%CURRENTQUARTER%' - Inserts the current quarter.
   • '%CURRENTDAY%' - Inserts the current day.
   • '%CURRENTWEEK%' - Inserts the current week.
   • '%PREVIOUSDAY%'- Inserts the previous day.
   • '%PREVIOUSWEEK%'-Inserts the previous week.
   • '%PREVIOUSMONTH%'-Inserts the previous month.
   • '%PREVIOUSYEAR%'-Inserts the previous year.
   • '%FIRSTHALFOFTHEMONTH%' - Inserts the first half of the month (days 1-15).
   • '%SECONDHALFOFTHEMONTH%' - Inserts the second half of the month (days 15-31).
   • '%CURRENTMONTH%' - Inserts the current month.
   • '%ONEWEEKAHEAD%' - Inserts the next week.
   • '%TWOWEEKSAHEAD%' - Inserts the next two weeks.
   • '%ONEWEEKAHEADANDBEFORE%' - Inserts seven ahead plus everything from the previous timespan. For example, you may use this string to query for all existing open work orders as well as those that are open/due in the next week.
   • '%TWOWEEKSAHEADANDBEFORE%' - Inserts the next two weeks plus previous timespan.
   • '%LOGGEDINUSERID%' - Allows you to filter on a Dashboard Frame or Frame Item based on the logged in User Name. Note that this is not on the Reserved Word list and must be manually entered into your filter.

     Note: When using the reserved words above, make sure that you enter them in all uppercase letters and enclose them in percent symbols (%) and single quotes (').

3. If using a Filter to access the desktop application, be sure that WHERE clauses are in uppercase. This is required for the desktop app.
4. Click the Test and Count button to check the filter string and determine how many records will be returned.
5. Click Run Filter when you have finished building your filter. The new filtered dataset will appear.

Note: The Advanced Filters tab will not be displayed if the Filter dialog is launched from a previously filtered record set. Consult the Tab Filter topic for additional information.