topic Re: Filter based on relationship class? in ArcGIS Pro Questions

Filter based on relationship class?

JasonCarter — Fri, 22 Dec 2017 16:03:08 GMT

Hello,

I have a feature class of point data and a CSV file containing analytical chemistry results to each point (Groundwater Wells). Both are stored in an ArcGIS Pro file geodatabase. There are multiple results, on different dates, associated with the same well (point) in the CSV file. I have created a relationship class between the feature and the CSV file that links the results to the Well.

My question is - how can I filter the feature class points based on the analytical results? I'd like to filter by values greater than or less than a result, and then only show points that meet the criteria.

Also, is it possible to do this in ArcGIS online without hosting as a feature service?

Thank you.

Re: Filter based on relationship class?

XanderBakker — Fri, 22 Dec 2017 16:56:54 GMT

You can use a subquery to filter features based on a condition defined on related records: SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop

Re: Filter based on relationship class?

JasonCarter — Sat, 23 Dec 2017 19:32:38 GMT

Thank you. I've never done that before. I tried to create a query but the related table is not recognized.

Do I write the query on the feature layer or the related table? For instance I have the feature layer of Wells and the related table of analytical results. I want to show well point data where analytical results are > a value.

SELECT * FROM wells WHERE (SELECT result FROM chemical_results WHERE result > 0.005) but this doesn't work for me.

Re: Filter based on relationship class?

XanderBakker — Sun, 24 Dec 2017 20:08:26 GMT

Can you share (part) of your data so I can test?

Re: Filter based on relationship class?

JasonCarter — Mon, 25 Dec 2017 05:35:20 GMT

Yes, sure. The common key is CODE. Not every code in the location data has a result. I've done the relationship class. I just want to query the location set by the value of the results (FINDING), say > 0.005

Thank you

Location data:

CODE	LONGITUDE	LATITUDE
4910014-031	-122.678	38.23603
4910014-033	-122.677	38.2028
4910014-034	-122.667	38.19611
4910014-035	-122.674	38.19501
4910014-039	-122.672	38.2087
4910014-040	-122.67	38.20591
4910014-041	-122.708	38.22682
4910014-027	-122.678	38.22556
4910014-030	-122.688	38.22437
4910014-014	-122.679	38.22806
4910014-006	-122.704	38.21326
4910014-007	-122.678	38.19559
4910014-008	-122.678	38.19579
4910014-009	-122.688	38.21145
4910014-010	-122.681	38.22087
4910014-011	-122.689	38.22092
4910014-004	-122.696	38.21297
4910014-013	-122.689	38.2062
4910014-002	-122.698	38.21007
4910014-015	-122.699	38.23045
4910014-016	-122.694	38.22082
4910014-018	-122.692	38.23437
4910014-021	-122.676	38.22038
4910014-022	-122.671	38.21691
4910014-012	-122.687	38.23444
4910014-029	-122.676	38.23139
4910014-005	-122.675	38.21342
4910014-044	-122.678	38.23387
4910014-001	-122.691	38.21583
4910020-002	-122.884	38.36565
4910020-025	-122.873	38.38035
4910020-026	-122.873	38.3804
4910020-050	-122.87	38.38629
4910020-051	-122.87	38.38629
4910020-001	-122.887	38.36671
4910020-003	-122.881	38.36625
4910020-006	-122.768	38.25637
4910020-007	-122.873	38.37973
4910020-008	-122.873	38.38037
4910020-012	-122.879	38.36916
4910020-052	-122.87	38.38639
4910020-017	-122.877	38.36937
4910020-015	-122.877	38.36844
4910020-018	-122.877	38.37141
4910020-019	-122.806	38.29292
4910020-020	-122.791	38.27819

Results:

