Experience Builder Cannot Support Relational Databases because it Cannot Support Related Data

[USArmyCorpsEngineers]

Experience Builder cannot support related data and so cannot support relational databases, the most common type of database, as a platform for a user dash/interface.

• ENH-000161431-Expand on the capability use of Relational Databases in ArcGIS Experience Builder

The United States Coastal Research Program (USCRP) has built a relational database containing a variety of information related to Coastal Science and Research (Experiments, Results, Investigators, Awards, Outcomes, etc.). As a relational database, all the tables are associated to each other and the data within them are referenced via an assigned UID in the main table that is then used across tables as a foreign key. The database consists of 10 related tables with one-to-many relationships between them for cardinality; only two of these ten tables have spatial data (XY coordinates) within them. ArcPro was used to upload the 10 tables and establish their relatedness with the foreign key; this relationship can access/view all the related information from other tables within the ArcPro environment. The database is hosted on ESRI's Arcserver.

When this data is brought into Map Viewer with the REST API link, the data are NOT related anymore. Only  the information (fields and data) that is inherently within the main table and not the information related to it from the other tables via the foreign key. Appended to this main table, we should be able to see the related nine tables and expand them to view their fields and data in additional columns. When these same data are opened in Map Viewer Classic, the established relationship and expanded columns from other tables are visible/accessible. Map Viewer does not support related tables, but Map Viewer Classic does?

We tried to use Experience Builder to build a user interface (UI) for this database. The purpose of the UI is to allow users to filter and search the data. The data are funded projects and so a user will use one or many filters and/or an open search bar to query and establish multiple criteria to filter the data on where the criteria they would filter on are across tables and related via the foreign key and related status. None of the widgets permit related data in ESRI Experience Builder, i.e. Experience Builder does not support related data.

No matter what widget we open the data in in Experience Builder, the main table does not show the relationship across tables so that we cannot filter, search, or query across tables. No combination of actions can be used to work around this. Experience Builder effectively ignores that the relationship exists, just like Map Viewer. This is a major issue - Experience Builder cannot support relational databases, the most common type of database, as a platform without 1) fixing this issue or 2) the user getting very creative, using their time and effort ineffectively in a manner that vastly limits the power of a relational database.

Given the needs of our organization, we opted for option two above. As a temporary workaround, we created a ‘dummy’ dataset with a new REST API where we manually appended some of the information from other tables into the main table as strings in new fields. By doing this, we can allow users to filter the data provided, but all users are limited in that they can only query the small sliver of information we gave in that one table and not the actual full database.  This took a lot of time, is certainly not perfect, and only works kind of because the database is smaller right now, but would pose a major point of jamming in the future beyond the additional major issue of this not giving us the functionality or power of an actual relational database.

Thanks in advance for considering this request!

[Mike_Koutnik]

I have similar complex need. I have several feature classes and a few non-spatial tables that are each related in a one-to-many way to two "district" feature classes. The features in one district feature class are sub-parts of the other district feature class. The user needs to be able to pick one district (from either of the two district layers), have the map zoom to that layer, and view all the "transactions" within that district (from the other features classes). The challenge is that I need to present users with various summary indicators of the other feature classes. I can find no way to use the identity of the user-chosen district (using the district filter) to inform the filtering (selection?) of the other features and non-spatial tabular records that will be summarized in an indicator, chart, etc. The added complexity is that some features span multiple districts. And the features that do span multiple districts need to be accounted for in each district they touch. So I've created a non-spatial summary table that summarizes the transaction features for each district. It should then be easy to present those summary data in an indicator, graph, etc. But, again, I can't find a way to set a trigger on the filter to set an action on the summary table to select the correct records.

[danbarone]

I am building a multi-application platform in Experience Builder that will rely on relational databases.  This is a critical issue that needs to be addressed!