Arcade: in a pop-up menu, is it possible to show related records from a layer (ex: counties) that intersect the user-selected layer (ex: states)?
**Quick disclaimer – throughout this post, I will be using sample data to explain the issue I am trying to solve. The most important difference between the sample data and the real data (which I cannot share) is that I am not actually using county boundaries to define the “project areas”, but instead am using a slightly different geography. This geography is very similar to counties both in size and in its complete coverage of the United States, however, there is not a 1-1 match between the county boundaries and the boundaries I must use. For the sake of this post however, I will be using county boundaries to define “project areas” because I cannot share the boundaries I am using.**
Hello all – I have been asked to create pop-up menus that show information about projects at different geographic levels: state, congressional district, county, and census tract. I want to show both aggregated financial information for these projects, along with a “clickable” list of the projects whose project areas intersect the user-selected polygon. Below is some information about the data I am working with - I have also included a diagram to show the different joins and relates that are involved:
- The “project area” is determined by the counties (see above disclaimer) that the project is attributed to. In the data relationship shown below, the one-many relationship class between “CountyBoundaries” and “ProjectsbyCounty” establishes the counties that are attributed to each project.
- For example, Project 101a falls into York, Cumberland, and Oxford counties in Maine. The “project area” for 101a is these three polygons for York, Cumberland, and Oxford.
- Each unique projectID has a set of counties associated with it.
- Each county can have several projects associated with it. For example, York County is included in the project area for projects 101a, 201a, 301a, and 401a. Some counties have no projects associated with them, while others have many.
The information for each project comes from a many-one join between ProjectsbyCounty and ProjectsInfo. In ArcGIS Pro, I made the many-one join first, then made the one-many relationship class, and then published the web layer to AGOL. See the below diagram for the data relationship I am working with:
What I am trying to replicate
At the county-level, I have created the exact pop-up menus I have been asked to make. The user selects a county and gets info for all the projects whose project areas are in that county. I wrote arcade expressions to count the number of projects and to sum the “funding” column for the projects in the user-selected county. The key feature of this pop-up menu is the drill-down capability where the user can select an individual project and get more information about that project by pressing the “>” arrows. This drill-down capability is enabled by the one-many relationship class between CountyBoundaries and ProjectsbyCounty. See the below image for the pop-ups at the county-level:
What I cannot figure out how to do
I want to recreate the above pop-up menus for different geographies: state, congressional district, and census tract. I have been unable to determine how to do so. I cannot seem to figure out how to access the related records from CountyBoundaries when the user-selected polygon is a non-county layer (ex: state, congressional district, census tract).
- For example, I want a user to be able to click on the boundary for the state of Maine and see a pop-up menu with all the projects whose project areas intersect the Maine state polygon.
- Similarly, I want a user to select a census tract in Maine and see a pop-up menu with all the projects whose project areas intersect the census tract. For the census tract level, the idea is that I can grab the county or counties that intersect(s) the user-selected census tract, and then access info about the projects in those counties.
An example of what I am looking for at the state-level
Below is exactly what I am looking for at the state level, but for a variety of reasons, I cannot use the method I used to create these pop-ups for my purposes. The pop-up menu displays aggregated financial information about, and clickable drop down-menus for, all the projects whose project areas intersect the state of Maine. In other words, selecting a state will grab all the counties that fall into the state of Maine, and then show a clickable list and the aggregated funding column for all the unique projects that are attributed to those counties in Maine.
Summary
I know this has been a long post, but thought it was necessary to spell out the data relationships I am using and the information I am looking to show in the pop-up menus. My ultimate question: is it possible to use Arcade to create pop-up menus that show the related records (ProjectsInfo) for the counties that intersect the user-selected polygon, whether the user-selected polygon is a state, a congressional district, or census tract?
I imagine that a combination of $feature and the Intersects() might be needed, but I have been unable to determine the exact expression that shows the related records (ProjectsInfo) in a pop-up menu for the non-county layers (state, congressional district, census tract).
Thank you all for your time reading this post.
