Category Archives: Calculated & Rollup Fields

Count the number of related child records using a Rollup field

Posted on by
3

Its going to be a very simple and basic blog where i will show you how you can count number of related records for e.g.

  1. number of cases related to account/contact
  2. number of opportunities related to account/contact
  3. number of active quotes for a contact

this list can go one. In short wherever you have 1:N relationship ; you should be able to perform this operation. it will show something like below :

1

 

  1. To do this create a field on parent record – select the name and select the data type as “whole number” and  field type as “RollUp”  :2
  2. after doing this as soon as you click on “edit” – it saves the field and give you an editor , just like a business rule select the cases(customer) as a related entity.
  3. you can define condition below that for e.g where status of the case  = active or where cases owner is a particular user.
  4. finally in the Aggregation select “COUNT” and in the next field it will show you just one option as case.
    3
  5. finally save the editor and put this field on the form. When you open the form after the publishing all customization , you might see this as 0 at first place its because rollup fields are nothing but system jobs in the background which runs at particular time ( 12 hours to be specific) but you can always force it by clicking to that refresh button in the field :
    5

once all done you will have this field showing you the related records count.

 

i hope this helps!

Advertisement

Calculated Vs Roll Up Field

Posted on by
Reply

Hi folks , i have gathered a quick summary of important differences between a calculate field and a roll up field:

Calculated Field Roll Up Field
Data is calculated as soon as form is loaded The rollups are calculated by scheduled system jobs that run asynchronously in the background.
calculated fields let you automate manual calculations used in your business processes. . A rollup field contains an aggregate value computed over the records related to a specified record
The calculated fields comprise of calculations that use the fields from the current entity or related parent entities. The roll up fields comprise of Aggregation that use the fields from the current entity or related child entities.
The expression support is available on the current entity and the related parent entity fields in the Condition sections and the Action sections. The built-in functions include:

ADDHOURS, ADDDAYS, ADDWEEKS, ADDMONTHS, ADDYEARS, SUBTRACTHOURS, SUBTRACTDAYS, SUBTRACTWEEKS, SUBTRACTMONTHS, SUBTRACTYEARS, DIFFINDAYS, DIFFINHOURS, DIFFINMINUTES, DIFFINMONTHS, DIFFINWEEKS, DIFFINYEARS, CONCAT, TRIMLEFT, and TRIMRIGHT.

 Wide selection of aggregate functions. You can aggregate data by using the following functions: SUM, COUNT, MIN, MAXand AVG.
A seamless integration of the calculated fields with the forms, views, charts, and reports is available in real time. eamless integration with the user interface. You can include the rollup fields in forms, views, charts and reports.
The available data types for the calculated field:
Single line of text,Option Set,Two Options,Whole Number,Decimal Number,Currency,Date and Time		 The data types include decimal or whole numbers, currency, and date/time.

Above data has been collected from Microsoft technet.

if you wish to know more about these fields in-depth, please follow below:

Calculated Field :  https://technet.microsoft.com/en-us/library/dn832103.aspx

Rollup Field :  https://technet.microsoft.com/en-us/library/dn832162.aspx

 

Hope this helps!

Cheers!

How to get value from a lookup or parent entity using Calculated field in Microsoft Dynamics CRM

Posted on by
Reply

Did you ever need to get values from other entity or from a lookup on current entity? What comes to your mind, bet it is javascript , plugin etc?

Well, lets pause the coding life for minute and do it using Calculated Fields which used simple UI like business rules.

In Scenario below , i am retrieving the Budget Allocated amount from source campaign on a lead record.

  1. Create a currency Calculated field on lead record :1Give the appropriator name , select data type as Currency or as per your need > Select Field type as Calculated and click on Edit. As soon as you click on edit, system will actually create this field for you.
  2. After clicking on Edit button , you will be presented with the UI where you have put in  your logic:2

    As a first step i am checking if Budget Allocated field on campaign actually has data.To do this you have select Source campaign in the Entity and the corresponding field.

  3. Once this condition is set click on the Action :3

    In the editor start the typing the name of source campaign lookup or you can scroll though all fields/lookups available on the lead entity. Here i have select campaignid

  4. Once selected the campaignid , enter a dot(.) – post which you will start getting fields from the campaign:4

    Here i am selecting the budgetcost(Allocated Budget)

  5. Finally it will look like below :5Save and close the editor and also the field property window.
  6. Put this calculated field on the form and publish customization.
  7. Open a lead which has campaign associated to it:

    6campaign :

    7It is a very basic example i showed , i tried not to make it complex as it might become very confusing for a beginner.  You can do a lot manipulation with the data by using out of the box functions that are available for a calculated field or apply your custom logic.hope this helps!

    cheers!