Hey everyone, I'm having some trouble with th...

[Mic_L_Angelo]

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?

[Mic_L_Angelo]

@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

[Aleksi_Alkio]

How about SELECT(Table[Column],AND([states]=[_thisrow].[states],[regions]=[_thisrow].[regions],[areas]=[_thisrow].[areas]))

[Aleksi_Alkio]

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])) )

[Mic_L_Angelo]

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

1. Say you are a user using this app to look for pictures from in the South Boston area.

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?

2. Also, I noticed that Appsheet “remembers” the current selection in a dependent drop-down field when it disappears, which is another problem.

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

[Mic_L_Angelo]

@Mic_L_Angelo

[Mic_L_Angelo]

@Mic_L_Angelo

[Aleksi_Alkio]

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])) )

[Mic_L_Angelo]

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?

[Aleksi_Alkio]

You should put it into Valid_If

[Mic_L_Angelo]

@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

[Aleksi_Alkio]

Please give me the app name and Account ID… you can find them from the URL after appName=…

[pravse]

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

[Mic_L_Angelo]

@Aleksi_Alkio IGBostonContextDatabase-544570&nonce=521933522#Data.Columns

Thanks for the assist

[Aleksi_Alkio]

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.

[Mic_L_Angelo]

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

[Aleksi_Alkio]

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?

[Mic_L_Angelo]

@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

[Aleksi_Alkio]

May I ask this… when the user fills the form, does he/she know the location always?

[Mic_L_Angelo]

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

[Mic_L_Angelo]

@Aleksi_Alkio @praveen hey guys, any update on this issue? Or should i just assume this process cant be done?

[Aleksi_Alkio]

If they know the [Location] always, why don’t you fill States etc. automatically and they don’t need to search anything?

[Mic_L_Angelo]

Thanks for the suggestion. After thinking about it, i concluded that

this will not work for me, for 3 main reasons.

1. 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.

2. 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.

3. 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

[Mic_L_Angelo]

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.

[Aleksi_Alkio]

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?

[Mic_L_Angelo]

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

[Aleksi_Alkio]

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?

[Mic_L_Angelo]

@Aleksi_Alkio Exactly correct Translate

[Aleksi_Alkio]

Now I believe I have understood your request… you would like to have kind of “reverse” dependent dropdown.

[Mic_L_Angelo]

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

[Mic_L_Angelo]

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

1. The dependent drop down fields to be visible at all times (show_if doesn’t seem to have any effect on dependent drop downs)

2. 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

[pravse]

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