Allow other values in a Valid If enum

I have an ENUM column [Contact Name] that populates a filtered list of choices based on the currently-selected value of another column [Customer Name]. However, I’ve run into a snag where the contact name is new - not someone we’ve typed into the existing list yet. I have turned on the “allow other values”, but because of the “Valid if” constraint, I can’t actually type in a different value. How do I adjust the Valid If expression to allow user input?

@Belinda
Moving your Valid_if expression to Suggested_Values property will do the trick.

Thanks! I will give that a whirl!

That sort of works. It does put the “correct” choices at the top of the list, and allows for user input, but then it also shows all the rest of the choices as well, which would be very confusing to some of my users. Any ideas?

@Belinda
By-meaning, the list is Suggested Values so whilst you are typing a text, it automatically narrows down the list because it can also used for searching values from long lists rather than scrolling down. But you will also notice the + Use ‘YourTypedText’ link down below the entry bar which you can click on and add to the list.

Many times, no one can remember the name, so this won’t work. So In effect, there is actually no way to choose from a defined list of values which has been filtered by the current entry in another column, OR type your own in?

@Belinda
For sure you can create a filtered list of values for any dropdown. But I’m still not clear about what you are trying to achieve. In your original post, you asked for the flexibility to add a new item to an existing dropdown and I proposed moving your expression to Suggested_Values instead of Valid_if. Then you said that “it works, but have the other values as well so it’s confusing the user”. By-nature, the dropdown content became a list of suggested values and the items being in the list is inevitable. And if you want to add a new item to this list, you just can type it and click on “+ Use ‘Your Typed Text’” link. I couldn’t be able to understand where’s the problem still? Can you please bit elaborate? Thnx.

Let’s say I have 3 columns. Column A is my Unique ID - a warranty claim number. Column B is a list of Companies. Column C is the person who initiated the repair, and would generally be a Customer Contact who works for that company. I am given a unique claim number when I submit a claim, and part of that submittal is to identify the Company who owns the equipment, and the person who reported the issue for warranty.

So for tracking, I input the claim number in Column A, then I pick the Company in Column B (e.g, “Bridges, Inc.”), and in Column C, I would like the choices to narrow down to choose only employees who work in Bridges, Inc. The example problem is this: I normally only have let’s say, 2 employees who normally contact me for warranty repair. John Doe, or Susan Smith. This time, however, it wasn’t an employee who initiated the repair, it was actually a person who doesn’t even work at that company, so I need to be able to simply type in that person’s name.

When I put the expression in “Valid If”, I see only the two employees I’ve previously dealt with, John Doe, and Susan Smith, but I can’t add the new person who initiated the repair.

When I put the expression in “Suggested Values”, I see the two employees, and I am allowed to add someone new, but I also see a whole bunch of other names (possibly a random selection of employees from other companies?). I don’t want to see any of those other names.

Did that all make sense?

@Belinda
You can filter the dropdown list with expression like this

SELECT(Customers[Contact],[Customer]=[_THISROW].[Customer])

When I input that expression in the “valid if”, the app no longer allows “Other Value” to be input.

@Belinda
As I have proposed above, you need to use Suggested_Values property for that, not Valid_if

As I replied above, when I do that, the list that populates includes names from other companies, so that doesn’t work. I have specifically asked for any OTHER ideas which might work in this circumstance. I would appreciate any thoughts from people, and will be happy to at least try it out!

@Belinda
As I have replied above, you can filter the values as per Company, Customer whatever filtering condition you like.

Sir, I have done that. You keep repeating the same directions. Please read what I am saying:

The filter works fine in VALID IF, but I cannot type in my own value.

When I use the filter in SUGGESTED VALUES, I can type my own value, but the filter does NOT limit the results the way it does in ‘valid if’.

I am trying to find a solution that does BOTH - filters the values AND allows input. Your solution does NOT completely filter the values, it only sorts them.

@Belinda
The expression’s behaviour does not change if it’s set in Valid_if or Suggested_Values. The only difference is, with the Suggested_Values, you are allowed to add new entries to the list. So provided it’s behaving differently, your expression could be wrong. Please check my sample app below. Just click your name on the Home screen. On the facing form, select a company from the dropdown. 2 fields will appear: (1) dropdown values with Valid_if (2) dropdown values with Suggested_Values. Both have the same expression on the back-end. Isn’t this what you are querying for?

I’m sorry, no, mine does not act that way. Here’s a screen shot of the expression on the left, and the resultant values on the right. From what I can tell, my expression is exactly the same as yours.

Expression in “Suggested Values”
SELECT(Vaccon[CSR CONTACT], [CUSTOMER] = [_THISROW].[CUSTOMER])

In the example, I have chosen AMR for the “Customer”, and you can certainly see that all 4 AMR employees are at the top of the list, but below that, other employees are also present. I have added the company name to each employee initial for this example.

So what is wrong with mine? What’s different from mine than yours? Because yours seems to work the way I want it to work.

@Belinda
Is this a REF column??

No, it is an enum.

I figured it out though, in my enum, I had “auto-complete other values” clicked on. When I click that off, the rest of the options that don’t match go away.

Happy to hear that. If you had mentioned that your column type is an ENUM, I would have told you that at the very beginning. I was assuming that you are using a normal Text type column and thinking why the expression is not working, as I was pretty sure about the result :slight_smile:

Well thank you for sticking it out with me :slight_smile:
I did mention it though…it was my first sentence :slight_smile: