Query EnumList into one List

AppSheet AppSheet Q&A
1

Hi, the Bookings[Room] contain EnumLIST is there a way can can query into a list?

(SELECT(Bookings[Rooms],AND([_THISROW].[Checkin]<=[Checkin],[_THISROW].[Checkout]>[Checkin]))

  • SELECT(Bookings[Rooms],AND([_THISROW].[Checkin]<[Checkout],[_THISROW].[Checkout]>=[Checkout]))

  • SELECT(Bookings[Rooms],AND([_THISROW].[Checkin]>=[Checkin],[_THISROW].[Checkout]<=[Checkout]))

  • SELECT(Bookings[Rooms],AND([_THISROW].[Checkin]<=[Checkin],[_THISROW].[Checkout]>=[Checkout]))
    )

from the code above I got this as a result

3X_9_c_9c377e9abb7954c372d93dbb03ab531b157bbe09.png
3X_9_c_9c377e9abb7954c372d93dbb03ab531b157bbe09.png299Ă—230 6.54 KB

instead I want out come as one list.

Please help

2

Hi @Tech_Service

Here:
https://community.appsheet.com/search?q=merge%20enumlist

Can you change the category of your post into “Questions” please ? It is more relevant.

5 Likes
3

Thank you ! This really help!

2 Likes
4

Thank you @Aurelien

I ended up using SPLIT() to each Select then Add them up later like this.

SPLIT(SELECT(Bookings[Rooms],AND([_THISROW].[Checkin]<=[Checkin],[_THISROW].[Checkout]>[Checkin])))+ SPLIT(SELECT(Bookings[Rooms],AND([_THISROW].[Checkin]<[Checkout],[_THISROW].[Checkout]>=[Checkout])))+ SPLIT(SELECT(Bookings[Rooms],AND([_THISROW].[Checkin]>=[Checkin],[_THISROW].[Checkout]<=[Checkout])))+ SPLIT(SELECT(Bookings[Rooms],AND([_THISROW].[Checkin]<=[Checkin],[_THISROW].[Checkout]>=[Checkout])))
)
1 Like
5

Hi @Tech_Service

In your expression, that I allow myself to indent in order to add more readability:

SPLIT(
  SELECT(Bookings[Rooms],
    AND([_THISROW].[Checkin]<=[Checkin],
        [_THISROW].[Checkout]>[Checkin]))
)
+ SPLIT(
  SELECT(Bookings[Rooms],
    AND([_THISROW].[Checkin]<[Checkout],
        [_THISROW].[Checkout]>=[Checkout]))
)
+ SPLIT(
  SELECT(Bookings[Rooms],
    AND([_THISROW].[Checkin]>=[Checkin],
        [_THISROW].[Checkout]<=[Checkout]))
)
+ SPLIT(
  SELECT(Bookings[Rooms],
    AND([_THISROW].[Checkin]<=[Checkin],
        [_THISROW].[Checkout]>=[Checkout])))
)

There are various things that attract my attention. If you don’t mind, I would like to provide some corrections/improvements to it:

  1. According to the doc:
    SPLIT() | AppSheet Help Center
    delimiters are missing in your expression. This is curious you didn’t get any error message, as the delimiter is not optional.
  2. At this stage, and if you use only a SELECT expression inside of your SPLIT() ==> you can get rid of SPLIT part. SPLIT() is useful if you deal with some EnumList expression, for technical reasons (text separated with " , "), and used with CONCATENATE() expression as well.
  3. in your expression, all SELECT expressions are based on the same table and same column. You should use OR() expression inside one single SELECT statement.

So, your expression would become:

SELECT(Bookings[Rooms],
  OR(
    AND([_THISROW].[Checkin]<=[Checkin],
        [_THISROW].[Checkout]>[Checkin]
    ),
    AND([_THISROW].[Checkin]<[Checkout],
        [_THISROW].[Checkout]>=[Checkout]
    ),
    AND([_THISROW].[Checkin]>=[Checkin],
        [_THISROW].[Checkout]<=[Checkout]
    ),
    AND([_THISROW].[Checkin]<=[Checkin],
        [_THISROW].[Checkout]>=[Checkout]
    )
  )
)

For reference:

3 Likes