Is it possible to limit number of options chosen from an EnumList with base type "Ref"?

[neardj]

Hi,
I am developing my first App with AppSheet.
I created an “EnumList” column that uses “Ref” as the “base type”.
I tried to do as suggested in this topic: https://community.appsheet.com/t/is-it-possible-to-limit-number-of-options-cho/2789/2?u=neardj

but in my case it doesn’t work.
On “Valid IF” I have this formula: T_ROSA [ID_CICLIST] (which I use to retrieve the data for the EnumList) how can I also add the formula: COUNT ([_ THIS]) <= 8 ? (I want to limit max 8 choise)

Finally I wanted to ask you if you think it is possible that each value chosen by the EnumList goes on a new line instead of in a list delimited by a separator.

Thank you
Neardj

[Steve]

I’ve not tried this, but it might work:

In Suggested values, use this:

T_ROSA[ID_CICLIST]

In Valid If, use this:

AND(
  ISBLANK([_THIS] - T_ROSA[ID_CICLIST]),
  (COUNT([_THIS]) <= 8)
)

[neardj]

Thank you for your reply.
I’ve tried it but don’t work.
I see this error:

Column Name ‘ID_CICLISTA’ in Schema ‘T_INVIO_FORMAZIONE_Schema’ of Column Type ‘EnumList’ has invalid ‘Suggested Values’ of ‘=T_ROSA[ID_CICLISTA]’. The type of the Suggested Values does not match the column type.

[Steve]

Make sure the ID_CICLISTA column is of type EnumList with base type Ref and source table of T_ROSA.

[neardj]

Yes. I attached you the screenshot.
ref|507x500

[Steve]

Please post a screenshot of the error message.

[neardj]

So there!

[Steve]

Weird!

What if you use this instead?

LIST() + T_ROSA[ID_CICLIST]

[neardj]

I tried. But with this formula I see all the values ​​of the column and not only those of the reference item are filtered.
If I use T_ROSA [ID_CICLISTA] I only see the elements I need.

[Steve]

neardj:

I tried. But with this formula I see all the values ​​of the column and not only those of the reference item are filtered.

Sounds like you had been benefiting from the magic dependent dropdown feature. Complex Valid If expressions cannot be used with dependent dropdown, so we’ll have to recreate the functionality, which is doable.

Try this as the Suggested values expression:

