How to Create a Custom Grid in Mendix

The Mendix Data Grid 2 component gives Mendix developers a lot of options right out of the box, but it can’t do everything. I’ve had requirements for grids that counts number of records in each enumeration status or use custom filtering widgets like the Searchable Selector. Situations like these require building out grid manually, a design pattern I like to call a “Custom Grid”.

Custom Grid example with Employees

Custom Grids work by creating an object that stores the grid’s meta data and a Microflow that translates that meta data into the Xpath module. To learn why the Xpath module is used, consider reading the article ‘How to create Efficient, Dynamic Queries in Mendix’.

Custom Grid and Xpath module overview

When to make a Custom Grid vs using Data Grid 2

It takes more development time to make a Custom Grid than to use Data Grid or Data Grid 2. So Custom Grids should be reserved for only when they’re absolutely necessary. Here are a few things I consider when deciding to which to use:

Use Data Grid or Data Grid 2:

• The extra features that would be provided by making a Custom Grid are not required and/or do not provide much additional value (i.e. Making a Custom Grid that shows employees so the Department field can be a searchable dropdown, but the users rarely search by Department).
• Limited Time to implement. If the project has a significant time crunch, it would be better to use Data Grid 2 and then revisit making a Custom Grid in a later phase where there is more time to enhance.
• Admin screens and/or screens that are rarely used by the users are not worth the effort to customize (i.e. An admin checks the Account Overview page once every few months to add new users).
• The concern is only related to UI/UX (i.e. the user wants the search criteria to appear in a different place on the screen). Use CSS/SCSS to customize the styling instead.

Use Xpath Module:

• Search criteria is not supported (i.e. a searchable dropdown or a sliding bar to search years of cars).
• Aggregates that follow the grid’s filters (i.e. in any page of the results, how many records are in Submitted status vs how many in Approved status).
• A data source Microflow is performing slowly due to a large retrieve followed by loops and/or list operations that remove items from the list.

Project Download

Project Download (Mendix 9.24.5)

1. Download the .mpk file
2. Open Mendix Studio Pro version 9.24.5
3. Click “Open App Locally”
4. Navigate to where you downloaded the .mpk file and open the file
5. Choose where to store your app and an app name
6. Choose where you would like to generate the project structure and click “Okay”

Dependencies

Install the following modules from the Mendix Marketplace.

• Xpath module
• Community Commons
• Nanoflow Commons

The following widgets are not required to make a Custom Grid but make the process much, much easier.

• Advanced Pagination — Renders pagination buttons in multiple, common styles.
• Advanced Sorting — Makes it easier to add sorting to a Custom Grid. Can be used as a header that switches the sort direction or as a dropdown with sorting options.

Domain Model

For this example, the Custom Grid will be used to display Employees objects. The following domain model is the data structure that will be used. An employee is assigned to one Department and have one supervisor.

Employee domain model

Page Helper

Create a non-persistent entity to be used as a page helper with a name specific to the objects that get returned. It is going to return Employee objects so name the entity “SearchEmployees”.

Add the following attributes to SearchEmployees:

Initial custom grid page helper

• PageNumber (Number) (Default: 1)
• PageSize (Number) (Default: 10)
• ResultTotal (Number) (Default: 0)
• SortAttribute (String) (Lenght: Unlimited)
• SortAscending (Boolean)
• SearchEmployees_Session (association to System — Session)

Next, add attributes and associations for all the search criteria the user can enter or select. I added the following to show how to build queries for each type.

Page helper with search criteria

I won’t go into the details of each of these attributes and associations but here are some guidelines to consider:

• String attributes should have the same length as the attribute(s) they search against. (i.e. FuzzySearch is used to search the Employee’s full name so it should have the same attribute length as Employee — FullName)
• Date and Times should NOT be localized if they are searching againts non-localized fields. (i.e. HireDate is not localized so HireDate_Lower and HireDate_Upper should also not be localized)
• Associations are used for selecting other objects. For example, the grid will display a dropdown of all Departments. The user can select a specific Department and the Custom Grid will only show Employees that belong to that Department. The list of Departments the user selected is stored in the SearchEmployees_Departments association.

Lastly, be sure that the user has permission to edit the attributes of the page helper.

Actions

Create all of the following Microflows and Nanoflows in the same folder. Don’t forget to grant the user permission to execute these actions.

Folder Structure

ACT_SearchEmployees_Refresh

