Dynamics 365 Web API get entity using alternate key value that has apostrophe/single quote in it

In Dynamics 365, you can create alternate key for an entity using one of the attributes. Entity can be retrieved using alternate key via Web API. This MSDN article gives you an example on how to do that: https://msdn.microsoft.com/en-us/library/mt607871.aspx#BKMK_UsingAltKeys

I had an interesting error today when performing a query using alternate key. I tried to get an entity record from the User entity (systemusers) using an alternate key that is configured in the User entity. The alternate key uses the internalemailaddress attribute, which contains users' email addresses.

A user's email address is firstname.O'lastname@domain.com, and as you can see, there is an apostrophe/single quote in the email address.

When performing the query via Web API to get the user record, you can construct the below Url and simply paste into the browse to see the JSON response.

In this case, the Url is: https://organization.crm6.dynamics.com/api/data/v8.2/systemusers(internalemailaddress='firstname.O'lastname@domain.com')

You will get an error response from Web API:
"Syntax error: character '@' is not valid at position 38 in '(internalemailaddress='firstname.O'lastname@domain.com')'."

This is because the Url schema for OData uses single quote to denote parameter string value, the apostrophe in the email address tells it the parameter is internalemailaddress='firstname.O', and then the rest of the value in the Url is another parameter. But there is no parameter defined for lastname@domain.com, hence the error.

In this scenario, what you need to do is to use two apostrophes in the email address to represent the single quote, so it will become 'firstname.O''lastname@domain.com'. And to be safe, you can also perform Url encode on the whole string 'firstname.O''lastname@domain.com', that will give you 'firstname.O%27%27lastname%40domain.com'. And if you use this value in the OData query in the Url, it should response with success data.

However, with Dynamics 365 Web API, it still failed after the change, but a different error:

https://organization.crm6.dynamics.com/api/data/v8.2/systemusers(internalemailaddress='firstname.O%27%27lastname%40domain.com')

And response:
"A record with the specified key values does not exist in systemuser entity"

This is very strange, because I am certain the user is in the system. So I tried to query the user using the usual filter query like below:
https://organization.crm6.dynamics.com/api/data/v8.2/systemusers?$filter=internalemailaddress eq 'firstname.O%27%27lastname%40domain.com'

And this time it responded with the user data in JSON, no error!

It seems like the value of this email address has been modified or encoded somehow in the indexing file when the alternate key was created in Dynamics 365. So the email address is stored as it should be in the systemuser table, but different in the indexing file.

At the time of writing, I still haven't figured out how to get this to work. I am going to raise a Microsoft support ticket for this, and will update this post once I have a fix or workaround from Microsoft.

This bug has been fixed in Dynamics 365 version 8.2.1.359.

Comments

Post a Comment

Popular posts from this blog

Dynamics 365 sub-grid add new and add existing

Image
In Dynamics 365, if you put a sub-grid to display related records in a parent, there is always a plus (+) sign to allow user either adding a new child record or an existing record. The default behavior when user clicks the plus (+) sign is to display a lookup box, user can then click the lookup to drop down a list of child records or click the New button at the bottom of the drop down list. However, sometimes we may want to force the plus (+) sign to open the new record form straight away without displaying the lookup drop down to users. For example, we have a parent entity called Project, which is a custom entity. And we create a second custom entity called Service Contract, which has N to 1 relationship to Project. The project form looks like below. For the purpose of the demo, we say the requirement is to always open the new service contract form when user clicks the plus (+) sign. The easiest way to do this is to change the Project lookup field in the Service Con...
Read more

Global custom action returns 204 No Content rather than the defined output parameters

I have been working on a lot of custom actions lately in Dynamics 365, version 8.0 to 9.0. I have problem updating the custom actions again and again. The custom action suppose to return some output parameters, but it kept returning 204 No Content status, and there is no content in the response. This happened as soon as I made changes to the custom action, either the parameters or the steps, save and activate the custom action. It seems like there is a bug with the UI of the workflow designer for global actions. The generated workflow XAML of the action contains a Target entity and is set to NULL and this is invalid, it shouldn't even be in the XAML file at all. The trick to work around this and get the custom action working again is simply doing the following: - Deactivate the custom action; - Change the unique name of the custom action, I simply just put random number at the end of the current name; - Press the Save button (don't need to activate); - Change the uni...
Read more