Queries

Queries are one of the key tools of data filtering. While MRI Evolution has quick search and column specific filtering on grids; queries allow a User to narrow data in other ways e.g. Show all those Tasks that are still active or assigned or all non-urgent calls logged today.

Queries can be written by a User for their use only- create the Query in Settings > User Preferences > Queries, or they can be written within System Administration and be made available to all MRI Evolution Users.

The list of System Queries are maintained here.

Settings > User Preferences > Queries or
System Administration > System Preferences > System Appearance & Behaviour > Queries

The link will open the Query grid. It is a grid view which can be searched, sorted and customised as normal.

To add a new Query click New , to edit an existing Query click Edit . A pop up window will appear which will allow the creation or amendment of a Query.

ClosedClick to view - Query Fields and Descriptions

Field Name Description
Name The name of the Query.
Entity Type From the drop down select the entity type the query is to be written around.
Entity Click on the added entity to add fields and operators to the Query.
Field From the drop down select the required field – select add to add the field to the Query.
Relationship From the drop down select a related table to add to the Query – select add to add the filed to the query.
Operator Add groups to ensure the database is grouping information for calculations correctly.

Complete the Query and click Save and Close .

Query Functions

The functions below can be used in system and user queries

Notation Returns Example / Detail
{Me} Returns current logged Account ID.

CreatedBy EqualTo {Me}

Also includes any Job Share Accounts
{My Bookable Locations} Array of LO_SEQ values LO_SEQ in {My Bookable Locations}
{My Published Buildings} Array of BG_SEQ values

BG_SEQ in {My Published Buildings} 

Based on Building Publishing to Accounts
{My Published Resources}

Available from 4.8.0.0

Array of RES_SEQ values

RES_SEQ in {My Published Resources}

Based on Assigned Resources to the currently logged on Account
{My Published Suppliers}

Available from 4.8.0.0

SUP_SEQs published to the current Account

SUP_SEQ in {My Published Suppliers}

Based on the Supplier Publishing to Accounts
{Contact Building} KP_FKEY_BG_SEQ

BG_SEQ EqualTo {Contact Building}

Also includes Buildings linked to any Job Share Contacts
{Contact} KP_SEQ

BDET_FKEY_KP_SEQ EqualTo {Contact}

Also includes KP_SEQs for any Job Share Contacts
{My Contact Belongs To Department}

Available from 4.8.0.0

KP_SEC_FKEY_DP_SEQ of FADDRS linked to current Account

 This returns ONLY the KP_FKEY_DP_SEQ from the Contact linked to the current account. Unlike {My Department} it does not include any Job Share Contacts
{My Contact Building}

Available from 4.8.0.0

KP_FKEY_BG_SEQ of FADDRS linked to current Account

 This returns ONLY the KP_FKEY_BG_SEQ from the Contact linked to the current account. Unlike {Contact Building} it does not include any Job Share Contacts
{My Contact Class}

Available from 4.8.0.0

KP_FKEY_CON_CLASS_SEQ of FADDRS linked to current Account
{My Contact Supplier}

Available from 4.8.0.0

KP_FKEY_SUP_SEQ of FADDRS linked to current Account
{My Contact Working Department}

Available from 4.8.0.0

KP_FKEY_DP_SEQ of FADDRS linked to current Account
{My Contact}

Available from 4.8.0.0

KP_SEQ of FADDRS linked to current Account

 This returns ONLY the KP_SEQ linked to the current account. Unlike {Contact} it does not include any Job Share Contacts
{Buildings Linked to My Contact}

Available from 6.0.1.0

Returns Array of BG_SEQ via CONTACTLINKER

 This returns values for the Contact (KP_SEQ) linked to the current Account (Account_FKEY_KP_SEQ) and any Job Shared Accounts.
{Suppliers Linked to My Contact}

Available from 6.0.1.0

Returns Array of SUP_SEQ via CONTACTLINKER

 This returns values for the Contact (KP_SEQ) linked to the current Account (Account_FKEY_KP_SEQ) and any Job Shared Accounts.
{Sites Linked to My Contact}

Available from 6.0.1.0

Returns Array of BGP_SEQ via CONTACTLINKER

 This returns values for the Contact (KP_SEQ) linked to the current Account (Account_FKEY_KP_SEQ) and any Job Shared Accounts.
{Contracts Linked to My Contact}

Available from 6.0.1.0

Returns Array of CTR_SEQ via CONTACTLINKER

 This returns values for the Contact (KP_SEQ) linked to the current Account (Account_FKEY_KP_SEQ) and any Job Shared Accounts.
{My Cost Centre} KP_FKEY_FC_SEQ

FC_SEQ EqualTo {My Cost Centre}

Also includes Cost Centres linked to any Job Share Contacts
{My Department Group} FADDRS.FDEPTS.DP_FKEY_DPG_SEQ

DPG_SEQ EqualTo {My Department Group}

Also includes Department Groups linked to any Job Share Contacts
{My Department} KP_FKEY_DP_SEQ

DP_SEQ EqualTo {My Department}

Also includes Departments linked to any Job Share Contacts
{DateNow} Returns the current date. TA_DUE_DATE greater than {DateNow}
{DateNow+N days} Today's Date with time value 00:00:00 + N days TA_DUE_DATE less than {DateNow+N days}
{DateNow+N hours} Today's Date with time value 00:00:00 + N hours

TA_DUE_DATE less than {DateNow+N hours}

{DateNow+N mins} Today's Date with time value 00:00:00 + N mins

