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

AppSheet AppSheet Q&A
1

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?

2

@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

3

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

4

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

5

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?

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

6

@Mic_L_Angelo

7

@Mic_L_Angelo

8

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

9

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?

10

You should put it into Valid_If

11

@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

12

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

13

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

14

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

Thanks for the assist

15

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.

16

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

17

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?

18

@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

19

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

20

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