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

JuliusP

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