Answered

Combine / Improve this with only one collection....

(0) Share

Report

Posted on by sasrsc1966

219

I have my SP List (Events)
I do 2 things
1) Get the Title value from the Venues List based on matching ID=VenueID - creating a collection Events2
2) Append to that collection a bunch of previously calculated things. - using Events2 and creating the final Event Listing.

ClearCollect(Events2,AddColumns(Events,"VenueName",LookUp(Venues,ID=VenueId,Title)));

ClearCollect(
 EventListing,
 ForAll(
 Events2 As _e,
 Patch(
 _e,
 With(
 {
 _rec: LookUp(
 TicketRequestCounts,
 EventId = _e.ID
 )
 },
 
 {
 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 tried the "with" statement as I know there's a solution with that ... because @WarrenBelz always says that's the answer 😉
I thought I could simply add a 2nd declaration for the {with) but immediately syntax errors...

Here's the image so I can show the few changes I made...
Region Capture.jpg

and this is the text version in case you want to copy it.

ClearCollect(
 EventListing,
 ForAll(
 Events As _e,
 Patch(
 _e,
 With(
 {
 _rec: LookUp(
 TicketRequestCounts,
 EventId = _e.ID
 )
 },
 { _v: LookUp(Venues,ID=_e.VenueId)},
 {
 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,
 VenueName: _v.Title
 }
 )
 )
 )
);

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@sasrsc1966

You were close!!

Please consider changing your Formula to the following:

ClearCollect(EventListing, ForAll(Events As _e, With({_rec: LookUp(TicketRequestCounts, EventId = _e.ID), _v:LookUp(Venues,ID=VenueId,Title)}, Patch(_e, {VenueName: _v.Title, 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.
Verified answer

sasrsc1966 219 on at

Like
a
(2)

Report
Copy link

Link copied!
I had to make a minor tweak to Randy's solution but essentially he was absolutely correct.
Because the _v returns Title (not the entire record), then when I grab that value for VenueName I only need _v and not _v.Title.
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 } ) ) ) );
Because
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@sasrsc1966

Ah yes - sorry missed that LookUp column. Typing these things by hand always leads to something overlooked 😉