Check for overlapping time and available assets

(0) Share

Report

Posted on by Dioz

I really need help with figuring out the logic to check for overlapping dates in an asset checkout.

Following scenario: I’ve got a Canvas App based on three SharePoint lists with the relevant columns:

Assets (Title, Stock,...)
Events (Title, StartDate, EndDate,..)
AssetBooking (Title, Amount, Asset (LookUp), Event (LookUp),EventStart, EventEnd)

In my app I have a first screen where I choose the event in a gallery, which opens up the detail screen. There I can see all assets that are booked for this event. I have a button called “Add assets” which opens a popup with a ComboBox containing all assets.

If I choose an asset, I automatically want to check, how many assets are available during the time frame of the event. A slider control has the “max” property set to how many assets are available and then I can book the specified amount of assets.

What I tried is an OnChange on the ComboBox for the asset selection:

// OnChange property of the asset ComboBox control
Set(
 assetSelected,
 ComboBox2.Selected
);
ClearCollect(
 overlappingBookings,
 Filter(
 AssetBooking,
 Asset.Id = ComboBox2.Selected.ID &&
 Event.Id <> eventSelected.ID &&
 (DatePicker1.SelectedDate <= eventSelected.'End-Datum' &&
 DatePicker1_1.SelectedDate >= eventSelected.'Beginn-Datum'))
);
Set(
 totalBookedAssets,
 Sum(
 overlappingBookings,
 Amount
 )
);
Set(
 maxAvailableAssets,
 LookUp(
 Assets,
 Title = ComboBox2.Selected.Title
 ).Stock - Sum(
 overlappingBookings,
 Amount
 )
);
If(
 IsBlank(ComboBox2),
 "",
 If(
 maxAvailableAssets < 1,
 Notify(
 "No assets available during the selected time",
 NotificationType.Error
 ),
 Notify(
 maxAvailableAssets & " assets available during the selected time",
 NotificationType.Success
 )
 )
)

I'm aware that this isn't correct, but I hope it manages to show where I'm trying to go.

I already had a look at these two threads:

Solved: Check if date overlaps with collection - Power Platform Community (microsoft.com)

Solved: Booking check against selected dates and time - Power Platform Community (microsoft.com)

but I still didn't manage to make it work. Time isn't important for me, just the date. But it's important to integrate the available stock logic.

I'd be so thankful if someone could help me with this one.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Tony_Bowes_BP 280 on at

Like (1)

Report
Copy link

Link copied!

To look for overlapping bookings, your code should work as long as DatePicker1 is New Event Start Date, and DatePicker1_1 is New Event End Date (might want to rename them for clarity). If that is the correct way around, then you might just need to make all the variables visible and debug each stage in turn.

Was this reply helpful? Yes No
Dioz 61 on at

Like (0)

Report
Copy link

Link copied!

Thanks a lot for your input. Yes, that's how I used the datepickers, sorry for the confusion. As it is, it works correctly if we're looking at just one of the events. As soon as I switch to another event, it still shows all assets as available though, event if the events are at the same time and one of them already has a reservation for all of a specific asset.

Was this reply helpful? Yes No
Verified answer

Tony_Bowes_BP 280 on at

Like (1)

Report
Copy link

Link copied!

The code in isolation looks Ok, so it's likely some assumption of the way the collection is handled, or assumptions on other parts of the system (such as the "Amount" column in the collection)

When you swap to another event, confirm that
- Current event start and end date have updated to the new event.
- OverlappingBookings is a correct list of overlaps for the current event
- TotalBookedAssets is the correct amount from OverlappingBookings
- MaxAssets is the correct amount for the selected asset
- MaxAvailableAssets is the correct difference between TotalbookedAssets and MaxAssets

Easiest way is to have these displayed on the form to help with debugging

Was this reply helpful? Yes No
Dioz 61 on at

Like (0)

Report
Copy link

Link copied!
I feel like I made at least a little bit of progress now. For some reason, I have to open up the event detail screen twice, to make it work right though.

This is the rough concept I'm working on right now. The only booked asset is the "Scheinwerfer Timbo", which is only available once in stock, and that one is booked. Therefore, when I choose it, it should always give me a maxAvailableAssets 0 at events at the same time. If I open up the app, choose Event 1 and then the asset, it works correctly. Then I go back to the event selection, choose Event 2 which happens at the same time and it shows 1/1. The weird thing: If I then go back, select Event 2 again, it works as expected.

So for some reason, the ClearCollect seems to cause problems.

ClearCollect( OverlappingBookings, Filter( AssetBooking, Asset.Value = cmb_Asset.Selected.Title && (StartDate <= 'DatePicker - Start'.SelectedDate && EndDate >= 'DatePicker - End'.SelectedDate) ) ); Set( totalBookedAssets, Sum( OverlappingBookings, Amount ) ); Set( maxAvailableAssets, LookUp( Assets, Title = cmb_Asset.Selected.Title ).Stock - totalBookedAssets ); If( IsBlank(cmb_Asset), "", If( maxAvailableAssets < 1, Notify( "No assets available during the selected time", NotificationType.Error ), Notify( maxAvailableAssets & " assets available during the selected time", NotificationType.Success ) ) ); Set( assetSelected, cmb_Asset.Selected )

This is how my code looks like now on the OnChange of the cmb_Asset Combobox. There's nothing happening on the OnVisible properties of my screens so far, except for resetting the Combobox.

I thought it might have something to do with the SP delegation warning, but I rebuild the same thing in Dataverse and it's pretty much the same.

Was this reply helpful? Yes No
Dioz 61 on at

Like (0)

Report
Copy link

Link copied!

After a lot of frustration, I finally managed to make it work, by manually setting the variable eventSelected to Blank() when going back to the event screen, before selecting a new one.

Was this reply helpful? Yes No
Tony_Bowes_BP 280 on at

Like (0)

Report
Copy link

Link copied!

That type of error suggests it's a timing issue and there may be something being processed out of sequence (or something being processed after the fact, but it only appears on the next run). Regardless, the method you fixed it with is likely the pragmatic way forward (effectively a double refresh)

Was this reply helpful? Yes No