Introduction
I recently had a rare business case where the customer wanted to have a certain filter on the system view. It should filter on conditions X on current table OR conditions Y on the related table, but return both results if true. My initial thought was, that should be easy just configure it!
No, it’s not just configure it… you cannot ❌ do it. This was a headache 🤕. I wanted to avoid coding this but that wasn’t an option. So why doesn’t this work with configuration? Because you cannot include a related table within an OR operator.
So, the customer wanted a System View where table X attribute is equals A or related table Y attribute is equals B. Meaning, if both are true then both are returned, otherwise one or the other.
How I Solved It
When system views are loaded, they perform a retrieve multiple request. As you may know, there’s a trigger condition for this type of event.
When you enter the pre-plugin stage, you’ll see that the inputparameter contains QueryBase object (Context.InputParameter[‘Query’]). Not the actual records.
From this point, you are able to edit the QueryBase (which is basically a FetchExpression). In this example below, I had configured in the system view that the LastName attribute filters on XYZ. This is because I only want this Pre-Plugin to perform a certain task if this system view makes fetch request.
By knowing the fetchexpression contains my ZYX filter, I will remove that filter from the expression & pass a SharedVariable which will be used by Post-Plugin.
(Click here) You might be wondering….
…why not just fetch SavedQuery (SystemView) and go from there? The reason is that I do not want to make another request within this event type. Generally when working with Retrieve/RetrieveMultiple event types, you should try to make it as quick 💨 as possible because you want to reduce the impact it has on the system. Retrieve methods are quite common. This is also recommended by Microsoft, to make it as fast as possible.
After Pre-Plugin has executed and the query has fetched table records, you’ll see the results in the OutputParameter as shown below. The name of the key is “BusinessEntityCollection”.
From here, we can start with the filtering and returning a manipulated set of table records.
The result of these two plugins that will be displayed on the System View:
- Contacts that has LastName “Doe”.
- Contacts that has a ParentCustomerId which AccountNumber ends with “321”.
Perhaps poor example, but I hope you understand the solution.
Happy D365 CE Plugin-ing!
Van Carl Nguyen 