Allow other values in a Valid If enum

Belinda
Participant IV

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?

Solved Solved
1 40 5,525
1 ACCEPTED SOLUTION

@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?

View solution in original post

40 REPLIES 40

LeventK
Participant V

@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

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

Do apologize than @Belinda, I might have missed it in the beginning. Sorry if I have pissed you off with insistingly replying the same way

Not at all, I was mainly frustrated that I couldn’t seem to get my idea across. Your sample app helped a lot in understanding that something about the settings in my app was different, and that I was missing something.

Thanks again!

You’re welcome @Belinda, truly my pleasure.

I have an enum field. I put the values in suggested values but I don’t have that option to add a new value. Any suggestions?

3X_5_7_57ab43cdf5020f42b937edbfae3f621748d0a237.png

I just want to thank you for this little tip, I had been scratching my head for nearly an hour trying to figure out why my lists were doing this. All sorted now… (pun intended) : P

This helped me, I was in same trouble

I'm unable to get my Suggested_Values to hide before selecting the first option, like demoed on your app above, they seem to be visible all the time. can someone help me out 

@LeventK hi - Does this formula  only work if you're referencing another table for the values?

For example, my main table is "Deals" and when adding a new deal, I want the address values to only show those already listed for the selected law firm SELECT(Partners[Law Firm Address], ([Law Firm Name] = [_THISROW].[Law Firm Name])). This works great.

I have another form to add a new Partner, using a Partner table. I use the same exact formula on the Law Firm Address field of the partner table and it doesnt return all address options (its only pulling the value for the first corresponding row.)

Hi LeventK,
Do you know where are stored all data added by the “+” ?

Thanks
Thomas

@Thomas_CLERICO
In your base gSheet for sure

Tin_Nguyen
Participant I

Got it. Thanks. The add looks different probably due to an update. However, once added, I don’t know how to delete it. It’s not saved on the table so I can’t find it. Can you help?

I don’t understand. Could you elaborate?

Tin_Nguyen
Participant I

On my table I have 3 choices. I connect my main form to this table using Emmy. It will show as a drown down. I put in the suggested box so I can add to this list. I can add it on the app itself but it doesn’t add to the spreadsheet. I want to delete the new choice but it’s not on the spreadsheet so I can’t find it. I’m not sure where it’s stored to delete the new option.

Try setting Allow other values to OFF, save, then turn it back on.

Rifad
Participant V

When I use Valid IF. The coloumn is hidden from form view. It was working fine. I checked everything. But I am not able to figure out exact issue.

The behavior you describe typically occurs when the Valid if expression produces an empty list, which tells AppSheet there are no valid input possibilities for that column. In such a case, AppSheet will hide the column because there’s nothing the user could enter that would be valid, so why even allow the input? Check your Valid if expression.

Adm_Tech
Participant III

I know that problem of this topic was solved but I’ve probably similar case and cannot reach the result.

My column is type EnumList, with multiple choice where I defined suggested values with select expression on the another table column. These are ordered jobs for that item. And it works well.

But I need to show on that list also another, neutral value e.g. “NOT ORDERED JOB” in order to be eventually marked by the user. It should be always visible (even if the list of suggested values is empty).
I have added value “NOT ORDERED JOB” in Type Details, as a value, but I cannot make it visible on the list.

My configuration:

3X_6_a_6a92a79a2e3d34d3ec6303aca414175cc415399a.png

Independently of a different configuration, I don’t see the fixed value on the list. I don’t want to let add any other value. Only suggested values + value from a Type Details…
3X_7_9_79621dd083863dcc46c91771d3fb82c04e2d95f1.png

You’ll have to include the value in the Suggested values list explicitly:

SELECT(...) + LIST("NOT ORDERED JOB")

You then won’t need to define the value in Type Details.

That works, thank you, Steve!

Top Labels in this Space