Answered

Gallery - Repeating the Last Row in gallery that contains full amount of rows from collection

(0) Share

Report

Posted on by kariseightytwo

374

I know the title is cryptic. Sorry. I'm really having a problem with this code. It's sort of doing what I want, but not well enough.

Here's a screenshot to start:

What the code is currently accomplishing is getting the total SUM of Hours for each month based on multiple dropdowns and criteria. What it boils down to is a table with more than one row for each date. Sometimes there may be as many as three rows per date.

Here's my problem. When you get to the yellow numbers in April, the code is duplicating the last sum of HOURS from the LAST non-blank Date. The thing is, in this case, there are three rows for April and two of them are blank. In this case, I need to go to the previous row where all the rows are populated.

Here's the portion of code that affects the estimated months:

Text(

Sum(
 Filter(
 colSelectedYearHoursFiltered,
 Hours > 0 && 

If(IsBlank(Select_Record_Type_1.Selected.Value),
 Hours_Type = Hours_Type,

If(
Select_Record_Type_1.Selected.Value = "Employee",
 Hours_Type = "Employee" ||
 Hours_Type = "European",

If(
 
 Select_Record_Type_1.Selected.Value = "Contractor",
 Hours_Type = "Contractor" ||
 Hours_Type = "US Contractor" ||
 Hours_Type = "CAN Contractor" ||
 Hours_Type = "CA & US 3rd Party Bulk" ||
 Hours_Type = "FBO"

))) 

&&
 Hours_Date = Last(
 Filter(
 colSelectedYearHoursFiltered, 
 Hours > 0 

&&

If(IsBlank(Select_Record_Type_1.Selected.Value),
 Hours_Type = Hours_Type,

If(
Select_Record_Type_1.Selected.Value = "Employee",
 Hours_Type = "Employee" ||
 Hours_Type = "European",

If(
 
 Select_Record_Type_1.Selected.Value = "Contractor",
 Hours_Type = "Contractor" ||
 Hours_Type = "US Contractor" ||
 Hours_Type = "CAN Contractor" ||
 Hours_Type = "CA & US 3rd Party Bulk" ||
 Hours_Type = "FBO"

)))
 )
 ).Hours_Date
 
), Hours), "#,###.00")

I've tried adding With variables that count the number of rows and then compare vs the number of expected rows, but it doesn't seem to be working.

For what it's worth, I've tried applying other labels in my gallery too that don't seem to help:

I'm open to suggestions if anyone can help.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

kariseightytwo 374 on at

Like (0)

Report
Copy link

Link copied!
My original code appears to be redundant? Here's the shortened version that's still giving me those results:

Text( Sum( Filter(colSelectedYearHoursFiltered, Hours_Date = Last( Filter(colSelectedYearHoursFiltered, Hours > 0 && If(IsBlank(Select_Record_Type_1.Selected.Value), Hours_Type = Hours_Type, If(Select_Record_Type_1.Selected.Value = "Employee", Hours_Type = "Employee" || Hours_Type = "European", If(Select_Record_Type_1.Selected.Value = "Contractor", Hours_Type = "Contractor" || Hours_Type = "US Contractor" || Hours_Type = "CAN Contractor" || Hours_Type = "CA & US 3rd Party Bulk" || Hours_Type = "FBO" )))) ).Hours_Date ), Hours), "#,###.00")

Was this reply helpful? Yes No
kariseightytwo 374 on at

Like (0)

Report
Copy link

Link copied!

I have been banging on my head on a wall for days with this, and I'm getting desperate.

I have added a column in my collection for "ExpectedRows".
Even though there may be two out of three rows that meet criteria, they will all have the same value in "ExpectedRows".
If in the picture above where the yellow starts in April, it needs to be showing 417,100.90 for the remainder because there are not enough rows in April to match the value in "ExpectedRows"

Was this reply helpful? Yes No
Verified answer

kariseightytwo 374 on at

Like (0)

Report
Copy link

Link copied!
Okay, I finally, finally, finally created a solution.
I created a new collection that gave me the information I needed and removed the unwanted rows:
ClearCollect( colFilteredCount, AddColumns( GroupBy( colSelectedYearHoursFiltered, "field_0", "group" ), "CountRows", CountRows(group), "ExpectedRows", First(group).ExpectedRows, "Hours", Sum(group, Hours) ) ); RemoveIf( colFilteredCount, CountRows <> ExpectedRows )

Was this reply helpful? Yes No