Date Calculation Incorrect During Patch() Formulas in Published Mode

[gymcode]

I have a Patch() function that create 1 parent 'Reservation' record, and multiple child 'Reservation Details' records, and it skips weekends and a collection of holiday dates.

When I am executed Patch() function in Editing mode, both 'Reservation and 'Reservation Details' records were created on the correct days (weekdays).

However, when the app is published, 'Reservation Details' records created there was incorrect (1 day behind).

Scenario

- FromDate_Datepicker : Mon (05 Feb 2024)
- ToDate_Datepicker = Sun (11 Feb 2024)

	05 Feb	06 Feb	07 Feb	08 Feb	09 Feb	10 Feb	11 Feb
	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Editing Mode	Created	Created	Created	Created	Created	Skipped	Skipped
Published Mode	Skipped	Created	Created	Created	Created	Created	Skipped
Published Mode is 1 day behind	*Sun	*Mon	*Tue	*Wed	*Thu	*Fri	*Sat

Based on the results above, Published Mode is 1 day behind Editing Mode.

Question: May I know how can I resolve this, and is a code change to fix this possible?

Thank you.
_____________________________________________________________________________________

Below is my full Patch() function:

//Creates 1x parent 'Reservation' record
If(
    BookingType_Self_Cb.Checked,
    ClearCollect(
        ReservationRecord,
        Patch(
            Reservations,
            Defaults(Reservations),
            {
                'Start Date': FromDate_Dp.Value,
                'End Date': ToDate_Dp.Value,
                'Reserved For': 'Reserved For (Reservations)'.Employee,
                Facility: Building_Ddl.Selected,
                Area: Area_Ddl.Selected,
                'Employee Name': Self_Section_EmployeeName_Ddl.Selected,
                'Reserved Desk': Self_Section_Seat_Ddl.Selected,
                'Booked By': Self_Section_EmployeeName_Ddl.Selected
            }
        )
    )
);
// Store created parent 'Reservation' record in a variable 'newReservationRecord'
Set(
    newReservationRecord,
    First(ReservationRecord)
);

//Create 'Reservation Details' record for each date and associated it with 'Reservation' record created by 'Reservation Details'.'Reservation'
//Only create records if date matches weekdayt dates in WeekdayDates collection
Patch(
    'Reservation Details',
    ForAll(
        Sequence(
            DateDiff(
                FromDate_Dp.Value,
                ToDate_Dp.Value,
                TimeUnit.Days
            ) + 1
        ),
        With(
            {
                _Date: DateAdd(
                    FromDate_Dp.Value,
                    Value - 1,
                    TimeUnit.Days
                )
            },
            If(
                _Date in WeekdayDates,
                {
                    Date: _Date,
                    Reservation: newReservationRecord,
                    Name: newReservationRecord.'Reservation ID' & "_" & _Date,
                    'Reserved Desk': newReservationRecord.'Reserved Desk'
                }
            )
        )
    )
);

 
- 'Reservations'.'Start Date' is correct
- 'Reservations'.'End Date' is correct
- 'Reservation Details'.'Date' is wrong

[gymcode]

I have also checked that the values in 'WeekdayDates' collection indeed do not contain weekend dates, so it is a very weird situation.

[mmbr1606]

is there a chance that it is an issue with the time zone settings?

[gymcode]

Highly probable.

I'm in GMT+8 timezone, and my user personal options, I have set it to GMT+8 as well.

However, when I go to Settings > Administration > System Settings > Formats, I see that the 'Time' displayed is not matching against my local time.

I'm not sure if this is related to the issue too...

[gymcode]

I'm aware that CRM DB stores their datetime in UTC format.

Is possible, that Editing Mode follows my timezone, but when it is Published Mode, it is actually running in CRM's database, and for UTC timing, against my GMT+8, it will be 1 day prior.

[gymcode]

Hi @mmbr1606 , Hi @VauBee , Hi @WarrenBelz ,

I have narrowed the issue to two scenarios, of different behaviour when filtering by each collection.

Firstly, let me list the methods of creating my weekday dates collection, and public holiday dates collection.

1. varDateRange formula

// Generate a one-column table of all dates between start date & end date

varDateRange: ForAll(
    Sequence(ToDate_Dp.Value - FromDate_Dp.Value + 1),
    FromDate_Dp.Value + Value - 1
)​

2. WeekdayDatesCol formula

ClearCollect(
    WeekdayDatesCol,
    Filter(
        varDateRange, 
        Weekday(Value) in [ 2, 3, 4, 5, 6 ] 
    ) 
);​

3. PublicHolidayDatesCol formula

ClearCollect( 
    PublicHolidayDatesCol, 
    Filter( 
        varDateRange, 
        Value in Filter( 
            'Service Calendars', 'Service Calendars (Views)'.'Active Service Calendars' 
        ).Date
    ) 
);​

Next are 2 formulas that I want to combine, but unable to because there is DateTime difference when processing each collection, causing an issue where it needs to parse in different date values in order for the correct date to be displayed in the created record.

WeekdayDatesCol

//Record created if _Date FOUND in WeekdayDatesCol.
//Need to parse _Date-1 for correct date value in created record.
If(
    (_Date in WeekdayDatesCol),
    {
        Date: _Date-1
    }
);​

PublicHolidayDatesCol

//Record created if _Date NOT FOUND in PublicHolidayDatesCol
//_Date can be parsed directly to created record.

If(
    Not(_Date in PublicHolidayDatesCol),
    {
        Date: _Date
    }
);​

I know it may not be a root cause solution, but is it possible to come up with a workaround to combine the 2 formulas above?

Thank you.