Count and Retrieve value from an entity in data grid of another entity

I have two entities here, Project and Invoice. I want to add a column called pending invoice in the data grid which is of type Project. There is an in the Invoice entity called Status which has enumerations Due, Not Due, Invoiced and Collected. I want to count and show the invoices for every project which are not ‘Collected’. Meaning if the project does not have an invoice then it should show zero otherwise it should show the number of invoices which are not ‘Collected’. The Invoice has an association with the Project entity as well. How should I do this? The data grid path is of type project and cannot be changed to Invoice since most of the data in the columns is coming from Project entity.
1 answers

Add a calculated field 'NumberOfPendingInvoices’ to entity Project. Assign a microflow to 'NumberOfPendingInvoices’ that does the calculation by a retrieve-activity for entity Invoice with xpath-constraint [Status = 'Due’] followed by a AggregateList-activity Count returning the Count to the calculated field.

Add 'NumberOfPendingInvoices’ to your Project-Datagrid.

The calculation microflow:

The datagrid for the project-entity

And see → Forumquestions → Question 94949 for the live version