Cannot Join to a Standalone Table that has a Definition Query

[JuliusP]

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

1. Set a Definition Query on the standalone table for 1 subsystem (Ex: Roofing Built-up)
2. Join my Buildings layer with the filtered Standalone table
3. Export the joined Buildings layer to a new Buildings_RoofingBuiltUp layer
4. Repeat the process for each subsystem
5. // 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:

1. Join Buildings layer with Standalone table
2. 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,

JP

[BarryNorthey]

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

[JuliusP]

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

[BarryNorthey]

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.    

[JuliusP]

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

[BarryNorthey]

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.   

[JuliusP]

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

[JuliusP · Accepted solution]

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

[Robert_LeClair]

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

[JuliusP]

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)