{SCIRPTING TIP} Filter Lookup field based on Date Range in Dynamics CRM

Requirement: to filter a Lookup field options dependent on the date field in the current entity.

clip_image001

Description: The lookup field has fields for dates and field should be filtered based on them. For example,

Date on the current entity is 07/10/2015. Lookup field start date should only allow start date in lookup entity to be earlier or equal to 07/10/2015 and end date on the lookup field should be same or after the current entity date 07/10/2015. Only those dates should be allowed to selection.

Code:

This was achieved by adding Pre-filter to the lookup field as below:

function preFilterLookup()

{

      Xrm.Page.getControl(“lookupfieldname”).addPreSearch(function ()

      {

            var dateObject = Xrm.Page.getAttribute(“datefieldname”).getValue();

            var editedDate = yyyymmdd(dateObject);

           

            if (dateObject != null)

            {

                  // Filter the fetchxml by date range

                  fetchXml = “<filter type=’and’><condition attribute=’startdatefieldname’ operator=’on-or-before’ value='” + editedDate +

                        “‘ /><condition attribute=’enddatefieldname’ operator=’on-or-after’ value='” + editedDate +

                        “‘ /></filter>”;

                  // Apply the filter to the field

                  Xrm.Page.getControl(“lookupfieldname”).addCustomFilter(fetchXml);

            }

      });

}

Hope it helps and Happy CRMing!

Advertisements

{CODE TIP}Aggregate Fetchxml queries code for Dynamics CRM

I had written a post long back showcasing the aggregate queries on Dynamics CRM on the below link:

https://dynamicsofdynamicscrm.wordpress.com/2014/07/23/aggregate-fetchxml-queries-for-dynamics-crm-20112013/

This is a very performance effective and seldom used piece from most CRM girls and guys out there.

I had got lot of feedback and comments but somehow people were not able to use it in actual code.

So, I had committed to doing a follow-up article with actual reusable code asset. It took a while for

me to come back to this one, but I never break my promises.

clip_image001[4]

So here it goes(The aliased value is Money in my example, it can be decimal and integer as well): 

    public static decimal GetSum(string primaryEntityId, IOrganizationService service)

        {

            string fetchXml = @”<fetch mapping=’logical’ aggregate=’true’ version=’1.0′>” +

                                        “<entity name=’childentityname’>” +

                                         “<attribute name = ‘attributetoadd’ alias=’sum_attributetoadd’ aggregate=’sum’ />” +

                                            “<filter>” +

                                             “<condition attribute = ‘primaryEntityIdfieldname’ operator=’eq’ value='” +

                                                primaryEntityId

                                                + “‘ />” +

                                                “</filter></entity></fetch>”;

            FetchExpression fetch = new FetchExpression(fetchXml);

            EntityCollection result = service.RetrieveMultiple(fetch);

            decimal sum = 0;

            if (result.Entities.Count == 1)

            {

                sum = ((Money)((AliasedValue)(result.Entities[0].Attributes[“sum_attributetosum”])).Value).Value;

            }

            return sum;

        }

 

Hope it helps you and Happy CRMing!