AddCustomFilter Not Working as Expected (Neither In-line NOR Lookup)

(0) Share

Report

Posted on by Community Member

I have found quite a few helpful resources about the AddCustomFilter method in jScript that can help us to filter a lookup in ways that OOB CRM cannot. I have been tackling this issue for a while, and have gone at it in a number of different ways.

Here is the requirement:
On a Work Order Product, the Product Lookup must be filtered to show only Products that belong to the PriceList which is set on the Work Order. Using related records (OOB) filtering, I can only achieve this by using the Default Price List of a product - which will not work for my requirement.

Basically, I want the Product Lookup to only return Products that exist in the Price List that is set on the Work Order.

Right now, I am getting *some type* of filtered list, but it isn't correct. And further, inside the lookup window (where you can change the views, do search, etc), if I click the Magnifying Glass (Search) icon twice, the 'filter' (which again is not correct) disappears, and then a list of ALL FS Products is shown. Why is that?

Here is my code. This is the simplest implementation I've tried over a number of attempts. I just recently added the Try/Catch blocks, since this was so dang simple, I wasn't thinking i'd need them. But at this point, I am not catching any errors, no errors in F12 Debug, and no errors popped by CRM UI.

Any thoughts on why this his happening?

function FilterWOProductList() {
    
        var PriceList = Xrm.Page.getAttribute("msdyn_pricelist").getValue();
        //checking if pricelist fields is empty before we apply the filter
        if (PriceList != null) {
            Xrm.Page.getControl("msdyn_product").addPreSearch(Filter);
        }
    }

    function Filter() {

        var PriceListValue = Xrm.Page.getAttribute("msdyn_pricelist").getValue();
        var PriceList = Xrm.Page.getAttribute("msdyn_pricelist").getValue();
        //if PriceList has a value, proceed
        if (PriceList != null) {
            //used to retrieve Name of the Price List held in the PriceList field
            var PriceListTextValue = PriceListValue[0].name;
            //GUID used in filter (pricelist GUID)
            var PriceListID = PriceListValue[0].id;

            try {
                var plist_filter = "<filter type='and'>" + "<condition attribute='msdyn_pricelist' operator='eq'  value='" + PriceListID + "' />" + "</filter>";
                Xrm.Page.getControl("msdyn_product").addCustomFilter(plist_filter, "msdyn_product");
            }

            catch (e) {
                Xrm.Utility.alertDialog("addFilter Error: " + (e.description || e.message));
            }
        }
    }

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (2)

Report

Hello,

Try to replace line

Xrm.Page.getControl("msdyn_product").addCustomFilter(plist_filter, "msdyn_product");

with line

Xrm.Page.getControl("msdyn_product").addCustomFilter(plist_filter, "product");

or even

Xrm.Page.getControl("msdyn_product").addCustomFilter(plist_filter);

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Hi Andrew,

Thanks for the suggestion. I can't believe I hadn't seen that as a potential issue before. I think you have gotten me 1 step closer to where I need to be. My work day is just about done, and I need to take a break, but this is the error I am getting

Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: 'Product' entity doesn't contain attribute with Name = 'msdyn_pricelist' and NameMapping = 'Logical'. MetadataCacheDetails: ProviderType=Dynamic, StandardCache=True, IsLoadedInStagedContext = False, Timestamp=3336869, MinActiveRowVersion=3336869Detail: <OrganizationServiceFault xmlns:i="www.w3.org/.../XMLSchema-instance" xmlns="schemas.microsoft.com/.../Contracts"> <ActivityId>3900c80d-a949-40d8-9164-51668ceb57be</ActivityId> <ErrorCode>-2147217149</ErrorCode> <ErrorDetails xmlns:d2p1="schemas.datacontract.org/.../System.Collections.Generic" /> <Message>'Product' entity doesn't contain attribute with Name = 'msdyn_pricelist' and NameMapping = 'Logical'. MetadataCacheDetails: ProviderType=Dynamic, StandardCache=True, IsLoadedInStagedContext = False, Timestamp=3336869, MinActiveRowVersion=3336869</Message> <Timestamp>2018-02-28T21:40:48.5389181Z</Timestamp> <ExceptionRetriable>false</ExceptionRetriable> <ExceptionSource i:nil="true" /> <InnerFault i:nil="true" /> <OriginalException i:nil="true" /> <TraceText i:nil="true" /> </OrganizationServiceFault>

This makes sense, as Price List does not live on Product. So, can I even do an addCustomFilter call here? Do I have to go with the AddCustomView instead? Or, perhaps, should I be doing a different query? Maybe use the IN instead of the EQ operator? Even then, I am not sure the query, as constructed, will fire as I expect, since it is trying to grab the Price List from the product. I think I would need to join to Price List Items to be able to do the comparison that I need.

