locked
How to check and not select null values after using Fetch.xml RRS feed

  • Question

  • Hi.

    I'm organizing forms, views and fields in MS CRM 2011 (UR5, Windows Server 2008 R2) for a Marina Company. And have a question about it.

    To find the related boats' contact information, I used a dynamic search approach coded below;

    function getContactInformation() {
    
        var relatedGrid = document.getElementById("contactInformationSubGrid");
        var lookupField = Xrm.Page.getAttribute("new_identitycode").getValue();
    
        if (relatedGrid == null || relatedGrid.readyState != "complete") {
            setTimeout('getContactInformation()', 2000);
            return;
        }
    
        var fetchXml = "<?xml version='1.0'?>";
            fetchXml += "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";
            fetchXml += "<entity name='new_boatcard'>";
            fetchXml += "<attribute name='new_boatownerid' />";
            fetchXml += "<attribute name='new_captainid' />";
            fetchXml += "<attribute name='new_communicationid' />";
            fetchXml += "<attribute name='new_realownerid' />";
            fetchXml += "<attribute name='new_responsibleid' />";
            fetchXml += "<order attribute='new_boatownerid' descending='false' />";
            fetchXml += "<filter type='and'>";
            fetchXml += "<condition attribute='new_identitycode' operator='eq' value='"+lookupField+"'/>";
            fetchXml += "</filter>";
            fetchXml += "</entity>";
            fetchXml += "</fetch>";
    
        try {
            relatedGrid.control.setParameter("fetchXml", fetchXml);
            relatedGrid.control.refresh();
        }
        catch (e){
            relatedGrid.control.SetParameter("fetchXml", fetchXml);
            relatedGrid.control.Refresh();
        }
    }
    

    There's no problem right now. But here is the situation;

    If these fields have NULL values stored in DB, the function coded above returns the NULL values too. And subGrid displays NULLs(which is normal)

    <attribute name='new_boatownerid' />
    <attribute name='new_captainid' />
    <attribute name='new_communicationid' />
    <attribute name='new_realownerid' />
    <attribute name='new_responsibleid' />

    If whole 5 values are NULL,  function returns an empty row displayed in subGrid(and when I click, I get an exception).

    So, I want to check values if these 5 values are NULL, don't display the row. Is there any way to do this?

    Thanks in advance.

    Can


    Tuesday, September 3, 2013 12:48 PM

Answers

  • Hi,

    The fact that you get an exception when clicking on an empty row is strange as the presence/absence of values in the row should not affect the behaviour of the row when clicked. To resolve that issue, you could try explicitly adding the primary key (new_boatcardid) to the list of retrieved attributes.

    To exclude rows that have no values in those 5 fields, change the FetchXML as follows:

    var fetchXml = "<?xml version='1.0'?>";
    fetchXml += "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";
    fetchXml += "<entity name='new_boatcard'>";
    fetchXml += "<attribute name='new_boatownerid' />";
    fetchXml += "<attribute name='new_captainid' />";
    fetchXml += "<attribute name='new_communicationid' />";
    fetchXml += "<attribute name='new_realownerid' />";
    fetchXml += "<attribute name='new_responsibleid' />";
    fetchXml += "<order attribute='new_boatownerid' descending='false' />";
    fetchXml += "<filter type='and'>";
    fetchXml += "  <condition attribute='new_identitycode' operator='eq' value='"+lookupField+"'/>";
    fetchXml += "  <filter type='or'>";
    fetchXml += "    <condition attribute='new_boatownerid' operator='not-null' />";
    fetchXml += "    <condition attribute='new_captainid' operator='not-null' />";
    fetchXml += "    <condition attribute='new_communicationid' operator='not-null' />";
    fetchXml += "    <condition attribute='new_realownerid' operator='not-null' />";
    fetchXml += "    <condition attribute='new_responsibleid' operator='not-null' />";
    fetchXml += "  </filter>";
    fetchXml += "</filter>";
    fetchXml += "</entity>";
    fetchXml += "</fetch>";


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    • Proposed as answer by MJFPalmer Tuesday, September 3, 2013 1:44 PM
    • Marked as answer by cankabaklarli Thursday, September 12, 2013 6:43 AM
    Tuesday, September 3, 2013 1:43 PM

