Grids appear throughout the Office Modules and display records of data utilizing vertical and horizontal lines to subdivide data fields. Grids provide easy access and ways to view and filter additional details and information associated with the specific records of the selected Module. Grid functionality is consistent throughout the modules.
A Club is reviewing records of data within the Accounts Payable module, and would like to add an additional column of data to the grid, so they can see all balances for the Vendors that are over 90 days. After adding the column, the Club would like to add a filter to the grid that will show only Vendors with balances over 90 days. The Club would then like to sort the data by highest balance to lowest balance within the grid, and then export the data into Excel for further analysis.
This video contains highlights from a training Webinar given on Member Grid (Export to Excel, Customize Columns, Filtering, Research and Reporting).
To access a Grid, click on a Module (for example, Accounts Payable.)
When clicking any module, the Default grid will open on the Home tab.
The Home Tab displays the default Module information. For example:
Accounts Payable Module opens Manage Vendors Grid
Employees opens Manage Employees Grid
Membership opens Manage Members Grid
Each Grid has columns that display relevant information for the Module.
Add or delete columns from the Grid one of two ways.
1) Right click in the Column Header, and select Column Chooser.
2) Or, right click anywhere within the grid and select Customize Columns.
Both methods open another interface that lists the available columns to add to the grid.
Add the column to the Grid by double clicking on the field name, or by dragging and dropping the field into the column header.
Organize grid columns by simply selecting the column title in the column header and dragging the column to the desired new location.
New column position results.
To remove a column, right click on the column title and select Remove This Column.
Or, drag the column back into the Drag & Drop Column Chooser tool.
To auto-resize a column so that the entire width of the column is shown, hover over the far right side of the column heading until a black two-headed arrow appears.
Then, double-click. Column width will resize appropriately.
Or, right-click in the column header, and choose Best Fit (for a specific column), or Best Fit (all columns).
Most grids have search functionality.
The Module Search feature is smart and finds the search word in all the columns available as the word is typed.
For instance, typing the word Best in the main search field for the Item Module brings up any Item with the word Best included.
Searches can be narrowed by using the Auto Filter Row search feature (discussed in next section).
The All Word feature, when selected, looks for every word in the grid that matches the search criteria.
For example, if the search criteria is Tee Times, then the search is looking for both words together to return a response.
The Any Word feature looks for any word that matches the search criteria.
For example, if the search criteria is Mark Sherman, the search may return Mark only or Sherman only responses.
Auto Filter Row
Below the column titles (in Grid format) is an empty field used to search for results in a specific column.
If the empty field is not visible, turn on the field by right clicking on any column title and select Show Auto Filter.
Click in the blank field under the column heading for the column to be searched, and type a word or words.
Search results will return in the grid below based on All Words typed.
Alternatively, to turn off the Auto Filter, click on Hide Auto Filter.
Columns may be filtered by selecting the specific category to display. Select the down arrow next to the Column Title and select the items to be displayed.
For instance, from the Manage Members Grid, filter to only show members in a certain member type. Grid will return results based on set filter.
To clear set filters from the grid and return all results, click the Clear Filter icon.
A built-in Filter for Active/All Records has been included in the toolbar.
To view All records, click the Active icon.
Note the Grid displays all records and highlights deleted or inactive records in a different color.
To return to viewing Active records, click the All Records icon.
Specific column headers can be filtered to display data in Ascending or Descending order.
For text fields, ascending order will display A-Z, and descending order will display Z-A.
For numeric fields, ascending order will display least amount to greatest amount, and descending order will display greatest to least.
To sort in ascending or descending order, click the Column Name. A small (up) arrow will appear in the Column header, and the data will display in ascending order.
Clicking the arrow when it is up, will turn the arrow down, and display the data in descending order.
Conversely, clicking the arrow when it is down, will turn the arrow up, and will again display the data in ascending order.
Another way to access the sort function is to right-click on the Column Title. Then, choose the desired order to sort from the displayed menu.
Exporting to Excel
Grids can be easily exported to Excel. This feature provides a great resource for reporting purposes and/or additional analysis.
For instance, let’s assume a club wants a list of all items in the system that are marked as Tax Inclusive.
- First, filter the grid to only show items that are marked as Tax Inclusive.
- Then select the Export tool on the Tool Bar. This will export the current grid to Excel.
Name the file, designate the location to save, and click Save.
Open the Excel file to view.
The Grid is set to auto-refresh in a certain amount of time.
To designate the time interval for the refresh, right-click on the grid, select Auto Refresh, and choose the desired interval.
To force a manual refresh at any time, click the Refresh icon.
To Group the display by a specific column, right-click on the Column Heading, and choose Group by This Column.
Note how the results display, and can be expanded to view detailed records within the grouping.
To remove the grouping, right-click on the Grouped Column heading, and choose UnGroup.
The Multi Edit function allows a User to easily change values for multiple records at one time.
To use this function, select the desired records from the grid (hold CTRL while selecting records with mouse).
Then, right-click on the grid, and choose, Multi Edit.
Next, select a Column utilizing the drop-down arrow. In the event, the selected Column is to be designated to the specified value, click the Specify a Value box.
Then, click OK to continue.
Next, confirm the change to affect the selected records by clicking Yes.
Success message will return (unless Only Show Failed option is selected above.) Click OK.
Then, click Close to continue.
Note, once records were set to Active (Inactive to False), to change them back to Active, again select the Column, and then, Specify a Value.
Note how this impacts the change. Records now change to Inactive, as indicated by the message "...Inactive to True."
Depending on the Column selected, the Specify a Value field may contain an opportunity to populate a value, or select from a list in addition to simply marking it true.
Custom Grid Layout
Create your own Custom Grid Layout.
First, right-click anywhere in the Grid and select Grid Layouts then New Layout.
Name the Layout and Select OK.
Then, create/filter the grid to fit your needs. Changes will automatically be associated with the newly created customized grid.
To return to the original (Default) grid, right click anywhere in the Grid and select Grid Layouts, Load Layout, and click Default.
To return to a Customized Grid, follow the above steps, and click the desired Grid in step 4 instead of the Default Layout.
To delete a customized grid (either created in error, or that is no longer serving a purpose), right click anywhere in the Grid and select Grid Layouts, Delete Layout, and choose the Layout to delete.
Confirm deletion by clicking Yes.
Reset Layout returns the New Grid to the original layout without saving changes.
Customized grids are unique to Individual Modules. (ie - Custom grid created in Accounts Payable Module does not exist within the Membership Module
Other users will have the ability to access the Custom Layouts.
The Module Grid retains the last Grid display options used.
The Filter Editor provides more advanced filtering capabilities.
The Tool can be accessed by right clicking anywhere on the column headers and selecting Filter Editor.
The Filter Editor works as a string value system that can be filtered to include more specific criteria.
Click And to launch the Operand menu selector.
Options include And, Or, Not And, and Not Or. Conditions or Groups may also be added.
Options available will depend on the specific Grid in use. Click blue field to view available column options.
Click on desired field.
Please see available options in screenshot below.
Enter value to filter by.
When finished, click Apply to view results from the filter without closing out the Filter Editor.
Or, click OK to view results and close the Filter Editor.
To add another line to the filter, click, the green + button.
Repeat steps for defining new filter line.
To delete a filter line, click the X at the end of the line.
Click OK to view results.
In this example, Audubon International is the only Vendor with a balance over 90 days exceeding $5,000.00.
From the Manage Members Grid, let’s assume the Board wants a list of Active Members who are ages 50 to 75. Note results are returned.
The list can now be easily exported to Excel for further manipulation.
When finished, remember to clear filters to see all data.
Conditional formatting is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula.
This allows for easy interpretation of data within the grid. To access, right-click on the Column Header, and choose Conditional Formatting.
For instance, within the Membership Grid, to highlight all Members over the age of 50 in a specific color, right-click on the Age column.
Select Conditional Formatting, Highlight Cell Rules, choose Greater Than, and then define rules.
Note that Members over 50 are highlighted in Yellow, and Members 50 and under are not.
To clear conditional formatting added, click Conditional Formatting, Clear Rule, Clear Rules from This Column or form All Columns depending on need.
Utilize Filters, Sorting, and Searches to help identify the specific data you would like to view.
Easily export views/data to Excel to further manipulate/analyze.
Remember to clear filters to view all data.
Q. Can others view custom grid layouts I create?
A. Yes, once created, grid layouts are automatically available for other Users.
Q. What is the best way to view a subset of data based on certain parameters?
A. Use filters and/or advanced filters to define what range of data you would like to see, and exclude the records that do not apply.
Q. Can I use grids to make the same change to Multiple records at a time?
A. Yes, utilize the Multi Edit feature to adjust multiple records at once. To access, right-click on the grid, and select Multi Edit from the menu.
Q. I can’t see all of my data any more - how do I get it back?
A. Click the Clear Filter icon to ensure all filters have been removed, and all data is visible.
Q. Is it possible to see deleted records that are no longer in the default grid?
A. Yes, click the Active icon to show All records (including deleted records).