Answered

Multiple "withs, forall, groupby" what's wrong with this...

(1) Share

Report

Posted on by sasrsc

678

I know this is alot of code... it does not error but it doesn't produce what I thought it would do...
_c is meant to give the _p some info as single row with single columns.
However, what's happening is that _c is passing records to _p as opposed to a single value.
So walking through it...
_c is a GroupBy of all comments by ProjectID giving me the most recent comment for each ProjectId (sorted descending Created value)
so _c.MostRecentComment should be the latest column and not a set of records (in the way I envision it). Any thoughts what I'm doing wrong...

With(
 {
 _c: ForAll(
 GroupBy(
 'Project Comments',
 "ProjectId",
 "GroupedItems"
 ),
 With(
 {
 _top: First(
 Sort(
 GroupedItems,
 Created,
 Descending
 )
 )
 },
 {
 ProjectId: ProjectId,
 MostRecentComment: _top.Comment,
 DateCreated: _top.Created,
 CommentWho: _top.'Created By'.DisplayName,
 StatusId: _top.StatusId,
 PercentComplete: _top.PercentComplete,
 CommentCount: CountRows(GroupedItems)
 }
 )
 )
 },
 ClearCollect(
 ProjectStatus,
 ForAll(
 Projects As _p,
 With(
 {
 _cat: LookUp(
 'Project Lookup Values',
 ID = _p.ProjectCategoryId,
 Title
 )
 },
 Patch(
 _p,
 {
 ProjectCategory: _cat,
 MostRecentComment: _c.MostRecentComment,
 DateCreated: _c.DateCreated,
 CommentWho: _c.CommentWho,
 PercentComplete: _c.PercentComplete,
 CommentCount: _c.CommentCount
 }
 )
 )
 )
 )
)

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

iAm_ManCat 18,256 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Heya,

By using ForAll and a {} format, you've created records for each _c, which when you are doing ForAll _p doesn't have any reference to which _c it should be getting for each _p if that makes sense.

You can probably do a filter and use the With lower down in your Formula, instead of GroupBy which is not a delegable function, so something like this:

ClearCollect( ProjectStatus, ForAll( Projects As _p, With( { _pComments: Sort( Filter('Project Comments' As ProjComms, ProjComms.ProjectId = _p.ProjectId ), Created, Descending ) }, With( { _cat: LookUp( 'Project Lookup Values', ID = _p.ProjectCategoryId, Title ), _c: { //Removed columns you are not referencing below in patch MostRecentComment: First(_pComments).Comment, DateCreated: First(_pComments).Created, CommentWho: First(_pComments).'Created By'.DisplayName, PercentComplete: First(_pComments).PercentComplete, CommentCount: CountRows(_pComments) } }, Patch( _p, { ProjectCategory: _cat, MostRecentComment: _c.MostRecentComment, DateCreated: _c.DateCreated, CommentWho: _c.CommentWho, PercentComplete: _c.PercentComplete, CommentCount: _c.CommentCount } ) ) ) ) )

Might need some minor tweaking but that should help 🙂

Cheers,

Sancho

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@sasrsc

Also, taking @iAm_ManCat 's formula a little further to reduce the amount of data operations in the formula...

ClearCollect( ProjectStatus, ForAll( Projects As _p, With({_projectComments: Filter('Project Comments' As ProjComms, ProjectId = _p.ProjectId), _cat: LookUp('Project Lookup Values', ID = _p.ProjectCategoryId, Title) }, With({_top: First(Sort(_projectComments, Created, Descending))}, Patch(_p, { ProjectCategory: _cat, MostRecentComment: _top.MostRecentComment, DateCreated: _top.DateCreated, CommentWho: _top.CommentWho, PercentComplete: _top.PercentComplete, CommentCount: CountRows(_projectComments) } ) ) ) ) )

I hope this is helpful for you.

Was this reply helpful? Yes No
sasrsc 678 on at

Like
a
(0)

Report
Copy link

Link copied!

As usual Randy you make it look simple.... if only I had your depth of knowledge!
Thank you also @IAM_manscat ...

Was this reply helpful? Yes No
sasrsc 678 on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes - hey one follow on ... I added one extra piece and I'm wondering if you would have done it the same way as I propose here...I added the bottom line "Progress: LookUp('Project Lookup Values',ID=_top.StatusId,Title)" - It's using the temporary _top table and that statusId value... so not sure if you code it the way I have or something else... always welcome your mentoring. thanks

ClearCollect( ProjectStatus2, ForAll( Projects As _p, With({_projectComments: Filter('Project Comments' As ProjComms, ProjComms.ProjectId = _p.ID), _cat: LookUp('Project Lookup Values', ID = _p.ProjectCategoryId, Title) }, With({_top: First(Sort(_projectComments, Created, Descending))}, Patch(_p, { ProjectCategory: _cat, MostRecentComment: _top.Comment, DateCreated: _top.Created, CommentWho: _top.'Created By'.DisplayName, PercentComplete: _top.PercentComplete, CommentCount: CountRows(_projectComments), Progress: LookUp('Project Lookup Values',ID=_top.StatusId,Title) } ) ) ) ) )

Was this reply helpful? Yes No