All replies

  • Hi,

    The fact that you get an exception when clicking on an empty row is strange as the presence/absence of values in the row should not affect the behaviour of the row when clicked. To resolve that issue, you could try explicitly adding the primary key (new_boatcardid) to the list of retrieved attributes.

    To exclude rows that have no values in those 5 fields, change the FetchXML as follows:

    var fetchXml = "<?xml version='1.0'?>";
    fetchXml += "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";
    fetchXml += "<entity name='new_boatcard'>";
    fetchXml += "<attribute name='new_boatownerid' />";
    fetchXml += "<attribute name='new_captainid' />";
    fetchXml += "<attribute name='new_communicationid' />";
    fetchXml += "<attribute name='new_realownerid' />";
    fetchXml += "<attribute name='new_responsibleid' />";
    fetchXml += "<order attribute='new_boatownerid' descending='false' />";
    fetchXml += "<filter type='and'>";
    fetchXml += "  <condition attribute='new_identitycode' operator='eq' value='"+lookupField+"'/>";
    fetchXml += "  <filter type='or'>";
    fetchXml += "    <condition attribute='new_boatownerid' operator='not-null' />";
    fetchXml += "    <condition attribute='new_captainid' operator='not-null' />";
    fetchXml += "    <condition attribute='new_communicationid' operator='not-null' />";
    fetchXml += "    <condition attribute='new_realownerid' operator='not-null' />";
    fetchXml += "    <condition attribute='new_responsibleid' operator='not-null' />";
    fetchXml += "  </filter>";
    fetchXml += "</filter>";
    fetchXml += "</entity>";
    fetchXml += "</fetch>";


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    • Proposed as answer by MJFPalmer Tuesday, September 3, 2013 1:44 PM
    • Marked as answer by cankabaklarli Thursday, September 12, 2013 6:43 AM
    Tuesday, September 3, 2013 1:43 PM
  • Apply above code will change the fetch results.

    try this code. Generate fetchxml from Advanced Find and change it with this code. It might be a loading issue:-

    function UpdateSubGrid(){ 
    if (Xrm.Page.ui.getFormType() ==2){
        var leadGrid = document.getElementById("Condensate");  
        //If this method is called from the form OnLoad, make sure that the grid is loaded before proceeding   
        //Included extra null check as a rollup 5 fix
        if (leadGrid ==null || leadGrid.readyState != "complete") {
            //The subgrid hasn't loaded, wait 1 second and then try again     
            setTimeout('UpdateSubGrid()', 1000);     
            return; 
        }  //Update the fetchXML that will be used by the grid.
        var oPack = Xrm.Page.getAttribute("new_condensateid").getValue()[0].id;
        var fetchXml= '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"><entity name="new_pack"><attribute name="new_packid" /><attribute name="new_name" /><attribute name="createdon" /><attribute name="new_totalprice" /><attribute name="new_quantity" /><attribute name="new_listprice" /><order attribute="new_name" descending="false" /><filter type="and"><condition attribute="new_productpackid" operator="eq" value="'+oPack+'"/></filter><link-entity name="product" from="productid" to="new_productid" visible="false" link-type="outer" alias="a_7696afcf6bf0e2119c5178e3b508aaa1"><attribute name="productnumber" /></link-entity></entity></fetch>';
       // alert(fetchXml);
       
        leadGrid.control.SetParameter("fetchXml", fetchXml); 
        //Force the subgrid to refresh 
        leadGrid.control.Refresh();
    }
    }


    Regards Faisal

    Tuesday, September 3, 2013 2:19 PM
  • I'll try your advice and write you back.

    Thanks MJFPalmer and Faisal Fiaz

    Wednesday, September 4, 2013 10:17 AM