Solved: Re: Get Enum/Enum List from a data sheet in a spec...

dalebyers

Hello, I am using a data list to pull a dynamic enum list based on Category, Subcategory & Field chosen. This is working great, except I need to be able to control the order of the list. Appsheet is not pulling the order as it is listed in my google sheet. Example:

My expression:
SELECT(
DetailValues[Value],
AND(
[Category] = [_THISROW].[Category],
[Subcategory] = [_THISROW].[Subcategory],
[Field] = "In Attendance"
)
)

Category = Inspection Details

Subcategory = General

Field = In Attendance

Values:
Client
Client's Agent
Listing Agent
Home Owner
Other

But the enum comes in as:
Client's Agent
Listing Agent
Home Owner
Other
Client

I would like to use this method for many columns, but order will be very important - any way to control the order the list displays?

Steve

@dalebyers wrote:

So I would have a field showing the Key for the item that was chosen from the enum and another recording the Value for that Key?

Yes.

so that would mean adding a lot of additional columns to dereference each

Yep.

is there any way to dereference it inside the expression that captures the selection?

Nope.

View solution in original post

JuneCorpuz

Hello there-

First, you need to add the values in #1, and please turn off #2 and #3 (See screenshot below)

dalebyers

I am attempting to use a list from a sheet vs listing them in appsheet - what I have done is using an expression in the Valid If field and it works just fine, I just need to be able to control the order

JuneCorpuz

Maybe you can save the data in another sheet in the order you want, and just use SELECT() without using ORDERBY() or SORT()

dalebyers

Yes, that is what I am doing, but Appsheet is not pulling the enum in the same order as the sheet, so I think I need to explicitly define the order?

Suvrutt_Gurjar

Does sorting help?

SORT(SELECT(
DetailValues[Value],
AND(
[Category] = [_THISROW].[Category],
[Subcategory] = [_THISROW].[Subcategory],
[Field] = "In Attendance"
)
), FALSE

)

dalebyers

This does work if I want them alphabetical, which in this example works, but alphabetical is not going to work for some of my uses

I thought of adding another column to the table - [Order] - would there be a way to have it sort by this column?

Steve

Theoretically, if you use an Enum column and define the allowed values in the desired sorting order (as @JuneCorpuz suggested), you could then use SORT() on a list of values from that Enum column (as @Suvrutt_Gurjar suggested) to sort the values in the order the allowed values are listed. This works best when you always want the values sorted as defined in the Enum column's configuration.

Another approach would be to use INTERSECT() to impose an arbitrary order:

INTERSECT(
  LIST(
    "Client",
    "Client's Agent",
    "Listing Agent",
    "Home Owner",
    "Other"
  ),
  SELECT(
    DetailValues[Value],
    AND(
      [Category] = [_THISROW].[Category],
      [Subcategory] = [_THISROW].[Subcategory],
      [Field] = "In Attendance"
    )
  )
)

LIST(...) defines the allowed values and order; SELECT(...) pulls the actual values from existing rows. Only values that occur in both LIST(...) and SELECT(...) will be returned, and will be returned in the order they occur in LIST(...).

dalebyers

Thanks Steve - this gets me 90% where I need to be and gives me the ability to pull an enum from a google sheet and control the order. However, I am trying to set this to be able to update dynamically - this method will require me to update the Valid If expression if things change - is there any way to have the expression find the list in the sheet and order the list based on a value in another column in the sheet - so if I have them numbered for example using a column titled "Order" - numbering 1 through whatever - could appsheet pull in the enum based on that order? I hope I am making sense - here is an image of my sheet:

Steve

Hmm... Based on what I'm seeing, I'd suggest making the column you're trying to populate with Listing Agent, Client, Home Owner, etc. of type Enum with base type of Ref and referenced table of the table above, which appears to be DetailValues. The Valid if expression would then be:

ORDERBY(
  FILTER(
    "DetailValues",
    AND(
      [Category] = [_THISROW].[Category],
      [Subcategory] = [_THISROW].[Subcategory],
      [Field] = "In Attendance"
    )
  ),
  [Order]
)

Doing this, though, would require the Value column be the label column of the DetailValues table, and will require you to dereference the chosen Enum values to get the actual value.

dalebyers

Geez, seems so simple, but I couldn't see it! Thank you again for your help!!

dalebyers

This is working great visually, but it returns the [ID] (key) value to the field vs the label - so instead of entering Client in the In Attendance field, it enters a 1
I suspect this has something to do with your comment

"and will require you to dereference the chosen Enum values to get the actual value."

I am not sure I understand this - can you explain?

Steve

Why do you want the label column value stored rather than the key column value?

dalebyers

the data will be used to build reports using appscript directly from the data source - outside of appsheet - it seems it would be easier to have the value vs key for that reason. I also have many of columns/fields like this that will pull the enum and enum lists from the same table - so the concern is multiplied

Steve

Add another column with an App formula that dereferences the chosen DetailValues value, like [Value].[Value].

dalebyers

Thanks Steve - to confirm - you mean add another column to the table where the enum will be selected? So I would have a field showing the Key for the item that was chosen from the enum and another recording the Value for that Key? The issue is, I have many columns that this will be a factor for, so that would mean adding a lot of additional columns to dereference each - is there any way to dereference it inside the expression that captures the selection?

Steve