Drop down list without the "Add or search" option

[1minManager]

 I'm just really checking that what I'm trying to do is impossible.  Because I think I've tried every option 😤

So what I want is:

1. A drop down list of items, to be pulled from a different table
2. The list to be displayed as a Dropdown
3. A Valid_IF rule that checks this column doesn't equal another column (with an error on message on this row if it does)
4. The user not to be able to add new items

You'd have thought this was easy...

What I've found is that the above only works if I make either of these changes:

• Display as Buttons or a Stack
• Set the column as TEXT and have the Valid_IF just create the list.  Presumably then I need to have the VALID_IF duplicate check rule and error message attached to a different column

Every other post on this forum says to just do it  as TEXT with a VALID_IF to create the list.  Which works.  But you seemingly can't have a valid_if create both an list and check for something.  Likewise the "Allow other values option" for an ENUM.  It seems unticking this does nothing, checkout below:

Am I missing something....?

[AleksiAlkio]

Use Enum column with the base of Ref and set the source table (for the dropdown) as Read_Only. Then set both options "Allow other values" and "Auto-complete other values" as OFF. Read the data from the table then with the "Suggested values" and it should work.

[1minManager]

Not sure I could use an ENMU/Ref column as dropdown pulls data from numerous columns on another table.  So just seeing the label column wont work.

But created a ReadOnly slice, changed EMUM from TEXT to Ref and linked to ReadOnly slice.  And IT WORKS!!!!  Though I do now have a yellow triangle on every option.  How can I get rid of that?

[AleksiAlkio]

You have the ref broken as you have earlier saved another value than the key.

[1minManager]

Not sure exactly what you mean, but these two table are not parent/daughter.  So aren't really linked in any way.  The only way I get data from them they all have a column where USEREMAIL() is the inital value

[AleksiAlkio]

You don't need to have parent/child relationship at all and when using Enum bease of Ref, it doesn't create any relationship either. The base type of Ref only means it will show the label value with your dropdown. And of course dereferencing works as well.

[AleksiAlkio]

Btw.. when using Enum base type of Ref, the label will be shown in the same way than when using a Ref column.

[1minManager]

Not sure this is gonna work...  Now I can't save and even if I could would it be saving the Key column of the referrenced table?

[AleksiAlkio]

With the Enum base of Ref, yes it saves the key value.

[1minManager · Accepted solution]

I've managed to sort it as follows.  So in this example we are pulling 10 items from another table, without being able to use references.  The user is then allowed to choose 5 from those 10 but they are not allowed to choose any item twice.  Here's what I used in the VALID_IF

SORT(UNIQUE(
LIST(
   ANY(Select(CareerSifting[Career1],[CareerSiftingUser]=UserEmail())),
   ANY(Select(CareerSifting[Career2],[CareerSiftingUser]=UserEmail())),
   ANY(Select(CareerSifting[Career3],[CareerSiftingUser]=UserEmail())),
   ANY(Select(CareerSifting[Career4],[CareerSiftingUser]=UserEmail())),
   ANY(Select(CareerSifting[Career5],[CareerSiftingUser]=UserEmail())),
   ANY(Select(CareerSifting[NewCareer1],[CareerSiftingUser]=UserEmail())),
   ANY(Select(CareerSifting[NewCareer2],[CareerSiftingUser]=UserEmail())),
   ANY(Select(CareerSifting[NewCareer3],[CareerSiftingUser]=UserEmail())),
   ANY(Select(CareerSifting[NewCareer4],[CareerSiftingUser]=UserEmail())),
   ANY(Select(CareerSifting[NewCareer5],[CareerSiftingUser]=UserEmail()))
)
))
-
LIST([Career2],[Career3],[Career4],[Career5])

Notes for any appsheet beginers 😀

1. We are having to use ANY(Select(... for each item because we are pulling multiple columns from the same row.  Whereas Select() is normally used to pull one column from many rows.
2. Unique() is there because CareerSifting[NewCareerX] columns are text, so it feasible the user might type the same thing twice.  So this strips out all duplicates.
3. SORT() simply puts them in alphabetical order, just to look nice.
4. The clever bit is the -LIST(...)  The removes anything they've already selected in any of the other 4 columns in this table.  This formula is in [Career1] which is why its checking whats in columns career 2-5.
5. Doing it this way means you don't need any error messages because the user can't choose and invalid item nor can they add an item.

Hope this helps someone else.  

[Giangdam089]

thankyou

[Giangdam089]

thankyou topic