Bank that Cash. Gallery Group by Date and Summarise. Daily Sequence between start and end date and Patch multiple values to data source.

 

It took me three evenings, but I did it at the end.

Learnt new formulae, though I still think the performance can be improved.

Lots of thanks to Reza Dorrani for nested gallery example.

Group By in Power Apps Gallery with Large SharePoint Lists - YouTube

and WiZey for

Solved: Collect all dates within a date range - Power Platform Community (microsoft.com)

ForAll(
  Sequence(
    DateDiff(StartDate, EndDate)
  ),
  DateAdd(StartDate, Value, Days)
)

 I also found similar solution is MS Documentation About Sequence() function

Sequence function in Power Apps - Power Platform | Microsoft Learn

This  helped me a lot to come up with setting a sequence of the dates between first and last date in array.

Multi Select Checkbox in PowerApps - YouTube

by taik18 - Mohammed Adnan was a great help with collecting checked items

Bulk Patch was copied from  Create or update bulk records in Power Apps - Power Apps | Microsoft Learn

Department Dropdown 

OnChange 

Also on Screen Visible

ClearCollect(colSub,

Filter(

SalesHeader,'CashBanked?'="N"&&

(Cash>0||Cash<0)&&DepartmentID=Value(Dropdown1.Selected.Id)));

ClearCollect(colCashDates,

ForAll(Sequence(

DateDiff(

First(Sort(

Filter(SalesHeader,'CashBanked?'="N"&&

(Cash>0||Cash<0)&&

DepartmentID=Value(Dropdown1.Selected.Id)),

Date,SortOrder.Ascending)).Date,

First(Sort(

Filter(SalesHeader,'CashBanked?'="N"&&

(Cash>0||Cash<0)&&

DepartmentID=Value(Dropdown1.Selected.Id)),

Date,SortOrder.Descending)).Date+1)),

DateAdd(First(

Sort(

Filter(

SalesHeader,'CashBanked?'="N"&&

(Cash>0||Cash<0)&&

DepartmentID=Value(Dropdown1.Selected.Id)),

Date,SortOrder.Ascending)).Date-1,

Value)))

OnSelect

Reset(cbAll)

Submit

OnSelect

ForAll(colTemp,

Patch ( [@SalesHeader],

LookUp([@SalesHeader],ID=colTemp[@ID]),

{'CashBanked?':"Y", CashBankedDate:DatePicker1.SelectedDate}));

Clear(colTemp);

Clear(colTempR);

Reset(cbAll);

ClearCollect(colSub,Filter(SalesHeader,'CashBanked?'="N"&&

(Cash>0||Cash<0)&&

DepartmentID=Value(Dropdown1.Selected.Id)));

ClearCollect(colCashDates,ForAll(

Sequence(DateDiff(First(

Sort(Filter(SalesHeader,'CashBanked?'="N"&&

(Cash>0||Cash<0)&&

DepartmentID=Value(Dropdown1.Selected.Id)),Date,SortOrder.Ascending)).Date,

First(Sort(Filter(SalesHeader,'CashBanked?'="N"&&

(Cash>0||Cash<0)&&

DepartmentID=Value(Dropdown1.Selected.Id)),Date,SortOrder.Descending)).Date+1)),

DateAdd(First(Sort(Filter(SalesHeader,'CashBanked?'="N"&&(Cash>0||Cash<0)&&

DepartmentID=Value(Dropdown1.Selected.Id)),Date,SortOrder.Ascending)).Date-1,Value)))

Total Cash Not Banked

Text

Sum(ForAll(Filter(

SalesHeader,'CashBanked?'="N"&&

DepartmentID=Value(Dropdown1.Selected.Id)),Cash),Value)

Total Bank Me

Text

Sum(colTemp,Cash)

   

CheckBox Level1 Check All

OnCheck

RemoveIf(colTemp, ID=Blank());Clear(colTempR);

Collect(colTempR,colSub,{Value:1});

RemoveIf(colTempR, ID in Filter(colTemp,ID).ID);

    Collect(colTemp, colTempR)

OnUncheck

RemoveIf(colTemp, ID=Blank());Clear(colTempR);

Collect(colTempR,colSub,{Value:1});

RemoveIf(colTemp, ID in Filter(colTempR,ID).ID)

   

Flexible Gallery 1st Level Group by Date:

Items 

colCashDates

Button Grouped Transactions by Dates

OnSelect

If(varExp=DateValue(Self.Text),

Set(varExp,Blank()),

Set(varExp,DateValue(Self.Text)))

Total Day Cash Not Banked

Sum(ForAll(Filter(SalesHeader,'CashBanked?'="N"&&

Date=ThisItem.Value&&DepartmentID=Value(Dropdown1.Selected.Id)),Cash),Value)

Total Day Cash Bank Me

Sum(Filter(colTemp,Date=ThisItem.Value),Cash)

CheckBox Level2 Check All with same date

OnCheck

RemoveIf(colTemp, ID=Blank());Clear(colTempR);

Collect(colTempR,Filter(

colSub,DateValue(Date)=DateValue(ThisItem.Value)),{Value:1});

RemoveIf(colTempR, ID in Filter(colTemp,ID).ID);

    Collect(colTemp, colTempR)

OnUncheck

RemoveIf(colTemp, ID=Blank());Clear(colTempR);

Collect(colTempR,Filter(

colSub,DateValue(Date)=DateValue(ThisItem.Value)),{Value:1});

RemoveIf(colTemp, ID in Filter(colTempR,ID).ID)

Default

cbAll.Value

All controls visibility:

CountA(Filter(SalesHeader,'CashBanked?'="N"&&

Date=ThisItem.Value&&(Cash>0||Cash<0)&&

DepartmentID=Value(Dropdown1.Selected.Id)).ID)>0

Flexible Gallery 2nd Level Individual Transactions:

Items 

Filter(colSub,Date=DateValue(btnDateGroup.Text))

Height

CountA(Filter(SalesHeader,'CashBanked?'="N"&&

Date=ThisItem.Value&&(Cash>0||Cash<0)&&

DepartmentID=Value(Dropdown1.Selected.Id)).Title)*(Label6_16.Height+5)

Transaction Cash Bank Me

If(Value(cbID.Value)=1,Value(ThisItem.Cash),0)

Transaction Cash Not Banked

ThisItem.Cash

CheckBox Level3  

OnCheck

If(CountA(Filter(

colTemp, ID= ThisItem.ID).ID)>0,"",

Collect(colTemp,Filter(colSub,ID=ThisItem.ID),{Value:1});

RemoveIf(colTemp, ID=Blank()))

OnUncheck

ClearCollect(colTempR,{ID:ThisItem.ID, Value:1});

RemoveIf(colTemp, ID in Filter(colTempR,ID).ID);

RemoveIf(colTemp, ID=Blank())

Default

If(cbDay.Value=true,true,

If(LookUp(colTemp,ID=ThisItem.ID,Value)=1,true,false

))

If you wish to say thank you please donate to the charity I use to work for.

Donate here