[TIPS] Filter lookup type field based on multiple values acrossing multiple entities

 [TIPS] Filter lookup type field based on multiple values acrossing multiple entities

Reference: 1  2  3 4

There are three requiments below.

Requirement 1:

Filter "Product" filed options based on "Request" value.




Analysis:

       To filter the product lookup values, need to get related product list (GUID).
        Product list comes from related Product Inventory list.
        Product Inventory list is filterd by Source Warehouse.
        Source Warehouse is got from Request.   


JavaScript:

//Added by Forrest on Jan 5, 2023.
//Purpose: Filter Product lookup value based on Fulfillment Request
//Triggered When: Request field On Changed and Form On Load
function filterProductLookupValue(executionContext)
{
    debugger;
    var formContext = executionContext.getFormContext();

    //Get Request Made By Fire field value
    var requestValue = formContext.getAttribute("qt_request") == null ? null : formContext.getAttribute("qt_request").getValue();

    var fetchXml = null;
    var productFilter = "<filter type='and'><condition attribute='productid' operator='in'>";
    var productGUIDValue = "";

    if (requestValue != null)
    {
        Xrm.WebApi.retrieveRecord("qt_fulfillment", requestValue[0].id, "?$select=_qt_sourcewarehouse_value").then(
            function success(result)
            {
               
               // formContext.getControl("qt_msdyn_warehouse").addPreSearch(function()
                {
                    fetchXml = "?fetchXml=<fetch mapping='logical'>" +
                                   "<entity name='msdyn_productinventory'>" +
                                       "<attribute name='msdyn_product' />" +
                                            "<filter><condition attribute='msdyn_warehouse' operator='eq' value='"+result._qt_sourcewarehouse_value+"' />" +
                                                    "<filter><condition attribute='statecode' operator='eq' value='0' />" +
                                                    "</filter>" +  
                                            "</filter></entity></fetch>";

                    Xrm.WebApi.retrieveMultipleRecords("msdyn_productinventory", fetchXml).then(
                        function success(result)
                        {
                            debugger;
                            for (var i = 0; i < result.entities.length; i++)
                            {
                                productGUIDValue += "<value>"  + result.entities[i]._msdyn_product_value + "</value>";
                            }

                            productFilter += productGUIDValue + "</condition></filter>";

                            debugger;

                            formContext.getControl("qt_product").addPreSearch(function()
                            {
                                formContext.getControl("qt_product").addCustomFilter(productFilter, "product");
                            });
                           
                        },
                        function (error)
                        {
                            console.log(error.message);
                            // handle error conditions
                        }
                    );
                };
                debugger;
            },
            function (error) {
                console.log(error.message);
            }
        );
    }
}


Key Points:

1. In fechXML script, when getting value from variable, can follow below formula :
<condition attribute='attribute logical name' operator='eq' value='"+variable or parameter+"' />

2. When filter from a value list/array, can use for() to create the filter script.
 for (var i = 0; i < result.entities.length; i++)
    {
        productGUIDValue += "<value>"  + result.entities[i]._msdyn_product_value + "</value>";
    }

Sample:
<fetch>
  <entity name="msdyn_productinventory">
    <attribute name="msdyn_product" />
    <filter>
      <condition attribute="msdyn_warehouse" operator="in">
        <value>a3d79a1d-887c-ed11-81ad-000d3a0a0557</value>
        <value>a3d79a1d-abcd-ed11-81ad-000d3aabcdef</value>
        <value>a3d7d3aa-00d3-ed11-81ad-000d3ad79aas</value>
        <value>a3d79a1d-0a05-ed11-81ad-000d3a0a000d</value> keep adding values...
      </condition>
    </filter>
  </entity>
</fetch>

3. When registering lookup field filter callback function,
if the callback function is not complex, can just create it directly in addPreSearch().
It's also easy to pass the parameter to callback function under this way.

formContext.getControl("qt_product").addPreSearch(function()
    {
        formContext.getControl("qt_product").addCustomFilter(productFilter, "product");
    });


Requirement 2:

Auto-populate "Unit" lookup filed based on "Product" value.


