Loop through a collection and skip numbers found

[Smitsky34]

I'm creating a fleet management tool and have a list that has all the fleet reservations (car, begin time, end time).  I want to populate a date/time drop down with the available times and exclude the times that the car is already reserved.  I've got a collection of reservations from a SharePoint List for the specified car and date and I want to populate the hour and 30 minute drop down with available times.

How do I process the collection (some While Loop or ForAll) to increment a counter and if it's found in the collection then it is skipped, and if not found then the hour is displayed in the dropdown.
In this example there is one reservation for a car from 10:00 to 1:00, so the available hours would be 0 - 10 (not really 10 because the car has to be checked back in before 10:00, 13 - 23.  How do I loop through the hours 0-23 and check to see if there is a reservation and skip that hour(s) in the dropdown population.

[EddieE]

@Smitsky34 

The answer really depends on a few things, 

- how your booking data is collected

- business rule around which hrs to keep exclude eg if 10AM - 1PM do you exclude Hr = 13 (ie 1PM)?

I made a simple model to show you some of the parts needed, on a button add this code

ClearCollect(
    colBookings,
    {
        ID:1, Loc: "Canon City", Office: "Canon City Office", Vehicle: "TEST FOR MIKE", 
        StartDate: Date(2023,08,05), StartTime: TimeValue("10:00 AM"), EndDate: Date(2023,08,05), EndTime: TimeValue("1:00 PM"),
        NumHrs: Hour(TimeValue("1:00 PM")) - Hour(TimeValue("10:00 AM"))
    }
);

Clear(colMissingHrs);

ForAll(
    Sequence( First(colBookings).NumHrs + 1, 0, 1) As counter,
    Collect(
        colMissingHrs,
        {Hour: Hour( First(colBookings).StartTime) + counter.Value}
    )
)

On a dropdown for Hrs add this code

Filter( [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23], !(Value in colMissingHrs.Hour))

When you run the button code, the Hrs displayed in the dropdown will be missing 10-13 (inclusive). You can change the Sequence Start from 0 to 1 if you want to include Hr = 13?

[Smitsky34]

The question I have now is how do I create the collection of reservations and specify calculated columns?

Here is my initial collection definition:

ClearCollect(
ReservedTimes,
Filter('Van Check-Out',
Van.Value = ddVanSelected.Selected.Title
And (
DateValue('Check Out Date') >= DateValue(BeginDate)
And DateValue('Expected Check In Date') <= DateValue(EndDate)
)
)
)

I've tried to add ShowColumns before the filter and add in the calculated column for the Hours Reserved but it doesn't like the syntax:
ClearCollect(
ReservedTimes,
ShowColumns(
Filter('Van Check-Out',
Van.Value = ddVanSelected.Selected.Title
And (
DateValue('Check Out Date') >= DateValue(BeginDate)
And DateValue('Expected Check In Date') <= DateValue(EndDate)
)
), Hours('Expected Check In Date') - Hours('Check Out Date') As CheckedOutHoursCount
)

[EddieE]

@Smitsky34 

ShowColumns just shows specific columns only, for example, on your first code snippet you could use it like this to just show the 3 columns

ClearCollect(
   ReservedTimes,
   ShowColumns(
      Filter(
         'Van Check-Out',
         Van.Value = ddVanSelected.Selected.Title, 
         And(
            DateValue('Check Out Date') >= DateValue(BeginDate),
            DateValue('Expected Check In Date') <= DateValue(EndDate)
         )
      ),
   // Note: the _x0020_ is used to represent spaces in your column names 
   // You may/may not need to use it?
   "Van", "Check_x0020_Out_x0020_Date", "Expected_x0020_Check_x0020_In_x0020_Date"
   )
)

If you want a new / calculated column inside of your gallery you use AddColumns()

[Smitsky34]

I punted on trying to calculate the Hours each reservation is reserved for within the code and just created a calculated field in my list (expected return date - checkout date).

How do I use that in the creation of the collection that I will use to trim the hour list?

I need to process the list of reservations and remove all the hours from the drop down that the van is checked out.  the first statement worked when there was only one item in the collection.  How do I update the ForAll statement to process and remove a 7-9 am reservation and a 10-1 reservation?

Clear(RemoveHoursFromStart);

ForAll (
Sequence(
First(ReservedTimes).NumHour +1, 0, 1) As counter,
Collect(RemoveHoursFromStart,
{NumHour: Hour(First(ReservedTimes).'Check Out Date') + counter.Value}
)
)

[EddieE · Accepted solution]

@Smitsky34 
You can do this without having to create Number of Hours per record. 

If I change my above button code to this

ClearCollect(
    colBookings,
    {
        ID:1, Loc: "Canon City", Office: "Canon City Office", Vehicle: "TEST FOR MIKE", 
        StartDate: Date(2023,08,05), StartTime: TimeValue("10:00 AM"), EndDate: Date(2023,08,05), EndTime: TimeValue("1:00 PM")
        //,NumHrs: Hour(TimeValue("1:00 PM")) - Hour(TimeValue("10:00 AM"))
    },
    {
        ID:2, Loc: "Canon City 2", Office: "Canon City Office", Vehicle: "TEST FOR MIKE", 
        StartDate: Date(2023,08,05), StartTime: TimeValue("07:00 AM"), EndDate: Date(2023,08,05), EndTime: TimeValue("09:00 AM")
        //,NumHrs: Hour(TimeValue("1:00 PM")) - Hour(TimeValue("10:00 AM"))
    },
    {
        ID:3, Loc: "Canon City 3", Office: "Canon City Office", Vehicle: "TEST FOR BOB", 
        StartDate: Date(2023,08,05), StartTime: TimeValue("07:00 AM"), EndDate: Date(2023,08,05), EndTime: TimeValue("09:00 AM")
        //,NumHrs: Hour(TimeValue("1:00 PM")) - Hour(TimeValue("10:00 AM"))
    }
);

Clear(colMissingHrs);

ForAll(
    Filter(colBookings, StartDate = dpBookingDate.SelectedDate, Vehicle = ddVehicle.Selected.Value)  As _data,
    ForAll(
        Sequence( Hour(TimeValue(_data.EndTime)) - Hour( TimeValue(_data.StartTime))+1, 0, 1) As counter,

        Collect(
            colMissingHrs,
            {Hour: Hour( _data.StartTime) + counter.Value}
        )
    )
)

I get three records, 2 for the van named "TEST FOR MIKE".

The above will error out at first until you do the following:

- add a DatePicker called dpBookingDate and

- add a dropdown called ddVehicle, with Items = Distinct(colBookings, Vehicle) <-- you'll need to possibly change this to Choices(ReservedTimes.Van)??

Now, select 5th Aug 2023 from the DatePicker and "TEST FOR MIKE".

The Hour selected should now only display the available hours.

Some adjustment will be needed for you situation but this gives you all the working parts - I think?

[Smitsky34]

Yeah buddy!!!!

A few tweaks here and there and I got it to work.

Thank you so much for your help!!!!

[EddieE]

@Smitsky34 

No worries mate, happy to help.