The following is a list of all the reserved words that are available in Lucity Web.
|
'%LOGGEDINUSERID%' - Returns the currently logged in users 'Login ID'.
 Example
When the user 'Josh' runs the filter.
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_TY = '%LOGGEDINUSERID%'
it becomes the following
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_TY = 'JOSH'
|
'%LOGGEDINEMPCODE%' - Returns the currently logged in users 'Employee ID'. This is found in the EM_EMPL_CD field in the WKUEMP table. When used in a filter, the system automatically replaces the reserved word with the Work Employee Code of the currently logged in user.
Requirements - You must own Work to use this string. In order to use this string, the logged in employee must have their login ID associated to an employee record in the Work Flow Setup > Employees module.
Example
When the user 'Josh' runs the filter.
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_CD = '%LOGGEDINEMPCODE%'
it becomes the following
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_CD = 'JoshL '
|
'%FISCALYEAR%' - Returns the current fiscal year from the Planning and Budgeting, Fiscal Year module in the Desktop application.
Requirements - 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.
Example
When the filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%FISCALYEAR%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '01/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '01/01/2015 12:00 AM'
|
'%CURRENTYEAR%' - Inserts the current calendar year.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTYEAR%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '2014-01-01' and EFEQUIP.GE_CUR_DT < '2015-01-01'
|
'%CURRENTQUARTER%' - Inserts the current quarter.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTQUARTER%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.GE_FL_ID = EFFLEET.FL_ID WHERE EFEQUIP.ge_cur_dt >= '04/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
|
'%CURRENTMONTH%' - Inserts the current month.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTMONTH%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
|
'%CURRENTWEEK%' - Inserts the current week surrounding the current date. The week runs from the previous Sunday to the next Sunday.
Example
When this filter is run on 6/30/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/29/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/06/2014 12:00 AM'
|
'%CURRENTWEEK1%' - Inserts the current week surrounding the current date. The week runs from the previous Sunday to the next Sunday.
Example
When this filter is run on 6/30/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK1%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/29/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/06/2014 12:00 AM'
|
'%CURRENTWEEK2%' - Inserts the current week surrounding the current date. The week runs from the previous Monday to the next Monday.
Example
When this filter is run on 6/30/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK2%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/30/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/07/2014 12:00 AM'
|
'%CURRENTWEEK3%' - Inserts the current week surrounding the current date. The week runs from the previous Tuesday to the next Tuesday.
Example
When this filter is run on 6/30/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK3%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/08/2014 12:00 AM'
|
'%CURRENTWEEK4%' - Inserts the current week surrounding the current date. The week runs from the previous Wednesday to the next Wednesday.
Example
When this filter is run on 6/30/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK4%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/02/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/09/2014 12:00 AM'
|
'%CURRENTWEEK5%' - Inserts the current week surrounding the current date. The week runs from the previous Thursday to the next Thursday.
Example
When this filter is run on 6/30/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK5%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/03/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/10/2014 12:00 AM'
|
'%CURRENTWEEK6%' - Inserts the current week surrounding the current date. The week runs from the previous Friday to the next Friday.
Example
When this filter is run on 6/30/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK6%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/04/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/11/2014 12:00 AM'
|
'%CURRENTWEEK7%' - Inserts the current week surrounding the current date. The week runs from the previous Saturday to the next Saturday.
Example
When this filter is run on 6/30/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK7%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/05/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/12/2014 12:00 AM'
|
'%CURRENTDAY%' - Inserts the current 24 hour period. This is a range from 12:00 AM of the current day to 12:00 AM of the next day.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTDAY%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/28/2014 12:00 AM'
|
'%PREVIOUSYEAR%' - Inserts the year previous to the current year.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSYEAR%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '01/01/2013 12:00 AM' AND EFEQUIP.ge_cur_dt < '01/01/2014 12:00 AM'
|
'%PREVIOUSMONTH%' - Inserts the month previous to the current month.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSMONTH%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '05/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/01/2014 12:00 AM'
|
'%PREVIOUSWEEK%' - Inserts the week previous to the current week.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSWEEK%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/15/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/22/2014 12:00 AM'
|
'%PREVIOUSDAY%' - Inserts the day previous to the current day.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSDAY%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/26/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/27/2014 12:00 AM'
|
'%FIRSTHALFOFTHEMONTH%' - Inserts the first half of the month (days 1-15).
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%FIRSTHALFOFTHEMONTH%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/16/2014 12:00 AM'
|
'%SECONDHALFOFTHEMONTH%' - Inserts the second half of the month (days 15-31).
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%SECONDHALFOFTHEMONTH%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/16/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
|
'%ONEWEEKAHEAD%' - Inserts the next week.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%ONEWEEKAHEAD%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/05/2014 12:00 AM'
|
'%TWOWEEKSAHEAD%' - Inserts the next two weeks.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%TWOWEEKSAHEAD%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM'' AND EFEQUIP.ge_cur_dt < ''07/12/2014 12:00 AM'
|
'%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.
Example
When this filter is run on 6/27/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%ONEWEEKAHEADANDBEFORE%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt <= '07/05/2014 12:00 AM'
|
'%TWOWEEKSAHEADANDBEFORE%' - Inserts the next two weeks plus previous timespan.
Example
When this filter is run on 6/30/2014
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%TWOWEEKSAHEADANDBEFORE%'
it becomes the following
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt <= '07/15/2014 12:00 AM'
|
'%LAST(X,Y)%' - Inserts a range of dates (X) going backward in time starting a given number of days (Y) prior to the current date.
Example
When this filter is run on 6/27/2014
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%LAST(30,40)%'
it becomes the following
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt >= '4/18/2014' AND WKORDER.wo_mod_dt <= '5/28/2014'
The current date is 6/27. The reserve word goes back 30 days to 5/28 to find the end of the date range. It then goes back 40 days to 4/18 to find the beginning of the date range.
|
'%NEXT(X,Y)%' - Inserts a range of dates (X) going forward in time starting a given number of days (Y) following to the current date.
Example
When this filter is run on 6/30/2014
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%NEXT(0,10)%'
it becomes the following
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt >= '6/30/2014' AND WKORDER.wo_mod_dt <= '7/10/2014'
The current date is 6/30. The reserve word goes forward 0 days to 6/30 to find the beginning of the date range. It then goes forward 10 days to 7/10 to find the end of the date range.
|
'%TODAY%' - Inserts today's date.
Example
When this filter is run on 6/30/2014
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%TODAY%'
it becomes the following
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '6/30/2014'
|