Combining GroupBy with the current With statement

[forbudt4u]

Hello, I see plenty of other posts similar to what I'm trying to do, but nothing that covers it exactly.  What I have is a Gallery with the following expression:

With({records: Sort(Filter(STHoursReportPrint,!IsBlank('Actual Service Completion Date')),'Actual Service Completion Date')},
    ForAll(Sequence(CountRows(records)),Patch(Last(FirstN(records, Value)),{rowNumber:Value})))

and within the Gallery's Template Fill property the below expression:

If(Mod(ThisItem.rowNumber,2)=0,White,RGBA(237, 237, 237, 1))

If you look at the output below, the report is doing exactly what I want it to do along with the alternating fills for every other row based on my template fill.  However, it has been requested to combine any records that have the same date, job # and cost/phase code into one entry that combines the respective hours.

I can only assume this will require a GroupBy statement of some sort, but I could be wrong.  So, with the case above, I need the report to combine the 2 records to give me one combined row of:

Date              Job #    Cost/Phase Code     Hours

4/19/2022    10684          033716                  4

Any advice on how to accomplish this while maintaining the Items/Hours count I have at the bottom of the screen (not a must, but would be nice) would be awesome! 

[WarrenBelz · Accepted solution]

Hi @forbudt4u ,

See if this does the job

