organize boat assignment to optimize bookings

Giuseppe88 · 12-19-2023 08:41 AM

Hi, I'm asking for help from you more experienced people to get some help

I am developing an app to manage my nautical charter business for daily excursions. Each excursion is assigned multiple boats with a certain maximum passenger capacity. I would like to create a function that assigns boats to reservations, optimizing the distribution of passengers of reservations on boats associated with the booked excursion. Practically for each new booking the automation should reassign the boats to each booking to obtain the best redistribution of passengers. the number of passengers in the reservations are rightly not divisible. And the number of passengers on the boat is given by the sum of those reservations that meet the maximum passengers on the boat.

I hope I was clear

I insert the tables I work on

TABLE: Boat

boat id, boat name, maximum passengers

TABLE: EXCURSION

excursion id, excursion name, maximum passengers (the number of associated boats x maximum passengers)

BOAT-EXCURSION TABLES

id ,boat (Boat ref),excursion (excursion ref) This table creates the correlation between boats and excursions, allowing you to assign multiple boats to multiple excursions

BOOKING TABLE

Booking ID, Username, Telephone, Nationality, Excursion(Excursion REF), Excursion Date, Total Number of Passengers, Boat(Boat REF)

Denny774

Hey man,

it's pretty simple.

Your Booking table layout is wrong. It should be:
Booking ID, Username, Telephone, Nationality, Total Number of Passengers, Boat-Excursion ID

The Excursion table should be:
excursion id, Excursion Date, excursion name, maximum passengers (the number of associated boats x maximum passengers)

Cheers

Giuseppe88

thanks but I don't think it's a solution

I sketched out a possible formula but it doesn't work

IF(
ISBLANK([BOAT]),
SWITCH(
TRUE,
AND(
[TOTAL RESERVATION] <= ANY(SELECT( excursion boats[MAX PAX BOAT], AND([EXCURSION] = [_THISROW].[Excursion], [BOAT]=[_THISROW].[BOAT])),
[TOTALRESERVATION] <= SELECT(SUM(BOOKING[TOTAL RESERVATION]), [Boat] =[_THISROW].[[Boat]))
),
ANY(SELECT(boats excursions[BOAT], AND([Excursion] = [_THISROW].[Excursion], ISBLANK([BOAT])))),
TRUE,
"Reservation not bookable"
),
""
)

Denny774

Hey,

maybe something like:

Boat (Booking Table):
INDEX(FILTER("Boat", AND([maximum passengers] >= [_THISROW].[Total Number of Passengers], NOT[Boat_taken])), 1)

Cheers