idea Add Join — Pseudo-ObjectID (row number) as unique ID field for 1:M join in ArcGIS Pro Ideas

Add Join — Pseudo-ObjectID (row number) as unique ID field for 1:M join

Bud — Mon, 15 Jul 2024 04:39:17 GMT

Regarding BUG-000154798: The 'Select By Attributes' tool does not select only records with specified field values added through a one-to-many join but instead selects all records with duplicate ObjectIDs. (Status: Known Limit)

Select By Attributes on joined data — For rows that are 1:M, all rows in the join table get selected, despite selection criteria
Is ArcGIS Pro the right tool for tabular/join-based geodatabase analysis?

Could the various issues with 1:M (one-to-many) joins in Pro be solved by having a pseudo-ObjectID (row number) field as a unique ID?

For example, Oracle's ROWNUM pseudo-column works fine for query layers as the unique ID. Could a similar row-number-based mechanism work for Add Join in the attribute table, too?

select
    cast(rownum as int) as pseudo_objectid,
    lc.*,
    pf.*
from
    lc_events lc --one
left join
    lc_events_project_finances pf --many
    on lc.event_id = pf.event_id

Edit: I think this approach would work for M:M (many-to-many) joins too.

Re: Add Join — Pseudo-ObjectID (row number) as unique ID field for 1:M join

JonathanNeal — Tue, 18 Jun 2024 19:25:26 GMT

@Bud Yes, internally selections work off of objectId's, stored in the layer. When there is a 1:many join, selections will select duplicates. For the Add Join tool, we would require additional development by the Geodatabase team.
The team does have an issue for this:
Title: "Relquerytable to support a unique generated field"
Note1: this is a tough task when implementing it across all data sources, so it will require lots of considerations.
Note2: this will also make analysis tools work correctly on joined layers.

Re: Add Join — Pseudo-ObjectID (row number) as unique ID field for 1:M join

Bud — Tue, 18 Jun 2024 23:33:14 GMT

@JonathanNeal

Would you generate the row number field values using SQL or using ArcObjects? If SQL, I wonder if you would need the FGDB SQL folks to add row number functionality to FGDB SQL: ROW_NUMBER function in FGDB SQL

Re: Add Join — Pseudo-ObjectID (row number) as unique ID field for 1:M join

Bud — Thu, 05 Sep 2024 13:52:24 GMT

Make Query Table (Data Management)
You can provide a key field option and key fields list. This information defines how rows are uniquely identified and is used to add a dynamically generated Object ID field to the data. Without an Object ID field, selections will not be supported.

How to join using Make Query Table?

Limitation: The data in a Make Query Table can't be edited.

Re: Add Join — Pseudo-ObjectID (row number) as unique ID field for 1:M join

Bud — Thu, 05 Sep 2024 15:14:30 GMT

@JonathanNeal and @JillScholz_esri

Make Query Table has functionality that lets us generate an OBJECTID field on the fly in feature layers and table views. The OBJECTID values are truly unique, even if the relationship of the "join" is one-to-many.

Make Query Table (Data Management) > Key Field Options parameter
Generate a key field—If no key fields have been specified, an Object ID field that uniquely identifies each row in the output table will be generated.

Could the same mechanism be used in the attribute table of a proper one-to-many join via Add Join?

Would that help solve the 1:M join selection issue (duplicate OBJECTIDs)?
BUG-000154798: The 'Select By Attributes' tool does not select only records with specified field values added through a one-to-many join but instead selects all records with duplicate ObjectIDs.

Re: Add Join — Pseudo-ObjectID (row number) as unique ID field for 1:M join

JonathanNeal — Thu, 05 Sep 2024 17:11:33 GMT

@Bud If you are after certain selected records in the related table, consider this approach.

Use the Add Relate tool (or make a relationship class if you want it permanent)
Enable the layer property Automatically select related records—ArcGIS Pro | Documentation
Do the selection
On the related table, You can highlight records you wish to process.