Publish services using ArcGIS Pro with Joined Data

[MichaelHino2]

This request is to implement an enhancement to ArcGIS Pro to be able to join, relate, or whatever process is necessary, data from different data sources and publish it as a service to be consumed in a web application while still allowing the data to be dynamic, reading from the data sources.

Currently, ArcGIS Pro cannot publish a service to ArcGIS Enterprise while an in memory join is present. However, this function can be critical when needing to link to different data sources (feature class and tabular data). For instance, if the tabular data is part of a mission critical read only system that is dynamic that the user would like to connect to a feature class and bring into a web mapping application. I have also tried creating a relationship class but that cannot be created on a view.

[TanuHoque]

By any chance the database where your "standalone sql table from a separate sql server instance" is a non-geodatabase...? or maybe a geodatabase but the table is not a registered table?

@ScottAndersonMississauga 

I got the answer from your last comment. For some reason I didn't see that comment when I posted mine.

With that as I said before the bug is fixed in 11.3. Please let us know if you still run into this issue in 11.3 

[ScottAndersonMississauga]

Yes that's correct @TanuHoque . It's a separate SQL Server instance.  Is the bug also causing the % sign when adding in the Map?  Or is this works as designed functionality. I  only ask so that when I document this process for other user I use the recommended workflow.  If that workflow is to join to the outside DB via the Join dialog and NOT by adding to the Map, that's fine and I'll document as such.  If the % thing is still a bug I may add that as a future option if that's corrected at some point. I'm not sure what the technical back-end process is so they may be entirely different behind the scenes.

Thank you everyone.

[TanuHoque]

@ScottAndersonMississauga 

• It doesn't matter how out create a join (*). Whenever you create join in ArcGIS Pro, and one or both parties is an un-registered table, you will see % sign in fully qualified field names.
  • having % sign in fully qualified field name is by design
• The bug (BUG-000156380) was that when you publish that as a map service, and you make query using one of those field names (with % sign in it), map service query was failing
  • as you mentioned, typically you run into issues like when you add that map service in Map Viewer and you click a feature to see its attributes; popup window either shows nothing or fails.

(*) IIRC someone might have mentioned earlier in this thread that creating the join in a database view or using query layer as solution. Those are good solutions. I believe that would work as long as they both belong in the same SQL Server instance (in your case they are from two different databases) -- , unless there are some additional capabilities at the db level that will allow you to create joins using tables from multiple databases instances. I'm not aware of that.

[ScottAndersonMississauga]

Not to be argumentative @TanuHoque , but please see these screen grabs.  The first shows the resultant attribute fields when using a direct join and the second shows the join when using the table from the map. I suspect there is a bug in one workflow that doesn't exist in the other.  However, it does function "correctly" at this time.  Hopefully this method also doesn't break.

And the resultant successful pop-up in a Portal.