• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Exploring Field Types – Part 2: Calculated Fields

June 13, 2017   Microsoft Dynamics CRM
hands coffee cup apple 300x214 Exploring Field Types – Part 2: Calculated Fields

Welcome back to our two-part blog series on Field Types. In Part I of the series, we tackled Rollup fields – if you missed it, check it out here. In today’s entry, we’ll take it a step further and discuss Calculated Fields, including when and why to use them and how to add them. Let’s get started…

Recall that in Part I, we assumed that we wanted to easily see the number of tasks against each opportunity, as well as how many of those tasks have been completed at any given time. To accomplish this, we created two rollup fields: Tasks
and
Tasks Completed. The result was a View that looked like this:

060917 2128 ExploringFi1 Exploring Field Types – Part 2: Calculated Fields

It allowed us to see that the “4G Enabled Tablets” opportunity, for example, required 9 pieces of effort (or, more familiarly, Tasks), of which only 3 had been completed. This is useful information, of course, but it would sure be nice to take it a step further…

Let’s say that for each opportunity (or Topic), we want to quickly see the percentage of tasks completed and the number of tasks outstanding (after all, nobody should have to do all that math in their head). Luckily, it’s just a matter of creating a couple new fields. Piece of cake!

First, let’s define the math required to achieve our desired information:

% Completed = (Tasks Completed / Tasks) * 100

Tasks Outstanding = Tasks – Tasks Completed

Note that both formulas require that we know Tasks Completed and Tasks. It is no coincidence that those are exactly the two fields we created in PART I of this series! J OK, without further ado, let’s get started.

Begin by creating the first of two more new Fields – in our example below, we’ve named it % Completed. Set “Data Type” to Decimal Number; set “Field Type” to Calculated, as shown:

060917 2128 ExploringFi2 Exploring Field Types – Part 2: Calculated Fields

Note in the screenshot above that the Schema automatically removes the “%” from the “Name” – but don’t worry, because the “Display Name” (which is what we will eventually see in our View) retains the “%.”

Click Edit (circled above) to modify the properties of the Calculated field.

You may recall that in Part I of this blog series (when we created two new fields: Tasks and Tasks Completed), we made a special note of the fact that we were including the word “Tasks” in the name of each new field. Well, here’s where that comes in handy:

On the next screen, click in the action area (to the right of the equal sign) and start typing “Tasks.” As you begin typing, all field names that match the text you’ve entered will appear, as shown below. How cool is that?

060917 2128 ExploringFi3 Exploring Field Types – Part 2: Calculated Fields

At this point, we want to define the actual calculation we want performed in the % Completed field. Earlier, we defined this as % Completed = (Tasks Completed / Tasks) * 100. So, simply double-click on new_taskscompleted from the available options – this will add the field name to the action area. Next, type “/.” Then double-click on new_tasks to add it to the action area. Type “*100.” Finally, add the necessary brackets. When you’re done, it will look like this:

060917 2128 ExploringFi4 Exploring Field Types – Part 2: Calculated Fields

Note that adding a “CONDITION” is optional – in our example, none is needed since we want to apply the calculation to every Opportunity record without exclusion.

Next, we’ll create the second of two new Fields – in our example below, we’ve named it Tasks Outstanding. Set “Data Type” to Whole Number; set “Field Type” to Calculated, as shown:

060917 2128 ExploringFi5 Exploring Field Types – Part 2: Calculated Fields

Click Edit to modify the properties of the Calculated field. Once again, begin typing “Tasks” in the action area to reveal the available fields.

At this point, we want to define the actual calculation we want performed in the Tasks Outstanding field. Earlier, we defined this as Tasks Outstanding = Tasks – Tasks Completed. So, simply double-click on new_tasks from the available options – this will add the field name to the action area. Next, type “-.” Then double-click on new_taskscompleted to add it to the action area. When you’re done, it will look like this:

060917 2128 ExploringFi6 Exploring Field Types – Part 2: Calculated Fields

Once again, note that adding a “CONDITION” is optional – in our example, none is needed since we want to apply the calculation to every Opportunity record without exclusion.

Next, we need add our two newly-created fields to our existing Section in the Form. (For more information, see Customizing Entities.)

Next, we need to add our new fields to a View, which is as simple as selecting the View and adding the columns. (For detailed information on how to do this, please see Customizing Views.)

Publish all changes. Unlike Rollup fields, which take up to 24 hours for the changes to be applied, Calculated fields are updated immediately. So, all your data is populated right away, as shown below:

060917 2128 ExploringFi7 Exploring Field Types – Part 2: Calculated Fields

Now, we can very quickly and easily see that the “4G Enabled Tablets” opportunity, for example, is 33.33% complete with 6 outstanding tasks. All the math is done for us! Pretty slick, no?

Well, that’ll do it for Part II. Happy CRM’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

Calculated, Exploring, Field, Fields, Part, types
  • Recent Posts

    • Accelerate Your Data Strategies and Investments to Stay Competitive in the Banking Sector
    • SQL Server Security – Fixed server and database roles
    • Teradata Named a Leader in Cloud Data Warehouse Evaluation by Independent Research Firm
    • Derivative of a norm
    • TODAY’S OPEN THREAD
  • Categories

  • Archives

    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited