C0

Spring 2018 release introduced many new features. Common Data Service for Apps can now have server-side logic and calculation defined at entity level for data quality and consistency. Which means logic will be applied once at data level instead of individual application level. These new capabilities include:

  • Calculated and Rollup Fields
  • Business Rules
  • Workflows
  • Business logic using custom code

In this blog we will see how to benefit from Calculated and Rollup Fields. Let’s first understand what Calculated and Rollup fields are.

  • Calculated Fields – stores computed value from other fields of entity based on pre-defined logic such as numeric calculations, date operations and text formatting
  • Rollup Fields – stores aggregated value of child entities based on pre-defined logic such as count of products for a company or average salary for an employee

Create Calculated Field:

In Part 2, we created Company Product entity with field “Expiration Date” for a product. Using this field, we will create a new calculated field “Expired Since” which will calculate and display number of days since a product is expired.

  • Login to PowerApp web portal. Click on Entities and select Company Product entity. Click on + Add field in top navigation

C1

  • Enter Display Name “Expired Since”, select Whole Number as Data type. Click on + Add for Calculated or Rollup and select Calculation. You will be asked to Save entity, Click on Save

Note – Option to add calculated or rollup field will be disabled for a field while creating entity. Save your entity first and then create calculated or rollup field.

C2

  • Calculated Field creation dialog will open (takes little time). You need to define condition and action for your calculation, condition here is optional.
    • Condition – will allow you to select entity, field and conditional operator
    • Action – will allow you to define calculation formula similar to excel macros

For “Expired Since” it looks like below:

C3

  • Click Save & Close
  • Click Done

Create Rollup Field:

In Part 1, we create Company entity which is parent to Company Product entity. We will create a new Rollup field in Company field which will aggregate and shows number of products each company owns.

Note: Rollup field is created on parent entity to aggregate value of child entities.

  • Click on Entities and select Company entity. Click on + Add field in top navigation
  • Enter display name “No of Products”, select Whole number as Data type. Click on + Add for Calculated or Rollup and select Rollup this time. You will be asked to Save entity, Click on Save

C4

  • Rollup Field creation dialog will open. You will be asked to select related/child entity and aggregation logic (count, sum, min, max, avg). In my case it looks like below:

C5

Note: You will see warning message at bottom of this dialog which means once rollup field is created, it takes 12 hours for job to start and perform calculation. Please refer my blog for more details on how to update job to run immediately instead of waiting for 12 hours.

  • Click Save & Close
  • Click Done

Once you are done with creation of Calculated and Rollup fields, you can add them to views and forms. Follow below steps to add fields on views:

  • Select Views tab on entity page. Click on Remove filter button to display all views in list. Double click on view you want to edit. “Active Companies” in my case

C6

  • View editor screen will open. Click on Add Columns in right pane, select field to be added in view. “No of Products” in my case

C7

  • Click Ok
  • Click Save & Close
  • Similarly add “Expired Since” calculated field in view for Company Product entity

Browse Views:

Select your App from apps list (refer Part 3 on how to create app) and click Play. In case new fields not visible in views, refresh your page or edit & publish your app once

C8

 

C9

Part 4

Part 6