How to easily connect your data and make their interaction dynamic in Experience Builder with multiple data source.
Hello everyone, thanks to @JeffreyThompson2 for all the posts in this group. Today it's my turn to add my contribution, which, although it may seem basic to some, will surely help many users to better manage the interaction between several data sources in ArcGIS Experience Builder.
That tips is avaiable for Online Users and Enterprise User (i'm on ArcGis Enterprise 11.1) by using an ArcGIS Experience Builder Developer Edition (more detail here : https://developers.arcgis.com/experience-builder/guide/install-guide/)
ArcGIS Experience Builder is perfect for creating applications that combine maps, charts, tables, lists and more. Layout and production of a user application is really very simple. You'd be tempted to throw your entire database into it, and with just a couple of clicks you'd be able to interact in all directions, resulting in an ergonomic and efficient application. But despite its power, Expérience Builder does have its limitations, particularly when it comes to managing multiple data sources simultaneously.
When I started with version 1.10 of Experience Builder, the Filter widget could only manage a single data source, which made it difficult to cross-analyze several themes.
In my business, I need to cross-reference five types of data:
- Buildings
- Current offers (e.g.: 1000 m² available out of 15,000 m²)
- Past transactions (e.g.: 5000 m² leased, 15,000 m² sold)
- Completed assignments (customer history)
- Property owners
If I can enter all this information for a given building, I've won.
In a conventional information system (IS), each theme is a table linked by identifiers (IDs).
Here's a simple example of what cleanly processed tables look like in a database. Primary and foreign keys to link everything together.
A simple query on the “Buildings” table would update all the others.
But in Experience Builder version 1.10, it was impossible to apply a filter on multiple data source. Filtering buildings didn't update the other data.
My only solution was to set up selection actions between all the data and explain to my users that they had to manually select the buildings after filtering to update the other elements contained in other widgets (offers & transactions being listed in Table widgets for example) ... a method that wasn't very user-friendly.
A second method, if all our data were geographical (which is not necessarily the case), would have been to make all the Feature Layers linked to the buildings invisible and, via a selection on the map, to make everything “dynamic” via the selection, but the principle remains the same.
Then, version 1.14 introduced group filters, enabling multiple sources to be filtered at the same time. Progress, but with a limit: if I filter buildings by surface area (>5000 m²), how can I apply the same filter to other tables/layers if, for example, the “Missions” table doesn't contain this information?
The solution: add the fields required for filtering to each table. This works, but it forces you to store useless data in tables that weren't designed to hold it, just to get around the system's limitations.
So first tip: be sufficiently friendly with your Data Manager, and he'll accept that you ask him for a column to count strawberries in a table that details the quality of bananas. (in a real world you just joined the strawberries ID on the banana table and use a join function to see all data about these delicious strawberries).
Even if, from an IS point of view, this method is clearly contrary to the creation of a solid model, at least you'll be bringing a good dose of dynamism into your application.
Continuing my research, I discovered another cool feature: relationship classes.
In ArcGIS Pro, the “Create Relationship Class” tool enables you to link two sources via a common ID. By exporting the main layer with its relationships to Portal/ArcGIS Online, you can retrieve all linked data directly in a tooltip via Arcade.
var transactions = FeatureSetByRelationshipName($feature,"SDE.TRANSACTIONS_UTILISATEURS")
All the Arcade functions you'll need to call another data source connected to your main Feature Layer need to use the FeatureSetByRelationshipName function to works.
In my Arcade exemple i called the user's transactions table and write some code below to see the information about these transactions when i clicked on one of my buildings on the map.
var info = ''
for (var f in transactions){
info += `<div style='text-align: left;'><span style='font-size: 16px; font-family: Calibri, sans-serif; color: rgb(1, 42, 132);'>
<b>Date : </b>${iif(f.TRA_TRIMESTRE == '#VALUE!','', f.TRA_TRIMESTRE)} ${DefaultValue(Text(f.TRA_DATE,'Y'), 'Pas de date de transaction recensée')}<br>
<b>Preneur : </b>${iif(isEmpty(f.TRA_ACQUEREUR), "Pas de preneur recensé", f.TRA_ACQUEREUR)}<br>
<b>Surface : </b>${iif(IsEmpty(f.TRA_SURFACE),"Pas de surface rencensée", Text(Round(f.TRA_SURFACE,0),'#,###')+" m²")}<br>
<b>Valeur au m² : </b> ${iif(isempty(f.tra_valeur__m_),'Pas de valeur recensée',Round(f.tra_valeur__m_,0)+" €/m²")}<br>
<br></div>`
}
However, this solution has one drawback: the data you're going to retrieve via the relationship class can only be accessed via Arcade (or you may be able to develop widgets to call these classes, in which case you don't need this post). So it's impossible to integrate this data into a Table or Chart widget after the fact, because Experience Builder doesn't handle relationship classes at all (and I hope that one day it will be able to.
However, Arcade is quite magical, and with third-party APIs it's easy to integrate graphs, tables and the like.
Here's an example of a graphic integrated into an Arcade tooltip using the Quickchart.io application. The other amazing thing is that graphs can be generated using connected data, thanks to our relationship classes.
With this method, we're able to display all the information related to a building without complicating the database or the Data Manager's work. A clean, efficient approach to filtering multiple sources simultaneously!
Last tip, very simple : combine the two methods !
1st Method (Group Filter) : Filter 25,000 buildings down to a short-list of 25, with access to the mass of information behind thanks to the group filters and our manipulation on the database. Result : your Table widget with all the transactions is updated and shows you the batch of transactions associated of your 25 final buildings selected by filter. You have not to made a manual selection.
2nd Method (Relationship Class) : Click on one of the 25 final buildings selected by filter. At the level of the individual building, you can see precisely each type of information (offers, transactions, etc) concerning THAT specific building.
With these two methods combined, you can offer both massive and individual analysis of your data.
I'd like to finish with a few words for the future: a few days ago, I wrote an idea proposal in the “Ideas” section of the ArcGIS Experience Builder board : https://community.esri.com/t5/arcgis-experience-builder-ideas/improve-filter-widget-to-bring-more-dy... (you can support this idea if you like it).
The idea is to propose an ultimate evolution of the Filter widget to bring more dynamism and fluidity to the application, but above all, to its users/developers.
To achieve this, the tool would need to be capable, in the same way as the relationship classes, of detecting, when an entity is selected or filtered, all the other entities, from all the other data sources selected/filtered, which also have this identifier in their data table.
I hope this post has provided you with some useful information for your work. A year ago, when I was stuck trying to get dynamism in my application, I was running out of answers and this is the kind of solution I was hoping to find.
Gone°
