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

[WarrenBelz]

Hi @mseliot ,

I must admit I misread (or did not read it fully) your post and thought you were after weekdays, hence the code. The rest sort of got lost in the error until you got it working. If you look at the code there is no reference to the check boxes, hence it will never work picking specific days. What you are after is (putting it mildly) complex and the code below should work and did so on a brief test I gave it. Three items you need to replace DayName with the Text field in your checkbox gallery (the week day displayed), galDays with your gallery name and ckDay with the checkbox name.

With(
   {
      wDates: 
      ForAll(
         Sequence(
            DateDiff(
               dpFromDate.SelectedDate,
               dpToDate.SelectedDate,
               Days
            ) + 1
         ),
         {
            wNewDay: 
            With(
               {
                  wDay: 
                  DateAdd(
                     dpFromDate.SelectedDate,
                     Value - 1,
                     Days
                  ),
                  wSel:
                  Filter(
                     galDays.AllItems,
                     ckDay.Value
                  )
               },
               With(
                  {
                     wDayNo: 
                     Weekday(
                        wDay,
                        Monday
                     )
                  },
                  If(
                     (wDayNo = 1 && "Mon" in wSel.DayName) || 
                     (wDayNo = 2 && "Tue" in wSel.DayName) || 
                     (wDayNo = 3 && "Wed" in wSel.DayName) || 
                     (wDayNo = 4 && "Thu" in wSel.DayName) || 
                     (wDayNo = 5 && "Fri" in wSel.DayName) || 
                     (wDayNo = 6 && "Sat" in wSel.DayName) || 
                     (wDayNo = 7 && "Sun" in wSel.DayName) || 
                     CountRows(wSel) = 0,
                     wDay
                  )
               )
            )
         }
      )
   },
   With(
      {
         wActual: 
         Filter(
            wDates,
            !IsBlank(wNewDay)
         )
      },
      Patch(
         Staff_Schedule,
         ForAll(
            wActual,
            {
               DateTest: wNewDay,
               Title: DataCardValue44.Text
            }
         )
      )
   )
)

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 so much @WarrenBelz !!

I encountered the error messages below.. Any idea?

[WarrenBelz]

@mseliot ,

Can you please post the full code you are using (in Text) and confirm you have replaced the items mentioned with your values.

[mseliot]

Here you go! Tons of Thanks!

----------------------------------

With(
{
wDates: ForAll(
Sequence(
DateDiff(
DataCardFrom.SelectedDate,
DataCardTo.SelectedDate,
Days
) + 1
),
{
wNewDay: With(
{
wDay: DateAdd(
DataCardFrom.SelectedDate,
Value - 1,
Days
)
},
wSel:
Filter(
galScheDays.AllItems,
chScheDays.Value
)
},
With(
{
wDayNo:
Weekday(
wDay,
Monday
)
},
If(
(wDayNo = 1 && "Mon" in wSel.Monday) ||
(wDayNo = 2 && "Tue" in wSel.Tuesday) ||
(wDayNo = 3 && "Wed" in wSel.Wednesday) ||
(wDayNo = 4 && "Thu" in wSel.Thursday) ||
(wDayNo = 5 && "Fri" in wSel.Friday) ||
(wDayNo = 6 && "Sat" in wSel.Saturday) ||
(wDayNo = 7 && "Sun" in wSel.Sunday) ||
CountRows(wSel) = 0,
wDay
)
)
)
}
)
},
With(
{
wActual: Filter(
wDates,
!IsBlank(NewDay)
)
},
Patch(
Staff_Schedule,
ForAll(
wActual,
{
Date: NewDay,
Title: DataCardValue44.Text
}
)
)
)
)

[WarrenBelz]

@mseliot ,

It is a bit too big to completely compare and I cannot validate with your values, but a couple of things I noticed were NewDay instead of wNewDay and an added { bracket between wDay and wSel. I have transposed your values into my code (which I assumed you would do), which works with my values and it should work for you.

With(
   {
      wDates: 
      ForAll(
         Sequence(
            DateDiff(
               DataCardFrom.SelectedDate,
               DataCardTo.SelectedDate,
               Days
            ) + 1
         ),
         {
            wNewDay: 
            With(
               {
                  wDay: 
                  DateAdd(
                     DataCardFrom.SelectedDate,
                     Value - 1,
                     Days
                  ),
                  wSel:
                  Filter(
                     galScheDays.AllItems,
                     chScheDays.Value
                  )
               },
               With(
                  {
                     wDayNo: 
                     Weekday(
                        wDay,
                        Monday
                     )
                  },
                  If(
                     (wDayNo = 1 && "Mon" in wSel.DayName) || 
                     (wDayNo = 2 && "Tue" in wSel.DayName) || 
                     (wDayNo = 3 && "Wed" in wSel.DayName) || 
                     (wDayNo = 4 && "Thu" in wSel.DayName) || 
                     (wDayNo = 5 && "Fri" in wSel.DayName) || 
                     (wDayNo = 6 && "Sat" in wSel.DayName) || 
                     (wDayNo = 7 && "Sun" in wSel.DayName) || 
                     CountRows(wSel) = 0,
                     wDay
                  )
               )
            )
         }
      )
   },
   With(
      {
         wActual: 
         Filter(
            wDates,
            !IsBlank(wNewDay)
         )
      },
      Patch(
         Staff_Schedule,
         ForAll(
            wActual,
            {
               DateTest: wNewDay,
               Title: DataCardValue44.Text
            }
         )
      )
   )
)

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

[akbarmahfuzalam]

@WarrenBelz 

I always refer your formula and code which is very easy and understandable
I tried replicate your formula here but the data storing here is a duplicate entry wherein I was looking to get 2 different date which i have used in datePicker
I selected startdate as 6 Feb and enddate as 9 feb
but when i save the date it giving me same data data twice
Can i get the data like one is start date as 6th feb and
2nd as 7th Feb

and 8th Feb

and last 9th Feb
Formula used as below

With({wDates:ForAll(Sequence(DateDiff(StartDate.SelectedDate,EndDate.SelectedDate,Days)+1),{NewDay:With({wDay:DateAdd(StartDate.SelectedDate,Value-1,Days)},If(Weekday(wDay,Monday)<6,wDay))})},With({wActual:Filter(wDates,!IsBlank(NewDay))},Patch(ASCELeaveManagement,ForAll(wActual,{Title:ddEMPName.Selected.Result,EMPLOYEE_EID:ddEMPEID.Selected.Result,SupervisorName:ddTeam.Selected.Result,START_DATE:StartDate.SelectedDate,END_DATE:EndDate.SelectedDate,LEAVE_TYPE:ddLeaveType.Selected.Value,LeavesComments:txtComments.Text}))));Set(varResetdata, false );Set(varResetdata, true );

[WarrenBelz]

@akbarmahfuzalam ,

This is an old closed post and unfortunately, I do not have the time presently to try and debug that as I would have to build a model. Please post on a new thread and to assist the understanding, it helps enormously to post it in a Text Box (the </> icon at the top) properly parsed like this

With(
   {
      wDates:
      ForAll(
         Sequence(
            DateDiff(
               StartDate.SelectedDate,
               EndDate.SelectedDate,
               Days
            )+1
         ),
         {
            NewDay:
            With(
               {
                  wDay:
                  DateAdd(
                     StartDate.SelectedDate,
                     Value-1,
                     Days
                  )
               },
               If(
                  Weekday(
                     wDay,
                     Monday
                  ) < 6,
                  wDay
               )
            )
         }
      )
   },
   With(
      {
         wActual:
         Filter(
            wDates,
            !IsBlank(NewDay)
         )
      },
      Patch(
         ASCELeaveManagement,
         ForAll(
            wActual,
            {
               Title: ddEMPName.Selected.Result,
               EMPLOYEE_EID: ddEMPEID.Selected.Result,
               SupervisorName: ddTeam.Selected.Result,
               START_DATE: StartDate.SelectedDate,
               END_DATE: EndDate.SelectedDate,
               LEAVE_TYPE: ddLeaveType.Selected.Value,
               LeavesComments: txtComments.Text
            }
         )
      )
   )
);

[akbarmahfuzalam]

@WarrenBelz 

I have managed to get the desired result. But nevertheless thanks for your effort.