topic Cannot Join to a Standalone Table that has a Definition Query in ArcGIS Pro Questions

Cannot Join to a Standalone Table that has a Definition Query

JuliusP — Wed, 11 Dec 2024 22:30:37 GMT

ArcGIS Pro 3.2.2 , File Geodatabase

Hello all, Iʻm sure this worked on ArcMap but why is ArcGIS Pro not letting me? I basically am joining 2 tables via Building Number:

Buildings Layer
Subsystems Backlog standalone table

As you can see this is a One-to-Many relationship. My end goal is to create choropleth maps for each subsytem. My workflow in ArcMap was

Set a Definition Query on the standalone table for 1 subsystem (Ex: Roofing Built-up)
Join my Buildings layer with the filtered Standalone table
Export the joined Buildings layer to a new Buildings_RoofingBuiltUp layer
Repeat the process for each subsystem
// to reduce latency I dont want these maps to be symbolized via live joins //

I am trying to do this process in ArcGIS Pro but when joining, it tells me that "The join table has a definition query which will be applied to the input after performing the join." This results in a blank map as my Buildings layer now has a definition query applied to it. Removing that definition query lets me see my Buildings again but it basically joins it to the first record in my standalone table for that building:

This is essentially the same results as:

Join Buildings layer with Standalone table
Set a Definition Query for the subsystem

But this will only join with the first record, and I wouldn't be able to export the view I need!

I need to be able to filter the standalone table BEFORE joining my Buildings layer. It seems ArcGIS Pro is not letting me do that. Am I stuck with having to create separate standalone tables for each subsystem??

Thanks,

Re: Cannot Join to a Standalone Table that has a Definition Query

BarryNorthey — Wed, 11 Dec 2024 23:53:39 GMT

Make sure that the standalone table is in the same fGDB as the feature class.

Re: Cannot Join to a Standalone Table that has a Definition Query

JuliusP — Thu, 12 Dec 2024 00:41:47 GMT

Thanks for the reply Barry. I just tried that and I am still having the same issue of a blank map.

I ended up using ArcMap, which worked perfectly even if the feature class and the standalone table are in different fGDBʻs. Itʻs equivalency issues like this that makes it difficult to fully transition to Pro

Re: Cannot Join to a Standalone Table that has a Definition Query

BarryNorthey — Thu, 12 Dec 2024 01:10:33 GMT

I forgot to mention that you must pick the Join Operation you want in the Add Join dialog when doing a 1-M join. Joining to a table that is outside the fGDB only gives the Join one to first option. The Join Table table must also have an OBJECTID type field. This gets added if a table such as a csv with no OBJECTID field is exported to the fGDB.

Re: Cannot Join to a Standalone Table that has a Definition Query

JuliusP — Thu, 12 Dec 2024 01:28:26 GMT

May I ask which version of Pro you have? I am not seeing that on my end , v3.2.2

Re: Cannot Join to a Standalone Table that has a Definition Query

BarryNorthey — Thu, 12 Dec 2024 03:36:55 GMT

I am using 3.4 and it looks like things have changed since 3.2. As previously noted, make sure that both tables have an OBJECTID type field and are in the same fGDB. I recommend pressing the Validate Join button after making a join to verify that you got a 1-M join.

Re: Cannot Join to a Standalone Table that has a Definition Query

Robert_LeClair — Thu, 12 Dec 2024 16:14:54 GMT

And to add to the discussion, I'm fairly confident that ArcMap did not support 1-M cardinalities but I know ArcGIS Pro does support 1-M

Re: Cannot Join to a Standalone Table that has a Definition Query

JuliusP — Thu, 12 Dec 2024 18:57:11 GMT

Thanks Barry, I'll try installing 3.4 on another machine to test it out

Re: Cannot Join to a Standalone Table that has a Definition Query

JuliusP — Thu, 12 Dec 2024 19:05:14 GMT

No it did not, which is why I try to filter down the standalone table via definition query, so it will be a 1-1 cardinality, before I would join it with my buildings layer.

That process worked with ArcMap, but does not with Arc Pro (at least in 3.2.2)

Re: Cannot Join to a Standalone Table that has a Definition Query

JuliusP — Fri, 13 Dec 2024 23:19:12 GMT

Thanks for all the tips Barry. I have confirmed the following:

The Join Operation option shows up even at version 3.3.2
If the two sources are in different fGDBs, only Join one to First will appear

This sequence will work:
- Join feature class to standalone table with Join one to many option
- Validating Join return multiple matches
- Set definition query AFTER join where StandaloneTable.Subsystem = Total by Building
This sequence will NOT work:
- Set definition query before join where StandaloneTable.Subsystem = Total by Building
- Join feature class to standalone table with Join one to many option
- Validating Join return 0 matches

I guess if there is a solution to the problem, it would be to upgrade to at least v3.3 and use a specific sequence before running a Definition Query.

No way to solve it in v3.2 as the Join Operation option is needed and filtering the table first before a join yields 0 matches... unless people manually create multiple separate tables with the queries they want.

- Dragon