TA_DUE_DATE less than {DateNow+N mins}

{DateNow+N months} Today's Date with time value 00:00:00 + N months

TA_DUE_DATE less than {DateNow+N months}

{DateNow+N weeks} Today's Date with time value 00:00:00 + N weeks

TA_DUE_DATE less than {DateNow+N weeks}

{DateNow+N years} Today's Date with time value 00:00:00 + N years

TA_DUE_DATE less than {DateNow+N years}

{DateNow+N} Today's Date with time value 00:00:00 + N days TA_DUE_DATE less than {DateNow+N}
{DateNow-N days} Today's Date with time value 00:00:00 - N days TA_DUE_DATE greater than {DateNow-N days}
{DateNow-N hours} Today's Date with time value 00:00:00 - N hours TA_DUE_DATE greater than {DateNow-N hours}
{DateNow-N mins} Today's Date with time value 00:00:00 - N mins TA_DUE_DATE greater than {DateNow-N mins}
{DateNow-N months} Today's Date with time value 00:00:00 - N months TA_DUE_DATE greater than {DateNow-N months}
{DateNow-N weeks} Today's Date with time value 00:00:00 - N weeks TA_DUE_DATE greater than {DateNow-N weeks}
{DateNow-N years} Today's Date with time value 00:00:00 - N years TA_DUE_DATE greater than {DateNow-N years}
{DateNow-N} Today's Date with time value 00:00:00 - N days TA_DUE_DATE greater than {DateNow-1}
{Last N Days} Returns the last N days up to the end of yesterday. This notation does not include the current date in the results. To include current date in the query add an OR clause and use the Today notation
{Last n hours}

Available from 4.9.0.0

Time between now and N hours ago

TA_DUE_DATE EqualTo {Last 1 hours}
{Last n minute}

Available from 4.9.0.0

Time between now and N minutes ago

TA_DUE_DATE EqualTo {Last 1 minute}
{Last n months}

Available from 4.9.0.0

Date and Time between now and N months ago

TA_DUE_DATE EqualTo {Last 1 months}
{Last n weeks}

Available from 4.9.0.0

Date and Time between now and N weeks ago

TA_DUE_DATE EqualTo {Last 1 weeks}
{Last n years}

Available from 4.9.0.0

Date and Time between now and N years ago

TA_DUE_DATE EqualTo {Last 1 years}
{Next N Days} Returns the next N end days from the beginning of today.

TA_DUE_DATE EqualTo {Next 1 Days}

 
{Next n hours}

Available from 4.9.0.0

Time between now and N hours from now

TA_DUE_DATE EqualTo {Next 1 hours}
{Next n minute}

Available from 4.9.0.0

Time between now and N minutes from now

TA_DUE_DATE EqualTo {Next 1 minute}
{Next n months}

Available from 4.9.0.0

Date and Time between now and N months from now

 TA_DUE_DATE EqualTo {Next 1 months}
{Next n weeks}

Available from 4.9.0.0

Date and Time between now and N weeks from now

TA_DUE_DATE EqualTo {Next 1 weeks}
{Next n years}

Available from 4.9.0.0

Date and Time between now and N years from now

TA_DUE_DATE EqualTo {Next 1 years}
{Now} Returns current date and time.  
{Now+N days} Current date and time + N days TA_DUE_DATE less than {Now+1 days}
{Now+N hours} Current date and time + N hours TA_DUE_DATE less than {Now+1 hours}
{Now+N mins} Current date and time + N mins TA_DUE_DATE less than {Now+1 mins}
{Now+N months} Current date and time + N months TA_DUE_DATE less than {Now+1 months}
{Now+N weeks} Current date and time + N weeks TA_DUE_DATE less than {Now+1 weeks}
{Now+N years} Current date and time + N years TA_DUE_DATE less than {Now+1 years}
{Now-N days} Current date and time - N days TA_DUE_DATE greater than {Now-1 days}
{Now-N hours} } Current date and time - N hours TA_DUE_DATE greater than {Now-1 hours}
{Now-N mins} Current date and time - N mins TA_DUE_DATE greater than {Now-1 mins}
{Now-N months} Current date and time - N months TA_DUE_DATE greater than {Now-1 months}
{Now-N weeks} Current date and time - N weeks TA_DUE_DATE greater than {Now-1 weeks}
{Now-N years} Current date and time - N years TA_DUE_DATE greater than {Now-1 years}
{This year}

Available from 4.9.0.0

All dates and times this Calendar Year

TA_DUE_DATE EqualTo {This year}
{ThisMonth}

Available from 4.9.0.0

All dates and times this Calendar Month

TA_DUE_DATE EqualTo {ThisMonth}
{ThisWeek}

Available from 4.9.0.0

All dates and times this week (start day Monday)

TA_DUE_DATE EqualTo {ThisWeek}
{ThisWeekUS}

Available from 4.9.0.0

All dates and times this week (start day Sunday)

 TA_DUE_DATE EqualTo {ThisWeekUS}
{Today} Returns between <system date> 00:00 and <system date> 23:59. TA_DUE_DATE EqualTo {Today}
{Null} Allows matching nulls. This notation is now redundant as IsNull is available in the query designer as a comparison operator
{PMS Batch End Date - N mins/hours/days}    
{PMS Batch End Date + N mins/hours/days}    
{PMS Batch End Date}    
{PMS Batch Start Date - N mins/hours/days}    
{PMS Batch Start Date + N mins/hours/days}    
{PMS Batch Start Date}