Child pages
  • Grid/Query
Skip to end of metadata
Go to start of metadata

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 names.  The order of the columns can be changed by simply dragging and dropping them in the desired order.  Column widths may be changed by hovering over the column line and dragging it to the desired size.  Each user's customized settings will be automatically saved and used the next time they access the grid, even after logging out and back into the system.

Grid results are "lazy-loaded", meaning that 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 highlight view the user can click on the row again or click on the  in the corner of the view.

Filter

Users will be able to filter grid results directly via a "filter row", which is directly beneath the column headings as shown here: 

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 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 (only works for text fields; this is the default behavior for text fields if no operator is entered)
  • value1..value2 or .between value1,value2 will select records between the values listed 

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.  In my vendor grid example below, I clicked on Vendor 1 (which stays selected) and the highlight view displays off to the right.

Report

Users can generate a report from any of the grids using the Report icon .  The report automatically includes all columns on your grid and pulls data from what you have entered on the filter row.  For example, the screenshot below, you can generate a quick report of active 1099 vendors containing a YTD amount of $600 or higher.   After selecting your report options, click on  to generate the report.  If you want to save the report for future use, enter a report name in the  box and click on .  Your saved report will be automatically added to the Report Manager grid.

More

The More  icon will show a list of additional columns that can be added to the grid. Columns can be selected to be added to the grid by checking the associated box. To remove a column from the grid, uncheck the associated box. The page will refresh when the 'More' option is closed to add/remove the selected columns. If no changes are made to the Column Selection, then the page will not automatically refresh when the 'More' option is closed. 

It is recommended to make changes to the grid prior to sorting or filtering the grid since the filters will be removed when the page refreshes. On grid pages with multiple tabs (Accounts), the page will revert to the first tab after the refresh.

Adding multiple columns with calculated figures (amounts) or fields from linked pages could have an unfavorable effect on grid performance. If too many calculated columns are added the time it takes to load the grid  will drastically increase. This can specifically be an issue when adding totals to the Account grids. At this time, it is recommended to only add one or two calculated fields maximum. 

Related performance improvements are in progress.


Reset

The Reset  icon will reverse any changes the user has made to the grid (columns added/removed) and will revert the grid columns to default.

Advanced Query

An advanced query function is available by clicking on the Magnifying glass at the top right of the grid as shown below.

Advanced Query is a more elaborate search where the user selects the property to search on, the operation to search by, and enters what they are searching for.  This may be useful for complex queries or to search by information without having to first add columns to your grid.  

 The screenshot below shows an example of using the Advanced Query option from the Core/Vendor menu option.  

 To add properties the user can either double click the desired property from the "Properties" box on the left, or drag and drop it to the center section of the screen.  In this example, the property  'defaultPaymentType' was selected and thus appears in the "Display Name" column.  The user would then select an Operation from the dropdown box and enter in a Filter Value.  For example, selecting an operation of '.eq' and entering a Filter Value of 'electronic' would result in searching for all vendors with a default payment type of electronic.  The user can add as many search lines as desired by double-clicking or dragging and dropping more properties from the "Properties" box into the center section.  The last step is to click on  to apply the search criteria to the grid.

The  can be used to remove a search line, and can be used to clear the entire advanced search query.

As properties, operations, and text are entered there is an expert box on the right side of the screen that fills in automatically. The expert search box may also be used by itself if the user prefers to write their own query using the properties and operations, or to copy and paste a query you have received from someone. The expert search is useful for troubleshooting and for easy sharing of queries with other users.

The advanced searches can be saved and recalled later, and will also be available as filters in the detail reports. If the user chooses to save the search they would enter in a Query name and click on . To recall a previously saved query select the desired one from the drop down box on the right hand side of the advanced query and all previously saved criteria is loaded in. At this point the user may make changes if desired and once satisfied with the query they would then click on the .

Advanced Query Column Details:

A "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 or int- 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

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 
    • One of - list of possible values; must be separated by commas
    • Not Equals 
    • Like - begins with
    • Contains -  will search for the value anywhere in the field
    • Between - range; values must be separated by commas
    • Is null - blank
    • Not null - not blank
    • Greater than  
    • Greater or equal  
    • Less than  
    • Less or equal 
    • Not one of - exclude list of possible values; must be separated by commas
    • Sort - to place in order
      • vendor example: "lastactivitydate sort" will sort vendors by last activity date
    • Type - the class type.  Must be a fully qualified class name.

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.

Advanced Query/Search Video

Grid Video



  • No labels