topic Re: Creating a Joined View Layer using 3 tables in ArcGIS Online Questions

Creating a Joined View Layer using 3 tables

JoshB123 — Wed, 10 Jul 2024 12:59:49 GMT

Hi,

I have a Survey123 form that collects data into a feature layer that contains 3 related tables.

In order to create summaries, reports and analysis on the data, I need to be able to join all tables together.

Is it possible to create layer views joining more than 2 tables? If not, this seems like a major limitation if Arconline allows me to create the tables in the first place.

Joining multiple tables together is a fundamental task within SQL databases I manage.

Thanks

Josh

Re: Creating a Joined View Layer using 3 tables

jcarlson — Wed, 10 Jul 2024 14:27:54 GMT

No, you cannot create a joined layer that flattens your tables. Well, you can with two, but not more than that.

We get around this by using Data Expressions to flatten the data, but that would only be of use in a Dashboard, really.

Re: Creating a Joined View Layer using 3 tables

JoshB123 — Wed, 10 Jul 2024 15:04:14 GMT

Thanks that's useful to know.

It seems like a limitation that if improved, could open doors to a lot smarter reporting and analysis.

It seems strange that it doesn't already exist?

Re: Creating a Joined View Layer using 3 tables

jcarlson — Wed, 10 Jul 2024 18:37:35 GMT

Well, it's one of the trade-offs with hosted layers. You don't have to do any of the direct-database maintenance, but complex SQL commands are no longer available to you. Presumably, users who require the more advanced stuff would publish a registered service that connects to an Enterprise GeoDatabase, rather than using a hosted layer.

Using Survey123 Connect, it's possible to create a survey that connects to an existing feature service, so you could create a survey that hits your database tables, but then also publish your own registered view layer based on the SQL query of your own devising.

More work, but you can get the more exact thing you need.

Re: Creating a Joined View Layer using 3 tables

JoshB123 — Thu, 11 Jul 2024 13:51:14 GMT

My initial thought was to use Arcgis Server in our SQL Server to do the backend database management, but im out of my depth knowing if this is a good solution.

When you say "hits database tables" do you mean an Enterprise GeoDatabase within a SQL engine?

Thanks!