This Nanoflow receives the SearchEmployee object and refreshes it using the Refresh Object activity from Nanoflow Commons. This will be used by the Advanced Pagination and Advanced Sorting widgets to force the Custom Grid to re-retrieve results.

ACT_SearchEmployees_Refresh

ACT_SearchEmployees_Reset

This Nanoflow manually changes all the SearchEmployee’s attributes and associations back to their default values then refreshes the grid.

ACT_SearchEmployees_Reset

ACT_SearchEmployees_Search

This Nanoflow changes the SearchEmployee’s PageNumber back to 1 then refreshes the grid. It can be used on all the OnChange actions for the search criteria to apply the search or as a search button.

ACT_SearchEmployees_Search

DS_Employees_bySearchEmployees

This Microflow uses the Xpath object from the SUB_Xpath_bySearchEmployees Sub-Microflow to execute the query and returns the list of Employees. Using the “Retrieve by xpath” Java action, select the following:

DS_Employees_bySearchEmployees

• Offset: ($SearchEmployees/PageNumber -1) * $SearchEmployese/PageSize

Next, use the “Retrieve by xpath aggregate int” Java action select the following:

Why are there 2 retrieves? The first retrieve is limited to only retrieve what will be displayed on the page by the page size. While the second retrieve counts of all objects that meet the criteria.

Finally, set the SearchEmployees object’s ResultTotal to the count. Do NOT set this change object activity to refresh in client or you will create an infinite refresh loop.

DS_SearchEmployees

This Microflow retrieves the list of SearchEmployee objects from the currentSession by association. It is responsible for saving the search criteria if the user leaves the page and comes back.

DS_SearchEmployees

If there are no SearchEmployee objects found, then the flow will create a new SearchEmployee object and associate it to the currentSession. If there is a SearchEmployee object already, then the flow will return the first object in the list.

SUB_Xpath_bySearchEmployees

This Sub-Microflow is the meat and potatoes of the Custom Grid. It will map the SearchEmployees object into the Xpath and SortMap objects. For now, only build out the basic structure below to get the code to compile. The later section ‘’Buiding the Xpath String” will expand this Microflow to add decision spits for each of the search criteria.

Initial SUB_Xpath_bySearchEmployees

• The Create Object activity should create and Xpath object from the Xpath Module. Default the Query attribute to ‘’ or any Xpath statement that should always be applied.
• Add a decision split for trim($SearchEmployees/SortAttribute) != ‘’
• If there is a SortAttribute, create a new SortMap object as you see above.
• The Microflow should return the $NewXpath object.

Mendix Page

Below is an example of a Custom Grid layout.

Page Layout

• The page-wide data view calls DS_SearchEmployees
• The list view calls DS_Employees_bySearchEmployees
• All of the input widgets have their On Change action set to ACT_SearchEmployees_Search, so the search criteria are applied immediately after each selection. This could also be done a button instead of an OnChange on each input.

Advanced Pagination

This is a widget I created specifically to make Custom Grid creation easier. To use this widget, add it to the page and set the following properties.

Advanced Pagination Settings

Page, Result Count, Page Size, and Refresh Action are the only required properties for the widget to work. Details on the other settings and how they can be further customized can be found on the GitHub ReadMe.

Note: only ONE Advanced Pagination widget per page should have Auto Correct enabled

Advanced Sorting

This widget is also made to help make Custom Grids. Add the widget to every header in your grid and set the following properties:

Advanced Sorting settings for the Full Name column

Sort Attribute, Sort Ascending, Refresh Action, and Attribute Name are the only required properties for the widget to work. Details on the other settings and how they can be further customized can be found on the GitHub ReadMe.

The attribute name should be the exact name of the attribute in the data base. When navigating across associations, the format should look like this: {ModuleName}.{AssociationName}/{ModuleName}.{EntityName}/{AttributeName}

For example, the Department column looks like this:

Advanced Sorting settings for the Department column

You should now be able to run the project and use the Custom Grid. The objects should show on the page, the headers should be sortable, and the pagination buttons should work.

Demo of pagination and sorting

The query string is the only thing left to build, so the filter widgets affect the result list.

Building the Xpath String

Now we’re finally at the fun part! We need to update the Xpath Object in SUB_Xpath_bySearchEmployees. All of these decision splits should go after the check for the SortAttribute.

Initial SUB_Xpath_bySearchEmployees

Some tips while building these out:

• Don’t forget to add the brackets
• A disabled retrieve activity can be used to get the Xpath syntax. Buidl out a section of the query there and then copy & paste the Xpath query into the change object activities

Disabled retrieve activity to help get syntax

