cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Usman2
Super User
Super User

Calculate progress value depending on related Task entity "Activity Status" column value.

I have two custom entities Student and Task. The relation between these entities is N-to-N. My requirement is to calculate the Student progress depending on average score of related Tasks and save it in the database because this progress column will be used in many places in the app.

 

The formula for progress is that if the related Task "Activity Status" column value is Open then progress is 0, if it is Completed or Canceled then it is 100.

 

Example 1

Student A has 2 related Tasks (A,B)

Task A "Activity Status" value is Open

Task B "Activity Status" value is Completed or Canceled

Student A progress value is 50

 

Example 2

Student B has 3 related Tasks (A,B,C)

Task A "Activity Status" value is Open

Task B "Activity Status" value is Completed

Task C "Activity Status" value is Canceled

Student B progress value is 75

 

 

Note

I am using the default Activity Status choice

Usman2_0-1645003632424.png

 

9 REPLIES 9
dpoggemann
Super User
Super User

Hi @Usman2 ,

 

Couple things here.  First, I expect you mean the relationship is 1:N from Student to Task so that the tasks are related to the specific student right unless you have tasks that are shared across multiple students?  

 

PowerFX Formulas (https://powerapps.microsoft.com/en-us/blog/power-fx-coming-to-model-driven-power-apps-dataverse-and-...) would help you here greatly but these are not yet available for the custom columns.  At this time I would do the following:

  1. Create a rollup field like Total Tasks at the Student level that will count the number of Tasks related to the Student
  2. Create another rollup field for Total Open Tasks at the Student level that will count the number of Open Tasks related to the Student
  3. Create a calculated column for Percent Complete that is equal to the calculation of Open Tasks / Total Tasks.  Be careful to check for 0 in Total Tasks so you don't get a divide by 0 situation.  

Nice article walking through both types of fields:  https://www.futurelearn.com/info/courses/cloudswyft-msft-dynamics-365-using-power-platform-ap/0/step... 

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Hi @dpoggemann 

The N:N relation is the problem because of specific business requirement I need this type of relation and because N:N is not supported by Roll up columns so I created an intersecting entity Student Task like this 
1:N from Student to Student Task

1:N from Task to Student Task

 

Now the problem is that when I create the roll up column in Student table I am not able to filter the tasks based on "Activity Status" since this column is not accessible.

dpoggemann
Super User
Super User

Hi @Usman2,

N:N is definitely an issue as you mentioned.  It is an interesting business requirement, not sure why this would be the case because it means that you could have multiple students assigned to the same task but no worries if that is the case.

 

The intersect entity approach is what I would do, like you have done.  Can you create a calculated field on your Student Task entity that will set a field based on the related Task status?  You should be able to reference the Task entity since it is a direct relationship from your Student Task entity.  Once you set this value then utilize it in your rollup as a filter? 

 

Only thing I am not sure, and you want to try is would that violate the limitation in the rollup fields of:

A rollup column formula can’t reference complex calculated column. Only calculated column that reference simple columns in the same record can be used with rollups.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

@dpoggemann 

I have create calculated column in Student Task entity like this

Usman2_0-1645015737214.png

 

And when I try to access it in Student entity roll up column it is not available there.

Usman2_1-1645015818318.png

 

I do not think that the calculated column is complex enough to violate the limitation in the rollup fields of:

A rollup column formula can’t reference complex calculated column. Only calculated column that reference simple columns in the same record can be used with rollups.

 

 

dpoggemann
Super User
Super User

Hi @Usman2 

 

What type of field is Progress on the Student Task entity?  Is it a Whole Number field?

 

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Hi @dpoggemann 

Yes it is whole number.

dpoggemann
Super User
Super User

Hi @Usman2 ,

 

Just tried myself and it will not let me utilize the calculated field either...  I guess it is too complex as identified.

 

At this point I think what I would do is the following:

Create Power Automate Flow

  1. Trigger on the status change of Task entity
  2. Find all Student Task records where Task matches the triggered Task
  3. Set the Progress field through Power Automate Flow (note, this would now not be a calculated field, you would setup as a standard field)
  4. At this point, since it is a standard field, you will be able to use the rollup fields on the Student entity

 

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
Usman2
Super User
Super User

Hello @dpoggemann 

I tried your solution and it works, but there is one problem. When I update the Student Task record Progress value through flow I expect the roll-up column to immediately give me the updated value but as the recurring jobs are scheduled I have to wait for 1 hours every time the value in Progress column is updated to get the latest aggregated value in roll-up column. 

dpoggemann
Super User
Super User

HI @Usman2 ,

 

Correct, this is how rollup columns work, they are not real time.  If you need this to be more real time then you could update the Student record directly when you update the Student Task record in the flow and remove the rollup field.

 

Power FX is coming to model apps in the future which should help a lot and fix the issue but this is not out until May (estimated) at this time.  https://docs.microsoft.com/en-us/power-platform-release-plan/2022wave1/power-apps/power-fx-formulas-... 

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (3,178)