Assign value to all dates in Sharepoint list that are between 2 dates

(0) Share

Report

Posted on by CiaronWatts

HI all,

New to PowerApps and have hit a block that I can't find a solution for.

I have a form with a start date picker, and an end date picker, as well as a number slider.

I also have a different Sharepoint list with a range of dates on it (not the datasource for the form).

Finally I have a submit button.

What I want the submit button to do OnSubmit is to collect all the dates between the start date and end dates, find them within the Sharepoint list (in a column called Date), and add the resulkt of the number slider to all of those dates on another column (call it Number).

i.e - start date = 01/01/2020

end date = 05/01/2020

slider = 15

all date records in the sharepoint list >= StartDate, <= EndDate have the number 15 in their respective Number column.

All help is appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

CiaronWatts 6 on at

Like
a
(0)

Report
Copy link

Link copied!

I have managed to work it out part-way, but the below formula is only writing the value to the first date in the collection:

ClearCollect(
DateRange,
AddColumns(
FirstN(
[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29],
DateDiff(STARTDATE.SelectedDate, ENDDATE.SelectedDate, Days) + 1),
"Day",
"Day " & (Value + 1),
"Date",
DateAdd(STARTDATE.SelectedDate, Value, Days)
));ForAll(DateRange,Patch(SHAREPOINTLIST,LookUp(SHAREPOINTLIST,DATECOLUMN in DateRange.Date),{NUMBERCOLUMN: SLIDER.Value}))

Everything in it works, the collection returns the dates I expected, and the slider value is patched correctly, but only to the FIRST item from the collection. I assume this is due to using Lookup, but Filter doesn't seem to work.

Any ideas?

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @CiaronWatts ,

Do you want to filter SP list to find the records with the Date in range of the two Date Pickers, and fill the “Number” column with the value of Slider?
The data type of the Date column is Date and Time, am I right?
The table of collection you created contains dates less than 31 so I suppose it is not the proper method.

To achieve your goal, the formula of Submit could be modified to:

UpdateIf(SHAREPOINTLIST, DATECOLUMN >=STARTDATE && Created <=ENDDATE,{NUMBERCOLUMN: SLIDER.Value})

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it more.

Was this reply helpful? Yes No
CiaronWatts 6 on at

Like
a
(0)

Report
Copy link

Link copied!

That works a charm, thank you for your help!

Was this reply helpful? Yes No