Acquiring Filters

After clicking Acquire Filter this popup will appear. It has several fields and buttons

Click the Acquire button. The following window will appear:
Select a Program and Component from the drop down lists. This is required at a minimum.
- If the filter is being created for Asset Hierarchy Tree or Crystal Report the Program and Component fields are locked to the selections made on the Add Plugin window.
- Notice that a basic SQL Statement will appear in the Database Syntax field below.
There are two options when choosing a filter:
1. Select an existing filter that was created in Lucity Web or Desktop.
  - Check the Use or Modify an Existing Filter box. The two boxes below will become enabled.
  - Choose the File Author. A list of all that user's filters for the selected module will be displayed in the Filter Name list.
  - Select a filter from the Filter Name list.
  - The SQL statement for that filter will appear in the Database Syntax field in the next section. This SQL statement can then be altered if desired. This will not modify the filter inside of Lucity Web or Desktop
2. Manually enter the SQL statement in the large Database Syntax field.
  - Modify the filter that is currently showing in this field to select the desired record set.
  - To search for, and enter a Reserved Word, Table Name, or Column Name click in the Database Syntax field where that object should go. Then select the word, table, or column from the dropdown lists found below the Database Syntax field. When selected they will automatically be added to the SQL statement.
After creating the desired filter click the Test "Where" button. A popup will appear saying if the statement was valid, and how many records it returned.
When the filter is complete and works click OK.

Restrictions: Dashboard filters cannot contain ORDER BY clauses. The program will strip out the ORDER BY clause if you try to use one. Additionally, if you are using a Filter to access the Desktop application, be sure that WHERE clauses are in uppercase. This is required for the desktop app.

Select the spot in the filter in the Database Syntax field where the reserved word should go.
Select the desired Reserve Word from the list. It will be automatically added to the filter at the selected location.

Available Reserved Words:

'%LOGGEDINUSERID%' - This maps to the Initials field in the USER_INFO table. When this string is used in a filter, the system automatically replaces it with the Lucity Login ID of the currently logged in user.
'%LOGGEDINEMPCODE%' - You must own Work to use this string. It maps to the EM_EMPL_CD field in the WKUEMP table. When this string is used in a filter, the system automatically replaces it with the Work Employee Code of the currently logged in user. In order to use this string, the Logon ID (EM_EMPLID) needs to match the employee's Initials in USER_INFO and the employee record needs to be active.
'%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.
'%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 the next week plus 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.
Note: When using the reserved words above, make sure that to enter them in all uppercase letters and enclose them in percent symbols (%) and single quotes (').

Examples:

These following examples show the filter created using a reserved word, and how the filter changes when it is run on a certain day.

WKORDER WHERE WO_MOD_DT = '%CURRENTYEAR%'

becomes the following when run on 8/28/2012

WKORDER WHERE WO_MOD_DT >= '01/01/2012 12:00 AM' AND WO_MOD_DT < '01/01/2013 12:00 AM'

WKORDER WHERE WO_MOD_DT = '%CURRENTQUARTER%'

becomes the following when run on 8/28/2012

WKORDER WHERE WO_MOD_DT >= '07/01/2012 12:00 AM' AND WO_MOD_DT < '10/01/2012 12:00 AM'

WKORDER WHERE WO_MOD_DT = '%CURRENTDAY%'

becomes the following when ran on 8/28/2012

WKORDER WHERE WO_MOD_DT >= '08/28/2012 12:00 AM' AND WO_MOD_DT < '08/29/2012 12:00 AM'

WKORDER WHERE WO_MOD_DT = '%FIRSTHALFOFTHEMONTH%'

becomes the following when ran on 8/28/2012

WKORDER WHERE WO_MOD_DT >= '08/01/2012 12:00 AM' AND WO_MOD_DT < '08/16/2012 12:00 AM'

Program *	Allows the user to select the part of the program that the plugin needs to access. The program selected will filter down which modules will appear in the module list
Module *	Allows the user to select the module this plugin is for. Selecting the module causes the program to begin building the filter
Use or Modify an Existing Filter	This allows users to select a filter that was saved in the selected Module in Lucity Desktop or Web.
Database Syntax	This displays the current SQL Statement that the filter will use. This will appear whether the user is manually writing the SQL or has selected a saved filter.
Select reserved word	Allows the user to select a reserved word to use in the filter. Reserved words can be used to add specific types of dynamic data to the filterset. Example: If you wanted to show today's work orders you could create a filter that said: SELECT * FROM WKORDER WHERE WO_STRT_DT = '9/26/2012'. On September 26 it would show all the work orders for that day, but this wouldn't be useless on any other day, and would have to be changed daily to be useful. However, using reserved words you could create this filter SELECT * FROM WKORDER WHERE WO_STRT_DT = '%CURRENTDAY%'. Every day the SQL would change to reflect the current day, and only show work orders for that day.
Test "Where"	This tests if the SQL statement's "Where" clause is valid, and tells how many records that filter returns.
OK	Saves the filter and closes this window