CODE	DATE	FINDING
4910020-030	7/15/2003	0
4910020-030	8/11/2003	0
4910020-030	9/16/2003	0
4910020-030	12/17/2003	0
4910020-030	2/18/2004	0
4910020-030	3/18/2004	0.5
4910020-030	4/6/2004	0
4910020-030	5/11/2004	0
4910020-030	10/12/2004	0
4910020-030	11/9/2004	0.5
4910020-030	12/7/2004	0
4910020-030	1/5/2005	0
4910020-030	2/10/2005	0.005
4910020-030	3/8/2005	0
4910020-030	4/13/2005	0
4910020-030	4/13/2005	0.005
4910020-030	5/12/2005	0.005
4910020-030	6/20/2005	0.005
4910020-030	8/16/2005	0.005
4910020-030	9/28/2005	0.005
4910020-030	10/25/2005	0.005
4910020-030	5/25/2006	0.005
4910020-030	6/19/2006	0.005
4910020-030	9/18/2006	0.005
4910020-030	12/11/2006	0.005
4910020-030	3/27/2007	0.010
4910020-030	6/19/2007	0.005
4910020-030	9/17/2007	0.005
4910020-030	12/4/2007	0.036
4910020-030	3/3/2008	0.005
4910020-030	6/18/2008	0.005
4910020-030	9/8/2008	0.005
4910020-030	12/10/2008	0.005
4910020-030	6/9/2009	0.005
4910020-030	9/16/2009	0.005
4910020-030	3/16/2010	0.005
4910020-030	6/8/2010	0.005
4910020-030	9/1/2010	0.005
4910020-030	12/14/2010	0.005
4910020-030	3/15/2011	0.005
4910020-030	9/19/2011	0.005
4910020-030	12/15/2011	0.005
4910020-030	3/13/2012	0.005
4910020-030	6/12/2012	0.005
4910020-030	9/17/2012	0.005
4910020-030	12/10/2012	0.005
4910020-030	3/11/2013	0.005
4910020-030	6/12/2013	0.005
4910020-030	9/3/2013	0.005
4910020-030	12/3/2013	0.005
4910020-030	3/5/2014	0.005
4910020-030	6/10/2014	0.005
4910020-030	9/23/2014	0.005
4910020-030	12/9/2014	0.005
4910020-030	3/4/2015	0.005
4910020-030	6/2/2015	0.005
4910020-030	9/9/2015	0.005
4910020-030	12/17/2015	0.005
4910020-030	3/9/2016	0.005
4910020-030	6/27/2016	0.005
4910020-030	9/27/2016	0.005
4910020-030	12/14/2016	0.005
4910020-030	3/14/2017	0.005
4910020-030	6/7/2017	0.005
4910020-030	9/13/2017	0.005
4910014-001	4/26/1989	0
4910014-001	7/25/1989	0
4910014-001	10/3/1989	0
4910014-001	3/6/1990	0
4910014-001	2/5/1991	0
4910014-001	2/25/1999	0
4910014-001	9/20/2005	0
4910014-002	3/6/1990	0
4910014-002	1/15/1991	0
4910014-002	2/25/1999	0
4910014-002	9/20/2005	0
4910014-004	5/9/1989	0
4910014-004	7/11/1989	0
4910014-004	9/12/1989	0
4910014-004	3/6/1990	0
4910014-004	1/15/1991	0
4910014-004	2/25/1999	0
4910014-004	9/20/2005	0
4910014-005	5/9/1989	0
4910014-005	7/11/1989	0
4910014-005	9/12/1989	0
4910014-005	3/6/1990	0
4910014-005	4/16/1991	0
4910014-005	2/25/1999	0
4910014-005	9/20/2005	0
4910014-006	4/26/1989	0
4910014-006	7/25/1989	0
4910014-006	10/3/1989	0
4910014-006	3/6/1990	0
4910014-006	2/5/1991	0
4910014-006	2/25/1999	0
4910014-006	9/20/2005	0
4910014-007	7/25/1989	0
4910014-007	9/12/1989	0
4910014-007	3/6/1990	0.0057
4910014-007	1/29/1991	0
4910014-007	5/25/1999	0
4910014-007	6/8/1999	0
4910014-007	6/29/1999	0
4910014-007	9/19/2005	0
4910014-008	4/26/1989	0.006
4910014-008	5/16/1989	0
4910014-008	7/25/1989	0
4910014-008	9/12/1989	0
4910014-008	3/6/1990	0
4910014-008	1/29/1991	0
4910014-008	2/25/1999	0
4910014-008	9/19/2005	0
4910014-009	5/2/1989	0
4910014-009	5/2/1989	0
4910014-009	7/11/1989	0
4910014-009	9/12/1989	0
4910014-009	5/21/1991	0
4910014-009	3/2/1999	0
4910014-009	9/19/2005	0
4910014-010	8/1/1989	0
4910014-010	9/12/1989	0
4910014-010	3/6/1990	0
4910014-010	2/12/1991	0
4910014-010	3/2/1999	0
4910014-010	9/19/2005	0
4910014-011	5/2/1989	0
4910014-011	8/1/1989	0
4910014-011	9/12/1989	0
4910014-011	3/6/1990	0
4910014-011	2/12/1991	0
4910014-011	2/12/1991	0.009
4910014-011	2/25/1999	0
4910014-011	9/19/2005	0
4910014-012	5/2/1989	0
4910014-012	8/1/1989	0
4910014-012	9/12/1989	0
4910014-012	12/3/1991	0
4910014-012	2/25/1999	0
4910014-012	9/20/2005	0
4910014-013	6/27/1989	0
4910014-013	8/8/1989	0
4910014-013	9/12/1989	0
4910014-013	3/6/1990	0
4910014-013	1/15/1991	0
4910014-013	2/25/1999	0
4910014-013	9/20/2005	0
4910014-014	5/2/1989	0
4910014-014	5/2/1989	0
4910014-014	8/1/1989	0
4910014-014	9/12/1989	0
4910014-014	3/6/1990	0
4910014-014	1/29/1991	0
4910014-014	3/2/1999	0
4910014-014	9/19/2005	0
4910014-015	5/9/1989	0
4910014-015	8/8/1989	0
4910014-015	3/15/1990	0
4910014-015	1/29/1991	0
4910014-015	3/1/1999	0
4910014-015	12/20/2005	0
4910014-016	5/2/1989	0
4910014-016	8/8/1989	0
4910014-016	9/12/1989	0
4910014-016	3/6/1990	0
4910014-016	2/5/1991	0
4910014-016	5/25/1999	0
4910014-016	9/19/2005	0
4910014-018	8/8/1989	0
4910014-018	9/12/1989	0
4910014-018	3/15/1990	0
4910014-018	7/30/1991	0
4910014-018	1/12/1993	0
4910014-018	8/31/1999	0
4910014-018	9/20/2005	0
4910014-021	5/2/1989	0
4910014-021	8/8/1989	0
4910014-021	9/12/1989	0
4910014-021	3/6/1990	0
4910014-021	2/9/1993	0
4910014-021	3/1/1999	0
4910014-021	9/20/2005	0
4910014-022	5/9/1989	0
4910014-022	8/8/1989	0
4910014-022	9/12/1989	0
4910014-022	3/20/1990	0
4910014-022	3/16/1993	0
4910014-022	3/1/1999	0
4910014-022	9/20/2005	0
4910014-027	4/26/1989	0
4910014-027	7/25/1989	0
4910014-027	9/12/1989	0
4910014-027	3/6/1990	0
4910014-027	1/29/1991	0
4910014-027	2/2/1993	0
4910014-027	3/1/1999	0
4910014-027	9/20/2005	0
4910014-029	4/26/1989	0
4910014-029	7/25/1989	0
4910014-029	9/12/1989	0
4910014-029	3/6/1990	0
4910014-029	1/15/1991	0
4910014-029	1/20/1993	0
4910014-029	3/1/1999	0
4910014-029	9/20/2005	0
4910014-030	7/25/1989	0
4910014-030	9/12/1989	0
4910014-030	3/6/1990	0
4910014-030	1/15/1991	0
4910014-030	1/12/1993	0
4910014-030	3/1/1999	0
4910014-030	9/20/2005	0
4910014-031	4/26/1989	0
4910014-031	7/25/1989	0
4910014-031	9/12/1989	0
4910014-031	3/6/1990	0
4910014-031	1/15/1991	0
4910014-031	1/20/1993	0
4910014-031	3/1/1999	0
4910014-031	11/9/2004	0
4910014-031	9/20/2005	0
4910014-033	6/27/1989	0
4910014-033	7/5/1989	0
4910014-033	9/12/1989	0
4910014-033	3/6/1990	0
4910014-033	2/5/1991	0
4910014-033	9/2/1993	0
4910014-033	8/31/1999	0
4910014-033	9/20/2005	0
4910014-034	7/28/1989	0
4910014-034	10/9/1990	0
4910014-034	3/12/1991	0
4910014-034	3/2/1993	0
4910014-034	3/1/1999	0
4910014-034	9/20/2005	0
4910014-035	3/15/1990	0
4910014-035	10/9/1990	0
4910014-035	2/26/1991	0
4910014-035	7/29/1991	0
4910014-035	11/30/1993	0
4910014-035	3/2/1999	0
4910014-035	9/20/2005	0
4910014-039	11/27/1990	0
4910014-039	6/16/1992	0
4910014-039	3/2/1993	0
4910014-039	3/1/1999	0
4910014-039	9/20/2005	0
4910014-040	10/11/1990	0
4910014-040	3/2/1993	0
4910014-040	3/1/1999	0
4910014-040	9/20/2005	0
4910014-041	7/9/1992	0
4910014-041	8/10/1993	0
4910014-041	11/30/1993	0
4910014-041	2/15/1994	0
4910014-041	5/17/1994	0
4910014-041	3/1/1999	0
4910014-041	9/19/2005	0
4910014-044	6/8/1999	0
4910014-044	12/21/1999	0
4910014-044	3/21/2000	0
4910014-044	6/20/2000	0
4910014-044	9/19/2000	0
4910014-044	9/19/2005	0

