Popup and Oracle DB

[ChristineBaver]

Working with ArcGIS Pro.

Hello, I am writing to request confirmation that my end goal is possible (or not possible). I would welcome any links that would provide guidance. Example below is provided for clarity. 

I have a shapefile of the location of buildings with a unique building ID. 

I would like to connect to an Oracle DB (attribute information, not spatial information) that contains information about the building. Information in the Oracle DB changes daily. Oracle DB includes tables include the building ID (that is also part of the shapefile).

I would like to create a popup that references information from the Oracle DB. More specifically, I'd like to create a popup that has a graph (bar plot) of the last 6 months (contained in the Oracle DB) of data. 

I am comfortable writing SQL script to obtain the last 6 months of data. Unsure if I need to learn python, ARCADE and/or java and if my goal is possible in ArcGIS Pro. 

Thank you very much!

[RhettZufelt · Accepted solution]

Sure.  Once you create a database connection in Pro that connects to your Oracle database, you then just need to right-click on the shapefile and Add Join.

Join Table will be the Oracle data table, and buildingID would be the join fields for each table.

Now, the Oracle data will be (virtually) appended to the right side of the shapefile attribute table, and all the field/values will be available for the popup configuration.

Then, you can put a definition query on the shapefile to only show last 6 months of data.

However, since definition query doesn't support ALL the functionality of SQL, not sure if you can have it query the last 6 month, or if you will have to us "is on or after" a specified date.

Another option, make a view in Oracle that just has the last 6 months of data.  Then, add the join using this view instead of the table, and un-check the "Keep All Target Features" in the Add Join dialog.

This will limit what you see in the shapefile to only features that have matching join fields in the view table.

R_

[ChristineBaver]

Thank you! 

I was able to successfully connect to the Oracle database. 

I right clicked on my shapefile to do a join with the Oracle database and I was unsuccessful in using "Add Join." I was unable to refence a table from my Oracle database as the join table (input table was my shapefile). 

I ended up importing my dataset. This dataset contains information on building ID, monthly dates, and counts. 

I think I was able to create a one to many join by right clicking on my shapefile (one) and adding a join to my imported (many) dataset. I'm not entirely sure I was successful in the one to many join though. I got the message:

Checking for OIDs...
Checking for join cardinality (1:1 or 1:m joins)...
A one - to - one join has matched 568 records.
The input table has 2503 and the join table has 35612 records.

If it was possible, I'd like to do an left outer join but not convinced that ArcGIS Pro allows this. 

From there I tried to configure my popups. My goal was to allow the user to select a building (with a unique building ID) and have a chart popup containing information on the monthly counts to the specific building (building ID) that was selected. I'm not seeing seeing all of the fields that were included in the join (missing the date field from the imported (many) table) and I'm not seeing any place to specify what should be graphed (information from imported table that equal building ID selected). Am I going about this incorrectly?

Thank you for any added suggestions!

I'm working in ArcGIS Pro 2.7, so I can't use the Make Aggregation Query Layer.