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! Go to 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?
@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?
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
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?
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.
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.
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:
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โฆ
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!
User | Count |
---|---|
42 | |
29 | |
25 | |
23 | |
13 |