Re: Filter based on relationship class?

XanderBakker — Tue, 26 Dec 2017 11:58:48 GMT

Hi Jason Carter ,

Try this expression on the locations point featureclass:

CODE IN (SELECT CODE FROM RelTbl WHERE FINDING > 0.005)‍

In this case CODE is the field that the relationshipclass between the points featureclass and the related table is based on. RelTbl is the name of my related table. FINDING is the field with the values.

These points (in green) will be filtered:

Which are these locations:

Validation:

Do a select by attributes on the related table with the following query:

FINDING > 0.005‍

7 records get selected:

Select the related records:

Results in the same 3 locations:

Re: Filter based on relationship class?

JasonCarter — Tue, 26 Dec 2017 16:37:26 GMT

Thank you, that worked very well! I see now how the sub queries work now.

How did you change the symbol on the selected feature? When I perform a definition query the results that don't match disappear.

Re: Filter based on relationship class?

XanderBakker — Tue, 26 Dec 2017 17:14:29 GMT

Glad it worked for you! Can you mark the post with the correct answer and the correct answer? This way it will be easier for other users to find the answer when they have a similar problem.

I didn't change the symbology. I copied the layer, changed the symbology and applied the filter to visually show which locations match the query. When you apply a filter (definition query) it will not select features or change symbology, it will just show the matching features in the symbology defined for the layer.