With(
   {
      records: 
	  AddColumns(
         GroupBy(
            Sort(
               Filter(
                  STHoursReportPrint,
                  !IsBlank('Actual Service Completion Date')
               ),
               'Actual Service Completion Date'
            ),
            "Date",
            "Job#",
            "Cost/Phase Code",
            "Data"
         ),
         "Hours",
         Sum(
            Data,
            Hours
         )
      )
   },
   ForAll(
      Sequence(
         CountRows(records)
      ),
      Patch(
         Last(
            FirstN(
               records, 
               Value
            )
         ),
         {rowNumber:Value}
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[forbudt4u]

@WarrenBelz

Okay, so it appears it did the job for the top record of the gallery, but I cannot confirm because both records are zero hours, so summing cannot be completed.  However, based on what it's doing for the 4/19/2022 records with the same Job # & Cost Code, the hours sum is not working.  Here is your expression with my actual column names so you have that information.  

With(
    {
        records: 
        AddColumns( 
            GroupBy(
                Sort(
                    Filter(
                        STHoursReportPrint, 
                        !IsBlank('Actual Service Completion Date')
                    ),
                        'Actual Service Completion Date'), 
                    "Actual_x0020_Service_x0020_Compl", 
                    "ServiceJobNumber", 
                    "ServiceCostCode",
                    "Service_x0020_Tech_x0020__x0023_0",
                    "Service_x0020_Tech_x0020__x0023_2",
                    "Data"
                ), 
            "Hours", 
            Sum(
                Data,
                Hours
            )
        )
    }, 
    ForAll(
        Sequence(
            CountRows(records)
        ),
            Patch(
                Last(
                    FirstN(
                        records, 
                        Value
                    )
                ), 
                    {rowNumber:Value}
            )
    )
)

And, here is a screencap of what's actually happening in my gallery with your expression.  As you can see, it's not fully grouping nor does it appear to be summing the hours.

I'm not sure if this matters, but let me give you the full context as best I can.  All of this data stems from 2 sources... one in a SharePoint list (actual work order entries), and the other from individual manual text input controls in PowerApps (not a form, which I have my reasons for).  Below is a screencap of the PowerApps screen with descriptions of what is happening.  I will put the gallery and button expressions below.

STReportGallery Expression

Sort(
    FirstN(
        Filter(
            Filter('Work Orders',
            ThisRecord.'Actual Service Completion Date' >= STReportStartDateSelector.SelectedDate && 
            ThisRecord.'Actual Service Completion Date' <= STReportEndDateSelector.SelectedDate),
        STReportServiceTechSelector.Selected.Title = 'Service Tech # 1'.Value || STReportServiceTechSelector.Selected.Title = 'Service Tech # 2'.Value),
        2000),
'Actual Service Completion Date', Ascending)

Run Printable Report Button OnSelect Expression

ClearCollect(STHoursReportPrint,STReportGallery.AllItems,
    Table(
            {'Actual Service Completion Date': STReportStartDateSelector_1.SelectedDate,
            'Service Job Number': 'STReportManualJob#'.Text,
            'Service Cost Code': STReportManualCC.Text,
            'Service Tech # 1 Hours': Value(STReportSTHoursConverted.Text)},
            {'Actual Service Completion Date': STReportStartDateSelector_2.SelectedDate,
            'Service Job Number': 'STReportManualJob#_1'.Text,
            'Service Cost Code': STReportManualCC_1.Text,
            'Service Tech # 1 Hours': Value(STReportSTHoursConverted_1.Text)},
            {'Actual Service Completion Date': STReportStartDateSelector_3.SelectedDate,
            'Service Job Number': 'STReportManualJob#_2'.Text,
            'Service Cost Code': STReportManualCC_2.Text,
            'Service Tech # 1 Hours': Value(STReportSTHoursConverted_2.Text)},
            {'Actual Service Completion Date': STReportStartDateSelector_4.SelectedDate,
            'Service Job Number': 'STReportManualJob#_3'.Text,
            'Service Cost Code': STReportManualCC_3.Text,
            'Service Tech # 1 Hours': Value(STReportSTHoursConverted_3.Text)},
            {'Actual Service Completion Date': STReportStartDateSelector_5.SelectedDate,
            'Service Job Number': 'STReportManualJob#_4'.Text,
            'Service Cost Code': STReportManualCC_4.Text,
            'Service Tech # 1 Hours': Value(STReportSTHoursConverted_4.Text)},
            {'Actual Service Completion Date': STReportStartDateSelector_6.SelectedDate,
            'Service Job Number': 'STReportManualJob#_5'.Text,
            'Service Cost Code': STReportManualCC_5.Text,
            'Service Tech # 1 Hours': Value(STReportSTHoursConverted_5.Text)},
            {'Actual Service Completion Date': STReportStartDateSelector_7.SelectedDate,
            'Service Job Number': 'STReportManualJob#_6'.Text,
            'Service Cost Code': STReportManualCC_6.Text,
            'Service Tech # 1 Hours': Value(STReportSTHoursConverted_6.Text)}
    )
);
Navigate(ServiceTechHoursReportPRINT)

As I said, not sure if all this matters, but maybe something in this process is being missed since I'm already grouping data in a previous step?

[forbudt4u]

@WarrenBelz 

Still no resolution but I decided to add a DataTable with the same expression in the items properties so I could view the table data.  When I actually look at the columns, I get an Error in the Hours Column that was added.  See the screencap below.  Not sure if this is part of the error, but it may be tied into the Sum not working.  Still doesn't explain why the 4/19 entries are not grouping together though.

  

[forbudt4u]

@WarrenBelz 

I'm glad it took a while for you to respond because it forced me to dig in finding out why this wasn't working.  The gist of the story is to keep it simple, stupid!  For some reason, I assumed that I needed to add in the columns in the grouping for the actual hours columns seeing that the respective Service Tech could be listed as the primary or the secondary service tech.  After going through countless attempts at fixing this I went back to your expression and took out the two columns I added on my own.  Once I did that... BAM, it worked.  

I totally see where I messed up in my assumption, so thank you once again for yet another simple solution.  

[WarrenBelz]

Hi @forbudt4u ,

I suspect time zone is in play for my response time (6:36am Wednesday here in Oz). Also Data Tables are not the best thing to be working with beyond displaying basic Tables.

[forbudt4u]

@WarrenBelz 

I wasn't trying to come across as impatient... just wanted to reiterate that it's probably best that I was able to figure out my own mistake rather than someone saving me (which makes it more likely to not make the mistake again).  

Your comment about data tables, are you referring to my mentioning I had inserted a data table in order to view the table itself, or do you believe there's a potential issue with the way I used the table expression in my Run Report Button OnSelect Property?  

[WarrenBelz]

HI @forbudt4u ,

No offence taken, just letting you know where I was in the world (Queensland, Australia). I am just not a fan of Data Tables when a Gallery can do all the same things with a little more effort, but a lot more functionality.

[forbudt4u]

@WarrenBelz

Okay, noted... 16 hrs ahead of me, could you possibly give me Wed's winning lottery numbers (if only time worked that way) 😆.  

On the Data Tables, I agree as I absolutely hate working with them and much prefer galleries!

Have a good day, mate!