Do you have any more suggestions? Thanks for the quick help, Andrew!

Was this reply helpful? Yes No
Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (2)

Report

Hello,

It happens because product doesn't have msdyn_pricelist field - it have pricelevelid instead.

If you want to filter by related entities check following - http://missdynamicscrm.blogspot.com/2014/08/crm-2013-using-addcustomfilter-to-get-filtered-lookup-field-based-on-linked-entity.html

To build valid query to implement approach in this blog you can use CRMRestBuilder.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Andrew. Using the pricelevelid (Default Price List) will not work for me. If I wanted to use that, I would be able to use the OOB Filtered Lookups.

My products are going to be long to multiple price lists, and regardless what is set for, or if anything is set for "Default Price List" - I would need all products that are on that price list to return in my Lookup window.

Does that make sense?

Was this reply helpful? Yes No
Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (0)

Report

Yes, it makes sense. Check second past of my previous reply - I gave you an answer. In your particular case you will have to retrieve Price Level Items records (productpricelevel) to get the list of all applicable for given pricelist products.

Was this reply helpful? Yes No
Suggested answer

rbrand 15 on at

Like (2)

Report
Hi,

Coincidentally I recently encountered the exact same issue. It might even be for the same organization. Anyway I solved the issue by implementing a synchronous XMLHttpRequest() in JS on the OnLoad().

Even though some time has past it could still be usefull for someone. Just save the JS as a webresource and add it to the form onLoad event.

// Function to set up a custom product lookup on a Dynamics 365 form function setUpCustomProductLookup() { // Define entity names and attribute names var workOrderProductEntityName = "product"; var productLookupAttribute = "msdyn_product"; var priceListLookupAttribute = "msdyn_pricelist"; // Add a pre-search event handler to the product lookup attribute Xrm.Page.getControl(productLookupAttribute).addPreSearch(function () { // Function to retrieve product IDs based on the selected price list function getProductidsByPricelist() { // Array to store retrieved product IDs var productArray = []; // Get the ID of the selected price list var priceListId = Xrm.Page.getAttribute(priceListLookupAttribute).getValue()[0].id; // Build the URL for the Web API request to retrieve product IDs based on the price list var url = Xrm.Page.context.getClientUrl() + "/api/data/v9.2/productpricelevels"; var filterExpression = "?$filter=_pricelevelid_value eq " + encodeURIComponent(priceListId); var queryOptions = "&$select=_productid_value"; // Create a new XMLHttpRequest object var req = new XMLHttpRequest(); // Configure the request req.open("GET", url + filterExpression + queryOptions, false); // Set request headers req.setRequestHeader("OData-MaxVersion", "4.0"); req.setRequestHeader("OData-Version", "4.0"); req.setRequestHeader("Accept", "application/json"); req.setRequestHeader("Content-Type", "application/json; charset=utf-8"); req.setRequestHeader("Prefer", "odata.include-annotations=\"*\""); // Send the request req.send(); // Check if the request was successful (status code 200) if (req.status === 200) { // Parse the response JSON var result = JSON.parse(req.responseText); // Iterate through the retrieved products and extract product IDs for (var i = 0; i < result.value.length; i++) { var product = result.value[i]; var productId = product._productid_value; // Add the product ID to the array productArray.push(productId); } // Return the array of product IDs return productArray; } else { // Log an error message if the request was not successful console.error("An error occurred: " + req.statusText); // Return null in case of an error return null; } } // Call the function to get product IDs based on the selected price list var productArray = getProductidsByPricelist(); // Build FetchXML to filter the product lookup based on retrieved product IDs var fetchXml = "<fetch version='1.0' mapping='logical' distinct='false'>" + "<entity name='product'>" + "<attribute name='statecode' />" + "<attribute name='name' />" + "<attribute name='productid' />" + "<filter type='and'>" + "<condition attribute='productid' operator='in'>"; // Add product IDs to the FetchXML filter productArray.forEach(item => { fetchXml += "<value>" + item + "</value> \n"; }); // Complete the FetchXML structure fetchXml += "</condition>" + "</filter>" + "</entity>" + "</fetch>"; // Log FetchXML for debugging (optional) //console.log("fetchXml: ", fetchXml); // Apply the custom filter to the product lookup Xrm.Page.getControl(productLookupAttribute).addCustomFilter(fetchXml, workOrderProductEntityName); }); } // Call the function to set up the custom product lookup setUpCustomProductLookup();

Was this reply helpful? Yes No