Filter by Lookup with retrieveMultipleRecords JavaScript WebApi in Offline Mode

This post will explain how filtering using the lookup column is different between a normal online scenario and a mobile offline scenario in Xrm.WebApi.retrieveMultipleRecords. You will also learn about how to write a script to support both scenarios.

Before setting up the Dynamics 365 app or Power Apps model-driven app in offline mode, it is important to read through this Microsoft Docs article to understand the capabilities, tips, and limitations. Even if you understand all those capabilities and limitations mentioned in the articles, you will still encounter unexpected issues when you start testing the app in offline mode. This is one of the caveats that you should be aware of.

In the example below, there are x4 Accounts and the requirement is to query the name of the Active Account which has "Sample User A1" (with GUID 033bbb38-611c-ec11-b6e7-000d3acb20df) as the Primary Contact using JavaScript. For that query, only one row of data will be returned because there is no other Account that matches the criteria above.

For such requirements, the filter would be "_primarycontactid_value eq 033bbb38-611c-ec11-b6e7-000d3acb20df". Even thought the logical name of the Primary Contact is primarycontactid, the filter needs to use _{navigation property name}_value as mentioned in this Microsoft Docs article. (read more at this blog post by Ben Gribaudo)

Xrm.WebApi.retrieveMultipleRecords("account", 
"?$select=name,_primarycontactid_value&" + 
"$filter=_primarycontactid_value eq 033bbb38-611c-ec11-b6e7-000d3acb20df and statecode eq 0").then(
	function success(result) 
	{
		Xrm.Navigation.openAlertDialog( { text: "Count: " + result.entities.length + " | " + 
		result.entities[0]["name"] }); 
	}
);

The script above will show the alert as "Count: 1 | Test A" correctly when the script is triggered on the browser (online scenario).

But when the same script is triggered on the mobile with offline mode (with all Accounts and Contacts synced), the count is shown as 4. That means the whole $filter part is ignored and the query returns all rows in the table. After checking out the documentation for Xrm.WebApi.retrieveMultipleRecords, I realised that _<name>_value syntax option is not supported for mobile clients in offline mode. For offline mode, the single-value navigation property name (e.g. primarycontactid) should be used for both retrieving ($select) and filtering ($filter).

Solution 1

To make the script work in both scenarios, the filter syntax needs to be changed by checking if the mobile client is in offline mode or not. For that case, we can use isOffline() method from getGlobalContext.client.

let filterQuery = "$filter=_primarycontactid_value eq 033bbb38-611c-ec11-b6e7-000d3acb20df and ";
if (Xrm.Utility.getGlobalContext().client.isOffline())
{			
	filterQuery = "$filter=primarycontactid eq 033bbb38-611c-ec11-b6e7-000d3acb20df and "
}
Xrm.WebApi.retrieveMultipleRecords("account", 
"?$select=name,_primarycontactid_value&" + filterQuery
filterQuery + "statecode eq 0").then(
	function success(result) 
	{
		Xrm.Navigation.openAlertDialog( { text: "Count: " + result.entities.length + " | " + 
		result.entities[0]["name"]  + " | " + 
		result.entities[0]["_primarycontactid_value"] });
	}
);

Even though the single-value navigation property name (e.g. primarycontactid) is used for retrieving in ($select), _<name>_value syntax needs to be used for reading the value from the response (e.g. result.entities[0]["_primarycontactid_value"] ) 🤷‍♂️

.

Solution 2

To make the script work in both scenarios without using different $filter syntax, you can query using FetchXML.

let fetchXml = "<fetch>" + 
	"<entity name='account'>" + 
		"<attribute name='name'/>" + 
		"<attribute name='primarycontactid'/>" + 
		"<filter type='and'>" + 
			"<condition attribute='primarycontactid' operator='eq' value='033bbb38-611c-ec11-b6e7-000d3acb20df'/>" + 
			"<condition attribute='statecode' operator='eq' value='0'/>" + 
		"</filter>" + 
	"</entity>" + 
"</fetch>";
Xrm.WebApi.retrieveMultipleRecords("account", "?fetchXml=" + fetchXml).then(
	function success(result)
	{
		Xrm.Navigation.openAlertDialog({ text: "Count: " + result.entities.length + " | " + 
		result.entities[0]["name"]  + " | " + 
		result.entities[0]["_primarycontactid_value"] });
	}
);

The same FetchXML query will work without any issue in both online and offline scenarios.

Summary

Setting up the model-driven app in offline mode is never smooth especially when the configurations and customisations were not initially designed with the mobile offline capabilities and limitations in mind. Reserving enough time is important not only to test the app thoroughly in the offline mode and identify the issues like these but also to update the scripts and make sure the app is working well in both online and offline scenarios.