For all types of search criteria, include a decision split that checks that the relevant attribute or association is not empty. For strings, use the trim function.

Example of checking if a string and an integer is not empty

For filters where the user can type in a literal string, ALWAYS do a trim() and replaceAll(). The trim() helps with user experience for when the entered string ends or starts with a space character. The replaceAll() prevents a hard java error if the user types a single quote (i.e. the user search for last name O’ Brian).

String handling example

trim(replaceAll($SearchEmployees/SearchID, '''',''''''))

Strings and Enumerations

Use an equal sign, contains, or startsWith functions. Encase the string from the helper object in 3 single quotes (This makes the output string include a single quote)

Fuzzy search example

$NewXpath/Query + 
'[contains(FullName,'''+$FuzzySearchID_String+''')]'

$NewXpath/Query + 
'[FullName = '''+$FuzzySearchID_String+''']'

For enumerations, you must use an equal sign with the enumeration’s key. For example, if you had an enumeration with the caption “In Progress” and the key “In_Progress”, the query would look like this:

$NewXpath/Query + 
'[Status = ''In_Progress'']'

If you’re using an enum attribute, be sure to ALWAYS use toString() and NOT getCaption(). The database stores these values as the enum’s key not the caption.

$NewXpath/Query + 
'[Status = '''+toString($SearchEmployess/Gender)+''']'

Note: enumerations do not support the contains or startsWith functions.

Booleans

To evaluate when a boolean is true, either use the name of the attribute alone or with ‘true’. For false, you must use = false.

$NewXpath/Query + 
'[Terminated]'

$NewXpath/Query + 
'[Terminated = true]'

$NewXpath/Query + 
'[Terminated = false]'

Auto Numbers, Integers, and Decimals

Use the integer or decimal directly in the expression. Symbols =, <, <=, >, or >= can be used.

Date and Time

Use the dateTimeToEpoch function to get an integer representation of the date and time. Symbols =, <, <=, >, or >= can be used.

To use Mendix tokens like CurrentDateTime, use the same syntax with 2 single quotes.

$NewXpath/Query + 
'[HireDate >= '+dateTimeToEpoch($SearchEmployees/HireDate_Lower)+']' +
'[HireDate < ''[%CurrentDateTime%]'']'

Reference by ID

1. Retrieve the object by association. Do not retrieve by database as the object should already be in memory.
2. Pass the object from step 1 into the getGUID Java action from the Community Commons module.
3. Use an equal sign with the GUID and the association name.

$NewXpath/Query 
+'[CustomGridExample.Employee_Department = '+$Department_GUID+']'

Reference by Attribute

Use the full association path to the attribute on the other object.

$NewXpath/Query + 
'[CustomGridExample.Employee_Department/CustomGridExample.Department/Name = '''+trim($SearchEmployees/FuzzySearch)+''']'

Note: retrieving a reference by ID is always computationally faster than retrieving by an association’s attribute. Use Reference by ID where possible.

Reference Set

(1) Retrieve the list of selected objects by association.

(2) Create a string variable with the default value being the navigation part of the query.

'[CustomGridExample.Employee_Supervisor/CustomGridExample.Employee['

The second open bracket is used to create a sub-query. This is the most effecient way to query against a list of selected objects.

(3) Use a loop to iterate of the selected objects. Inside the loop, pass the iterator object into the getGUID Java action from the Community Commons module.

(4) Change the string variable and append it with the GUID and an ‘ or ’ at the end.

$SupervisorXpath + 'id = '+$Supervisor_GUID+' or '

(5) Use the substring function to remove the last ‘ or ’ and add the closing brackets

$NewXpath/Query 
+substring($SupervisorXpath, 0, length($SupervisorXpath) - 4) + ']]'

Conclusion

Now with all the filters added, the Custom Grid is fully operational! From this point, the search criteria, Advanced Pagination (GitHub ReadMe), and Advanced Sorting (GitHub ReadMe) widgets can be further customized.

Extra Tips & Next Steps

• Domain Model Access Rules —To applie any existing access rules, open the properties for the list view’s data source (DS_Employees_bySearchEmployees) and enable “Apply entity access”.

How to apply entity access

• List view selection — To easily add row selections via checkboxes or clicking the cell, see my widget List View Selection made specifically for this purpose.
• Aggregates that follow search criteria — To have counts, averages, mins, maxs, or sums of an attribute, re-use the SUB_Xpath_bySearchEmployees Microflow along with a Java action from the module.

Example of calculating Average Salary