You’re offline. This is a read only version of the page.
355
I'm writing a JavaScript function which will be associated to the form of the entity "Collection".
"Collection" has a lookup field to the entity "Director" which shows all directors in the system by default.
"Collection" is also connected to "Movie", which is a related entity.
"Movie" has a lookup field to "director".
So let's say that I'm on the form of "Collection One", which the movies "Taxi Driver" and "Apocalypse now" are related to, and when I click on "Directors" I see all directors saved on my CRM.
What I want to achieve:
Filter the query which retrieves all the directors to see only those which have directed any of the books related to this collection ( "Martin Scorsese" and "Francis Ford Coppola").
What I did so far:
Share
Reportfunction filterLookup() {
var associatedMovies;
var associatedDirectors;
var targetId = Xrm.Page.getAttribute("collectionid").GetValue();
// retrieved movies associated to this collection
Xrm.WebApi.retrieveMultipleRecords("movie", "?$select=movieid, directorid,&$filter=collectionid eq" targetId).then(
function success(result) {
for (var i = 0; i < result.entities.length; i ) {
associatedMovies.push(result.entities[i]);
};
function () {
console.log("error");
}
}
);
// retrieve directors associated to each m in associatedMovies
foreach(m in associatedMovies){
Xrm.WebApi.retrieveMultipleRecords("director", "?$select=directorid, name,&$filter=directorid eq" m.getAttribute("directorid").getValue).then(
function success(result) {
for (var i = 0; i < result.entities.length; i ) {
associatedDirectors.push(result.entities[i]);
};
function () {
console.log("error");
}
}
);
}
// filter lookup
// fetchXML to retrieve director connected to the associatedMovies?
}
Can I complete the function and filter the results by using a fetchXML?
I wonder whether I really need those two variables I've been used so far (associatedMovies and associatedAuthors) or if there is a easier way to achieve the task by using a single fecthXML.
Hi,
I finally applied successfully both of the approaches that you suggested and wrote, thank you very much Jason and Leah. I couldn't find a way to make the filter work even when no movies are retrieved, anyhow it works properly when result.entities is populated.
The main issue I was experiencing in the second version was due to the fact that in the retrieveMultipleRecords I was using the lookup field parentid (collectionid ) instead than the _prefix_parentEntityid_value (_prefix_collection_value) therefore I could not compare it with the GUID (previously stored in the variable collectionID).
I'm retrieving the target GUID without deleting the curly braces (without formatting), it works as well:
var collectionid = formContext.data.entity.getId();
All responses (
Answers (
// account is the logical name for director
function filterLookup(executionContext) {
debugger;
var formContext = executionContext.getFormContext();
var collectionid = formContext.data.entity.getId();
var fetchXmlFilter = ""
"";
var fetchmovies = "";
Xrm.WebApi.online.retrieveMultipleRecords("prefix_movie", "?fetchXml=" fetchmovies).then(
function success(results) {
debugger;
for (var i = 0; i < results.entities.length; i ) {
var directorid_value = results.entities[i]["_prefix_accountid_value"];
var directorID = directorid_value.toUpperCase();
var directorID2 = "{" directorID "}";
fetchXmlFilter = "" directorID2 "";
}
fetchXmlFilter = "";
formContext.getControl("prefix_defaultmoviedirector").addPreSearch(function () {
// name (not schema name)
formContext.getControl("prefix_defaultmoviedirector").addCustomFilter(fetchXmlFilter, "account");
});
},
function (error) {
debugger;
Xrm.Navigation.openAlertDialog(error.message);
}
);
}
* * * Version 2 - retrieveMultipleRecords without fetchXML, and PreSerach event for the lookup defined outside (I'm not using the namespace for the function though)* * *
I edited some things, including the following:
I declared the var formContext outside the getCollectionsMovies function, immediately after the declaration of var fetchDirectorFilter = "".
Rather than using the _prefix_director_value in the $select I'm using the schema name of the lookup field (prefix_accountID), as on my CRM Movie and Author/account have a N:1 relationship and Author is a related entity.// account is the logical name for director
var formContext;
var fetchdirectorFilter = "";
Filtermoviesdirectors = function () {
formContext.getControl("prefix_defaultmoviedirector").addCustomFilter(fetchdirectorFilter, "account");
}
getCollectionsMovies = function (executionContext) {
formContext = executionContext.getFormContext();
var collectionid = formContext.data.entity.getId();
// prefix_accountID is the schema name of the lookup field, in movie (when I used the logical name I got errors) ->
Xrm.WebApi.retrieveMultipleRecords("prefix_movie", "?$select=prefix_accountID,_prefix_accountid_value&$filter=(_prefix_collectionid_value eq " collectionid ")").then(
function success(result) {
debugger;
if (result.entities.length > 0) {
fetchdirectorFilter = "";
for (var i = 0; i < result.entities.length; i ) {
var directorid_value = result.entities[i]["_prefix_accountid_value"];
var directorID = directorid_value.toUpperCase();
var directorID2 = "{" directorID "}";
fetchdirectorFilter = "" directorID2 "";
}
}
fetchdirectorFilter = "";
formContext.getControl("prefix_defaultmoviedirector").addPreSearch(Filtermoviesdirectors);
},
function (error) {
debugger;
Xrm.Navigation.openAlertDialog(error.message);
}
);
}
Thank you for helping me to look at the same problem from different approaches, it was really useful.
Nice glad Leah was able to answer your question!
Hello,
thank you for your reply. I'm using double quotes now.
I noted that in your code, in the retrieveMultipleRecords, you used _jcmvp_collection_value (instead than collectionid) to make the comparison with the variable collectionid which stores the id of the current entity:
var collectionID = formContext.data.entity.getId().replace("{", "").replace("}", "");
Xrm.WebApi.retrieveMultipleRecords("jcmvp_movie", "?$select=_jcmvp_director_value&$filter=(_jcmvp_collection_value eq " collectionID ")")
Does this _jcmvp_collection_value correspond to the lookup field inside the retrieved entity or is it a different one? If it's different, where can I find its name? I'm inside the solution -> Components -> Entities -> Movie --> Fields, but I can't see it.
Just to be sure: the attributes that you're passing as parameters in the retrieveMultipleRecords are the field names (not schema names), is it correct?
Hey Joel;
I'm definitely get to answering each of your questions but to your last statement it's worth noting there are some differences in your code vs mine around getting the entity ID from the form.
//My collection ID
var collectionID = formContext.data.entity.getId().replace("{", "").replace("}", "");
//Your collection ID
var collectionid = formContext.data.entity.getId().replace('{', '').replace('}', '');
I wanted to make sure I highlight that your using single quotes where I'm using double in this scenario. I did simplify your replace down to a single line as well for comparison. I'll dig in and respond to your other questions in a bit as well!
Thanks for following back up with questions.
Hi, I followed your tips, but I'm experiencing another issue, the WebApi call is not successful and this is the error message in the errorCallBack:
A binary operator with incompatible types was detected. Found operand types 'Microsoft.Dynamics.CRM.collection' and 'Edm.Guid' for operator kind 'Equal'."
I assume that it's related to the incompatibility between the collectionid inside the filter option (it's the lookup field in "Movie" related to the entity "Collection") and the collectionidFormatted previously defined, which contains the id of the current collection, but I don't know how to overcome the issue:
var collectionid = formContext.data.entity.getId();
var collectionidFormatted = collectionid.replace('{', '').replace('}', '');//remove {}
Xrm.WebApi.retrieveMultipleRecords("movie", "?$select= directorID, name&$filter=collectionID eq " collectionidFormatted)
The complete error message looks like:
code: 2147951872
errorCode: 2147951872
message: "A binary operator with incompatible types was detected. Found operand types 'Microsoft.Dynamics.CRM.collection' and 'Edm.Guid' for operator kind 'Equal'."
raw: "{"errorCode":2147951872,"message":"A binary operator with incompatible types was detected. Found operand types 'Microsoft.Dynamics.CRM.collection' and 'Edm.Guid' for operator kind 'Equal'.","code":2147951872,"title":"","raw":"{\"_errorCode\":-2147015424,\"_errorFault\":{\"_responseXml\":null,\"_errorCode\":0,\"_innerFault\":null,\"_callStack\":null,\"_responseText\":\"{\\\"error\\\":{\\\"code\\\":\\\"0x0\\\",\\\"message\\\":\\\"A binary operator with incompatible types was detected. Found operand types 'Microsoft.Dynamics.CRM.collection' and 'Edm.Guid' for operator kind 'Equal'.\\\"}}\",\"_annotations\":{},\"_messages\":[\"A binary operator with incompatible types was detected. Found operand types 'Microsoft.Dynamics.CRM.collection' and 'Edm.Guid' for operator kind 'Equal'.\"},\"_message\":\"A binary operator with incompatible types was detected. Found operand types 'Microsoft.Dynamics.CRM.collection' and 'Edm.Guid' for operator kind 'Equal'.\",\"_exception\":null,\"_innerError\":null,\"_errorSource\":0,\"_blockErrorReporting\":false,\"_stack\":null,\"_faultedRequestIndex\":-1,\"_clientRequestId\":\"5d829a44-c34f-413e-a55b-e04d32a5dde8\",\"_serverResponseId\":\"34f9bc1f-99d1-4e54-9f91-dae7f9a6fad1, 56b00625-a9a8-47a7-83ef-a7eedbdacdd6\",\"_httpStatusCode\":400,\"_retryAfter\":null,\"_date\":\"2021-02-06T21:29:22.000Z\",\"_isRequestTooLong\":false,\"message\":\"A binary operator with incompatible types was detected. Found operand types 'Microsoft.Dynamics.CRM.collectiona' and 'Edm.Guid' for operator kind 'Equal'.\",\"name\":\"Error\",\"stack\":\"No stack available.\",\"__action\":{\"source\":{\"actionType\":\"oData.retrieveMultiple.async\",\"actionStack\":\"oData.retrieveMultiple.async\",\"contextId\":\"d8c6ae26-5137-4ce5-940a-1ae44589f58c\"},\"child\":{\"actionType\":\"oData.retrieveMultiple.async\",\"actionStack\":\"oData.retrieveMultiple.async\",\"contextId\":\"d8c6ae26-5137-4ce5-940a-1ae44589f58c\"}}}"}"
title: ""
Hi, thank you all for your replies.
@Jason, thank you very much for your blog and code, I looked at it and. As I'm new to JavaScript and to debugging client-side, I have few questions:
- it's not clear to me whether the PreSearch for the lookup field is optional or required, why do I need it? Could I filter the lookup just by using addCustomFilter instead?
- I tried to test your code by using a simpler structure, but how can I handle the asynchronous call? More specifically, I wrote the following, which is based on your code, but the structure is slightly different (I declared the global variable and defined the main function and the function which is being called then):
var FetchDirectorFilter = "";
GetCollectionMovies = function (executionContext) {
debugger;
var formContext = executionContext.getFormContext();
var collectionID = formContext.data.entity.getId().replace("{", "").replace("}", "");
Xrm.WebApi.retrieveMultipleRecords("movie", "?$select=directorID&$filter=(collectionid eq " collectionID ")").then(
function success(result) {
debugger;
if (result.entities.length > 0) {
FetchDirectorFilter = "filter type='and'>< result.entities.length; i ) {
FetchDirectorFilter = "" result.entities[i].getAttribute("directorid").getValue "";
}
FetchDirectorFilter = "";
// do I need preSearch?
formContext.getControl("director").addPreSearch(filterMovieDirectors);
},
function (error) {
Xrm.Navigation.openAlertDialog(error.message);
}
);
}
filterMovieDirectors = function () {
formContext.getControl("director").addCustomFilter(FetchDirectorFilter, "director");
}
I'm debugging it runtime: once I jump outside the asynchronous WebApi call (after the last } of the GetCollectionMovies function), if I press "Resume script execution" I'm redirected to another JS file associated to the same form, in other words the block of instructions inside the callback function (lines 10-25) never runs.
Hello Joel;
I rebuilt your scenario and created the JavaScript needed for this to work. I wrote a blog at: http://www.cosmansdynamics.com/2021/01/28/addpresearch/.
The order of what needs to be done is essentially:
The javascript file I created can be found and reviewed at: https://github.com/jasoncosman/Collections/blob/master/jcmvp_collection.js.
Please take a read and post any follow-up questions or concerns and I'll be happy to answer. If I misunderstood the scenario please let me know as well. Thank you and have a great day!
Hi Joel,
you can refer following link to know how to add dynamic value in fetchXml.
And i try to use your example to test, it can achieve by using following code:
I found that two variables (associatedMovies and associatedAuthors) both don't need, and you can just retrieve movie entity to get directorID, there is no need to retrieve director entity again, which is redundant.
you can use Rest Builder tool that i mentioned above to build web api to retrieve movie entity:
Copy the code and replace hardcode with dynamic value.
function filterDirector(executionContext) {
var formContext = executionContext.getFormContext();
var collectionId = formContext.data.entity.getId().replace('{', '').replace('}', '');//current collection id
var fetchXml = ""
""
// retrieved movies associated to this collection to get Corresponding director id
Xrm.WebApi.online.retrieveMultipleRecords("new_movie", "?$select=_new_directorid_value,new_name&$filter=_new_collectionid_value eq " collectionId).then(
function success(results) {
for (var i = 0; i < results.entities.length; i ) {
var _new_directorid_value = results.entities[i]["_new_directorid_value"];//director id
var _new_directorid_value_formatted = results.entities[i]["_new_directorid_value@OData.Community.Display.V1.FormattedValue"];
var _new_directorid_value_lookuplogicalname = results.entities[i]["_new_directorid_value@Microsoft.Dynamics.CRM.lookuplogicalname"];
var new_name = results.entities[i]["new_name"];
var directotID = _new_directorid_value.toUpperCase();
var directotID2 = "{" directotID "}";//Add {} to guid and Letter capitalization
fetchXml = "" directotID2 "";
}
fetchXml = "";
formContext.getControl("new_directorid").addPreSearch(function () {
formContext.getControl("new_directorid").addCustomFilter(fetchXml);
});
},
function (error) {
Xrm.Utility.alertDialog(error.message);
}
);
}
You can copy code i provided above, just replace filed and entity logic name.
Test Result:
Only Two directors related to two movies can show in the lookup list.
Regards,
Leah Ju
Hi Leah,
thanks for replying.
How can I make movies ID dynamic inside the fetchXML?
In your fetchXML you compare the director ID to the lookup directorID in the movies I already have in the crm.
But the author ID of the movies are not "static", as some movies can be added or deleted from the crm system, but me I want that the filter is valid no matter which the books are.
Hi Joel，
As you mentioned, there are multiple movie related records, so you need add multiple director id as condition value in fetchxml, like following sample:
(1)use ‘in’ operator with single condition.
var fetchXml =
""
""
"{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}"
"{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}"
"{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}"
""
"";
Xrm.Page.getControl("lookup").addCustomFilter(fetchXml);
(2) use ‘eq’ operatoe with multiple condition.
fetchXml = " "
""
"";
Xrm.Page.getControl("lookup").addCustomFilter(fetchXml);
And as code shown, you can use addCustomFilter () function to add filter to the lookup field(director).
Finally, I have doubt about your original code(line 5),
The js run on the collection record form, right? There is no collectionid field shown in the form, you need use following code to get current record’s id:
var id = Xrm.Page.data.entity.getId();
var id = id.replace('{', '').replace('}', '');//remove {}
For more detailed information, you can refer following link:
And you can download & install Rest Builder tool to build the web api query to retrieve records.
Refer this blog: https://carldesouza.com/dynamics-crm-rest-builder/
You can download the tool here: https://github.com/jlattimer/CRMRESTBuilder/releases
Regards,
Leah Ju
Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156