service with layers with a join - "cannot access data" ??? But other feature classes work fine

[KevinMacLeodCAI]

Does Dashboards not work at all with layers with a join?  I have a service with some feature classes in it which work fine. However, any of the ones with Joins, do not work. I have seen the % symbol that joins put in to field names cause issues before. Is there a bug with Dashboards and joins with % characters?  I used aliases to give the fields more typical names. They were joined in ArcMap 10.7.1. They display just fine in the webmap. They are on our SQL Server 1017 SDE on ArcGIS 10.7.1 and again, they come in to the webmap fine. Just the pie chart and other charts in Dashboard don't work when I create a dashboard on ArcGIS Online, with the layers with joins. Layers without joins work perfectly fine. Thus so far it seems there is a join bug. This is an urgent issue for some projects.  I called Support and they said this could be a bug from the April 2021 update and are looking but I wanted to see if anyone here as seen similar issues.  Thanks all!

[KevinMacLeodCAI]

These joined layers are also coming from non-registered Views. Although they have a GlobalID field which has unique values and is guid for the field type. https://community.esri.com/t5/arcgis-dashboards-questions/dashboard-arcgis-enterprise-10-8-1-views-i...  I'll have to follow up on that too.  But... the other views that work just fine in the Dashboard. Only the joined ones don't work as above.

I can't share the service or I would unfortunately.

[MagalyClément-Zaber]

Hi,

Don't you have a prerformance problem with views ? In the webmap no problem for me but in the dahsboard, it is very slow and I have a timeout.

I tested a materialized view, it work but, after 20 seconds ...

Maybe it's because I have a view accessed by dblink in Oracle + two inner join to create the view used by the web service ....

[KevinMacLeodCAI]

Hi @MagalyClément-Zaber I had to use a view because of another bug with Esri where ArcMap doesn't query by Max date correctly - https://community.esri.com/t5/geodatabase-questions/max-date-group-by-id-in-model-builder/td-p/10082...  (or if a SQL guru from Esri or the community can shed light on this and I had the syntax wrong please excuse me! I would be glad to do it in a definition query)

Basically I just want to show the most recent inspection of manholes, so I can see the number of manholes that have been inspected and put it in a pie chart. I.e. 12 out of 50 manholes have been inspected. Only way I could think to do that was by grabbing the most recent related inspection. I merged the related inspections records together with a view so they are stacked points (let's say 3 points all on top of each other, indicating a manhole inspected three different times) I grab the most recent one, and thus these can be counted up indicating the fraction of manholes that have been inspected. I also have another layer of this where I set NASSCO = Yes to count those.  I'll do the same thing for sewer line length cleaned and length inspected. This is a fairly common workflow and thing to track and I would think this has been done but I didn't seen another example. I wish I could share the service and app but I can not alas.

Support thinks this may be a bug. This is a high priority project and so I hope to ascertain whether it is a bug and if there is a workaround. Otherwise I could do everything in SQL views but that is only a last-ditch strategy if the joins can't work on Dashboards.

[KevinMacLeod3]

I though I had a work-around... use the Make Query Layer tool. That makes a 'permanent' join vs an mxd join and so there are no % symbols in the field names.  However, apparently ArcMap can't publish a feature service with a 'complex' Query Layer.   Which is another limitation; it would be fine if Dashboards could just use map image layers. Will continue testing.  All I have, is a SELECT joining two different views together, just a regular join.   Is that really a "complex" Query?

[KevinMacLeodCAI · Accepted solution]

Solution: I created views in SQL. Then I could publish with feature query access. Another solution: publish with query layers with complex queries; as just a map image layer. Then add each sublayer from the service one by one to the webmap so it forces loading as a featureLayer.

[BrianOevermann]

Regarding the % symbols in field names--I had this issue back in the Flex Viewer days with some of my map services. After much banging of my head against my desk I found that my usual way of adding tables to my ArcMap projects was the culprit.

If you normally drag-and-drop feature classes and tables from Catalog into ArcMap (or from the Catalog sidebar within ArcMap), the percent symbol is added to the table name, and thus the fully qualified field name.

However, if you use the 'Add data' toolbar button (or menu item) and add your table, percent signs are not added to the table name.

Esri Tech Support did not have a solution for me. I ultimately figured this out on my own and shared the info with them. This info is apparently buried in my old support case and not shared further.

I am unsure if this bit of info will allow you to do what you want to do within your dashboard but I am hoping it helps with avoiding percent signs in table/field names.

[rboreux]

Hello,

The problem is still present in ArcGIS Enterprise 10.9.1. I opened a case with Esri Belux. I'm waiting for an answer.

Kr,

Simon