cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How to prevent users from registering overlapping hours

Hi everyone,

 

I am building an app where users register time and I want to prevent the user from registering overlapping hours. For example, I register hours from 07:00 am to 15:00 pm, now the user is not allowed to register any hours within the period of time that already is posted. I have found one solution that works for the first registered hours by using the function under, but if I register two or more separate registrations then only the first one will trigger the rule. 

 

 

 

 

If(
        /*If starttime is earlier than existing time*/
        Time(
            Value(FromHour_12.SelectedText.Value),
            Value(FromMinute_12.SelectedText.Value),
            0
        ) <= LookUp(
            CalendarEventsGallery1.AllItems,
            !IsBlank(CalendarEventsGallery1.AllItems),
            TimeValue(StartTime)
        ),/*Then*/
        Time(
            Value(FromHour_12.SelectedText.Value),
            Value(FromMinute_12.SelectedText.Value),
            0
        ) <= LookUp(
            CalendarEventsGallery1.AllItems,
            !IsBlank(CalendarEventsGallery1.AllItems),
            TimeValue(StartTime)
        ) && Time(
            Value(ToHour_12.SelectedText.Value),
            Value(ToMinute_12.SelectedText.Value),
            0
        ) <= LookUp(
            CalendarEventsGallery1.AllItems,
            !IsBlank(CalendarEventsGallery1.AllItems),
            TimeValue(StartTime)
        )/*If not*/
,
        Time(
            Value(FromHour_12.SelectedText.Value),
            Value(FromMinute_12.SelectedText.Value),
            0
        ) >= LookUp(
            CalendarEventsGallery1.AllItems,
            !IsBlank(CalendarEventsGallery1.AllItems),
            TimeValue(EndTime)
        ),
        Time(
            Value(FromHour_12.SelectedText.Value),
            Value(FromMinute_12.SelectedText.Value),
            0
        ) >= LookUp(
            CalendarEventsGallery1.AllItems,
            !IsBlank(CalendarEventsGallery1.AllItems),
            TimeValue(EndTime)
        ) && Time(
            Value(ToHour_12.SelectedText.Value),
            Value(ToMinute_12.SelectedText.Value),
            0
        ) >= LookUp(
            CalendarEventsGallery1.AllItems,
            !IsBlank(CalendarEventsGallery1.AllItems),
            TimeValue(EndTime)
        )
    ),Edit,Disabled

 

 

 

 

As you can see from the pictures below, I have to registrations, and now I am not supposed to be able to register hours between 15:30 and 18:30, but "Ferdig" is not disabled. 

 

CathrineB_0-1596534224247.png

CathrineB_1-1596534300084.png

 

Thank you,

 

Best,

 

Cathrine

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: How to prevent users from registering overlapping hours

Hi @v-bofeng-msft,

 

Thank you for your helpful reply. I was able to achieve a satisfying result by making a few changes to your suggestion. I used the CountIf function instead of CountRows to check if any of the rows contains {Value:1} and added another link in the syntax preventing the user from submitting hours if they selected hours before and after as well. 

 

 If(CountIf(
        ForAll(
            CalendarEventsGallery1_1.AllItems,
            If(
                (Time(
                    Value(FromHour_15.SelectedText.Value),
                    Value(FromMinute_15.SelectedText.Value),
                    0
                ) > TimeValue(StartTime) && Time(
                    Value(FromHour_15.SelectedText.Value),
                    Value(FromMinute_15.SelectedText.Value),
                    0
                ) < TimeValue(EndTime)) Or (Time(
                    Value(ToHour_15.SelectedText.Value),
                    Value(ToMinute_15.SelectedText.Value),
                    0
                ) > TimeValue(StartTime) && Time(
                    Value(ToHour_15.SelectedText.Value),
                    Value(ToMinute_15.SelectedText.Value),
                    0
                ) < TimeValue(EndTime)) Or (Time(
                    Value(FromHour_15.SelectedText.Value),
                    Value(FromMinute_15.SelectedText.Value),
                    0
                ) < TimeValue(StartTime) && Time(
                    Value(ToHour_15.SelectedText.Value),
                    Value(ToMinute_15.SelectedText.Value),
                    0
                ) > TimeValue(EndTime)),
                {Value: 1},
                {Value: 0}
            )
        ),
        Value > 0
    ) >= 1,
    Disabled,
    Edit
)

 