Re: Filter based on relationship class?

JasonCarter — Tue, 26 Dec 2017 17:25:28 GMT

Great, thank you so much. I had a feeling that is what you did. Just wanted to double check to make sure I wasn't missing out on functionality! Cheers

Re: Filter based on relationship class?

deleted-user-H23pxZFaj1tR — Fri, 09 Aug 2019 18:23:06 GMT

Is this possible using Arcade in AGOL? I'm looking for interactive input of values that drive the query.

Re: Filter based on relationship class?

XanderBakker — Mon, 12 Aug 2019 13:29:22 GMT

Hi DTreering@invenergyllc.com_invenergyllc .

Can you elaborate a little more on what you are looking for?

Re: Filter based on relationship class?

deleted-user-H23pxZFaj1tR — Mon, 12 Aug 2019 14:19:04 GMT

HiXander Bakker‌,

I have a county polygon layer (PK=FIPS) related to a bridge table with two fields (FK1=FIPS, FK2=ReportID), which is related to a Reports table (PK=ReportID). Using your fantastic guidance here, I was able to retrieve the reports from the second related table for the clicked $feature.FIPS by iterating over the FeatureSet returned from the bridge table, and sending a second query to the Reports table. Bonus points: I have three other overlapping polygon geographies with bridge tables that are returning Reports by using Intersect functions, all from within the Counties layer.

