Store multiple days from Date Range in individual data rows in SharePoint

[mseliot]

Hello, 

I am working on a scheduling APP. It has an option either Recurring Schedule or not. When Recurring Schedule is No, only one date picker shows up. When Recurring Schedule is Yes, there are options - a date range from and to along and days as shown below. The goal is 1) figure out what actual dates would be from the selection (date range and days - Mon - Sun) and 2) store them in SharePoint as an individual row.  

I have no clue where to start. I feel I need to make a collection, but any advice or help would be incredibly appreciated!

Thanks!

Eliot

[Akser]

Hi @mseliot, 

Have you seen this?

https://www.youtube.com/watch?v=yGvehOu-oZ8

[mseliot]

Hi @Akser Thank you for sharing. Yes, I checked the video and still working on it. Do you happen to know any resources to sort out dates from the Day selection (Mon-Sun) within the date range? For example, if a user selects 11/1/22-11/30/22 and Monday, Tuesday and Wednesday, the APP should know what dates they are to store in SP.

I searched extensively with no success 😥

Thanks!

[WarrenBelz]

Hi @mseliot ,

This should work between the dates - I tested it with two Date Pickers, so you will need to add your control names

With(
   {
      wDates: 
      ForAll(
         Sequence(
            DateDiff(
               dpFromDate.SelectedDate,
               dpToDate.SelectedDate,
               Days
            ) + 1
         ),
         {
            DayName: 
            With(
               {
                  wDay: 
                  DateAdd(
                     dpFromDate.SelectedDate,
                     Value - 1,
                     Days
                  )
               },
               If(
                  Weekday(
                     wDay,
                     Monday
                  ) < 6,
                  Text(
                     wDay,
                     "dddd dd/mm/yyyy"
                  )
               )
            )
         }
      )
   },
   ClearCollect(
      colDates,
      Filter(
         wDates,
         !IsBlank(DayName)
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[mseliot]

Thank you @WarrenBelz ! Where can I put this? OnSelect of Submit button or OnSuccess of the Form?

[WarrenBelz]

@mseliot ,

The code creates the collection you are after with a record for each week day between the two dates selected. If you want new records in SharePoint

With(
   {
      wDates: 
      ForAll(
         Sequence(
            DateDiff(
               dpFromDate.SelectedDate,
               dpToDate.SelectedDate,
               Days
            ) + 1
         ),
         {
            NewDay: 
            With(
               {
                  wDay: DateAdd(
                     dpFromDate.SelectedDate,
                     Value - 1,
                     Days
                  )
               },
               If(
                  Weekday(
                     wDay,
                     Monday
                  ) < 6,
                  wDay
               )
            )
         }
      )
   },
   With(
      {
         wActual: 
         Filter(
            wDates,
            !IsBlank(NewDay)
         )
      },
      Patch(
         SPListName,
         ForAll(
            wActual,
            {SPDateField: NewDay}
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[mseliot]

Thank you @WarrenBelz !

I added the codes below to OnSuccess property of the form and got the error message below. Any idea? 

In the patch statement, "Staff_Schedule" is the SP list name and "Date" is the date field name.

With(
{
wDates:
ForAll(
Sequence(
DateDiff(
DataCardFrom.SelectedDate,
DataCardTo.SelectedDate,
Days
) + 1
),
{
NewDay:
With(
{
wDay: DateAdd(
DataCardFrom.SelectedDate,
Value - 1,
Days
)
},
If(
Weekday(
wDay,
Monday
) < 6,
wDay
)
)
}
)
},
With(
{
wActual:
Filter(
wDates,
!IsBlank(NewDay)
)
},
Patch(
Staff_Schedule,
ForAll(
wActual,
{Date: NewDay}
)
)
)
)

[WarrenBelz]

@mseliot ,

Take a moment and look at the error - it is saying you have a compulsory field - so either patch to it or make in not required.

[WarrenBelz]

@mseliot ,

If you are not using the Title field for anything else

With(
   {
      wDates: 
      ForAll(
         Sequence(
            DateDiff(
               DataCardFrom.SelectedDate,
               DataCardTo.SelectedDate,
               Days
            ) + 1
         ),
         {
            NewDay: 
            With(
               {
                  wDay: 
                  DateAdd(
                     DataCardFrom.SelectedDate,
                     Value - 1,
                     Days
                  )
               },
               If(
                  Weekday(
                     wDay,
                     Monday
                  ) < 6,
                  wDay
               )
            )
         }
      )
   },
   With(
      {
         wActual: 
         Filter(
            wDates,
            !IsBlank(NewDay)
         )
      },
      Patch(
         Staff_Schedule,
         ForAll(
            wActual,
            {
               Date: NewDay,
               Title: " "
            }
         )
      )
   )
)

[mseliot]

Thank you @WarrenBelz !

This is the schedule screen.

If recurring schedule is checked in "No", there is only one date picker appears. This date picker has the SP field "Date", which is for the schedule date.

If recurring schedule is checked in "Yes", there are two date pickers (From and To) appears. This date pickers has the SP field "ScheFrom" and "ShceTo". Also there are multi-select checkboxes for day selection (Mon-Sun). Those checkboxes are housed in gallery within the datacard. I followed the instruction from https://www.youtube.com/watch?v=Vj8uP2zaMtA The SP field is "SchDays". 

Again, this APP should identify what dates would be from the date range and day selection. (e.g. if a user selects 12/1/22-12/15/22 and Sat and Sunday, the schedule for 12/3/22, 12/4/22, 12/10/22, 12/11/22 should be saved in the individual row.) 

I just checked your post. I used Title field as "Employee No". I added the patch statement as below. The error message went away but the SP still doesn't store the info correctly. 

Patch(
Staff_Schedule,
ForAll(
wActual,
{
Date: NewDay,
Title: DataCardValue44.Text
}
)