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...
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
}
)
)
)
);
Solved! Go to Solution.
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
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.
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
Ah yes - sorry missed that LookUp column. Typing these things by hand always leads to something overlooked 😉
User | Count |
---|---|
252 | |
101 | |
94 | |
47 | |
38 |