cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

ForAll Dates Between Range

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: ForAll Dates Between Range

@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.

2 REPLIES 2
Mike8
Level 10

Re: ForAll Dates Between Range

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",DateSmiley Very HappyatePicker1.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)

Highlighted
Super User
Super User

Re: ForAll Dates Between Range

@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.