Need help with my collection please to get

General
1

I need to know the sum of the progress.I realize this is a little complex… and maybe I’m making it harder than it is. The data is in dataverse.

3 tables…
Goals
Deliverables
DeliverableStatus

Relationships:
Goals have zero or more deliverables and deliverables have zero or more deliverableStatus.

Below in _ds I capture the latest status and progress for each individual deliverable using the latest deliverable status (based on descending ‘modified on’). All good.

In _d I return all the deliverables for a specific goal.

The CountRows is working to show how many Deliverables they have.
But I want to do a Sum of the Progress of all the Deliverables and I’m struggling where that Sum goes…

Any advice would be appreciated…


With(
    {
        _ds: ForAll(
            GroupBy(
                AddColumns(
                    DeliverableStatuses,
                    DeliverableGUID,
                    Deliverable.Deliverable
                ),
                DeliverableGUID,
                GroupedItems
            ),
            With(
                {
                    _top: First(
                        Sort(
                            GroupedItems,
                            'Modified On',
                            SortOrder.Descending
                        )
                    )
                },
                {
                    Deliverable: DeliverableGUID,
                    ModifiedOn: _top.'Modified On',
                    RYG: _top.RYG,
                    DateComplete: _top.DateComplete,
                    Progress: _top.Progress,
                    ActivityOwners: _top.ActivityOwners,
                    TargetDates: _top.ActivityTargetDates
                }
            )
        )
    },
    With(
        {
            _d: ForAll(
                _ds As _ddss,
                Patch(
                    _ddss,
                    {
                        Goal: LookUp(
                            Deliverables,
                            Deliverable = _ddss.Deliverable,
                            Goal.Goal
                        )
                    }
                )
            )
        },
        ClearCollect(
            colGoalStatus,
            ForAll(
                Goals As _g,
                With(
                    {
                        _delivers: Filter(
                            _d,
                            Goal = GUID(_g.Goal)
                        )
                    },
                    Patch(
                        _g,
                        {
                            DeliverableCount: CountRows(_delivers),
                            Progress: 100
                        }
                    )
                )
            )
        )
    )
)
2

@sasrsc
If you know how to use FetchXML Builder in the XRMToolbox I would recommend this approach instead:

  • Connect a flow to Power Apps
  • Add a Dataverse > List Rows action
  • Create a FetchXML query to get the desired aggregation
  • Return the result to the application

As shown in this MS Learn reference, FetchXML can aggregate the count based on some grouping.

3

Thanks Matthew, I didn’t know you could use that in a Power App. I thought the List Rows only worked as an action in Power Automate. That’s a great use case too to showcase the advantages to DV in a relationship database.

4

@sasrsc
To clarify, you would have to use the List Rows action in a flow and then add another flow action called “Respond In Power Apps” to return the data to the app.

From there, the ParseJSON function in Power Apps will handy to parse the output.

2 Likes
5

Or if you use the response action you can specify the schema in the flow and you won’t need to Parse the JSON in the app.

ResponseAction
ResponseAction2201×1011 124 KB

1 Like
6

thanks Matthew - I misunderstood. I get what you are saying…that would be an interesting experiment to call a Flow use the FetchXML and return that all to the app vs. doing it the “long way” (my current method). I have experimented with FectXML but so far only with a parent->child situation and not something more complex like this.