Getting data from 3 sharepoint lists in a delegable way

[johnjohnPter]

I have three SharePoint lists, as follow:-

1) BookingCalendar list. with these fields; ID, StartDate, EndDate

2) Network. With this field; ID

3) BookingCalendarNetwork. With these fields; ID, BookingCalendarID & NetworkID.

So a booking calendar can have multiple networks and a network can be associated with multiple booking calendars.

Now when creating a new booking calendar, i want to check for any conflict, the conflict can happen,

1) if a network inside the booking calendar is already been used as per the BookingCalendar StartDate and EndDate.

So, when creating or editing a booking calendar,  i did this to get all the BookingCalendar/s which are already busy during the current booking calendar + get the networks for the current booking calendar (the items selected inside the Networks combbox inside the Booking Calendar form) that is been created/edited:-

//check for booking calendar conflicts
UpdateContext({Bookinglist:Filter(
            'Booking Calendars',
            
            (
            StartDataCardValue.SelectedDate>=StartDate
            And
            StartDataCardValue.SelectedDate<=EndDate
            )
            Or
            (
            EndDataCardValue.SelectedDate>=StartDate
            And
            EndDataCardValue.SelectedDate<=EndDate
            )
            
           
        )});

//get the selected networks inside the Network combobox inside the Booking Calendar form
UpdateContext({relatedNetworksFortheBookingCalendar:bookingcaledarNetworksComboBox.SelectedItems})

but not sure how i can complete the business logic for checking the conflict in the booking calendars' networks in a delegable way?

Thanks

[M_Ali_SZ365]

Hi @johnjohnPter ,

1. Make sure the BookingCalendarID and NetworkID in your BookingCalendarNetwork list are set up as lookup fields. This helps with delegation, allowing your app to handle more items without running into trouble.

2. When you’re checking for conflicts during the creation or editing of a booking calendar entry:

   • First, get the start and end dates from your booking form.
   • Then find any existing bookings in the BookingCalendar list that would overlap with these dates.

3. You’ll need to collect the IDs of the networks you’ve selected in your booking form's network combo box.

4. Now, the trickier part: you'll want to see if any of these selected networks are already tied to booking calendars that fall within your new booking’s date range. Use a filter on the BookingCalendarNetwork list to check for any records that match both the conflicting booking IDs and the selected network IDs.

Here’s a simpler version of the code logic you might use:

// First, filter to find any overlapping booking dates.
varOverlapBookings = Filter(
  'Booking Calendars',
  StartDate <= varCurrentEndDate && EndDate >= varCurrentStartDate
)

// Grab the selected networks from your form.
varSelectedNetworks = ComboBox.SelectedItems

// Then find out if any of the selected networks are linked to the conflicting bookings.
varNetworkConflicts = Filter(
  'BookingCalendarNetwork',
  BookingCalendarID in varOverlapBookings.ID &&
  NetworkID in varSelectedNetworks.ID
)

// Now, if varNetworkConflicts has any data, you’ve got a conflict.
If(
  !IsEmpty(varNetworkConflicts),
  // If there's a conflict, let the user know.
  Notify("Oops, looks like there's a scheduling conflict.", NotificationType.Error),
  // If there's no conflict, go ahead with the booking.
  SubmitForm(YourForm)
)

This way, you’re only using checks that SharePoint can handle, even with lots of data, and you’ll be able to spot any scheduling issues before they happen. If there’s a conflict, you’ll get notified; if not, you can safely proceed with your booking.

Please accept this solution if it resolves the issue. ✅
Best regards,
Muhammad Ali

[johnjohnPter]

@M_Ali_SZ365 thanks for the reply. i always avoid to use SharePoint lookups as they cause a lot of issues and performance issues,,, so i simply store the Ids of the related items to build the relation manually

now you provided this formula:-

// Then find out if any of the selected networks are linked to the conflicting bookings.
varNetworkConflicts = Filter(
  'BookingCalendarNetwork',
  BookingCalendarID in varOverlapBookings.ID &&
  NetworkID in varSelectedNetworks.ID
)

 but it uses the "in" operator which will cause delegatino warning.. is there any workaround? Thanks

[johnjohnPter]

@M_Ali_SZ365 are you able to advice further on this? Thanks

[M_Ali_SZ365 · Accepted solution]

Hello @johnjohnPter 

Dealing with delegation issues in Power Apps, especially when working with SharePoint lists, can be a bit tricky. I understand you're looking for a way to check for booking conflicts without hitting delegation limits and without using SharePoint's lookup fields. Here’s a simplified solution:

Get Overlapping Bookings:
Firstly, filter your 'Booking Calendars' list to find any existing bookings that overlap with your new booking's dates.

Retrieve Selected Networks:
Collect the network IDs selected in your ComboBox.

Batch Check for Conflicts:
The key here is to avoid hitting delegation limits by processing the networks in batches. Use a loop to go through each selected network and check if it's linked to any of the overlapping bookings. This way, you won't run into the 'in' operator causing delegation issues.

// Assume varOverlapBookings and varSelectedNetworks are already set
Clear(colNetworkConflicts)
ForAll(varSelectedNetworks, 
    Collect(colNetworkConflicts, 
        Filter('BookingCalendarNetwork', 
            BookingCalendarID in varOverlapBookings.ID && 
            NetworkID = ThisRecord.ID
        )
    )
)

Final Conflict Check:
After running the loop, check if colNetworkConflicts has any records. If it does, there's a scheduling conflict.

Remember, this method is a bit more complex than using simple filters, but it helps in keeping your app within SharePoint's delegation limits. Test thoroughly with your data to ensure everything works as expected.

Hope this helps! Feel free to ask if you have more questions.
Here's a snippet of what this might look like in Power Apps:

Please accept this solution if it resolves the issue. ✅
Best regards,
Muhammad Ali

[johnjohnPter]

@M_Ali_SZ365 thanks for the update ,,, seems it is working, but i need to do one modification, since i am going to use this formula for both create and update booking calendars, so i need to exclude the current booking calendar from the comparison otherwise i will always get that there is one conflict. Thanks