topic Re: Return values based on SQL queries and dropdowns? in ArcGIS Experience Builder Questions

Return values based on SQL queries and dropdowns?

DrewPhillips3 — Wed, 28 Jul 2021 19:30:54 GMT

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:

Company 1 (say, ABC Co)
Company 2 (say, F Corp)
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!

Re: Return values based on SQL queries and dropdowns?

JonathanMcDougall — Wed, 28 Jul 2021 23:30:13 GMT

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

Re: Return values based on SQL queries and dropdowns?

DrewPhillips3 — Thu, 29 Jul 2021 16:13:04 GMT

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.

Re: Return values based on SQL queries and dropdowns?

JonathanMcDougall — Thu, 29 Jul 2021 17:10:53 GMT

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

Re: Return values based on SQL queries and dropdowns?

DrewPhillips3 — Tue, 03 Aug 2021 18:17:21 GMT

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

Re: Return values based on SQL queries and dropdowns?

DrewPhillips3 — Tue, 03 Aug 2021 19:01:34 GMT

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.