Have you ever had a request like this?
We want to be able to be able to dynamically update the lookup based on the data input. Advanced finds are too tricky, or our users don’t want to do that, or David in the sales team said that at one company he worked at they had this amazing CRM that could do this, and he doesn’t understand why we can’t just install more RAM to take care of it.
The Client – 2023
It’s been a common one for me (especially Dave the dick), and I have always just educated where I can and delivered the benefits of the out of the box functionality. Filtered Lookups, Advanced Lookups, setting the search to cover broad ranges of data points. Sometimes that isn’t enough, so you end up creating Business Rules to show and hide different versions of the same field or Flows to go off and set the value based on client spec.
Business rules are a pain in the arse at the best of times, and Flows aren’t “instant”, especially in the MD app where we want to see the fruits of our labour now! Save, wait, refresh, save, wait, refresh…
So what other options are there?
We can write a small piece of JavaScript that changes the lookup default view of 1 Lookup field to whatever Lookup view we want (in that table).
This is a basic piece of code; this is with static views inserted. It is an onChange event assigned to a Boolean “Change view” and when it is true set the Holiday list to an extended one and if false set it to restricted.
function setHolidayDefaultView(executionContext) {
var formContext = executionContext.getFormContext();
//Get Boolean Value
var changeview = formContext.getAttribute('dnp_changeview').getValue();
//If option is True
if(changeview == true){
// change the Lookup Default View
formContext.getControl('dnp_holiday').setDefaultView('{5738de69-e47f-ed11-81ad-00224842bb37}');
// Change the label of the Lookup column to reflect the new view
formContext.getControl('dnp_holiday').setLabel('Holiday Extended');
// alert to show we are now Looking up to an extended list
formContext.ui.setFormNotification('More lists loaded', 'INFO', 'holidaySettings');
}
// If option is False
else{
// revert to Default
formContext.getControl('dnp_holiday').setDefaultView('{47851c54-e47f-ed11-81ad-00224842bb37}');
formContext.getControl('dnp_holiday').setLabel('Holiday Restricted');
formContext.ui.clearFormNotification('holidaySettings');
}
}
Whilst this works, it’s not really viable for multi environment deployments. The View GUID is hard coded and environment specific. Sure, you can use XrmToolbox – View Transfer Tool to migrate the view each deployment but there is a better way to manage this, a way we can package the JS file as part of the solution and get the right views each time. And I’ll go through the steps now.
My use case
I have a holiday booking app. In this app I have a table of airports from all over the globe.
When I am selecting flights, I select from this list and as you can imagine, the default list is quite extensive.
Instead of adding another table as an intersect, say country, and creating a lookup from Airport > Country and creating a filtered lookup on my Flights table I wanted to have more control over what I get back, a view. With a view it’s easy to filter and sort.
What you will need.
- XrmToolbox with FetchXML Builder installed
- A place to write your code, I use Visual Studio Code
- Your filtered views created in Dataverse
- Open XRMToolbox and launch FetchXML.
- Set the entity to “savedquery”
- Attributes;
- savedqueryid
- name
- Attributes;
- Add a Filter;
- name equals {your saved view name} or
- name begins with {your table name} if you have named them in that fashion
Take a copy of your FetchXML – i went with name eq – either as FetchXML or by clicking view and selecting FetchXML JavaScript Code
Either way we will be formatting it in our JS file – exporting it as JS is one less task of wrapping each line in “”.
We only require the var FetchXML data with the condition value removed
"<fetch>", " <entity name='savedquery'>", " <attribute name='savedqueryid'/>", " <attribute name='name'/>", " <filter>", " <filter>", " <condition attribute='name' operator='eq' value=''/>", " </filter>", " </filter>", " </entity>", "</fetch>"
Now open your favourite IDE and add this code, this is the base camp for the whole function.
function getViewFetchXML(executionContext){
var formContext = executionContext.getFormContext();
// Define view names
var setView = ""; // This is our target View ID we set this in or conditions to be used in the FetchXML
// Define conditions and set our view names
// Define FetchXML this exact XML can be used as is
var viewFetchXml = "<fetch>"
+ " <entity name='savedquery'>"
+ " <attribute name='savedqueryid'/>"
+ " <attribute name='name'/>"
+ " <filter>"
+ " <filter>"
+ " <condition attribute='name' operator='eq' value='"+setView+"'/>"
+ " </filter>"
+ " </filter>"
+ " </entity>"
+ "</fetch>";
// Encode FetchXML string
viewFetchXml ="?fetchXml="+encodeURIComponent(viewFetchXml);
// Retrieve the fetched record and set the default view to the matched record
Xrm.WebApi.retrieveMultipleRecords('savedquery',viewFetchXml).then(
function success(items){
if(items.entities.length>0)
{
var viewId=items.entities[0].savedqueryid;
var destination = executionContext.getFormContext().getControl("dyn_destinationairport");
destination.setDefaultView(viewId);
}
},
function (error) {
console.log(error.message);
// handle error conditions
}
);
}
Almost there!
Get the names of the filtered views you created either from Dataverse or using the XRMToolbox tool from earlier. We want the Display Names.
Now we create the variables to store these in, to lookup into FetchXML down the line.
It will look something like this.
function getViewFetchXML(executionContext){
var formContext = executionContext.getFormContext();
// Define view names
var defaultView = "Active Airports"
var americasView = "Airports - USA";
var europeView = "Airports - Europe";
var asiaView = "Airports - Asia";
var setView = ""; // This is our target View ID we set this in or conditions to be used in the FetchXML
// Define conditions and set our view names
// Define FetchXML this exact XML can be used as is
var viewFetchXml = "<fetch>"
+ " <entity name='savedquery'>"
+ " <attribute name='savedqueryid'/>"
+ " <attribute name='name'/>"
+ " <filter>"
+ " <filter>"
+ " <condition attribute='name' operator='eq' value='"+setView+"'/>"
+ " </filter>"
+ " </filter>"
+ " </entity>"
+ "</fetch>";
// Encode FetchXML string
viewFetchXml ="?fetchXml="+encodeURIComponent(viewFetchXml);
// Retrieve the fetched record and set the default view to the matched record
Xrm.WebApi.retrieveMultipleRecords('savedquery',viewFetchXml).then(
function success(items){
if(items.entities.length>0)
{
var viewId=items.entities[0].savedqueryid;
var destination = executionContext.getFormContext().getControl("dyn_destinationairport");
destination.setDefaultView(viewId);
}
},
function (error) {
console.log(error.message);
// handle error conditions
}
);
}
Because I want to split my Airports between Europe, America, and Asia. I have created 3 separate views for these.
Now I have a choice column on my form with options Asia, America, Europe, and All. So I build in these conditions and set the view names to them like this.
My Default View is just set to the default Active Airports View in my table that contains all records.
function getViewFetchXML(executionContext){
var formContext = executionContext.getFormContext();
// Define view names
var defaultView = "Active Airports"
var americasView = "Airports - USA";
var europeView = "Airports - Europe";
var asiaView = "Airports - Asia";
var setView = ""; // This is our target View ID we set this in or conditions to be used in the FetchXML
// Define conditions and set our view names
var destinationRegion = formContext.getAttribute('dyn_destinationregion').getValue();
if(destinationRegion === 851640000){
setView = americasView
}else if(destinationRegion === 851640001){
setView = asiaView
} else if(destinationRegion === 851640002){
setView = europeView
}else{
setView = defaultView
};
// Define FetchXML this exact XML can be used as is
var viewFetchXml = "<fetch>"
+ " <entity name='savedquery'>"
+ " <attribute name='savedqueryid'/>"
+ " <attribute name='name'/>"
+ " <filter>"
+ " <filter>"
+ " <condition attribute='name' operator='eq' value='"+setView+"'/>"
+ " </filter>"
+ " </filter>"
+ " </entity>"
+ "</fetch>";
// Encode FetchXML string
viewFetchXml ="?fetchXml="+encodeURIComponent(viewFetchXml);
// Retrieve the fetched record and set the default view to the matched record
Xrm.WebApi.retrieveMultipleRecords('savedquery',viewFetchXml).then(
function success(items){
if(items.entities.length>0)
{
var viewId=items.entities[0].savedqueryid;
var destination = executionContext.getFormContext().getControl("dyn_destinationairport");
destination.setDefaultView(viewId);
}
},
function (error) {
console.log(error.message);
// handle error conditions
}
);
}
This is how it looks for me.
I added the JS functions to the onLoad event of the form and to the onChange event of Destination Region. You can add clear functions to the Lookup so that when the trigger is changed the Lookup gets cleared to avoid mismatching data.
Always looking up
This exact use case will not be for everyone, but with the base foundation you will be able to do things like.
- Define lookups based on logged in user role
- Case / record status reasons
- Calculated/Rollup column values
- Related table values such as customer location or account type
- many, many more that I haven’t listed
