Hey everyone, Iโm having some trouble with the Select () in my app. Would appreciate some advice.
Specifically, Iโm creating a tourist destination locator app. It allows allows users to select location based on drop-down entries.
I have different fields (generated from Columns in my Excel sheet): States, Regions, Areas, and Locations. I want the drop-down to change dynamically based on ANY field you select.
Example: I would like the โLocationsโ entries to be filtered depending on whether someone selects either the โAreasโ, โCountiesโ or โStatesโ.
In other words, i would like the field selections to behave as additional filters.
Ideally, all the fields would work just like a Valid_If, but that seems to only work for the adjacent field next to it (the affected fields disappears). I want to the Select function to make all the fields behave like the Valid_If would.
Can anyone offer any advice?
@Aleksi_Alkio Tried you code as is, got the error code
โIF function is used incorrectly:three inputs should be provided โ (condition, if-result, else-result).โ
I changed it to this IFS( ISBLANK([Areas & Counties]), SELECT(Location Assets[States],TRUE), ISBLANK([Hoods & Cities]), SELECT(Location Assets[Areas & Counties],AND([States]=[_THISROW].[States], TRUE)), ISBLANK([Location]), SELECT(Location Assets[Hoods & Cities],AND([States]=[_THISROW].[States],[Areas & Counties]=[_THISROW].[Areas & Counties],[Hoods & Cities]=[_THISROW].[Hoods & Cities])) )
but i get the following error
โThe expression is valid but its result type โListโ is not compatible with the expected type 'Enumโ
Not sure how to proceed from here
How about SELECT(Table[Column],AND([states]=[_thisrow].[states],[regions]=[_thisrow].[regions],[areas]=[_thisrow].[areas]))
I believe you need to create the feature like thisโฆ You need to read the situation with ISBLANK. For exampleโฆ
IF( ISBLANK([Areas & Counties]), SELECT(Locations[STATES],TRUE), ISBLANK([Hoods & Cities]), SELECT(Locations[Location],AND([States]=[_THISROW].[States],[Areas & Counties]=[_THISROW].[Areas & Counties])), SELECT(Locations[Location],AND([States]=[_THISROW].[States],[Areas & Counties]=[_THISROW].[Areas & Counties],[Hoods & Cities]=[_THISROW].[Hoods & Cities])) )
thanks for the response fellas.
@praveen The app is called โIGBoston Context Databaseโ feel free to dive in if you want.
@Aleksi_Alkio Iโll try your suggestion shortly. But i wanted to send screen so you understand whatโs going on.
This screen represents the fields that are currently being used with standard Valid_Ifsโฆ
Keep in mind this app is for new college students looking up images or info about a specific area in New England.
These are the two main problems Iโm having right now
To do that, you would have to put in
โMassachusettsโ for โStateโ,
โcity of Bostonโ for "Areas & Counties,
โSouth Bostonโ for โHoods and Citiesโ
and then โFort Point Channelโ for the โLocationโ.
(see first attached picture)
But what if you dont know the exact โHood or Cityโ category Fort Point Channel is in?
I attached a picture of just such a scenario. A user might put in โBostonโ for the โArea or Countyโ
โSouth Bostonโ for the โHood and Cityโ,
and then be unable to find โFort Point Channelโ in the โlocationโ.
So the user would have to determine what the right selection is (from both the โHoods & Citiesโ or the โAreas and Countiesโ) to find Fort Point Channel.
(see second attached picture)
That is why i want all the fields available. A user who selects โMassachusettsโ for โStatesโ, but doesnt know what to fill in for the โHoods and Citiesโ or โAreas and Countiesโ field can simply access the โLocationโ drop down to review all the selections with the
โMassachusettsโ States filter to find the appropriate location.
Praveen, In regards to your comments about my second point in my previous post, the other fields may indeed be individually evaluated, but i cant confirm because the fields disappear. Is it possible i can make the dependent drops down fields visible at all times?
As an example. say i switch the switch the States in this example to โMaineโ.
The โArea & Countiesโ still shows โCity of Boston skylineโ even though itโs not associated with Maine in my database (because itโs obviously not in Maine). This immediately produces a rather garish red โinvalidโ error on the field. The same thing happens with the subsequent fields once they become available again, which has user correcting error before they continue using the app.
(see third attached picture)
I find this behavior odd, particularly given you comment about the valid_if produces no results.
Is there any way to automatically clear the dependent drop down fields when they disappear, so they dont reappear with errors?
Thank again for the help, much appreciated
Yeapโฆ my mistake. Trying to think too late on the night:-) Tryโฆ
IFS( ISBLANK([States], SELECT(Location Assets[Location],TRUE), ISBLANK([Areas & Counties]), SELECT(Location Assets[Location],[States]=[_THISROW].[States]), ISBLANK([Hoods & Cities]), SELECT(Location Assets[Location],AND([States]=[_THISROW].[States],[Areas & Counties]=[_THISROW].[Areas & Counties])), TRUE, SELECT(Location Assets[Location],AND([States]=[_THISROW].[States],[Areas & Counties]=[_THISROW].[Areas & Counties],[Hoods & Cities]=[_THISROW].[Hoods & Cities])) )
No problem. @Aleksi_Alkio i understand and really appreciate the assist. The new code gives me this error
The expression is valid but its result type โListโ is not compatible with the expected type โEnumโ
Just to confirm,
iโm supposed to put the entire code in the app formula, correct?. And should the column remain a enum?
You should put it into Valid_If
@Aleksi_Alkio got it. I just did that. The code verifies correctly, but now none of the dependent drop downs appear.
Is there a way for me to share the app with you so you can look at the setup yourself? that might be the easier way for you to address this
Please give me the app name and Account IDโฆ you can find them from the URL after appName=โฆ
Hi @Mic_L_Angelo, please see this article about dependent dropdowns. help.appsheet.com - Dependent Dropdown
Whenever you want to constrain the set of allowed choices, you will be using the Valid_If constraint.
You can author SELECT() expressions in the Valid_If constraints. Eg: SELECT(Locations[Location],
AND(
OR(ISBLANK([_THISROW].[State]), [_THISROW].[State] = [State])),
OR(ISBLANK([_THISROW].[Area]), [_THISROW].[Area] = [Area]))
) )
It can get messy but it might be what you are looking for. Dependent Dropdown help.appsheet.com
@Aleksi_Alkio IGBostonContextDatabase-544570&nonce=521933522#Data.Columns
Thanks for the assist
There is a mismatch in the number of columns between the spreadsheet for Feature Records and the table schema. The table has 16 columns but the schema has 14 columns. Would you please fix this error first, thanks.
@Aleksi_Alkio apologies, i was making adjustments in my fields earlier this morning and forgot to regenerate. Itโs all set now. Let me know if you need anything else from me.
I checked your app and what do you want to do with your fileds? Now they have both Enum values and Valid_Ifs which will create a dependent dropdown also. You probably want to read options from a spreadsheet rather than using fixed values, correct?
@Aleksi_Alkio More than likely, that was the result of me experimenting with different configurations to resolve the issue. I have no strong reservations about how itโs set up, as long as it works as i need it to.
Re: reading options.
The short answer is yes. The options are coming from a spreadsheet. Let me know what youโs like me to to to correct the problem
May I ask thisโฆ when the user fills the form, does he/she know the location always?
@Aleksi_Alkio No. A user may know the State or city a location is in, but not necessarily the county. That is the primary issue that iโm having, and the central purpose of my request.
The way dependent dropdown are setup to work, a user HAS to know what to fill in the succeeding field in order to proceed.
So in my app, if someone doesnt know what
โArea or Countyโ field to select in order to select the โLocationโ they want, theyโll be stuck. The other fields will not be available to select
This is why i want all the fields to be available, so they can work like optional โfiltersโ for the location field if possible. Hope that helps clarify things.
@Aleksi_Alkio @praveen hey guys, any update on this issue? Or should i just assume this process cant be done?
If they know the [Location] always, why donโt you fill States etc. automatically and they donโt need to search anything?
Thanks for the suggestion. After thinking about it, i concluded that
this will not work for me, for 3 main reasons.
some entries do not have a dedication [Location] tied to them, just the [Cities] and some of those entries would have the same [City] field. So that would be confusing and inconsistent.
It much easier for me to filter and run metrics on the entry results if the locations were separated by categories. I have formulas that will utilize the other fields for data allocation.
The primary reason: That creates less entries to choose from in the [Location] field. I have over 400 entries. I would much rather have the user select from 20 entries in the [Location] field, rather than 400 hundred.
Iโm assuming thatโs the primary purpose of dependent dropdown: to โnarrowโ the selections of a particular fields. If i were to use the method you described, i wouldnโt need to create multiple dropdowns in the first place.
Besides which, if the optional โfilterโ fields do work like how i want, then the user will have the option to search for all the entries in the [Location] field if he wishes. I just want to set up the filter functionality so the user has alternatives.
But iโm getting this sense that this is not possible. Can this be done, or should i just figure out another method? @Aleksi_Alkio
thanks for the swift response @Aleksi_Alkio @praveen Really appreciate it!
Praveen, i was actually there yesterday, spent about half a day on that site to be frank. I used the code example you both offered. There were no errors, but it didnโt produced my desired result, so Iโll do my best to clarify.
I attached a screenshot of my data sheet, named โLocation Assetsโ. Itโs broken up in 4 columns (โStatesโ โAreas & Countiesโ, โHoods & Citiesโ & โLocationโ )
My ideal objective is for a person to be able to a view of a filtered list
any of the fields without having to fill in the proceeding one.
So in other words, i want ALL the dependent dropdown fields to be available to someone to choose, i dont want them to disappear. However, i still want the lists that they choose from to be dynamically filtered based on their choices in the previous fields.
As an example from this sheet.
If someone were to select โMassachusettsโ for the States, i would want for them to be able to select โFort Point Channelโ from a filtered list in the โLocationโ field, without the need to also select โSouth Bostonโ in the โHoods and Citiesโ or โCity of Boston skylineโ in the โAreas & Countiesโ fields.
With my current Valid_If set up, a person cannot fill in the โFort Point Channelโ location without selecting
the โCity of Boston skylineโ in โAreas & Countiesโ & โSouth Bostonโ in the โHoods & Citiesโ first. In fact the โLocationโ fields doesnโt even appear until the proceeding fields are entered. This is causing a lot of delay and frustration with data entry.
i would only want the succeeding fields to disappear (like it does with a Valid-If) if all the proceeding fields are blank, like how it is in rows 2-9.
Is there any further advice you can offer with how to setup the syntax here? It helps with a business need i have to solve, so i appreciate any insight you offer.
This was just an idea because the user could search the location typing the value with first few lettersโฆ and there it is. Is it okay if I take a copy of your app so I can play with it without messing your live app?
yes of course. I appreciate the suggestion.
And i agree regarding typing in the auto-fill functionality. Thatโs why this is ultimately not a deal-breaker for me. Just a convenience issue.
please, by all means go ahead and copy the app. If it cant be done, iโll be fine with it. I can readjust.
Regards,
-Mic
Soโฆ are you saying thisโฆ You want to filter the location field one by one? I mean first the list of values is big and more you choose, smaller the list will be? Am I understood correctly?
@Aleksi_Alkio Exactly correct Translate
Now I believe I have understood your requestโฆ you would like to have kind of โreverseโ dependent dropdown.
@Aleksi_Alkio to expand, i would like the user to have the ability to do that if they wish. I dont want them to always have to sequentially input data in the immediately proceeding field. Essentially, i just dont want the dependent drop downs to be hidden unless the list produces no options.
Iโm encountering users with situations in that they know the location they want to select, but not the area of county the location is in. Which means they have to select a random โArea or Countyโ (then โHood of Cityโ) hoping that is the one that will populate the desired location.
This situation makes the dependent drop more frustrating that just selecting from the entire list. Iโm hoping for a solution to solve that problem.
for even more clarification, the Valid_If actually works perfectly fine, it filters the lists as i want them. The issue is that Valid Ifs hides all the fields until the current one is selected.
I dont know if that is because AppSheet is not applying the filter effect to the other fields, or if this is just a UX Design decision.
So i need two things from this situation
The dependent drop down fields to be visible at all times (show_if doesnโt seem to have any effect on dependent drop downs)
The filter effect of different valid_ifs to โstackโ onto the same field.
(The Valid_Ifs only seems to work for the field immediately next to it)
Essentially just a way i can filter multiple fields at once to make the items in the selected fields smaller
hope that helps @Aleksi_Alkio
@Mic_L_Angelo, thanks for the clarification.
I think #1 is happening because the valid_if condition produces no results. If there is no valid result, the input itself is not shown because nothing valid can be put into it.
I donโt understand #2. There is nothing that limits Valid_ifs to the immediate next input. In fact, each of these is independently evaluated.
Could you perhaps
provide a screen capture to give us a better idea of what youโre seeing? Or you can give us the app name and steps to reproduce the problem.
User | Count |
---|---|
41 | |
36 | |
28 | |
23 | |
16 |