Grid/Query

Owned by Former user (Deleted)
Last updated: Sept 03, 2019
7 min read

Customize

Most data types show up in customizable grids.  The user is able to select the fields that they want to appear in each grid by clicking on the  in the upper right hand side of the grid and check marking the column header name, as well as the order of the columns by simply dragging and dropping them in the desired order on the grid.

 


In the Grid results, you can scroll down through all of the data and it gets loaded as you go.  This means there is no limit on maximum records that can be returned, and no "page numbers" to deal with to see your results.  Clicking on a row in the grid brings up a highlight view of the record you are on.  To remove the summary view the user can click on the row again or click on the  in the corner of the view.


Filter


The default for any text search fields is to select fields starting with the value that you enter.  You may also include '%' as a wildcard to find fields containing a value.  For example, '%abc%' would select records with 'abc' anywhere in the field.  The default for date fields and other numeric fields is to select values greater than or equal to the value entered.

You can also filter using any of the following. For example, typing in =1000  will find all records where the value in that column equals 1000.  Similarly, typing in ".eq 1000" would also find the same results.

  • %value - will let you enter the last few digits of SSN or Number and will find that SSN. **Example:  123456789  to find 6789 type in %6789 and will pull just this one employee up in the grid**
  • %value% - will let you enter a few digits of an SSN or Number and will find those SSN's with those numbers in it.  **Example:  123456789 to fing 345 type in %345% and will show all employees with 345 in their SSN
  • =value or .eq value will select all records equal to the value entered
  • > value or .gt value will select all records greater than the value entered
  • >= value  or .ge value will select all records greater than or equal to the value entered (default behavior for numeric fields)
  • < value or .lt value will select all records less than the value entered  
  • <= value or .le value will select all records less than or equal to the value entered
  • <> value or .ne value will select all records not equal to the value entered
  • ~value or .like value will select all records starting with the value entered. Example: ~8  will find all 800-899 codes in your search this is the default behavior for text fields if no operator is entered)
  • *= value1,value2,value3 etc. or .oneOf value1,value2,value3 etc. will select records containing one of the values listed

  • value1..value2 or .between value1,value2 will select records between the values listed                       

  • To find values that are blank, use the Advance Query option.  Select the Operation: Is_Null

To filter on more than one grid option click on the first option on the grid you would like to filter on.  To add another filtering option hold the Shift key and click on the next field option you wish to filter by on the grid. A number 1 will be added on the grid menu for your first option chosen and a number 2 will be added on the second filtering option you chose. To add another filtering option hold the Shift key and click on the next field option you wish to filter by on the grid. A number 3 will be added on the grid on the third filtering option you chose.


You can then enter in the filter option in the grid to filter the data-Example 1-Job Status-A for all Active employees, 2-Retirement Code-ST for all STRS employees, Code-05 for all Pay Group Code 05 employees.

Sort

Users can sort any column by ascending or descending order by simply clicking on the column header name. To sort by more than one column at a time the user would click on the column header name they wish to sort by first and then hold down the shift key and click on another column header name. The columns are noted with a number beside the column name to show the order it was sorted. The sort can be reset by clicking on any column header name.

Highlight View

If a user doesn't want to leave the grid and instead wants a quick view of a particular row's details, click anywhere on the row and a highlight view box will appear on the right side of the grid displayed the more popular properties.

Advanced Query

The is a more elaborate search where the user selects the property field to search on, the operation to search by, and enters what they are searching for. For example in the Attendance Advance Search,  Select Property of 'Activity Date', Select Operation of 'Equals', and a Filter Value of '12/19/2017'. The user is searching for Employee's with a default activity date of 12/19/2017.  The Filter Value is a value entered by the user based on what they are searching for and the property field type; for some operations this may be a list or range with the values separated by commas.  The information entered in the Filter Value box is dependent on the property field selected.  If the user chooses a property field of type Boolean, the user would have to enter in true or false.

The user can add as many search lines as desired by dragging and dropping them in the desired order into the Display box or double clicking the Properties.   The Search properties can be 'Removed' by clicking on the.

The Advance Query searches can be saved and recalled later.  The Query can be saved by entering in a Query Name and clicking on ''.  Then the Query can then be reloaded using the

  • 'Apply Query' starts the search
  • 'Clear Query' clears the search items and returns all the original values back to the grid
  • 'Hide Advanced Query Controls' hides the Advanced Search Query window

Mass Change

Example of adding a Stop Date on a Payroll Item:

Select the Payroll Item from the drop down box.  From here you can enter what Code you are wanting to enter a stop date for.  Click on  

In Maintenance Mode, select from the Script Definition (Normal Mode) drop down, select  and enter the stop date MM/DD/YYYY in the New Value box.

To save the Script Definition, enter a name in the Definition Name and click on .  This definition can then be used again for just that Payroll Item (example.. OSDI Tax Item) that was selected by clicking on

To Execute the load, click on  option.  Once this has been selected, it will show how many records will be modified . Verify and click on 


Search Operations

Operation determines how the data in each row of the grid will be compared to the filter value when the query is applied.   The following operations are available:

  • Equals- equal
  • One of - list of possible values; must be separated by commas
  • Not equals-not equal
  • Like - begins with
  • Contains -  consists of
    • Positions example: "positionDescription.contains sub" will search for Positions with the word "sub" anywhere in the Position Description field.
  • Between - range; values must be separated by commas
      • Payments/Payroll:"paymentTransactions.payment.number.between 10247,10273" will search for Payroll numbers between 10247 and 10273
  • is Null() - blank
  • Not Null() - not blank
  • Greater than- greater than
  • Greater or equal - greater than or equal to
  • Less than- less than
  • Less or equal- less than or equal to
  • Not one of - exclude list of possible values; must be separated by commas
  • Sort - to place in order; always places in ascending order only 
    • attendance example: "activityDate.sort" will sort attendance by activity date
  • Type-See Property Fields below

Property Fields 

Property is a field associated with the record the user is querying. All properties associated with the type of data you are querying will appear in the "Properties" box. If you hover over each property, it will show you the path to the field and the data type in parenthesis ().

  • Integer-a whole number that can be positive, negative, or zero 
  • String - a sequence of symbols or values, example of a string is an account number '1234567' or vendor name 'Sample'
  • Boolean - true or false
  • Calendar - is a date (YYYY/MM/DD, MM/DD/YYYY)
  • big decimal - any fractional number
  • int- a whole number that can be positive, negative, or zero 
  • set - multiple values

Filter Value - is a value entered by the user based on what they are searching for and the property field type; for some operations this may be a list or range with the values separated by commas.