Best,

 

Cathrine

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: How to prevent users from registering overlapping hours

Hi @CathrineB :

Do you hope that as long as the start or end time selected by the user is between the start and end time of any record in CalendarEventsGallery1.AllItems, the record cannot be submitted?

My method is to traverse all the records in CalendarEventsGallery1.AllItems:
1. If the start or end time selected by the user is between the start and end time of a record, a record {Value:1} will be returned.
2. Calculate the number of records returned. If the number of records returned is greater than 1, the condition is not met (disabled the'Ferding')

Please try this code:

 

If(
CountRows(
ForALL(CalendarEventsGallery1.AllItems,
       If(
          (Time(Value(FromHour_12.SelectedText.Value),Value(FromMinute_12.SelectedText.Value),0)<TimeValue(StartTime) && Time(Value(FromHour_12.SelectedText.Value),Value(FromMinute_12.SelectedText.Value),0)>TimeValue(EndTime)) 
          Or 
          (Time(Value(ToHour_12.SelectedText.Value),Value(ToMinute_12.SelectedText.Value),0)<TimeValue(StartTime) && Time(Value(ToHour_12.SelectedText.Value),Value(ToMinute_12.SelectedText.Value),0)>TimeValue(EndTime)),
          {Value:1}
          )
       ))>=1,Disabled,Edit)

 

If the solution cannot solve your problem, can you provide more specific requirements and details about your app.(Especially the data structure of CalendarEventsGallery1.AllItemss)

Best Regards,

Bof

Highlighted
Frequent Visitor

Re: How to prevent users from registering overlapping hours

Hi @v-bofeng-msft,

 

Thank you for your helpful reply. I was able to achieve a satisfying result by making a few changes to your suggestion. I used the CountIf function instead of CountRows to check if any of the rows contains {Value:1} and added another link in the syntax preventing the user from submitting hours if they selected hours before and after as well. 

 

 If(CountIf(
        ForAll(
            CalendarEventsGallery1_1.AllItems,
            If(
                (Time(
                    Value(FromHour_15.SelectedText.Value),
                    Value(FromMinute_15.SelectedText.Value),
                    0
                ) > TimeValue(StartTime) && Time(
                    Value(FromHour_15.SelectedText.Value),
                    Value(FromMinute_15.SelectedText.Value),
                    0
                ) < TimeValue(EndTime)) Or (Time(
                    Value(ToHour_15.SelectedText.Value),
                    Value(ToMinute_15.SelectedText.Value),
                    0
                ) > TimeValue(StartTime) && Time(
                    Value(ToHour_15.SelectedText.Value),
                    Value(ToMinute_15.SelectedText.Value),
                    0
                ) < TimeValue(EndTime)) Or (Time(
                    Value(FromHour_15.SelectedText.Value),
                    Value(FromMinute_15.SelectedText.Value),
                    0
                ) < TimeValue(StartTime) && Time(
                    Value(ToHour_15.SelectedText.Value),
                    Value(ToMinute_15.SelectedText.Value),
                    0
                ) > TimeValue(EndTime)),
                {Value: 1},
                {Value: 0}
            )
        ),
        Value > 0
    ) >= 1,
    Disabled,
    Edit
)

 

Best,

 

Cathrine

View solution in original post

Highlighted
Helper II
Helper II

Re: How to prevent users from registering overlapping hours

Hi,

 

I am working with same condition, for me i need to notify user if the selected time overlapped with registered time. can you help me with the logic?

 

 

Skjermbilde_tid.PNG

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,823)