cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CathrineB
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

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
v-bofeng-msft
Community Support
Community Support

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

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

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,360)