Dynamically filter and edit features — based on query to related table

Bud — Mon, 23 Jan 2023 11:16:07 GMT

Oracle18c EGDB

I have a feature class called EVENTS (fields: EVENT_ID, EVENT_YEAR) and a related table called FUNDING (fields: EVENT_ID, FUNDING_YEAR).

I want to filter (display in the attribute table) events where there are related funding rows from other years. For example:

Show a 2022 event that has funding from years other than 2022:
- Funding in 2021, 2023, etc.
- (it doesn't matter if there is funding from 2022 or not)
Don't show events that only have 2022 funding rows. And don't show rows that have no funding rows.

I want the filter to be dynamic. And I want the event rows to be editable. I don't need to display any fields from the related table.

What are my options?

Create a definition query that has an SQL subquery that points to the related table:

event_id in (
    select
        e.event_id
    from
        events e
    left join
        funding f
        on e.event_id = f.event_id
    where
        e.event_year <> f.fund_year
        and e.event_status in ('F','P','A','C')
    group by
        e.event_id
    )

I can edit the events in the attribute table. No joins required.

Or, create a database view that selects the applicable funding rows. In ArcGIS Pro, create an in-map join (only matches included) to the view via the common EVENT_ID. Edit the events in the event attribute table.
1. Or, instead of an in-map join, create an in-map relate. Select all funding rows and use the Attribute Table tools to select the related event rows.
Or, do #2 above, but use a Query Layer instead of a database view.

Are there any other options or anything I've overlooked?

Thanks.

topic Dynamically filter and edit features — based on query to related table in ArcGIS Pro Questions

Dynamically filter and edit features — based on query to related table