Delivery booking system

AppSheet AppSheet Q&A
1

Hi,

I would like to add a delivery booking system to my application.
I know that calendar views can be used for this but would there be a way of restricting how many deliveries can be booked in per hour.

For example, my warehouse can accept only 4 bookings per hour, so once 4 have been booked for a particular hour then there can’t be any more scheduled for that hour slot.

Would anyone know how this could be achieved?

Thanks in advance,
Chris.

2

Just some thoughts… you might be able to change the table security formula to only allow edit if the booking count is <= 4 in the current hour. Might also be able to do this using the Valid_if formula in a column (like booking date), and could return an error message if count <= 4?

3

You’ll likely want to use Valid if for this, especially as you may need to accept booking for different hours.

(
  COUNT(
    FILTER(
      "Bookings",
      (HOUR([When] - "00:00:00") = HOUR([_THIS] - "00:00:00"))
    )
  )
  < 4
)

  1. FILTER("Bookings", ...) gathers a list of bookings from the Bookings table that match the given criteria (...; see below).

  2. When is a DateTime column in the Bookings table. This entire Valid if expression is attached to that same When column. _THIS, then, refers to the When column of the current row (e.g., being edited in a form).

  3. HOUR([When] - "00:00:00") extracts the hour component of a Duration value. Because [When] is a DateTime value, we have to convert it to a Duration. A way to do that is to subtract another Date, DateTime, or Time value from it. In this case, we subtract a zero Time value.

  4. Ditto for HOUR([_THIS] - "00:00:00").

  5. (HOUR(...) = HOUR(...)) compares the hours of the row already in the table (the left-hand side) with the new row’s (the right-hand side). If both have the same hour component, the row of the table matches and will be returned by FILTER().

  6. (COUNT(...) < 4) counts the rows gathered by FILTER() that matched the criteria (existing rows with the same hour as this new row) and asks if that number is less than four. If so, there is at least one slot available, so the new booking time is valid.

2 Likes
4

Please remember what ever validation you do, the overbooking can still happen if two or even more users are making the booking at the same time.

1 Like
5

Hi All,

Thank you so much for your replies, I will try these suggestions and get back to you.
But I wonder, would there be a way that the user would be able to set the number of slots that can be taken in on particular hour rather than it being hardwired?

Thanks again in advance,
Chris.

6

Hi Chris, I looked at the app. Here’s my recommendation to get the base functionality working:

  • In table Slot Times, change the column header to “Slot Time”. Then regenerate the table.
  • In table Bookings, add the following show-if formula to “Loading Time” column: ISNOTBLANK([Loading Date])
  • In table Bookings, change “Loading Time” to type Enum with base Time
  • In table Bookings, add the following formula into Valid_if field:

SELECT(Slot Times[Slot Time],
COUNT(
SELECT(Bookings[Loading Time],
AND(
[Loading Time]=[Slot Time],
[Loading Date] = [_THISROW].[Loading Date]))
)<4
)

1 Like
7

Hi Derek,

Many thanks for the help,

One question, in the final step you ask me to enter the formula into the Valid_if section.

Is this within the column [Loading Time]?

8

Hi @Derek,

Thanks so much for assiting, it works even better than I expected it too.

C.

9

Hi @Chris_Jeal,

Yep, that formula goes into the valid_if field for the Loading Time column.

I’m glad to hear it is working well!

10

Hi @Derek,

I have added a Time Slot Capacity column to my Slot Times table so now the formula reads:

SELECT(Slot Times[Slot Time],
COUNT(
SELECT(Bookings[Loading Time],
AND(
[Loading Time]=[Slot Time],
[Loading Date] = [_THISROW].[Loading Date]))
)<[Capacity]
)

which is working great too.

My next question would be if we could go one step further and add another capacity column to the table but this would be for specific days of the week.

For example, if slot time capacity of 14:00 during Monday to Friday is 3 but for Saturday and Sunday it’s capacity is 1.

Do you think there would there be a way to add this into the formula, and how would the table’s columns be structured in your view.

Best,
Chris.

11

Hi @Chris_Jeal,

You can do this by adding a table and using the Weekday() and Lookup() functions. Weekday() will return the index of the day of the week corresponding to a date. Today is Monday, September 16, 2019. So writing WEEKDAY(TODAY()) will return 2.

Sunday = 1; Monday = 2; … Saturday = 7.

Let’s call the new table, Capacity. The columns are: “Day_Name”, “Day_Index”, and “Capacity”.

  • Day_Name has the days of the week: Sunday, Monday …
  • Day_Index has the corresponding index: 1, 2, …
  • Capacity has the capacity for that day

Then in the valid_if formula, replace [Capacity] with the following:
LOOKUP(
WEEKDAY([_THISROW].[Loading Date]),
Capacity,
Day_Index,
Capacity)

Note: Day_Name isn’t used in the formula. Its only purpose is to improve human readability.