Join/Relate Work Order Data to Feature layer

[GarrettStanley]

Hello, I have been tasked to analyze our work order data so that we can identify trends. I have a gdb that includes shapefiles for all facilities and a xlsx that has all work orders for the last year with a column for facility number (with many work orders per facility).

How do I join/relate this table to the shapefile such that I can do hotspot analysis/work orders by type/work orders by priority etc?

Thanks!

[Eugene_Adkins]

To begin with, you will need a unique identifier that will allow you to join your spatial data to your table. In this case it sounds like you have "facility number" in your table data, so if you have a corresponding "facility number" in your spatial data, this will allow you to create a join between the table and the spatial data.

If your spatial data does not contain "facility number" data, you will need to create this data or you will need to create/populate the spatial data with some unique and identical data contained in your table. From there you will be able to begin manipulating your existing data counts of your types or work order information to create your hotspot analysis.

Hope this helps. 

[GarrettStanley]

Will a join work if I have multiple work orders associated with each building number? Every time in the past that I have done a join, it simply adds the data from the other table as additional columns. In this case, I have multiple columns and rows associated with each building number.

[Bud]

It sounds like your data has a one-to-many relationship.

Arcmap only supported one-to-one joins. But ArcGIS Pro supports one-to-many joins in some cases. In Pro, it will display duplicate rows in the attribute table for each of the workorders on the "many" side.

But one-to-many joins only happen under specific circumstances. The short way of describing it is: if all your data is in the same geodatabase, then Pro will likely create a one-to-many join. But if some of your data is in Excel, then that means all of your data isn't in a single geodatabase, so the join won't be one-to-many, it will be one-to-one (one-to-first).

You might find it hard to decipher the documentation when it comes to why Pro chooses to create a one-to-many vs one-to-one join: Add Join (Data Management). Hint: search that page in your browser for the word "many".
This post might help make sense of why Pro made a one-to-many vs one-to-one join: Indicate what caused type of join to be used (1:1 vs 1:M)

But regardless, Pro will tell you what kind of join it used in the layer properties:

Just a heads up: One-to-many joins in ArcGIS Pro are quite buggy. You might find you need to export to a static copy (such as a separate feature class within the same geodatabase) in order to use the joined data in Pro. 

• Is ArcGIS Pro the right tool for tabular/join-based geodatabase analysis?
• Disallow 1:M join layers as GP inputs instead of producing unexpected results

[Bud]

For what it's worth, your blurb where you say "I have a gdb that includes shapefiles" doesn't really make sense. Data is either in a geodatabase, or a shapefile, but not both.

A geodatabase's layers are called feature classes, not shapefiles.