ForAll Dates Between Range

[Delid4ve]

Has anyone got a working solution / workaround to the following:

Add row to SQL table for each date between a range (selected from 2 x date pickers)

[Mike8]

Hello Delid4ve,

I would use Flow.
Trigger --> Powerapps
Action --> Execute a SQL query (Ask In Powerapps)

Example:
3 Datepickers.

Button1.OnSelect = 
Collect(Collection1,{ID:CountRows(Collection1)+1,RandomColumn:"ABC",Date:DatePicker1.SelectedDate})

Gallery1.Items= 
Filter(Collection1,Date>DatePickerStartDate.SelectedDate&&Date<DatePickerEndDate.SelectedDate)

Label1.Text= 
"INSERT INTO dbo.TableName (ColumnName1,ColumnName2)
VALUES "&Concat(Filter(Collection1,Date>DatePickerStartDate.SelectedDate&&Date<DatePickerEndDate.SelectedDate),"("&RandomColumn&","&Text(Date)&")"&If(!(ID=Last(Collection1).ID),","))

Button2.OnSelect = FlowName.Run(Label1.Text)

[Delid4ve · Accepted solution]

@Mike8 i wanted to avoid flow and other external sources as MS still havent addressed the account issue (basically if you have a connector like sharepoint it should use the specified credentials and not the user's credentials - a choice would be best)

I have found a workaround myself using only powerapps (as this is for staff holidays i dont need a large array):

Clear(Numbers);
Collect(Numbers,{Value:0});
Collect(Numbers,{Value:1});
Collect(Numbers,{Value:2});
Collect(Numbers,{Value:3});
Collect(Numbers,{Value:4});
Collect(Numbers,{Value:5});
Collect(Numbers,{Value:6});
Collect(Numbers,{Value:7});
Collect(Numbers,{Value:8});
Collect(Numbers,{Value:9});
Collect(Numbers,{Value:10});
Collect(Numbers,{Value:11});
Collect(Numbers,{Value:12});
Collect(Numbers,{Value:13});
Clear(Temp1);
ForAll(Filter(Numbers,Value<=DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate)),Collect(Temp1,{Value: DateTimeValue(Text(DatePicker1.SelectedDate + Value,DateTimeFormat.LongDateTime24))}))
ForAll(Filter(Temp1,Weekday(Value)<>1 And Weekday(Value)<>7),Patch('[dbo].[HR_AnnualLeave]',{Type:1,LeaveDate: DateTimeValue(Text(Value,DateTimeFormat.LongDateTime24)),Employee:Var_User,RequestedDate:Now(),Signature: PenInput1.Image,UserCancelled:0,CancelledDate:0})))

A little bit messy using a couple of temp tables but it works, and also excludes weekdays from the patch.