<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Benjamin Griggs on Medium]]></title>
        <description><![CDATA[Stories by Benjamin Griggs on Medium]]></description>
        <link>https://medium.com/@benjaminsgriggs?source=rss-f465d8fb841d------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*HzIPAQE7xYg1-9kQfPEuGA.jpeg</url>
            <title>Stories by Benjamin Griggs on Medium</title>
            <link>https://medium.com/@benjaminsgriggs?source=rss-f465d8fb841d------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 20:29:23 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@benjaminsgriggs/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[What is a Mendix Custom Grid and When Should I Use One?]]></title>
            <link>https://medium.com/@benjaminsgriggs/what-is-a-mendix-custom-grid-and-when-should-i-use-one-5ccb8c7dfbd4?source=rss-f465d8fb841d------2</link>
            <guid isPermaLink="false">https://medium.com/p/5ccb8c7dfbd4</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[mendix]]></category>
            <category><![CDATA[lessons]]></category>
            <dc:creator><![CDATA[Benjamin Griggs]]></dc:creator>
            <pubDate>Mon, 17 Feb 2025 20:33:57 GMT</pubDate>
            <atom:updated>2025-02-17T20:35:35.834Z</atom:updated>
            <content:encoded><![CDATA[<p>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 count the number of records in each enumeration status or filter with custom widgets like <a href="https://marketplace.mendix.com/link/component/219304">Combobox</a>. Situations like these require building out the grid manually, a design pattern I like to call a “Custom Grid”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pqnDuCySD1K6wzNiTjRQJQ.png" /><figcaption>Custom Grid example with Employees</figcaption></figure><p>Custom Grids work by creating an object that stores the grid’s metadata and a Microflow that translates that metadata into database retrieves. I like to think of it as writing my own query engine—a reusable piece of code that accepts basic inputs and can return a wide variety of data.</p><h3>Do I Need a Custom Grid?</h3><p>Before diving into creating a Custom Grid and its query engine, there are a few things to consider. Creating Custom Grids takes a <em>decent amount of time and effort</em>, so they should be reserved for when it is absolutely necessary. Here are a few things I consider when deciding which to use:</p><h4>Use Data Grid or Data Grid 2:</h4><p><strong>(1) The features will not add much value</strong>.</p><p>The extra features that would be provided by making a Custom Grid are not required and/or the vast majority of the requirements are covered by Data Grid 2.</p><p>For example, a Custom Grid that shows employees with the department filter as a searchable dropdown, but most users can only see employees from one or two Departments. At that point, use Data Grid 2’s text filter or drop-down filter widgets.</p><p><strong>(2) There is limited time to implement</strong>.</p><p>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 it.</p><p><strong>(3) The page is rarely used</strong>.</p><p>Admin screens and/or screens that are rarely used are not worth the effort to customize. For example, the Account Overview page is used once every few months, so it is not worth the effort to customize.</p><p><strong>(4) The concern is only related to UI/UX</strong></p><p>Do not use a custom grid just so the columns can look a certain way — use Data Grid 2’s custom content setting or CSS instead.</p><p>Always consider what is MVP (minimum viable product) for the project. Using a Custom Grid may give flexibility to make the grid stylish and pretty, but will the users be unable to do their job? Probably not.</p><h4>Create a Custom Grid:</h4><p>The following are a few requirements I’ve had that became custom grids. This should be considered in addition to the points above. If the requirements only tick a box or 2, consider alternatives that Data Grid 2 already provides or de-prioritizing the requirement.</p><p><strong>(1) The filter is not supported.</strong></p><p>Filtering a list of employees by department with a searchable <a href="https://marketplace.mendix.com/link/component/219304">Combobox</a> or filtering their salary with a <a href="https://marketplace.mendix.com/link/component/52704">Range Slider</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*daj4lYDjhH0GLGcb7u1hKg.png" /><figcaption>Filtering example</figcaption></figure><p><strong>(2) The filters affect more than just the grid</strong>.</p><p>Having aggregates get updated when the search criteria change. In the screenshot below, this would have the Average Salary and number of Active employees updated to only consider the ‘Product Management’ department.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/863/1*SZUhq7Db2S9IHQktKqz2VQ.png" /><figcaption>KPI/Dashboard Example</figcaption></figure><p>Another example would be the filters update a pie chart.</p><p><strong>(3) Improving Performance.</strong></p><p>While Data Grid 2s sourced by database or Xpath will do server-side pagination, Microflow data sources do client-side pagination. This can cause performance issues if the Microflow returns too much data. The query engine for Custom Grids will allow pagination by the database and the flexibility of a Microflow. For more performance details, see my article <a href="https://medium.com/@benjaminsgriggs/how-to-create-efficient-dynamic-queries-in-mendix-cc7a4b51ea53">here</a>.</p><h3>What Kind of Custom Grid do I need?</h3><p>After going through the reasons for or against making a Custom Grid, the next decision is what module to use for the query engine.</p><h4>Use Xpath module:</h4><p><strong>(1) Xpath engines are faster to implement than OQL engines.</strong></p><p>Default to using Xpath unless otherwise necessary. Xpath engines are easier to implement because the developer need only consider the WHERE clause of the query. OQL engines have to write SELECT, FROM, JOIN, WHERE, LIMIT, OFFSET, and potentially more clauses.</p><p><strong>(2) Heavily reliant on Entity Access Rules</strong>.</p><p>Entity access rules are a way to limit a user’s access to data system-wide. Xpath query engines simply have a setting on the Microflow to apply entity access.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/0*ueQ9zBe7KUZsBfOs.png" /></figure><p>Meanwhile, OQL query engines do NOT apply entity access rules at all, so any rules have to be manually written in the WHERE clause. If the entity has many rules, then this adds a significant amount of time to implement.</p><h4>Use OQL module:</h4><p><strong>(1) Horizontally large entity performance.</strong></p><p>The Data Grid 2 retrieves can perform poorly if the entity has too many attributes or associations. When using Xpath, Mendix does SELECT and JOINs for all attributes and associations on the entity.</p><p>In the image below, imagine the system retrieves 20 rows of data, but the amount of data per row still depends on the number of columns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/773/1*gPcR2vzoQ1JrRp8qQ46O_A.png" /><figcaption>Horizontal scaling example</figcaption></figure><p>Entities with 30+ attributes and associations perform poorly because the amount of data sent to the browser is growing horizontally.</p><p>OQL solves this by allowing the developer to specify which attributes/associations to retrieve. However depending on the project, it may be easier to split the entity into multiple entities with a one-to-one association.</p><p><strong>(2) Data export requirement</strong>s.</p><p>The latest version of Data Grid 2 already has an Excel export feature, so this requirement should not be the only reason to make a Custom Grid.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/375/1*TxkZ-ncEGkGU9TAcBusjvQ.png" /><figcaption>Data Grid 2 Export to Excel</figcaption></figure><p>However, Data Grid 2’s export is a basic file and the developer does not have control over the styling. If a Custom Grid is already needed <strong>and </strong>there is a requirement to export to CSV or Excel, then an OQL engine is easier to work with.</p><p>With an Xpath query engine, it is not easy to translate the data into a file. Typically, this involves a Microflow that maps the data from the Custom Grid to a separate entity and then exports that separate entity with the <a href="https://marketplace.mendix.com/link/component/726">Excel Exporter module</a>.</p><p>With an OQL query engine, the OQL module already has a Java action for exporting to CSV. This Java action is fairly easy to set up and use.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/356/1*IEkyrppq6tKEOdlK6Y6rvg.png" /><figcaption>ExportOQLtoCSV Java action</figcaption></figure><p>For exporting an OQL query engine to Excel, I made an <a href="https://marketplace.mendix.com/link/component/232764">OQL to Excel module</a>. My module is more complex to set up than the ExportOQLToCSV Java action, but it also has significantly more features.</p><p><strong>(3) Calculated Searchable/sortable columns.</strong></p><p>For example, an attribute called SalaryBreak is calculated as (Employee/Salary * Employee/BillableTarget)/100.</p><pre>SELECT Employee/MxID AS MxID, Employee/FullName AS FullName, <br>Employee/HireDate AS HireDate, Employee/Salary AS Salary, <br>Employee/Terminated AS Terminated, Department/Name AS Department, <br>Supervisor/MxID AS SupervisorMxID, Supervisor/FullName AS SupervisorFullName,<br>(Employee/Salary * Employee/BillableTarget):100 AS SalaryBreak</pre><p>If the user does NOT need to search or sort this attribute, do this calculation with an expression on the page instead of using an OQL engine.</p><p>This could also be accomplished with ‘calculated attributes’ in the database. However, calculated perform poorly across the board and should seldom be used.</p><h3>Conclusion</h3><p>Now with an understanding of when to use Custom Grids, it is time to start coding. I have a tutorial for implementing either module’s query engine:</p><ul><li><a href="https://medium.com/@benjaminsgriggs/how-to-create-a-custom-grid-in-mendix-485eed39bc97">How to Create a Custom Grid in Mendix with Xpath | by Benjamin Griggs | Medium</a></li><li><a href="https://medium.com/@benjaminsgriggs/how-to-create-a-custom-grid-in-mendix-with-oql-f64306087530">How to Create a Custom Grid in Mendix with OQL| by Benjamin Griggs | Medium</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5ccb8c7dfbd4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Create a Custom Grid in Mendix with OQL]]></title>
            <link>https://medium.com/@benjaminsgriggs/how-to-create-a-custom-grid-in-mendix-with-oql-f64306087530?source=rss-f465d8fb841d------2</link>
            <guid isPermaLink="false">https://medium.com/p/f64306087530</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[mendix]]></category>
            <category><![CDATA[lessons]]></category>
            <category><![CDATA[customization]]></category>
            <category><![CDATA[oql]]></category>
            <dc:creator><![CDATA[Benjamin Griggs]]></dc:creator>
            <pubDate>Mon, 17 Feb 2025 20:33:14 GMT</pubDate>
            <atom:updated>2025-02-17T20:36:24.481Z</atom:updated>
            <content:encoded><![CDATA[<p>Ever needed to manually create a grid in Mendix with filtering, pagination, and sorting? This design pattern is what I call a “Custom Grid”. For this tutorial, I will create a query engine using OQL and display the results in the Custom Grid seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NdrEa9zmp7Ou1Z6E77kriA.png" /><figcaption>Example of an OQL Custom Grid</figcaption></figure><p>This approach provides performant data handling and is a suitable customization for complex requirements over Data Grid 2. But with this complexity comes more development time. See my article linked below to find out when a Custom Grid is the right solution.</p><p><a href="https://medium.com/@benjaminsgriggs/what-is-a-mendix-custom-grid-and-when-should-i-use-one-5ccb8c7dfbd4">What is a Mendix Custom Grid and When Should I Use One? | by Benjamin Griggs | Feb, 2025 | Medium</a></p><h3>What is OQL?</h3><p>OQL (Object Query Language) statements consist of different ‘clauses’ instructing the database what to retrieve. For this article, the clauses used are SELECT, FROM, LEFT JOIN, WHERE, and ORDER BY. The <a href="https://docs.mendix.com/refguide/oql/">official Mendix documentation</a> is a great place to start exploring what each clause can do.</p><p>Xpath in the Mendix platform gets translated into OQL and then finally SQL before being sent to the database. Watch this happen by <strong>temporarily </strong>setting the <em>ConnectionBus_Retrieve</em> log node to Trace. I emphasize temporary because setting this will negatively impact the entire application’s performance.</p><p><em>Note: the clauses’ names are written in all capital letters to make them easier to find when reading a statement.</em></p><h3>Project Download</h3><p>Project <a href="https://github.com/bsgriggs/CustomGridExample/tree/main">Download </a>(Mendix 9.24.5)</p><p>1. Download the .mpk file ending with “OQL”<br>2. Open Mendix Studio Pro version 9.24.5<br>3. Click “Open App Locally”<br>4. Navigate to where you downloaded the .mpk file and open the file<br>5. Choose where to store your app and an app name<br>6. Choose where you would like to generate the project structure and click “Okay”</p><h3>Dependencies</h3><p>Install the following modules from the Mendix Marketplace.</p><ul><li><a href="https://marketplace.mendix.com/link/component/66876">OQL</a></li><li><a href="https://marketplace.mendix.com/link/component/170/Mendix/Community-Commons">Community Commons</a></li><li><a href="https://marketplace.mendix.com/link/component/109515">Nanoflow Commons</a></li></ul><p>The following widgets are not <em>required</em> to make a Custom Grid but make the process much, much easier.</p><ul><li><a href="https://marketplace.mendix.com/link/component/120670">Advanced Pagination</a> — Renders pagination buttons in multiple, common styles.</li><li><a href="https://marketplace.mendix.com/link/component/202511">Advanced Sorting</a> — 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.</li></ul><h3>OQL Module Setup</h3><p>There is a snippet in the OQL module that is <strong>extreamly </strong>useful. I recommend creating a page for ONLY the Admin role and adding this snippet to that page.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3SdCac-XY-hDEzoEZ9ulaw.png" /></figure><p>This snippet provides an interface to type, execute, and view the results of any OQL query in this app. It is paramount for debugging syntax errors. The invalid query can copied into the text area, adjust the query until the error is resolved, and then copy the fixed query back into Studio Pro.</p><p>The snippet is also helpful to view data in general without having to create a page. This is why I recommend only giving the Admin role access, malicious users could query anything in the system.</p><h3>Domain Model</h3><p>The Custom Grid in this article will display Employee objects. The following domain model is the data structure that will be used. An employee is assigned to one Department and has one supervisor.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/446/1*5Di4vc4msv0_exGSGO9pPw.png" /><figcaption>Employee Domain Model</figcaption></figure><h4>Page Helper</h4><p>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”.</p><p>Add the following attributes to SearchEmployees:</p><figure><img alt="Base custom grid page helper" src="https://cdn-images-1.medium.com/max/434/1*GO4oyNDeNlvPh6gb1HFWcg.png" /><figcaption>Initial custom grid page helper</figcaption></figure><ul><li>PageNumber (Number) (Default: 1)</li><li>PageSize (Number) (Default: 10)</li><li>ResultTotal (Number) (Default: 0)</li><li>SortAttribute (String) (Length: Unlimited)</li><li>SortAscending (Boolean)</li><li>SearchEmployees_Session (association to System — Session)</li></ul><p>Next, add attributes and associations for all the search criteria the user can enter or select.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/757/1*bgSB5mjIRRA-XHv5Rnb6tA.png" /><figcaption>Page helper with search criteria</figcaption></figure><p>I won’t go into the details of each of these attributes and associations but here are some guidelines to consider:</p><ul><li>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)</li><li>Date and Times should <strong>NOT </strong>be localized if they are searching against non-localized fields. (i.e. HireDate is not localized so HireDate_Lower and HireDate_Upper should also not be localized)</li><li>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 user’s selection is stored in the SearchEmployees_Departments association.</li></ul><p>Lastly, be sure that the user has permission to edit the attributes of the page helper.</p><h4>OQL Result Entity</h4><p>The list returned by the OQL query is not the same entity as the FROM clause. The OQL Java action needs a <strong>non-persistent</strong> entity with the same attribute names in the SELECT clause.</p><p>For this custom grid, I’ve chosen the SELECT clause:</p><pre>SELECT Employee/MxID AS MxID, Employee/FullName AS FullName, <br>Employee/HireDate AS HireDate, Employee/Salary AS Salary, <br>Employee/Terminated AS Terminated, Department/Name AS Department, <br>Supervisor/MxID AS SupervisorMxID, Supervisor/FullName AS SupervisorFullName</pre><p>This SELECT clause populates the non-persistent entity ‘EmployeeResult’.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/162/1*jjsXHru1bLmeLeR6T6ql1Q.png" /><figcaption>Result entity</figcaption></figure><p><em>Note: each item in the SELECT clause has an alias that matches the name of the attribute in the EmployeeResult entity exactly</em></p><p>This leads to the next decision. I need a button in the Custom Grid that the user can click to edit an employee record. However since the Custom Grid contains these EmployeeResult objects, I need to get the actual Employee object to open the Employee_NewEdit page.</p><p>There are two main approaches to solving this problem, each with its pros and cons:</p><p><strong>(1) An association to the original entity:</strong></p><p>Result entities can include associations that get populated by the OQL Java action. They are populated by including the GUID of a table and an alias that matches the association’s name.</p><pre>Employee/id AS EmployeeResult_Employee</pre><p>When the Java action encounters this, it will populate EmployeeResult_Employee with a link to the actual employee object.</p><p>This is the easiest of the two approaches, but this will impact performance. When the browser receives the list of EmployeeResults, it will also have to retrieve the list of Employees associated with those results. If the goal of using OQL is to improve performance due to too many attributes/associations, it may perform worse than Xpath.</p><p><strong>(2) An attribute that uniquely identifies each record:</strong></p><p>The more performant solution is to use unique identifiers. The Employees already have the attribute MxID as a unique identifier. I only need the actual Employee object if the user clicks on a button for that employee. So, I can have the button retrieve the exact Employee by their MxID.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/702/1*Sygbqt1ZvARlwIFfsa8fFw.png" /><figcaption>Edit button example</figcaption></figure><p>For this, I have to manually define and maintain unique identifiers. This can take time to set up. Unfortunately, the Java action does not allow me to set the Employee/id GUID to a normal attribute (feature request 🤞).</p><p>In this article, I will be using the second approach. The employee already has a unique identifier with MxID, and I only need one employee at a time.</p><h3>Actions</h3><p>Create all of the following Microflows and Nanoflows in the same folder. Don’t forget to grant the user permission to execute these actions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/314/1*-p6nwNMVc1RR73cNi2wC9w.png" /><figcaption>Folder Structure</figcaption></figure><h4>ACT_EmployeeResult_Delete</h4><p>This Microflow retrieves the actual Employee object by its MxID and deletes it. It is used to delete a specific employee from the grid.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/932/1*6sj3zV_8EeQe9h4QpKVZwQ.png" /><figcaption>Delete action example</figcaption></figure><p>Take note that the flow includes a parameter and refresh in client for SearchEmployees. This allows the flow to refresh the Custom Grid after the delete occurs.</p><h4>ACT_EmployeeResult_Edit</h4><p>This Microflow retrieves the actual Employee object by its MxID and opens a NewEdit page with it. It is used to edit a specific employee from the grid.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/702/1*Sygbqt1ZvARlwIFfsa8fFw.png" /><figcaption>Edit button example</figcaption></figure><p>Depending on the requirements, the Microflow may need to do more work. Because the Custom Grid shows EmployeeResult objects, editing an Employee will NOT refresh the grid. To get around this, the save button on Employee_NewEdit must refresh the SearchEmployees object. <em>An example is in the project download’s ‘Edit Employee from Grid’ folder.</em></p><h4>ACT_SearchEmployees_Refresh</h4><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/1*7_TtS04609aSfLAGNGy2LA.png" /><figcaption>ACT_SearchEmployees_Refresh</figcaption></figure><h4>ACT_SearchEmployees_Reset</h4><p>This Nanoflow manually changes all the SearchEmployee’s attributes and associations back to their default values and then refreshes the grid.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*_JlDtYfM8ELqyI28hFkINw.png" /><figcaption>ACT_SearchEmployees_Reset</figcaption></figure><h4>ACT_SearchEmployees_Search</h4><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/782/1*IHWismBqtF8pPt0ynYTpfQ.png" /><figcaption>ACT_SearchEmployees_Search</figcaption></figure><h4>DS_Employees_bySearchEmployees</h4><p>This flow is the root Microflow responsible for retrieving the EmployeeResult objects.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HDuFquLzAce5Aorh6Zm7yQ.png" /></figure><p>The first sub-microflow call will be detailed later. It accepts a SearchEmployees object and returns an OQL statement as a string.</p><p>The second action performs the retrieve from the database. Pass in the OQL string. Define which entity type to fill and return.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/644/1*5KucYjzqXB-2XGqGoihcUw.png" /><figcaption>Execute the OQL Statement</figcaption></figure><p>Set the amount as the page size and calculate the offset using the page number and size<strong>:</strong> <em>($SearchEmployees/PageNumber -1) * $SearchEmployese/PageSize</em></p><p>The third action counts the total number of objects in the database that meet the criteria.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/651/1*frzwm-UW5ohoGxQEnXpxGg.png" /><figcaption>Count the number of records that meet the criteria</figcaption></figure><p>Lastly, set the SearchEmployees object’s ResultTotal to the Count. This is important so the Advanced Pagination widget can calculate the number of pages. Don’t forget to return the EmployeeResultList.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/750/1*Y-8LcYpgskWUKcvIrmTdTA.png" /></figure><h4>DS_SearchEmployees</h4><p>This Microflow retrieves the list of SearchEmployee objects from the <strong>currentSession</strong> by association. It is responsible for saving the search criteria if the user leaves the page and comes back.</p><figure><img alt="DS_SearchEmployees" src="https://cdn-images-1.medium.com/max/778/1*ErLscHjzlJ8ohid-6r625A.png" /><figcaption>DS_SearchEmployees</figcaption></figure><p>If there are no SearchEmployee objects found, then the flow will create a new SearchEmployee object and <strong>associate it to the</strong> <strong>currentSession</strong>. If there is a SearchEmployee object already, then the flow will return the first object in the list.</p><h4>SUB_OQL_bySearchEmployees</h4><p>Just create the OQL statement 🙂. For readability and reusability, I like to split each clause into their own variables and then add them into a statement variable at the end.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K8dAzivIUdFVFDg6cN1sow.png" /><figcaption>Create the OQL statement string</figcaption></figure><p>This also makes it easier to customize a particular part of the query in the future. For example, I may improve the performance of this query by only including a JOIN under certain conditions.</p><p>Without sorting or any filters, the generated OQL looks like:</p><pre>SELECT Employee/MxID AS MxID, Employee/FullName AS FullName, <br>Employee/HireDate AS HireDate, Employee/Salary AS Salary, <br>Employee/Terminated AS Terminated, Department/Name AS Department, <br>Supervisor/MxID AS SupervisorMxID, Supervisor/FullName AS SupervisorFullName<br>FROM CustomGridExample.Employee AS Employee<br>LEFT JOIN Employee/CustomGridExample.Employee_Department/CustomGridExample.Department AS Department<br>LEFT JOIN Employee/CustomGridExample.Employee_Supervisor/CustomGridExample.Employee AS Supervisor<br><br>ORDER BY Employee/MxID ASC</pre><p>The SELECT, FROM, and JOINS variables always return the above parts.</p><p>If a search criteria is selected, the WHERE clause would be in the gap between the last LEFT JOIN and ORDER BY. It is created by the sub-microflow SUB_OQL_bySearchEmployees_WHERE.</p><p>The ORDER BY defines how the data is sorted. If the SortAttribute has a value, the user has selected a column to sort in the Custom Grid. Then, I set the ORDER BY using that SortAttribute and the direction based on SortAscending.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/644/1*-KVTVeyZumALxtjM3yi6XQ.png" /></figure><p>The bottom path is the default sorting. It sets “ORDER BY Employee/MxID ASC”. This isn’t necessary but improves the user experience.</p><p>Next, the flow needs to put all the clauses together. In OQL, the SELECT and FROM clauses are required. The remaining three clauses check if they have a value before adding to the statement.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/645/1*PRD8QryvhSNZyepyR-4ILg.png" /><figcaption>Put the statement together</figcaption></figure><p>Lastly, there is a log message activity. This should be set to Debug in a production app to ensure the log file isn’t overrun with queries.</p><h4>SUB_OQL_bySearchEmployees_WHERE</h4><p>This flow maps all selected filters to the language the database understands, a WHERE clause.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/875/1*q3pKYMqNyQg6hC14KPHEug.png" /></figure><p>I will come back to this microflow to add filters later. For now, just create a WHERE variable set to ‘’ and a change variable with:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/653/1*TTL-N9n697fvbZZ3-FtYZQ.png" /></figure><p>When I do add filters, they will all end with “ and ”. This expression checks if any filters were applied and then removes the final “ and ”. It also makes sure to return empty if no filters are applied. I prefer this method of creating the string because it makes the individual filters simpler.</p><h3>Mendix Page</h3><p>Below is an example of a Custom Grid layout.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TC9TdraW0RlSa-hdJ-vz7A.png" /><figcaption>Page Layout</figcaption></figure><ul><li>The page-wide data view calls DS_SearchEmployees</li><li>The list view calls DS_Employees_bySearchEmployees</li><li>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 with a button instead of an OnChange on each input.</li></ul><h4>Advanced Pagination</h4><p>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 properties.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/588/1*pn1iZ8OWe7fWIxVj3mtLvw.png" /><figcaption>Advanced Pagination Settings</figcaption></figure><p>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 <a href="https://github.com/bsgriggs/mendix-advanced-pagination/blob/master/docs/Customization.md">GitHub ReadMe</a>.</p><p><em>Note: only ONE Advanced Pagination per page should have Auto Correct enabled</em></p><h4>Advanced Sorting</h4><p>This widget is also made to help make Custom Grids. Add the widget for every header in the grid and set the properties:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/579/1*H-3j8R98FG-4BhRJktjM1Q.png" /><figcaption>Advanced Sorting settings for the Full Name column</figcaption></figure><p>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 <a href="https://github.com/bsgriggs/mendix-advanced-sorting#readme">GitHub ReadMe</a>.</p><p>The widget’s attribute name should be the exact database name of the attribute but ONLY if it is the same entity as the FROM clause.</p><p>If it is not the same entity as the FROM clause, then it must use the alias from the SELECT or JOIN clauses:</p><ul><li>{Alias from SELECT clause}</li><li>{Alias from JOIN clause}/{AttributeName}</li></ul><p>Associations can also use the full database path starting from the entity in the FROM clause but using the aliases performs <em>slightly </em>better:</p><ul><li>{ModuleName}.{AssociationName}/{ModuleName}.{EntityName}/{AttributeName}</li></ul><p>For example, the Department column looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/784/1*BMq6Ls-ZunzU7szvu3q0lQ.png" /></figure><p>The project with a Custom Grid should be able to run now. The objects should show on the Custom Grid page, the headers should be sortable, and the pagination buttons should work.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*mY_5kiPsGIZ-D9aFeP2O3w.gif" /><figcaption>Demo of pagination and sorting</figcaption></figure><h3>Building the WHERE Clause</h3><p>Reopen the SUB_OQL_bySearchEmployees_WHERE sub-microflow.</p><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/655/1*jPzTDG1p9Unzh1MJnIKa4A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/658/1*nETER03s-H4x2RvtcZRlnw.png" /><figcaption>Check that a search criteria is populated before applying a filter</figcaption></figure><p>For filters where the user can type in a literal string, ALWAYS do a trim() and replaceAll(). The trim() improves the user experience when they leave a space character at the beginning or end. The replaceAll() prevents a hard Java error if the user types a single quote (i.e. the user searches for last name O’ Brian).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/682/1*oB9_n47PiPHtEKISNsGP9w.png" /></figure><pre>trim(replaceAll($SearchEmployees/SearchID, &#39;&#39;&#39;&#39;,&#39;&#39;&#39;&#39;&#39;&#39;))</pre><p>Now, let’s go through how to filter each data type.</p><h4>Strings and Enumerations</h4><p>After doing the trim and replace, use an equals sign to do an exact match. Enumerations should always do an exact match.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/708/1*KzelYnO6TLPf2TwfpXDOHQ.png" /></figure><p>For contains or starts with comparisons, use the LIKE keyword with %s.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/639/1*R35QeccPdoYDBLH26G7CNA.png" /></figure><p>The percent sign is a wildcard, so the rules are:</p><ul><li>‘%wumbo%’ — anything that contains “wumbo”</li><li>‘%wumbo’ — anything that starts with “wumbo”</li><li>‘wumbo%’ — anything that ends with “wumbo”</li></ul><p>The OQL can check a list of strings using the IN keyword.</p><pre>&#39;Department/Name IN (&#39;&#39;Engineering&#39;&#39;,&#39;&#39;Accounting&#39;&#39;) and &#39;</pre><h4>Booleans</h4><p>Use an equals sign with either ‘true’ or ‘false’</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/528/1*C_f1fMmzFYjliPzROmiJ9w.png" /></figure><h4>Auto Numbers, Integers, and Decimals</h4><p>Use any of the operators: =, &lt;, &lt;=, &gt;, or &gt;=.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/538/1*U6Ufea2FW7_EOhWryACGWw.png" /></figure><h4>Date and Time</h4><p>Use the dateTimeToEpoch function to convert the date into an integer then compare it with the operators: =, &lt;, &lt;=, &gt;, or &gt;=.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*QuRKSiQ6KubFokqJD5Walw.png" /></figure><p>Mendix Tokens like [%CurrentDateTime%] still work in OQL!</p><pre>$WHERE + <br>&#39;Employee/HireDate &gt;= &#39;+dateTimeToEpoch($SearchEmployees/HireDate_Lower)+&#39; and &#39; +<br>&#39;Employee/HireDate &lt; &#39;&#39;[%CurrentDateTime%]&#39;&#39; and &#39;</pre><h4>Reference by ID</h4><ol><li>Retrieve the object <strong>by association</strong>. Do not retrieve by database as the object should already be in memory.</li><li>Pass the object from step 1 into the getGUID Java action from the Community Commons module.</li><li>Use an equal sign to compare the GUID with the alias from the JOIN clause’s ID.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/750/1*CFxR1gpYFq8KlCqudjItHg.png" /></figure><h4>Reference by Attribute</h4><p>Use the alias from the JOIN clause in combination with any of the above data types.</p><pre>$WHERE + <br>&#39;Department/Name = &#39;&#39;&#39;+trim($SearchEmployees/FuzzySearch)+&#39;&#39;&#39; and &#39;</pre><p><em>Note: retrieving a reference by ID is always computationally faster than retrieving by an association’s attribute. Use Reference by ID where possible.</em></p><h4>Reference Set</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E81G0-5MafUY1wrRghGV9g.png" /></figure><p><strong>(1) </strong>Retrieve the list of selected objects by association.</p><p><strong>(2) </strong>Create a string variable that defaults to ‘’</p><p><strong>(3)</strong> 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.</p><p><strong>(4) </strong>Change the string variable to create a comma-separated list of GUIDs (i.e. 123,234 etc)</p><pre>if $SupervisorGUIDS = &#39;&#39;<br>   then toString($Supervisor_GUID)<br>else $SupervisorGUIDS + &#39;,&#39; + $Supervisor_GUID</pre><p><strong>(5)</strong> Change the WHERE variable to use the IN keyword with the list of GUIDs from step 2</p><pre>$WHERE + <br>&#39;Supervisor/id IN (&#39;+$SupervisorGUIDS+&#39;) and &#39;</pre><p><em>Note: this method also works with attributes. The IN keyword can contain a list of strings, numbers, etc.</em></p><h3>Conclusion</h3><p>Now with all the filters added, the Custom Grid is fully operational! From this point, the search criteria, Advanced Pagination (<a href="https://github.com/bsgriggs/mendix-advanced-pagination/blob/master/docs/Customization.md">GitHub ReadMe</a>), and Advanced Sorting (<a href="https://github.com/bsgriggs/mendix-advanced-sorting#readme">GitHub ReadMe</a>) widgets can be further customized.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*ckZhHxWI0ghvOWOYgIog3Q.gif" /></figure><h3>Extra Tips &amp; Next Steps</h3><h4>Bonus Features</h4><p>Once the query engine is complete, it can be easily reused for other features. Here are just a few ideas to get the juices flowing:</p><ul><li>Export to CSV — ExportOQLToCSV Java action inside the OQL module</li><li>Export to Excel — my <a href="https://marketplace.mendix.com/link/component/232764">OQL to Excel module</a></li><li>Dashboards, Charts, and KPIs — reuse the WHERE generation with different SELECT and GROUP BY clauses</li></ul><p><em>Examples for exporting to CSV, Charts, and KPIs are available in the project download</em></p><h4>Entity Access &amp; Sub-Queries</h4><p>As mentioned, the OQL module does NOT support Entity Access Rules. To add them, I have to add filters to my WHERE clauses manually.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f64306087530" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Take Mendix Excel Reporting to the Next Level]]></title>
            <link>https://medium.com/@benjaminsgriggs/take-mendix-excel-reporting-to-the-next-level-d9102feb43c6?source=rss-f465d8fb841d------2</link>
            <guid isPermaLink="false">https://medium.com/p/d9102feb43c6</guid>
            <category><![CDATA[mendix]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[reporting]]></category>
            <dc:creator><![CDATA[Benjamin Griggs]]></dc:creator>
            <pubDate>Wed, 27 Nov 2024 20:56:25 GMT</pubDate>
            <atom:updated>2024-11-27T21:29:27.529Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Example of reporting with the OQL to Excel module" src="https://cdn-images-1.medium.com/max/1024/1*6tax97AF0VgGC_CHxHjG6g.png" /><figcaption>Example of reporting with the OQL to Excel module</figcaption></figure><p>Ever needed to make highly stylized, performant reports in Mendix? I’ve had requests for files that structurally change dynamically in the runtime, files that respond to on-screen filters, and even the ability for the user to select which columns to export. I wrote the <a href="https://marketplace.mendix.com/link/component/232764">OQL to Excel module</a> to provide Excel files with these features and many more.</p><p><em>Note: all the data in the screenshots of this article is fake data from </em><a href="https://www.mockaroo.com/"><em>Mockaroo</em></a></p><h3>Table of Contents</h3><ul><li>Why I Wrote the OQL to Excel Module</li><li>Features that Excel Exporter Does Not Have</li><li>OQL to Excel is NOT Always the Solution</li><li>Why use OQL?</li><li>Why Not Create my Own Java Action per Report?</li><li>A “Basic” Example</li><li>Conclusion</li><li>Next Steps</li></ul><h3>Why I Wrote the OQL to Excel Module</h3><figure><img alt="title image" src="https://cdn-images-1.medium.com/max/608/1*6eMZ74Jbl5kIDrKqGgvNZw.png" /><figcaption>Module Card</figcaption></figure><p>Let&#39;s begin with a story.</p><p>I originally developed this module for a project that had <em>already</em> implemented OQL for the <a href="https://medium.com/@benjaminsgriggs/how-to-create-a-custom-grid-in-mendix-485eed39bc97">Custom Grid</a> design pattern. This project had a grid with 30+ different search criteria. This app is confidential but see the image below and imagine there is more search criteria.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bzPlQ1Lcs_PUi6627bbFlQ.png" /><figcaption>Custom Grid Example</figcaption></figure><p>The user was able to export the results of that Custom Grid to CSV and select which columns appeared in the file. For this, we implemented the powerful <a href="https://marketplace.mendix.com/link/component/66876">OQL to CSV module</a>. <em>Then later,</em> we were tasked with writing polished and formatted Excel reports that also needed to consider all the search criteria. I realized then I could reuse the OQL generation logic and only needed a module to create Excel files from those OQL statements.</p><h4>MxModel Reflection can be Cumbersome</h4><p>When developing this new module, I wanted to have the developer make the ‘templates’ inside of Microflows instead of committing templates to the database and retrieving them later. The Excel Exporter module has MxModel Reflection and templating screens so end-users can create templates directly in the app. No developer is required. But I’ve never had a project where the end-user <em>could</em> create their own templates. It is challenging for them to know how to create a template without access to the domain model.</p><p>With Excel Exporter and MxModel Reflection, it is difficult to make sure all templates are up to date in every environment. You have to re-run MxModel Reflection, update your template, and re-test everything. There have been numerous times when I’ve deployed a change but forgot to update the templates.</p><p>With my module, MxModel Reflection is NOT needed. If the export works in your local, you can be fairly confident it will work in all the environments right after deploying. This also makes it much easier to create a ‘template’ at the click of a button. For example, a user clicks on a button to generate the Employee Salary report, then the system creates an Employee Salary template based on that particular user’s search criteria and settings.</p><p>However, the lack of MxModel Reflection means that any changes to how the Excel file is generated must require a developer to make a code change. Personally, I prefer this.</p><h3>Features that Excel Exporter Does Not Have</h3><p>When building my module, I wanted to add features that I needed in the past but are not available in Excel Exporter.</p><h4>Excel Formulas</h4><p>The biggest of these features is Excel formulas. I’ve had many projects where the users needed a column that was calculated by other columns. Usually the math is simple, but it becomes cumbersome to implement in a clean, performant manner. With formulas, I can depend on Excel to perform the calculation. Because my module supports formulas, any aggregates configured in the module also use formulas allowing them to update live.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Qo8-Ygdr2hrnfxLTOhWU-A.png" /><figcaption>Excel formula example</figcaption></figure><h4>Pivot Tables</h4><p>It also supports pivot tables. Pivot tables provide a great way of summarizing data. Typically, they include three or more columns to see how their sums compare when grouped together. They are also wonderful for the same reasons as formulas: I don’t have to add the cells manually and they get updated when the data changes. In addition, the user can change what the table is pivoting on in the Excel sheet!</p><figure><img alt="example pivot table" src="https://cdn-images-1.medium.com/max/610/1*EJTJwNLTBgSP5C1fA57oaw.png" /><figcaption>Pivot table example</figcaption></figure><h4>Conditional Formatting</h4><p>Conditional formatting allows a style to be dynamically based on the data in a cell. For end users, it allows them to easily see data that requires attention. Below, the salary cell is red if the value is ≥ 100,000 and otherwise green. I like to use ‘Coral’ for red and ‘Light Green’ for green as they are easier on the eyes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*xPlPbP26YYeoqIuChGaXIA.png" /><figcaption>Conditional formatting example</figcaption></figure><h4>Chained Cells</h4><p>I had a report where there were multiple different sections of results in the same Excel sheet. To accomplish this, I added what I call ‘Chained Cells’</p><figure><img alt="Chained cells example" src="https://cdn-images-1.medium.com/max/1024/1*6tax97AF0VgGC_CHxHjG6g.png" /><figcaption>Chained cells example</figcaption></figure><p>Chained cells will run sequentially after one another. For example, the OQL statement for Business Development is associated to the OQL statement for Accounting. The Accounting section will run, skip one row, and then run the Business Development section. This way, I didn’t have to keep track of how many rows will be in each section.</p><h3>OQL to Excel is NOT Always the Solution</h3><p>OQL can be difficult to write and implement. It takes a sizable amount of effort to get valid OQL query generation working. OQL queries are also just strings, so changing entity, attribute, or association names in the domain model will not automatically update your queries.</p><p>OQL queries do NOT apply entity access. Entity access rules have to be manually written. This may or may not be a problem depending on how heavily your app uses entity access rules, but it needs to be considered when estimating the time to implement.</p><p>By implementing OQL, developers supporting the project in the future have to support OQL. For people familiar with high code, it is easier to understand as it the syntax is <em>very</em> similar to SQL. People who are not familiar with query languages may find it challenging.</p><p>Once the OQL generation is finally working, then you create a Microflow to define how to style the file. This can also take a bit of time to fine-tune how the file looks.</p><p>In summary, using the OQL to Excel module should be reserved for when one of its features is <em>absolutely</em> necessary. If your requirements can be satisfied by the Excel Exporter module, use that instead.</p><h3>Why use OQL?</h3><p><a href="https://docs.mendix.com/refguide/oql/">OQL</a> gives you incredible flexibility.</p><pre>SELECT Employee/_ID, Employee/Salary, Employee/HireDate<br>FROM OQLToExcel.Employee AS Employee<br>WHERE Employee/HireDate &gt;= &#39;[%BeginOfCurrentYearUTC%]&#39;</pre><p>You can define filters for the file by writing your own WHERE clause. Even more powerful, you can use <a href="https://docs.mendix.com/refguide/oql-subqueries/">sub-queries</a> to narrow down the results. Sub-queries are a must-have when writing entity access rules. They help by splitting a problem into smaller, consumable parts.</p><pre>SELECT Employee/_ID, Employee/Salary, Employee/HireDate<br>FROM OQLToExcel.Employee AS Employee<br>WHERE Employee/id IN ( <br>  SELECT E/id<br>  FROM OQLToExcel.Employee AS E<br>  WHERE {example...} ) and Employee/HireDate &gt;= &#39;[%BeginOfCurrentYearUTC%]&#39;</pre><p>Especially with reporting, OQL gives you the ability to leverage the database’s performance for aggregating data. Imagine a report where you want to show the average salary for employees in every department. A Microflow would have to iterate every department, retrieve every employee for that department, calculate the average, and then store the result somewhere to get exported later. With OQL, you can do an <a href="https://docs.mendix.com/refguide/oql-aggregation/">aggregate</a> directly in the database with the <a href="https://docs.mendix.com/refguide/oql-group-by-clause/">GROUP BY</a> and HAVING clauses.</p><pre>SELECT Department/Name AS Department, AVG(Employee/Salary) AS AverageSalary<br>FROM OQLToExcel.Employee AS Employee<br>LEFT JOIN Employee/OQLToExcel.Employee_Department/OQLToExcel.Department AS Department<br>WHERE Employee/HireDate &gt;= &#39;[%BeginOfCurrentYearUTC%]&#39;<br>GROUP BY Department/Name</pre><figure><img alt="Result of Group By query above" src="https://cdn-images-1.medium.com/max/506/1*3k6-XLMmhIpTompOfhkalg.png" /><figcaption>Result of Group By query above</figcaption></figure><p>OQL allows you to do basic data manipulation by using <a href="https://docs.mendix.com/refguide/oql-functions/">OQL functions</a>. These kinds of data manipulations <em>perform</em> <em>significantly</em> <em>faster</em> than converting data with Microflows or storing additional persistent data, because they are executed by the database itself. I won’t go into each function but here is a list of a few examples:</p><ul><li>Remove underscores from enumeration values or substitute values with abbreviations using <a href="https://docs.mendix.com/refguide/oql-replace/">REPLACE</a></li><li>Show only the Month/Year of a date time using <a href="https://docs.mendix.com/refguide/oql-datepart/">DATEPART</a></li><li>Convert a number stored as a decimal in the database to appear as a percentage with <strong>* 100 </strong>using the * <a href="https://docs.mendix.com/refguide/oql-operators/">Operator</a></li><li>Concatenate two different string attributes into the same column using the + <a href="https://docs.mendix.com/refguide/oql-operators/">Operator</a></li></ul><pre>SELECT MxID AS ID, FullName AS Full_Name, <br>REPLACE(Gender,&#39;_&#39;,&#39; &#39;) AS Gender, <br>CASE WHEN Employee/HireDate = null THEN &#39;&#39; ELSE CAST(DATEPART(MONTH,Employee/HireDate,&#39;UTC&#39;) as String) + &#39;/&#39; + DATEPART(YEAR,Employee/HireDate,&#39;UTC&#39;) END AS Hire_Date,<br>Salary, Department/Name AS Department,<br>BillableTarget,<br>&#39;=E{r}*G{r}/100&#39; AS Salary_Break<br>FROM OQLToExcel.Employee AS Employee<br>LEFT JOIN Employee/OQLToExcel.Employee_Department/OQLToExcel.Department AS Department</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GacTJ4EjQWc90NWElukL-g.png" /><figcaption>Result from data manipulation query</figcaption></figure><p>My module also supports Excel formulas, so you can have a column that is calculated by other columns (i.e. column H in the screenshot above). You can do this by setting an OQL column to ‘=E{r}*G{r}/100’ and my module will replace the {r} with each row number in the Java action.</p><p>End-users typically prefer Excel formulas over a calculation, so they can see how a field is calculated while viewing the file.</p><h3>Why Not Create my Own Java Action per Report?</h3><p>While this is certainly a valid path for reporting, it — of course — requires a developer who is already familiar with Java. Not only to build the Java action but to maintain it in the future.</p><p>With my module, most of the functionality of the <a href="https://poi.apache.org/">Apache POI library</a> is already exposed to Mendix. So, developers who are only familiar with Mendix can still create or update complex reports.</p><p>If you’d still like to make your own Java actions, see this wonderful article by Rishabh Shadilya. <a href="https://medium.com/mendix/elevate-your-reporting-game-dynamic-excel-reports-with-oql-queries-in-mendix-31a3f6ee69f6">Elevate Your Reporting Game: Dynamic Excel Reports with OQL Queries in Mendix | by Rishabh Shandilya | Mendix Community | Medium</a></p><h3>A “Basic” Example</h3><p>This module doesn’t really have <em>“basic”</em> examples. As mentioned, this module is specifically for the use cases where you need a lot of customization and control.</p><p>The following example should be viewed more as a starting point where you would further refine. This example can also be viewed inside the module as “02. Examples” -&gt; “04. Hello World”.</p><p>We’ll be creating the following file using OQL to Excel.</p><figure><img alt="hello world file" src="https://cdn-images-1.medium.com/max/1024/1*kp0XpnY2dA11I0k15-j0IA.png" /><figcaption>Hello World file</figcaption></figure><h4>Domain Model</h4><p>Please familiarize yourself with the following domain model.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mq9IUYQJSAWP7RzBo60i5g.png" /></figure><p>This model can be overwhelming at first, but I’ll summarize each part. Think of the ExWorkSheet entity as the entry point of the data structure. <strong><em>Everything</em></strong> in the model is related to it even if indirectly.</p><figure><img alt="ExCellStyle details" src="https://cdn-images-1.medium.com/max/244/1*tTRhOn223InOPud_TgDWTQ.png" /><figcaption>ExCellStyle details</figcaption></figure><p>ExCellStyle objects define how a cell will look.<strong><em> All </em></strong>ExCellStyle objects are related directly to the ExWorkSheet object. Where the ExCellStyle is applied depends on one of the 6 associations pointing to it. This allows you to re-use an ExCellStyle across any number of cells. <em>Note: the ‘Name’ attribute is not required to use this module. It is only used in the admin pages to make it easier to see which style is used.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9ncXKKrF4A9SOXu88X8F1g.png" /></figure><p>An ExSheet has attributes for defining the default column width and freezing rows or columns. There is an attribute to automatically detect the width of each column, but I recommend against using it for larger files as it affects performance.</p><p>Each ExSheet can have ExColumnSettings related to it. These are used to customize the width of particular columns.</p><p>ExRegions can be tied to an ExSheet and their effect is determined by which association is populated. ExMergeRegion_ExSheet will merge all of those cells into a single cell. ExConditionalFormatRegion_ExSheet allows ExFormatRules to be applied. These are for applying styling based on a value (i.e. make a cell red when the value is ≥ 100,000).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/584/1*8v1-8YUqs42gLkYioIL1aw.png" /><figcaption>ExCell domain</figcaption></figure><p>ExCells have multiple different attributes ending with ‘Value’ for setting single-cell data. ExCells have the string attribute ‘OQLStatement’ that will be executed at that ExCell’s row and column position. In practice, one of the ‘Value’ attributes or the ‘OQLStatement’ attribute should be populated. Never both.</p><p>ExOQLColumnStyle objects can be related to an ExCell that has the OQLStatement attribute populated. They help fine-tune the styling for particular columns, headers, override the name of the column, and/or add aggregates.</p><p>If you’ve already downloaded the module, you can find documentation for each entity, attribute, and association by viewing each entity’s documentation tab or running the project and looking through the admin pages.</p><h4>File Entity</h4><p>You must have an entity that inherits System.FileDocument. This entity will be used to create the Excel file. Be sure that you give the appropriate user roles access to your entity, so they have permission to download the file after generation.</p><figure><img alt="File entity" src="https://cdn-images-1.medium.com/max/176/1*0n4JCEomHj0nLutdpw5iIw.png" /><figcaption>File entity</figcaption></figure><h4>The Three Microflows Structure</h4><p>I recommend creating three key Microflows for generating a file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/414/1*YybjOR0bg_N-q-hX3UBY-Q.png" /><figcaption>Folder structure</figcaption></figure><ul><li>One Microflow as an entry point to the report (ACT_HelloWorldExample_Download)</li><li>One Sub-Microflow to create an ExWorkSheet object and all of its children. (SUB_HelloWorldExample)</li><li>One Sub-Microflow that generates the OQL query. (SUB_EmployeeLookup_OQL)</li></ul><p>This structure allows you to reuse each part for other processes or making other files. Each of these Sub-Microflows can have their own Sub-Microflows for additional re-use.</p><h4>Entry Point</h4><p>This Microflow is what the user calls from the UI using a button. The EmployeeLookup object holds any filters the user selected.</p><figure><img alt="Entry point for generating a file" src="https://cdn-images-1.medium.com/max/1024/1*JkjVirgXx6C33bCXUeXR_g.png" /><figcaption>Entry point for generating a file</figcaption></figure><p>The SUB_HelloWorldExample Sub-Microflow will be explained further in the next section. In essence, it accepts an EmployeeLookup object and a FileName string, creates a ExWorkSheet object and all of its children, then returns that new ExWorkSheet object.</p><p>The AdvancedOQLToExcel Java action then accepts that new ExWorkSheet object and iterates through all of its children to create a file. The file object will be whatever type is selected as the ‘File Object Type’.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/644/1*baodoMWcAWEyhhQZYjF0_w.png" /><figcaption>AdvancedOQLToExcel Java Action</figcaption></figure><p>Then simply download the new file generated from the Java action.<em> Note: the StartTime variable and Debug log message are not required. They just log how long it took to generate a file for testing purposes.</em></p><h4>Create a ExWorkSheet and Its Children</h4><p>This Sub-Microflow is the meat and potatoes of the logic. It is a little overwhelming, so I’ll break it down into 6 parts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xqlhD4xoaW-cv35znIJfHA.png" /></figure><ol><li>Create a new ExWorkSheet object and set its FileName attribute.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/756/1*sBYc-hDQkN66MOOVha8Njw.png" /></figure><p>2. Create all the ExCellStyles needed for this Excel file.</p><p>Associate them to the ExWorkSheet. Style objects are not necessarily required. The module will assign default styling based on the data type and some constants. More details can be found in the constants section of the module.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kGWDHofUw1z8hQvrx9py5w.png" /></figure><p>Some data types should have styles that specify how to format the data. Especially decimals and integers.</p><figure><img alt="Currency data format example" src="https://cdn-images-1.medium.com/max/763/1*_sj4PqfbFIfT1wFyhQE2Bw.png" /><figcaption>Currency data format example</figcaption></figure><p>3. Create a new ExSheet object and associate it to the ExWorkSheet. The indexes start at 0. Give the sheet a name and a default column width.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/765/1*3lzp2edJWGI-xV8ztW6yRg.png" /><figcaption>Create a sheet</figcaption></figure><p>4. Create any cells you want as a header.</p><p>For this example, I just wanted a title and the date the report was run. This allows users to easily see if data was changed after the report was created. Again, note that indexes start at 0.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/743/1*v8A55QofVVHiKQclJ7PR1w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/762/1*5RAG_TyvE9p0OPbLyuZS9g.png" /><figcaption>Create static cells</figcaption></figure><p>Each ExCell has an association to an ExCellStyle as well as the ExSheet.</p><p>5. Generate an OQL statement and create an ExCell to execute that OQL statement at a specific position.</p><figure><img alt="OQL cell example" src="https://cdn-images-1.medium.com/max/765/1*jikKqM9oSx9lbVVnVsnjXg.png" /><figcaption>OQL cell example</figcaption></figure><p>Note that I’m setting the OQLHeaderStyle association here instead of the ExCellStyle association.</p><p>I append an ORDER BY to the query, because — in other examples — I want to do a COUNT(*) and COUNT(*) doesn’t work with ORDER BY. There are cleaner ways of doing this, but this is what I chose to make the other examples clearer.</p><p>I won’t dive into the specifics of creating the OQL statement in this article. Generally, I recommend having a Sub-Microflow that accepts a non-persistent helper object (in this case “EmployeeLookup”) and then returns the OQL String. Think of this as creating a ‘query engine’ where you can re-use this logic to create many queries by providing simple inputs.</p><p>I also recommend keeping the logic for each clause separate. This makes it easier to see what is happening in the flow and improves reusability. The WHERE clause is the most reusable, so keep that logic in its own Sub-Microflow.</p><figure><img alt="Example of OQL Generation" src="https://cdn-images-1.medium.com/max/1024/1*gDQ0extOMYqFq9SZ9X5QpA.png" /><figcaption>Example of OQL Generation</figcaption></figure><p>If you want to see how each of these variables works, download the module and look at the <strong>SUB_EmployeeLookup_OQL</strong> Sub-Microflow.</p><p>Just know that with no filters selected, the following OQL statement would get generated.</p><pre>SELECT MxID AS ID, FullName AS Full_Name, REPLACE(Gender,&#39;_&#39;,&#39; &#39;) AS Gender, <br>Employee/HireDate AS Hire_Date, Salary, Terminated, <br>ROUND(BillableTarget,2) AS Billable_Target, Department/Name AS Department,<br>Country/Name AS Country, Supervisor/FullName AS Supervisor, &#39;=E{r}*G{r}/100&#39; AS Salary_Break<br>FROM OQLToExcel.Employee AS Employee<br>LEFT JOIN Employee/OQLToExcel.Employee_Department/OQLToExcel.Department AS Department<br>LEFT JOIN Employee/OQLToExcel.Employee_Country/OQLToExcel.Country AS Country<br>LEFT JOIN Employee/OQLToExcel.Employee_Supervisor/OQLToExcel.Employee AS Supervisor</pre><p>6. Customize each column</p><p>In this example, I have a hard-coded SELECT clause. This means I know there will <strong>always </strong>be 11 columns. So, I have a while loop that will do 11 iterations (the Change Variable at the end of the loop adds 1 to the columnIterator variable).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/860/1*6AKIiLOdl4DEv9m-nWkUOQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/938/1*oAuY2-ITT_6PDQU6AW--8Q.png" /></figure><p>For each iteration, I create a new ExColumnSetting object associated to the ExSheet. Since the SELECT clause is hard-coded, I know that column index 0 is always going to be the Employee/MxID attribute. I can go through the 11 iterations and define the width of the columns from left to right.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/889/1*HLVm3gFgbgDsN3_fzXAT7g.png" /></figure><p>Creating ExOQLColumnStyle objects are very similar. Again, since the SELECT clause is hard-coded, I can go through each column from left to right. The ExOQLColumnStyle gets associated with the OQL ExCell and the styling of that column is set based on the columnIterator variable (i.e. I know column 4 will always be Salary, so set the Currency style).</p><p>Likewise, I’m customizing the name of the column based on the columnIterator. This isn’t <em>required</em> since the SELECT clause is already setting a name for the column with AS {name}. But the SELECT clause cannot have special characters (like spaces) or reserved words (like ‘sum’). The CustomColumnHeader attribute lets you get around this limitation.</p><h3>Conclusion</h3><p>And with that, we have a nice Excel file. You now have a starting point to start creating more complex files. Your mind may already be racing with the possibilities!</p><figure><img alt="hello world file" src="https://cdn-images-1.medium.com/max/1024/1*kp0XpnY2dA11I0k15-j0IA.png" /><figcaption>Hello World file</figcaption></figure><h3>Next Steps</h3><p>I recommend taking a look at some of the other examples provided inside the module. Each of the other examples looks more in-depth at a particular feature.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d9102feb43c6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Create a Custom Grid in Mendix]]></title>
            <link>https://medium.com/@benjaminsgriggs/how-to-create-a-custom-grid-in-mendix-485eed39bc97?source=rss-f465d8fb841d------2</link>
            <guid isPermaLink="false">https://medium.com/p/485eed39bc97</guid>
            <category><![CDATA[mendix]]></category>
            <category><![CDATA[lessons]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Benjamin Griggs]]></dc:creator>
            <pubDate>Mon, 15 Jan 2024 14:57:42 GMT</pubDate>
            <atom:updated>2025-02-17T20:38:23.450Z</atom:updated>
            <content:encoded><![CDATA[<h3>How to Create a Custom Grid in Mendix with Xpath</h3><p>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 count the number of records in each enumeration status or use custom filtering widgets like the <a href="https://marketplace.mendix.com/link/component/116917">Searchable Selector</a>. Situations like these require building out the grid manually, a design pattern I like to call a “Custom Grid”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pqnDuCySD1K6wzNiTjRQJQ.png" /><figcaption>Custom Grid example with Employees</figcaption></figure><p>Custom Grids work by creating an object that stores the grid’s metadata and a Microflow that translates that metadata into database retrieves. I like to think of it as writing your own query engine. A reusable piece of code that accepts basic inputs and can return a wide variety of data.</p><p>For this article, the ‘query engine’ will be using the Xpath module. To learn why the Xpath module is used, consider reading the article ‘<a href="https://medium.com/@benjaminsgriggs/how-to-create-efficient-dynamic-queries-in-mendix-cc7a4b51ea53">How to create Efficient, Dynamic Queries in Mendix</a>’.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*TisZLe-ZWmnfIVFuTvvkMg.png" /><figcaption>Custom Grid and Xpath module overview</figcaption></figure><h3>When to make a Custom Grid vs using Data Grid 2</h3><p>This approach provides performant data handling and is a suitable customization for complex requirements over Data Grid 2. But with this complexity comes more development time. See my article linked below to find out when a Custom Grid is the right solution.</p><p><a href="https://medium.com/@benjaminsgriggs/what-is-a-mendix-custom-grid-and-when-should-i-use-one-5ccb8c7dfbd4">What is a Mendix Custom Grid and When Should I Use One? | by Benjamin Griggs | Feb, 2025 | Medium</a></p><h3>Project Download</h3><p>Project <a href="https://github.com/bsgriggs/CustomGridExample/tree/main">Download </a>(Mendix 9.24.5)</p><p>1. Download the .mpk file ending with “Xpath”<br>2. Open Mendix Studio Pro version 9.24.5<br>3. Click “Open App Locally”<br>4. Navigate to where you downloaded the .mpk file and open the file<br>5. Choose where to store your app and an app name<br>6. Choose where you would like to generate the project structure and click “Okay”</p><h3>Dependencies</h3><p>Install the following modules from the Mendix Marketplace.</p><ul><li><a href="https://marketplace.mendix.com/link/component/120424">Xpath module</a></li><li><a href="https://marketplace.mendix.com/link/component/170/Mendix/Community-Commons">Community Commons</a></li><li><a href="https://marketplace.mendix.com/link/component/109515">Nanoflow Commons</a></li></ul><p>The following widgets are not <em>required</em> to make a Custom Grid but make the process much, much easier.</p><ul><li><a href="https://marketplace.mendix.com/link/component/120670">Advanced Pagination</a> — Renders pagination buttons in multiple, common styles.</li><li><a href="https://marketplace.mendix.com/link/component/202511">Advanced Sorting</a> — 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.</li></ul><h3>Domain Model</h3><p>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 has one supervisor.</p><figure><img alt="Employee domain model" src="https://cdn-images-1.medium.com/max/400/1*ljVV3VZtOQwcTPKlfPEnig.png" /><figcaption>Employee domain model</figcaption></figure><h4>Page Helper</h4><p>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”.</p><p>Add the following attributes to SearchEmployees:</p><figure><img alt="Base custom grid page helper" src="https://cdn-images-1.medium.com/max/434/1*GO4oyNDeNlvPh6gb1HFWcg.png" /><figcaption>Initial custom grid page helper</figcaption></figure><ul><li>PageNumber (Number) (Default: 1)</li><li>PageSize (Number) (Default: 10)</li><li>ResultTotal (Number) (Default: 0)</li><li>SortAttribute (String) (Length: Unlimited)</li><li>SortAscending (Boolean)</li><li>SearchEmployees_Session (association to System — Session)</li></ul><p>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.</p><figure><img alt="Page helper with search criteria" src="https://cdn-images-1.medium.com/max/722/1*VDYgjXpOGdDhdk9ra6MUhw.png" /><figcaption>Page helper with search criteria</figcaption></figure><p>I won’t go into the details of each of these attributes and associations but here are some guidelines to consider:</p><ul><li>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)</li><li>Date and Times should <strong>NOT </strong>be localized if they are searching against non-localized fields. (i.e. HireDate is not localized so HireDate_Lower and HireDate_Upper should also not be localized)</li><li>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.</li></ul><p>Lastly, be sure that the user has permission to edit the attributes of the page helper.</p><h3>Actions</h3><p>Create all of the following Microflows and Nanoflows in the same folder. Don’t forget to grant the user permission to execute these actions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/344/1*ZsZmY_-HHbYeH2Tf8wprxA.png" /><figcaption>Folder Structure</figcaption></figure><h4>ACT_SearchEmployees_Refresh</h4><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/1*7_TtS04609aSfLAGNGy2LA.png" /><figcaption>ACT_SearchEmployees_Refresh</figcaption></figure><h4>ACT_SearchEmployees_Reset</h4><p>This Nanoflow manually changes all the SearchEmployee’s attributes and associations back to their default values then refreshes the grid.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*_JlDtYfM8ELqyI28hFkINw.png" /><figcaption>ACT_SearchEmployees_Reset</figcaption></figure><h4>ACT_SearchEmployees_Search</h4><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/782/1*IHWismBqtF8pPt0ynYTpfQ.png" /><figcaption>ACT_SearchEmployees_Search</figcaption></figure><h4>DS_Employees_bySearchEmployees</h4><p>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:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1019/1*zNgqmCHZtaHLGmD1tTFvyw.png" /><figcaption>DS_Employees_bySearchEmployees</figcaption></figure><ul><li><strong>Offset:</strong> ($SearchEmployees/PageNumber -1) * $SearchEmployese/PageSize</li></ul><p>Next, use the “Retrieve by xpath aggregate int” Java action select the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1019/1*W6MpaZWrkV07454myfrrrA.png" /></figure><p><em>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.</em></p><p>Finally, set the SearchEmployees object’s ResultTotal to the count. Do<strong> NOT </strong>set this change object activity to refresh in client or you will create an infinite refresh loop.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ALzb5iDQ0YCTAFeKCuim_w.png" /></figure><h4>DS_SearchEmployees</h4><p>This Microflow retrieves the list of SearchEmployee objects from the <strong>currentSession</strong> by association. It is responsible for saving the search criteria if the user leaves the page and comes back.</p><figure><img alt="DS_SearchEmployees" src="https://cdn-images-1.medium.com/max/778/1*ErLscHjzlJ8ohid-6r625A.png" /><figcaption>DS_SearchEmployees</figcaption></figure><p>If there are no SearchEmployee objects found, then the flow will create a new SearchEmployee object and <strong>associate it to the</strong> <strong>currentSession</strong>. If there is a SearchEmployee object already, then the flow will return the first object in the list.</p><h4>SUB_Xpath_bySearchEmployees</h4><p>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.</p><figure><img alt="SUB_Xpath_bySearchEmployees" src="https://cdn-images-1.medium.com/max/918/1*1EEz1w2ns2cI5pX4iFlxlw.png" /><figcaption>Initial SUB_Xpath_bySearchEmployees</figcaption></figure><ul><li>The Create Object activity should create an Xpath object from the Xpath Module. Default the Query attribute to ‘’ or any Xpath statement that should always be applied.</li><li>Add a decision split for <strong>trim($SearchEmployees/SortAttribute) != ‘’</strong></li><li>If there is a SortAttribute, create a new SortMap object as you see above.</li><li>The Microflow should return the $NewXpath object.</li></ul><h3>Mendix Page</h3><p>Below is an example of a Custom Grid layout.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TC9TdraW0RlSa-hdJ-vz7A.png" /><figcaption>Page Layout</figcaption></figure><ul><li>The page-wide data view calls DS_SearchEmployees</li><li>The list view calls DS_Employees_bySearchEmployees</li><li>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 with a button instead of an OnChange on each input.</li></ul><h4>Advanced Pagination</h4><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/588/1*pn1iZ8OWe7fWIxVj3mtLvw.png" /><figcaption>Advanced Pagination Settings</figcaption></figure><p>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 <a href="https://github.com/bsgriggs/mendix-advanced-pagination/blob/master/docs/Customization.md">GitHub ReadMe</a>.</p><p><em>Note: only ONE Advanced Pagination widget per page should have Auto Correct enabled</em></p><h4>Advanced Sorting</h4><p>This widget is also made to help make Custom Grids. Add the widget to every header in your grid and set the following properties:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/579/1*H-3j8R98FG-4BhRJktjM1Q.png" /><figcaption>Advanced Sorting settings for the Full Name column</figcaption></figure><p>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 <a href="https://github.com/bsgriggs/mendix-advanced-sorting#readme">GitHub ReadMe</a>.</p><p>The attribute name should be the exact name of the attribute in the database. When navigating across associations, the format should look like this: <strong>{ModuleName}.{AssociationName}/{ModuleName}.{EntityName}/{AttributeName}</strong></p><p>For example, the Department column looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/728/1*YJhSCeEEra7r9991RaF1CQ.png" /><figcaption>Advanced Sorting settings for the Department column</figcaption></figure><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*mY_5kiPsGIZ-D9aFeP2O3w.gif" /><figcaption>Demo of pagination and sorting</figcaption></figure><p>The query string is the only thing left to build, so the filter widgets affect the result list.</p><h3>Building the Xpath String</h3><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/804/1*pyIN8w44nlEA7Z2o7OZ0BA.png" /><figcaption>Initial SUB_Xpath_bySearchEmployees</figcaption></figure><p>Some tips while building these out:</p><ul><li>Don’t forget to add the brackets</li><li>A disabled retrieve activity can be used to get the Xpath syntax. Build out a section of the query there and then copy &amp; paste the Xpath query into the change object activities</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/395/1*60S4R40mtvmvfGllhc0HcA.png" /><figcaption>Disabled retrieve activity to help get syntax</figcaption></figure><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/396/1*yKEOvJIuHFu672vsM4biWQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/441/1*ei_Bl8lQgK04HZHE_t6jqw.png" /><figcaption>Example of checking if a string and an integer is not empty</figcaption></figure><p>For filters where the user can type in a literal string, ALWAYS do a trim() and replaceAll(). The trim() improves the user experience when they leave a space character at the beginning or end. The replaceAll() prevents a hard Java error if the user types a single quote (i.e. the user searches for last name O’ Brian).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/665/1*1NyoVDYwyU9eYtqL9E1vzQ.png" /><figcaption>String handling example</figcaption></figure><pre>trim(replaceAll($SearchEmployees/SearchID, &#39;&#39;&#39;&#39;,&#39;&#39;&#39;&#39;&#39;&#39;))</pre><h4>Strings and Enumerations</h4><p>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)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/746/1*oSXiqJH7WReyIJ-iFtLgBA.png" /><figcaption>Fuzzy search example</figcaption></figure><pre>$NewXpath/Query + <br>&#39;[contains(FullName,&#39;&#39;&#39;+$FuzzySearchID_String+&#39;&#39;&#39;)]&#39;</pre><pre>$NewXpath/Query + <br>&#39;[FullName = &#39;&#39;&#39;+$FuzzySearchID_String+&#39;&#39;&#39;]&#39;</pre><p>For enumerations, you must use an equal sign with the enumeration’s <strong>key</strong>. For example, if you had an enumeration with the caption “In Progress” and the key “In_Progress”, the query would look like this:</p><pre>$NewXpath/Query + <br>&#39;[Status = &#39;&#39;In_Progress&#39;&#39;]&#39;</pre><p>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.</p><pre>$NewXpath/Query + <br>&#39;[Status = &#39;&#39;&#39;+toString($SearchEmployess/Gender)+&#39;&#39;&#39;]&#39;</pre><p><em>Note: enumerations do not support the contains or startsWith functions.</em></p><h4>Booleans</h4><p>To evaluate when a boolean is true, either use the name of the attribute alone or with ‘true’. For false, you must use = false.</p><pre>$NewXpath/Query + <br>&#39;[Terminated]&#39;</pre><pre>$NewXpath/Query + <br>&#39;[Terminated = true]&#39;</pre><pre>$NewXpath/Query + <br>&#39;[Terminated = false]&#39;</pre><h4>Auto Numbers, Integers, and Decimals</h4><p>Use the integer or decimal directly in the expression. Symbols =, &lt;, &lt;=, &gt;, or &gt;= can be used.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/431/1*zPOgzocgz4pL5vlxeOZfgA.png" /></figure><h4>Date and Time</h4><p>Use the dateTimeToEpoch function to get an integer representation of the date and time. Symbols =, &lt;, &lt;=, &gt;, or &gt;= can be used.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/582/1*ylRtRHGZd7zoQebDqJmJ6g.png" /></figure><p>To use Mendix tokens like CurrentDateTime, use the same syntax with 2 single quotes.</p><pre>$NewXpath/Query + <br>&#39;[HireDate &gt;= &#39;+dateTimeToEpoch($SearchEmployees/HireDate_Lower)+&#39;]&#39; +<br>&#39;[HireDate &lt; &#39;&#39;[%CurrentDateTime%]&#39;&#39;]&#39;</pre><h4>Reference by ID</h4><ol><li>Retrieve the object <strong>by association</strong>. Do not retrieve by database as the object should already be in memory.</li><li>Pass the object from step 1 into the getGUID Java action from the Community Commons module.</li><li>Use an equal sign with the GUID and the association name.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/748/1*ew1EuLM10rm5m6_xzhlf9Q.png" /></figure><pre>$NewXpath/Query <br>+&#39;[CustomGridExample.Employee_Department = &#39;+$Department_GUID+&#39;]&#39;</pre><h4>Reference by Attribute</h4><p>Use the full association path to the attribute on the other object.</p><pre>$NewXpath/Query + <br>&#39;[CustomGridExample.Employee_Department/CustomGridExample.Department/Name = &#39;&#39;&#39;+trim($SearchEmployees/FuzzySearch)+&#39;&#39;&#39;]&#39;</pre><p><em>Note: retrieving a reference by ID is always computationally faster than retrieving by an association’s attribute. Use Reference by ID where possible.</em></p><h4>Reference Set</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NAYWBy-RD4ouZ7YwfEb00w.png" /></figure><p><strong>(1) </strong>Retrieve the list of selected objects by association.</p><p><strong>(2) </strong>Create a string variable with the default value being the navigation part of the query.</p><pre>&#39;[CustomGridExample.Employee_Supervisor/CustomGridExample.Employee[&#39;</pre><p><em>The second open bracket is used to create a sub-query. This is the most efficient way to query against a list of selected objects.</em></p><p><strong>(3)</strong> 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.</p><p><strong>(4) </strong>Change the string variable and append it with the GUID and an ‘ or ’ at the end.</p><pre>$SupervisorXpath + &#39;id = &#39;+$Supervisor_GUID+&#39; or &#39;</pre><p><strong>(5) </strong>Use the substring function to remove the last ‘ or ’ and add the closing brackets</p><pre>$NewXpath/Query <br>+substring($SupervisorXpath, 0, length($SupervisorXpath) - 4) + &#39;]]&#39;</pre><h3>Conclusion</h3><p>Now with all the filters added, the Custom Grid is fully operational! From this point, the search criteria, Advanced Pagination (<a href="https://github.com/bsgriggs/mendix-advanced-pagination/blob/master/docs/Customization.md">GitHub ReadMe</a>), and Advanced Sorting (<a href="https://github.com/bsgriggs/mendix-advanced-sorting#readme">GitHub ReadMe</a>) widgets can be further customized.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*ckZhHxWI0ghvOWOYgIog3Q.gif" /></figure><h3>Extra Tips &amp; Next Steps</h3><ul><li>Domain Model Access Rules —To apply any existing access rules, open the properties for the list view’s data source (DS_Employees_bySearchEmployees) and <strong>enable </strong>“Apply entity access”.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*ZtJEr60Xw0UIE2dd3VaQEA.png" /><figcaption>How to apply entity access</figcaption></figure><ul><li>List view selection — To easily add row selections via checkboxes or clicking the cell, see my widget <a href="https://marketplace.mendix.com/link/component/212384">List View Selection</a> made specifically for this purpose.</li><li>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.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/838/1*TJOMRqGtI5_K3WHAUd-zbQ.png" /><figcaption>Example of calculating Average Salary</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=485eed39bc97" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Create Efficient, Dynamic Queries in Mendix]]></title>
            <link>https://medium.com/@benjaminsgriggs/how-to-create-efficient-dynamic-queries-in-mendix-cc7a4b51ea53?source=rss-f465d8fb841d------2</link>
            <guid isPermaLink="false">https://medium.com/p/cc7a4b51ea53</guid>
            <category><![CDATA[lessons]]></category>
            <category><![CDATA[mendix]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Benjamin Griggs]]></dc:creator>
            <pubDate>Mon, 15 Jan 2024 14:56:26 GMT</pubDate>
            <atom:updated>2024-01-16T19:06:04.367Z</atom:updated>
            <content:encoded><![CDATA[<p>Ever had a Microflow with a lot of objects running slowly? Ever needed to make a Microflow that splits into many possible retrieves based on the parameters? Ever needed a query that changes based on the user’s selections or system settings? The <a href="https://marketplace.mendix.com/link/component/120424">Xpath Module</a> provides the tools that make these problems easier and performant.</p><p>The Xpath Module contains Java Actions that allows Mendix developers to write flexible queries. The Java Actions receive an object containing an Xpath string and return a list of objects or an aggregate (Sum, Count, Average, Min, or Max). Because this Xpath object is created in the runtime, it’s values can change each time the Microflow is executed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/628/1*9XuFkZARfbx6wPd8p8Y8Cg.png" /><figcaption>Overview Diagram of the Xpath Module</figcaption></figure><p>The module uses the same Mendix Core APIs as the Retrieve by Database activity, so the Xpath syntax remains the same and the objects flow seamlessly into the rest of the Mendix platform.</p><h3>Why use the Xpath Module</h3><p>The most common use-case for dynamic queries is what I call a “Custom Grid”. A Custom Grid is a Data Grid where the developer manually creates the logic for each component of the grid: searching, sorting, and pagination. This takes more effort but offers complete flexibility of the design. Including the ability to use any widgets and shows aggregates for the data live in the grid for a tuned user experience.</p><p>As an example, the following is a Custom Grid showing a list of employees including some Pluggable Widgets for search criteria with the average salary, count of active employees, sorting on any column, and pagination to keep the performance quick.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CYyoYQqiwvoODGg2GVLjYw.png" /><figcaption>Custom Grid Example</figcaption></figure><p>This <em>can </em>be built using Out-of-the-Box components, but it is difficult to do so efficiently. Mendix developers typically implement one of two of the following approaches to meet these requirements.</p><h4>Out-of-the-Box Approach 1: List Manipulation</h4><p>The list view’s data source Microflow retrieves the full list of data then uses list operations to change the list. In the example image above, the user selected a ‘Hire Date ≥’. The Microflow to fill that list would filter all employees hired before that date and remove them from the list as seen in the image below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/847/1*YCk3VkbY9kRl2S0HLOlIEQ.png" /><figcaption>Microflow using list operations to filter</figcaption></figure><p>This Microflow is very inefficient, because the entire list of data must be retrieved at the start. As the number of records in the database becomes larger, this process increasingly decreases performance. Ideally, the process would have a maximum number of records it can retrieve at a time (i.e. pagination) and retrieve the desired list immediately.</p><h4>Out-of-the-Box Approach 2: Decision Splits</h4><p>The Microflow would use splits for each possible combination of search criteria. For example, the user has 3 switches they can enable/disable in any combination.</p><figure><img alt="Vanilla Mendix approach for the combinations for 3 booleans" src="https://cdn-images-1.medium.com/max/378/1*khbBLhlJH0jioini_xsgUw.png" /><figcaption>Combination of 3 search criteria</figcaption></figure><p>This is 2^n (2³ = 8) retrieves the developer had to setup to handle each possible filter. If the developer needs to add just one more search criteria, the Microflow would need 2⁴ = 16 different retrieves setup.</p><p>If the developer needs to add efficient, dynamic sorting, each retrieve above would need additional splits for each possible sort attribute. For the path where all 3 Booleans are false, it would then split into: Name ascending, Name descending, Date ascending, Date descending etc. (<em>Note: Sort list operations cannot be used after the retrieves in this case because it could no longer be paginated.)</em></p><p>This method may give the best performance but takes a long time to setup and is difficult to maintain/expand. A better solution needs to solve the following problems:</p><ol><li>Get the data with a minimum amount of database calls and no list manipulation.</li><li>The list is paginated, so an un-capped number of objects does not cause performance issues.</li><li>Adding additional search criteria shouldn’t create exponentially more work.</li></ol><h4>Xpath Module Approach</h4><p>Building a query with a combination of three Booleans with the Xpath module would look something like the following.</p><figure><img alt="Xpath module approach for the combination of 3 booleans" src="https://cdn-images-1.medium.com/max/1024/1*e_DeN8BYxYF29eYcg1pQIA.png" /><figcaption>Xpath module approach for the combination of three Booleans</figcaption></figure><p>This may not seem much easier at first but adding new search criteria to this flow is only one more decision split. The Microflow builds the string live based on the current state of the Booleans for the user.</p><p>Inside the Java Action, the developer selects which entity to retrieve, passes the object with the Xpath string, and optionally enters pagination details.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/660/1*mj6Sd7uUkxeDNb6jrXqw5w.png" /><figcaption>Retrieve by Xpath Java Action</figcaption></figure><p>To implement sorting, the developer creates a SortMap object associated to the Xpath object with the attribute and direction to sort the list. This approach allows the sorting to also be dynamically set.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/713/1*T-qvCiUZZZi35fQVcXkbng.png" /><figcaption>Sorting example</figcaption></figure><p>This solves the previously three mentioned problems by:</p><ol><li>Performing the least amount of data base retrieves and the list is directly returned without being manipulated.</li><li>The list has a maximum amount, so the memory used by the Microflow is limited.</li><li>Adding additional search criteria is simple. The developer only needs to add one attribute to store the value to search, a widget to the page to set that value, and one more decision split to the Microflow.</li></ol><h3>Other uses for the Xpath Module</h3><p>The Xpath Module can be used for more than Custom Grids. Since it returns a list of objects no different than a normal retrieve, it can be applicable in many places. Here are a few situations I’ve used it in the past:</p><ul><li>Batch processes with selectable criteria (i.e. an Admin sends bulk emails to a list of users).</li><li>Simplifying retrieves heavily built around business processes</li><li>Creating dynamic reports where the user selects some options then exports the results</li></ul><h3>When to use the Xpath Module</h3><p>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:</p><p>Use Data Grid or Data Grid 2:</p><ul><li>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).</li><li>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.</li><li>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).</li><li>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.</li></ul><p>Use Xpath Module:</p><ul><li>Search criteria is not supported (i.e. a searchable dropdown or a sliding bar to search years of cars).</li><li>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).</li><li>A Microflow is performing slowly due to a large retrieve followed by loops and/or list operations that remove items from the list.</li><li>Reports where the user can select some options that filter out some results in the report (i.e. export a list of employees to Excel where the user can pick which Department(s) to export).</li></ul><h3>Conclusion</h3><p>The Xpath module is a powerful tool that Mendix developers can be used to improve the performance and user experience of their applications. In some situations, it can even save development time. It uses the same core APIs as the Mendix platform allowing it to flow seamlessly into any application.</p><h3>Next Steps</h3><p>Ready to learn how to implement the Xpath module? See the article ‘<a href="https://medium.com/@benjaminsgriggs/485eed39bc97">How to Create a Custom Grid in Mendix</a>’.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cc7a4b51ea53" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>