Generate list of days, to can search this

AppSheet AppSheet Q&A
1

I have 4 columns (check-in date, check-out date, status, room) that are used to enter the guest of the reservation. Is it possible to generate a virtual column (list of types) with a list of days (date type) contained between these columns?
Later I want to be able to search in that list, the status romm according to different dates
Thanks

2

Oscar_Rossini:

Is it possible to generate a virtual column (list of types) with a list of days (date type) contained between these columns?

Yes, to a degree.

Oscar_Rossini:

Later I want to be able to search in that list, the status romm according to different dates

What do you mean by this?

1 Like
3

I am sorry, my english is very bad, haren’t spanish helpers…

i need avoid overbooking, then i need find previous bookings

1 Like
4

I think the request is to search for any date to see what availability is on that date.

For example (not the best example I know but…)
Room 1 is booked 2nd to 5th
Room 2 is booked 3rd
Room 3 is booked 2nd to 4th

If you search 4th, how many rooms are free?

5

exactly @alphacp

If you search 4th, how many rooms are free?

2 rooms are free , room 2 and room 3

6

Oscar_Rossini:

Is it possible to generate a virtual column (list of types) with a list of days (date type) contained between these columns?

I don’t think that will be possible in the way that you are wanting. I actually have a feature request for this:

[List Generator Expressions](https://community.appsheet.com/t/list-generator-expressions/39376) Feature Requests

NUMBERLIST(0,10) = {0,1,2,3,4,5,6,7,8,9,10} DATELIST(TODAY() , TODAY()+7) = {2/3/21 , 2/4/21,… , 2/10/21} ALPHALIST(“a” , “d”) = {“a” , “b” , “c” , “d”} DECIMALLIST(0,0.5,0.1) = {0,0.1,0.2,0.3,0.4,0.5}

What it seems like you should actually be doing is validating the room selection against the date selections. This will likely be a an extensive expression, to deal with the ranges of dates, so I won’t be giving you a complete answer at this time, but I’ll point you in the right direction.

I would have the user enter their dates first, then show them a list of rooms (in a Ref column, pointing to rooms) available for those dates using “dropdown from valid_if”.

Presumably you have a parent-child relationship here, rooms being parents, and appointments (or “bookings” or whatever you have), being the child, and so rooms will have a [Related appointments] VC.

So you can set your valid_if to something like this:

FILTER(
  rooms ,
  ISBLANK( 
    SELECT( [Related appointments][id] ,
      AND(
      .  ...multiple date checks here....
       )
    )
  )
)

For the “multiple date checks” please review the " Validate Non-Overlapping Date Range" section of this article:

2 Likes