Query EnumList into one List

[Tech_Service]

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

instead I want out come as one list.

Please help

[Aurelien]

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.

[Tech_Service]

Thank you ! This really help!

[Tech_Service]

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])))
)

[Aurelien · Accepted solution]

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:

help.appsheet.com

SELECT()

Gather column values from row in table or slice

help.appsheet.com

OR()

Are any true?