SELECT(
  T_ROSA[ID_CICLIST],
  OR(
    ISBLANK([THISROW].[IDSQUADRA]),
    ([IDSQUADRA] = [_THISROW].[IDSQUADRA]
  ),
  TRUE
)

[neardj]

I tried it. I have this error:
Expression ‘SELECT( T_ROSA[ID_CICLISTA], OR( ISBLANK([THISROW].[IDSQUADRA]), ([IDSQUADRA] = [_THISROW].[IDSQUADRA] ), TRUE )’ could not be parsed due to exception: Number of opened and closed parentheses does not match.

[Austin]

Steve:

SELECT(
  T_ROSA[ID_CICLIST],
  OR(
    ISBLANK([THISROW].[IDSQUADRA]),
    [IDSQUADRA] = [_THISROW].[IDSQUADRA]
  ),
  TRUE
)

Try this, the extra ( is before [IDSQUADRA]

[neardj]

Steve:

SELECT(
  T_ROSA[ID_CICLIST],
  OR(
    ISBLANK([THISROW].[IDSQUADRA]),
    [IDSQUADRA] = [_THISROW].[IDSQUADRA]
  ),
  TRUE
)

Thanks. I have tried it. Now I have this error:

Column Name ‘ID_CICLISTA’ in Schema ‘T_INVIO_FORMAZIONE_Schema’ of Column Type ‘EnumList’ has invalid ‘Suggested Values’ of ‘=SELECT( T_ROSA[ID_CICLISTA], OR( ISBLANK([_THISROW].[IDSQUADRA]), [IDSQUADRA] = [_THISROW].[IDSQUADRA] ), TRUE )’. The type of the Suggested Values does not match the column type.

[Steve · Accepted solution]

The fix I offered previously:

LIST()
+ SELECT(
  T_ROSA[ID_CICLIST],
  OR(
    ISBLANK([_THISROW].[IDSQUADRA]),
    ([IDSQUADRA] = [_THISROW].[IDSQUADRA])
  ),
  TRUE
)

[neardj]

With this formula the error is:

Unable to find column ‘THISROW’

[Steve]

Whoops! That was a typo. Should be _THISROW.

[neardj]

I just corret this. But the new error is:

Unable to find column ‘IDSQUADRA] [_THISROW’

[Steve]

Ack! Another typo. Corrected above.

[neardj]

Now your formula is correct but the limit max 8 choise don’t work. Now I can see the items but I can’t select nothing.

On Valid If I have insert your suggestion:

AND(
  ISBLANK([_THIS] - T_ROSA[ID_CICLISTA]),
  (COUNT([_THIS]) <= 8)
)

[neardj]

@Steve your suggestion works if I change the “input mode” to “Dropdown”. If I use “batteries” or “buttons” the value is not colored. So it is not clear if it has been selected. Why?

[neardj]

neardj:

@Steve your suggestion works if I change the “input mode” to “Dropdown”. If I use “batteries” or “buttons” the value is not colored. So it is not clear if it has been selected. Why?

@Steve Do you have an idea for this problem?

[Steve]

I don’t understand the problem. Please post a screenshot.

[neardj]

I’m sorry.
I hope to make myself understood better with pictures.

Before inserting the changes you advised me when the user selected his choice, the button was colored blue:

After entering the changes you recommended, the screen looks like this:

The selected cyclist / button (1) turns dark black while the unselected cyclist (2) remains black. For the user it becomes really difficult to understand who has selected and who has not.

Even if I change the mode to “light” it changes little. The selected button becomes dark gray and the unselected one slightly lighter gray.

Do you think it could be a bug?

[Steve]

Yes, it looks like a bug to me.

Even with the unclear coloring, do the selections work as desired? Do multiple selections get saved to your spreadsheet?

[neardj]

Steve:

Even with the unclear coloring, do the selections work as desired? Do multiple selections get saved to your spreadsheet?

Yes the selection work and I find the data on spreadsheet. How can I report the bug?

[Steve]

neardj:

How can I report the bug?

Send email to support@appsheet.com.

[neardj]

Thank you so much for your immense help.

[neardj]

Hi Steve,
sorry if i disturb you again.
If I wanted to add to the formula you suggested also the possibility of excluding from the list the cyclists who do not have an empty field in the T_ROSA table, in the “FINO_AL” column, which formula should I add?
I tried to insert another condition to the “OR” function for example ISNOTBLANK ([_ THISROW]. [FINO_AL] but it doesn’t function.
Always thank you very much

[Steve]

Please start a new topic for this question and provide a complete description of the problem.

[Austin]

Wouldn’t the Suggest Values be the EnumLists not the items inside of it if you use T_ROSA[ID_CICLIST]? So the suggested values are EnumLists of References not References?

[neardj]

Austin_Lambeth:

Wouldn’t the Suggest Values be the EnumLists not the items inside of it if you use T_ROSA[ID_CICLIST]? So the suggested values are EnumLists of References not References?

I use T_ROSA [ID_CICLISTA] because I only want to see some filtered values.

[Austin]

OH I misread, I thought your screenshot was the T_ROSA table. Is ID_CICLISTA the key for T_ROSA?

[neardj]

Yes it is.

[neardj]

@Steve I realized that by selecting “dropdown” first I see the 30 cyclists of the selected team but after the last value I also see another 40 cyclists who are part of other teams not selected. With “buttons” and “stack down” it does not happen.

[Steve]

Hmmm… Try unchecking Auto-complete other values in the EnumList column’s configuration.

[neardj]

Steve:

Try unchecking Auto-complete other values in the EnumList column’s configuration

Thank you! Now I only see the cyclists of the selected team.