How to connect multiple data sources via a common identifier and obtain dynamic rendering
Hello everyone,
This post will follow on from the previous one I wrote. (https://community.esri.com/t5/experience-builder-tips-and-tricks/how-to-easily-connect-your-data-and...)
The aim is to be able to filter a first data source (transactions, for example) so that, thanks to a common identifier present in each of the tables, the other data sources (properties, offers, assignments) present in other widgets (Map, List, Table, Graph) are updated. All this without having to duplicate columns in each table concerned or select items on a map to bypass the system.
Last time, I mentioned the difficulty that ArcGIS Experience Builder had in offering dynamic rendering between several widgets with different data sources.
Indeed, if I want to filter my data according to criteria linked to transactions, and have a map rendering that shows me the buildings linked to these transactions, I must add the fields that I use to filter my transactions to my Building table.
In this way, the data I display in my application about buildings is updated according to the transactions I've filtered.
But this process isn't easy to set up and requires a lot of changes to be made to our databases that aren't normally necessary.
Since this post, there's been nothing new on the ESRI side, and no update along these lines, so I had to get my brain working at full speed to find a way of cobbling together something that would work.
So I concentrated on the way widgets currently work: it's possible to set actions on our data. For example, we can add an action when items are selected and only then can we give the order to filter the data records that have been selected. This filtering is done using action parameters, creating a link between two different data sources (one triggering, and one ‘undergoing’ the action) using a common identifier.
So let's suppose that we're filtering a ‘Transaction’ data layer according to the years in which the transactions were signed, and that I don't have a replica of the ‘Transaction year’ column in my ‘Building’ data layer. I want to see the transactions for 2021 and therefore the buildings associated with these transactions on a map.
If I'm doing things using a ‘Modification of record selection’ action and if you followed my first post, this means that once I've filtered my transactions, I have to make a selection on the map by hand in order to filter the buildings.
Otherwise, I need to have added the ‘Transaction Year’ column to my Building layer beforehand for the Group Filter to work, but this solution is not possible if we have an N-1 relationship between our data*.
*the Transaction-Building relationship is N-1, there are N transactions for 1 building, which means that if I add the ‘Transaction Year’ column to the Building table, I'll have to ‘clone’ each building for each transaction involving that building, otherwise I'll lose information. And clearly, cloning buildings sucks because in terms of cartographic rendering it means having N points superimposed...
With that in mind, I was left with only 2 solutions:
- Pray that ESRI will finally develop an action parameter that is triggered not when you SELECT something, but rather when you FILTER something
- Succeed in creating a SELECTION action, without the user having to do it himself.
As you can imagine, I had to focus on the second solution.
The idea was simple: what if I used the Selection tool to simulate a Selection action without the user realising?
So we're going to take a Selection Widget and choose ‘Select according to attributes’ as the source. Then click on ‘New data’. To follow my example, I'm going to choose my data source ‘Transactions’ (in my case it's a simple Table), thanks to the SQL expression generator, I'll be able to set up Filters that will be used to select the data according to attributes (I'm using several fields with the ‘AND’ condition).
Once that's done, all that's left is to set up actions when there's a Modification of record selections. This action is set up with ‘Structure’ as the target, then ‘Filter data records’ is selected and all that's left is to choose the Building source (or any other source) which has an identifier in common with the transactions (in my case we have an ID called ‘ID_LP’).
The final result is as follows:
As you can see, we ‘disguise’ this Selection widget as being a sort of Filter widget, and thanks to this, I can filter my transactions according to using fields that are not present in any other table or entity layer AND I manage to filter my other cascading data sources. Something that used to be impossible.
The other advantage is that, because we've set up our actions so that the data records are filtered, it doesn't show up, either on the map rendering or even in a Table or List Widget. There's no big highlight.
Of course, you have to bear in mind that this technique has its limits:
- Even if there aren't any lines highlighted in blue or Highlight points in neon yellow, you're still selecting data, so if you make a selection by hand in addition, or click on an item, the widget can quickly find itself no longer knowing who the source of the selection is. For optimum use, remember to click on ‘Delete selection’ if you wish to make another type of selection, to avoid conflicts.
- Let's say you want to offer a view of the contents of the Transactions table. You integrate a Table widget so that the user can see the contents of the table. Be very careful with this: if you don't set any filter actions, you will only select the transactions in the table that you have filtered (logically). But if you set up a filter action, you have to do so by setting the same source (i.e. transaction & transaction) as the action's trigger and target. Well, by doing this, you're going to bug the filters in the Selection widget, which will no longer be able to be modified without first deleting the current selection.
Here's what I recommend to get round this little problem: if you want to display tables, maps or other widgets relating to the data source you've used in the Selection tool, duplicate this layer/table for the other widgets. By doing this, you're querying ANOTHER data source (which is actually the same one) and you can set up your filter action to filter the records without bugging the filters in the Selection tool.
I think I've come to the end of this tip, so I hope it's helped you. This solution is really in the ‘do-it-yourself’ category, but at least it has the merit of being able to resolve certain situations.
I'm still hoping that ESRI will one day come up with a solution for this, because I think it's a real shame that in such a powerful GIS tool you can't manage relationships between several data sources by simply using a common ID... something that's been done since the dawn of time in lots of other software.
Gone°