Reports have dates and types, and I'd like to provide filter options to select reports based on user-specified values: a range for dates, and a single selection for type.

Re: Filter based on relationship class?

XanderBakker — Mon, 12 Aug 2019 16:32:03 GMT

Hi David Treering ,

I'm afraid that user defined filters based on a "virtual" field (Arcade expression) is not possible yet. Do you want to do this in a Web Map of in a Web App or Dashboard? I also wonder when you filter a layer in your web map, if this will affect the results of any Arcade expression. I haven't tried that yet.

Re: Filter based on relationship class?

deleted-user-H23pxZFaj1tR — Mon, 12 Aug 2019 16:48:15 GMT

Thanks, I have not decided on the presentation yet, but either WAB or Dashboard, probably.

I tried filtering the Reports table in the Web Map, which is queried using FeatureSetByName($map,...); but that did not change the results returned, unfortunately.

So there is no way to pass a variable to the Arcade expression at runtime?

Re: Filter based on relationship class?

XanderBakker — Mon, 12 Aug 2019 18:53:37 GMT

Hi David Treering ,

I'm afraid there is no way (unless you want to develop this app) to pass a variable to an Arcade expression. I could have used this several times in the past, but it is not possible (yet?). Would be a good thing to have. If you create an idea and I will definitely +1 it: ArcGIS Ideas

Re: Filter based on relationship class?

deleted-user-H23pxZFaj1tR — Mon, 12 Aug 2019 19:45:06 GMT

Thank you! Here is the Idea.

Re: Filter based on relationship class?

XanderBakker — Tue, 13 Aug 2019 15:29:13 GMT

Hi David Treering ,

Don't forget to up-vote your idea yourself too. That does not happen automatically...

Re: Filter based on relationship class?

KennethBusby — Mon, 19 Aug 2019 19:34:10 GMT

Is it possible to filter a feature based on an attribute in a related feature?

I want to only see the animals that are related to complaints with a status of Open.

I can not find anything that will allow you to create a filter using related features or tables.

Re: Filter based on relationship class?

XanderBakker — Tue, 20 Aug 2019 13:05:38 GMT

Hi kbusby ,

I don't think this is supported (yet), but I do like this use case. What you could do in case you are working with static data, is to create a new field and use field calculation to extract the related values. After that you can filter the layer. The downside is, when your (related) data changes, the calculated values are not updated automatically.

Re: Filter based on relationship class?

deleted-user-H23pxZFaj1tR — Tue, 20 Aug 2019 13:22:06 GMT

Setting filters on the related table within the web map does not help. Since this is the case, I'm having trouble understanding exactly what $feature and $layer are referencing. Can this be clarified beyond what the documentation says?