More efficient method? Reducing collections

[sasrsc1966]

@RandyHayes  and @WarrenBelz are my inspirations for the use of "with"....
I've been asking various questions and they both have responded with eloquent answers each time improving my code. This is sort of the final piece bringing it all together. The code below works 

I'm down to this code... I don't really need a collection for TicketRequestCounts as it's used only to append to the EventListing collection. So how to improve this with "with"....

In the current "with" statement I'm already referencing the TicketRequestCounts as _rec. But what's confusing to me is the order of the statements. I need TicketRequestCounts but the aggregated column totals.

/*** create collection for the aggreated values from Ticket Request show we can show totals by event. ***/
ClearCollect(
    TicketRequestCounts,
    DropColumns(
        AddColumns(
            GroupBy(
                'Ticket Request',
                "EventId",
                "GroupedItems"
            ),
            "CountApproved",
            Sum(
                Filter(
                    GroupedItems,
                    TicketStatusId = 2
                ),
                'Seats Requested'
            ),
            "CountPending",
            Sum(
                Filter(
                    GroupedItems,
                    TicketStatusId = 1
                ),
                'Seats Requested'
            ),
            "CountWaitlisted",
            Sum(
                Filter(
                    GroupedItems,
                    TicketStatusId = 3
                ),
                'Seats Requested'
            ),
            "CountParkingPasses",
            Sum(
                Filter(
                    GroupedItems,
                    TicketStatusId = 2
                ),
                'Parking Pass Requested'
            )
        ),
        "GroupedItems"
    )
);

/* use Events and get
1. the Venue Name from Venues
2. and append all the aggregated metrics from the collection above
*/

ClearCollect(
    EventListing,
    ForAll(
        Events As _e,
        With(
            {
                _rec: LookUp(
                    TicketRequestCounts,
                    EventId = _e.ID
                ),
                _v: LookUp(
                    Venues,
                    ID = _e.VenueId,
                    Title
                )
            },
            Patch(
                _e,
                {
                    VenueName: _v,
                    ApprovedCount: _rec.CountApproved,
                    PendingCount: _rec.CountPending,
                    WaitlistCount: _rec.CountWaitlisted,
                    ParkingPassCount: _rec.CountParkingPasses,
                    AvailableCount: _e.'Seat Capacity' - _rec.CountApproved,
                    ParkingPassesAvailable: _e.'Parking Pass Capacity' - _rec.CountParkingPasses
                }
            )
        )
    )
);

[RandyHayes · Accepted solution]

@sasrsc1966 

You can combine the formula to the following:

With({_counts:
    ForAll( GroupBy('Ticket Request', "EventId", "GroupedItems"),
        {EventId: EventId,
         CountApproved: Sum(Filter(GroupedItems, TicketStatusId = 2), 'Seats Requested'),
         CountPending: Sum(Filter(GroupedItems, TicketStatusId = 1), 'Seats Requested'),
         CountWaitlisted: Sum(Filter(GroupedItems, TicketStatusId = 3), 'Seats Requested'),
         CountParkingPasses: Sum(Filter(GroupedItems, TicketStatusId = 2), 'Parking Pass Requested')
        }
    )
    },

    ClearCollect(
        EventListing,
        ForAll(Events As _e,
            With({
                _rec: LookUp(_counts, EventId = _e.ID),
                _v: LookUp(Venues, ID = _e.VenueId, Title)
            },
                Patch(
                    _e,
                   {
                    VenueName: _v,
                    ApprovedCount: _rec.CountApproved,
                    PendingCount: _rec.CountPending,
                    WaitlistCount: _rec.CountWaitlisted,
                    ParkingPassCount: _rec.CountParkingPasses,
                    AvailableCount: _e.'Seat Capacity' - _rec.CountApproved,
                    ParkingPassesAvailable: _e.'Parking Pass Capacity' - _rec.CountParkingPasses
                   }
                )
            )
        )
    )
);

I hope this is helpful for you.

[sasrsc1966]

In your opinion do you think reducing the code by a collection makes it more efficient. In other words would you do it? I think the code is just as readable this way. In my opinion, when stepping through it - it probably doesn't hurt to create collections for this, then that, etc... as you have the visual output that helps, but when you're satisfied it makes sense to combine like in your example. Do you agree?

[RandyHayes]

@sasrsc1966 

Not really.  efficiency in an app is not a "do this and not that" thing.  When to use a collection is ONLY in the scenario where you need one.  That would be for having an editable table in memory.  If you need to add, remove or alter rows of the in memory table, then the collection is the only choice (well, not really, but the best choice).

If you do NOT need to alter/add/remove, then store it in a variable - this is what is going on in the formula, it is being stored in a _counts variable.  It is done with a With function.  This means it will be regenerated anytime the data source changes.  Since you are using this as a behavioral action, every time the behavioral action is called, it will reevaluate that With variable.  

If there is NO expectation that the data source will change, then you can do the formula once and assign the result to a variable in the app instead.  This could be done when the screen is visible, or globally in the App OnStart.

If the data source might change and you need to have real-time accurate results in the variable AND you need to use the counts in multiple formulas in your app, then you would want to put this into a dynamic variable.  It would then update always and will be accessible anywhere in the app.

SO...all of the above are very specific scenarios.  One with Collection, one with With variable, one with context or global variable and one with dynamic variable.  Choosing the correct one is the answer to what is most efficient.  

For example, if you choose to use a variable where a collection is needed (for alter/add/remove) then you will write a lot of formulas to update the variable based on changes.  Likewise, if you need a dynamic variable and go with a collection instead, then you will write a lot of formulas to maintain and update the collection (as a collection is a variable as well).

To conclude...as mentioned there is no "do this not that" in PowerApps.  The route chosen is completely dependent on the need leveraged against the performance and scope.

Hopefully that all makes sense.

[sasrsc1966]

I imagine in a formal class this could be discussed in great length, but I'm glad to know that one way isn't always the best. As said earlier....I appreciate the responses. I'm learning. I've come a long way...

[RandyHayes]

@sasrsc1966 

Yes, each has its own advantages and disadvantages depending on the need.  It's a bit of a peeve for me that so many come out of the gate thinking that a collection is the thing to use for everything.  This is a bad practice and unfortunately so many examples and demos and docs use them - but they are to demonstrate, they are not best practices.  Plus it really mimics a development platform - PowerApps is not a development platform!  It is a design tool to build an app just like you would build an Excel spreadsheet.  The more people think "develop", the harder PowerApps is.  The more you reference and interact with controls, the easier it is.  Since collections can ONLY be built in Behavioral Actions, this creates a sense of development, which, as mentioned, makes it harder.

Anyway...keep on learning.  General rules - stay away from collections unless absolutely needed.  Stay away from behavioral actions as they lean toward development and thus...harder.  Stick with referencing controls and formula that provide down-stream data (i.e. a dropdown that supplies the necessary data for the controls that depend on it rather than having the relying controls have to determine their own data...again.)  There are lots of things to learn in order to do things the PowerApps way, but once you embrace that, it is a real joy!