Power Apps Formula for Checking Existing Events Based on Date, Time, and Location

[lathanzero]

Hi, I have a Sharepoint list with several fields to input, "From" field relates to when an event starts (This captures date and hour), "to" relates to when an event finishes, there is also "location" which is a multiple choice. This Sharepoint list is connected to Power Apps as a form. When a user fills this form I need something to check by looking in the Sharepoint list if the location that the user is trying to pick is in use on the same date and hour by another user.

I don't know how to capture the hour value... If you have any idea I would appreciate it.

[WarrenBelz · Accepted solution]

Hi @lathanzero ,

I have not looked at the rest of your logic, but try this structure - I am assuming here that you are selecting your hours values from drop-downs.

With(
   {
      _FromDT:
      DateValuel.SelectedDate + 
      Time(Value(HourValuel.Selected.Value), 0, 0),
      _ToDT:
      DateValue2.SelectedDate + 
      Time(Value(HourValue2.Selected.Value), 0, 0)
   },
   With(
      {
         _Data:
         Filter(
            Locations,
            From <= _FromDT && To >= _ToDT
         )
      },
      If(
         CountRows(
            Filter(
               _Data,
               Value in Locations
            )
         ) > 0,
         Notify("Error", Error),
         Notify("Available", Success)
      )
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[WarrenBelz]

Hi @lathanzero ,

UPDATE

I missed this before as I simply copied some of your code into the new structure - what exactly do you mean by

Value in Locations

 as Locations is the list name, not a field name and Value also would not tend to be a field name.

[lathanzero]

What I need (hope you can help me with this) So, I have a Sharepoint list called "ARF" and in this list, I have the field "From" (related to a starting date and hour for an event), "To" (related to finishing hour and date for an event) and a field called "locations" which is a multiple choice, so, this list is connected to a Power App as a form, so what I need is when a user filling a new form using the app, chooses the "from" date and hour, the "to" date and hour and when the user is choosing locations the app checks if there is an event already created in the SharePoint list (an event in the same locations and hour range selected by the current user) so if this is true, the app should show an error. If you can help me with that... I would appreciate it.

Thanks

[WarrenBelz]

Hi @lathanzero ,

You need to filter the data source (not Locations) - something like this. I am assuming here that Locations is a Single Line of Text field.

With(
   {
      _FromDT:
      DateValuel.SelectedDate + 
      Time(Value(HourValuel.Selected.Value), 0, 0),
      _ToDT:
      DateValue2.SelectedDate + 
      Time(Value(HourValue2.Selected.Value), 0, 0)
   },
   With(
      {
         _Data:
         Filter(
            ARF,
            From <= _FromDT && To >= _ToDT
         )
      },
      If(
         CountRows(
            Filter(
               _Data,
               DataCardValue12.SelectedItems.Value in Locations
            )
         ) > 0,
         Notify("Error", NotificationType.Error),
         Notify("Available", NotificationType.Success)
      )
   )
)

[lathanzero]

Hi, thanks again for following up, Locations in the Sharepoint list is a multiple choice and adding the formula you provided I am getting this error

I really appreciate all your help

[WarrenBelz]

@lathanzero ,

What type of field is Locations (if a choice field, is it multiple value) and what is the Items of DataCardValue12

[lathanzero]

Hi Warren, thanks again, the Locations is a multiple value, so the user can choose more than one option, the DataCardValue12 is where the Locations multiple choice list shows, so Locations is a field in my Sharepoint list and connects to DataCardValue12 to show the elements.

[WarrenBelz]

@lathanzero ,

You have a many to many relationship there which requires different treatment.

With(
   {
      _FromDT:
      DateValuel.SelectedDate + 
      Time(Value(HourValuel.Selected.Value), 0, 0),
      _ToDT:
      DateValue2.SelectedDate + 
      Time(Value(HourValue2.Selected.Value), 0, 0)
   },
   With(
      {
         _Dates:
         Filter(
            ARF,
            From <= _FromDT && To >= _ToDT
         )
      },
      With(
         {
            _Data:
            If(
               Len(DataCardValue12.Selected.Value) = 0,
               _Dates,
               Ungroup(
                  ForAll(
                     DataCardValue12.SelectedItems As _Items,
                     Filter(
                        _Dates,
                        _Items.Value in Locations.Value
                     )
                  ),
                  "Value"
               )  
            )
         },
         If(
            CountRows(_Data) > 0,
            Notify("Error", NotificationType.Error),
            Notify("Available", NotificationType.Success)
         )
      )
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[lathanzero]

Thanks for this option, what I get now is an error every time I pick a Location, the only way it gets Success is when the Location is empty, any ideas? 

Thank you!