Return values based on SQL queries and dropdowns?

[DrewPhillips3]

I'm trying to see if a project idea is feasible by using Dashboards or Experience Builder.

I have a spreadsheet (that can be a database) containing different construction projects. The dataset looks like:

Company	Area	Metric Value
ABC Co	CA	4
F Corp	CA	2
Momcorp	NY	8
F Corp	CA	7
Momcorp	NY	1

What I'd like to do: build several dropdowns allowing a user to select:

1. Company 1 (say, ABC Co)
2. Company 2 (say, F Corp)
3. An area (say, CA)

And return an average of the metrics for Company 1 and Company 2 in the selected area.

 At this point, I just want to see if this is possible / feasible using AGO tools.

Thanks a ton for your help!

[DrewPhillips3]

I'm trying to see if a project idea is feasible by using Dashboards or Experience Builder.

I have a spreadsheet (that can be a database) containing different construction projects. The dataset looks like:

Company	Area	Metric Value
ABC Co	CA	4
F Corp	CA	2
Momcorp	NY	8
F Corp	CA	7
Momcorp	NY	1

What I'd like to do: build several dropdowns allowing a user to select:

1. Company 1 (say, ABC Co)
2. Company 2 (say, F Corp)
3. An area (say, CA)

And return an average of the metrics for Company 1 and Company 2 in the selected area.

 At this point, I just want to see if this is possible / feasible using AGO tools.

Thanks a ton for your help!

[JonathanMcDougall · Accepted solution]

@DrewPhillips3 

Basically, yes (and I'm hope I'm close to being right when saying this).

I've created a rough, and  I do mean rough, example for you.

I took your data above, created a table, brought it into ExpB, created simple views for each of the filters (so, just two views), created the filters referring Filter 1 to View 1 etc,  and added Data Connected text boxes for the output, each text box containing the appropriate expression.

https://experience.arcgis.com/experience/438103e6c4c546338dd42aa516d27506/

As said, it's rough, but may help you with ideas.

[DrewPhillips3]

That's amazing! You really went above and beyond.

How do you create a view, though? The big issue I kept running into was being unable to get text boxes to show data from JUST 1 filter set if that makes sense.

[JonathanMcDougall]

@DrewPhillips3 

I'll take some screenshots and post up here a wee bit later. I'm in the UK so it's time for dinner 🙂

Glad to be of help.

[DrewPhillips3]

I'm getting close to figuring this out... From your incredibly helpful advice, I have 2 filters (for one company) that pass their queries to a table that shows selected values. These widgets live in a view. But I'm still struggling with getting the text box to show the dynamic values that I want. It seems like the settings I've chosen would work... but I can't figure it out! I must be missing something related to views...

[DrewPhillips3]

I'm going to reply to myself and say that I found the issue! In the text box pane, I was choosing the "Selected Features" option. I assumed that this meant that selected == filtered, which is not the case. I changed the checkbox to "Default" and it worked.

To have another section with another view doing the same thing for different queries, do I have to upload the data again? I'm having trouble ensuring that the queries result in different views.