//Added by Forrest on Jan 5, 2023.
//Purpose: Auto-populate product unit based on Product selected, Clear Product Quantity
//Triggered When: Product Field OnChange
function autoPopulateProductUnit(executionContext)
{
    debugger;
    var formContext = executionContext.getFormContext();
    var attrProduct = formContext.getAttribute("qt_product");
    var valueProduct = attrProduct == null ? null : attrProduct.getValue();
 
    if (valueProduct != null)
    {
        Xrm.WebApi.retrieveRecord("product", valueProduct[0].id, "?$select=_defaultuomid_value").then(
            function success(result) {
                debugger;
                Xrm.WebApi.retrieveRecord("uom", result._defaultuomid_value, "?$select=name").then(
                    function success(result) {
                        debugger;
                        var object = new Array();
                        object[0] = new Object();
                        object[0].id = result.uomid;
                        object[0].name = result.name;
                        object[0].entityType = "uom"
                        formContext.getAttribute("qt_fullfillmentproductunit").setValue(object);  
                    },
                    function (error) {
                        console.log(error.message);
                    }
                );
            },
            function (error) {
                console.log(error.message);
            }
        );
    }
    else
    {
        formContext.getAttribute("qt_fullfillmentproductunit").setValue(null);
    }

    //Clear Product Quantity
    if (formContext.getAttribute("qt_qty") != null)
    {
        formContext.getAttribute("qt_qty").setValue(null);
    }
   
}

Key Points:

1. When getting lookup type field value, you will get an array. So you have to pick the first component.
var valueProduct = attrProduct == null ? null : attrProduct.getValue();
....
 Xrm.WebApi.retrieveRecord("product", valueProduct[0].id, "?$select=_defaultuomid_value").then(

        .....

2. When setting lookup type field value, also need to create an array type variable.

    id, name and entityType are necessary.

var object = new Array();
                        object[0] = new Object();
                        object[0].id = result.uomid;
                        object[0].name = result.name;
                        object[0].entityType = "uom"
                        formContext.getAttribute("qt_fullfillmentproductunit").setValue(object);  

     

Requirement 3:

Check if there are enough products for the current request.

That is,  check if "Quantity" filed value input is bigger than available quantity.


//Added by Forrest on Jan 5, 2023.

//Purpose: Validate product quantity value based on Product selected
function validateProductQuantityValue(executionContext)
{
    debugger;
    var formContext = executionContext.getFormContext();
    var attrProduct = formContext.getAttribute("qt_product");
    var valueProduct = attrProduct == null ? null : attrProduct.getValue();

    if (valueProduct == null)
    {
        return;
    }
   
    //Get Request Made By Fire field value
    var valuerequest = formContext.getAttribute("qt_request") == null ? null : formContext.getAttribute("qt_request").getValue();

    //Get Product Quantity
    var valuePdtQty = formContext.getAttribute("qt_qty") == null ? null : formContext.getAttribute("qt_qty").getValue();    
   
    //If Product Quantity is blank, clear field notification
    if(valuePdtQty == null)
    {
        formContext.getControl("qt_qty").clearNotification("qt_qty");
    }

    var fetchXml = null;


    if (valuerequest != null)
    {
        Xrm.WebApi.retrieveRecord("qt_fulfillment", valuerequest[0].id, "?$select=_qt_sourcewarehouse_value").then(
            function success(result)
            {    
                fetchXml = "?fetchXml=<fetch mapping='logical'>" +
                            "<entity name='msdyn_productinventory'>" +
                                "<attribute name='msdyn_qtyavailable' />" +
                                        "<filter><condition attribute='msdyn_warehouse' operator='eq' value='"+result._qt_sourcewarehouse_value+"' />" +
                                                "<filter><condition attribute='msdyn_product' operator='eq' value='"+valueProduct[0].id+"' />" +
                                                "</filter>" +  
                                        "</filter></entity></fetch>";

                Xrm.WebApi.retrieveMultipleRecords("msdyn_productinventory", fetchXml).then(
                    function success(result)
                    {
                        debugger;
                        if (result.entities != null && valuePdtQty > result.entities[0].msdyn_qtyavailable)
                        {
                            formContext.getControl("qt_qty").setNotification("The maximum available quantity is " + result.entities[0].msdyn_qtyavailable, "qt_qty");
                        }
                        else
                        {
                            formContext.getControl("qt_qty").clearNotification("qt_qty");
                        }
                    },
                    function (error)
                    {
                        console.log(error.message);
                    }
                );
            },
            function (error) {
                console.log(error.message);
            }
        );
    }   
}

Key Points:

1. Create field notification use setNotification API.
formContext.getControl("qt_qty").setNotification("The maximum available quantity is " + result.entities[0].msdyn_qtyavailable, "qt_qty");

Syntax

formContext.getControl(arg).setNotification(message,uniqueId);

Parameters

NameTypeRequiredDescription
messageStringYesThe message to display.
uniqueIdStringNoThe ID to use to clear this message when using the clearNotification method.

2. Remember to clear notification when condition met.
Use the same notification uniqueid with previous created.
I found there is no issue when clear the notification even it never happeded.

formContext.getControl("qt_qty").clearNotification("qt_qty");

Syntax

formContext.getControl(arg).clearNotification(uniqueId);

Parameters

NameTypeRequiredDescription
uniqueIdStringNoThe ID to use to clear a specific message that was set using setNotification or addNotification. If the uniqueId parameter isn't specified, the currently displayed notification will be cleared.
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)