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)
Solved! Go to 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.
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)
@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.
User | Count |
---|---|
135 | |
132 | |
79 | |
75 | |
71 |
User | Count |
---|---|
210 | |
197 | |
69 | |
62 | |
53 |