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

[Usman2]

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

[dpoggemann]

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... 

[Usman2]

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]

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.

[Usman2]

@dpoggemann 

I have create calculated column in Student Task entity like this

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

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]

Hi @Usman2 

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

[Usman2]

Hi @dpoggemann 

Yes it is whole number.

[dpoggemann]

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

[Usman2]

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]

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-...