So I was running into similar problems and will give a rundown of my findings.
In my case, we have capitol improvement points and lines on a map. I am creating a dashboard to filter by year, type, status, etc using the widgets instead of selectors. I needed a table with all the records to supply data to the widgets/metrics from both points and lines. The fields in the points and lines match, so I created a sql view to union the points and lines into a table, then created a new objectid from the globalid’s. Then registered the view with the geodatabase.
Quasi code below
select c.*, cast(ROW_NUMBER() OVER(ORDER by c.globalid) as int) As objectid
from
(points union lines) c
this gave me the table I needed for the metrics. When using it in the dashboard I could create metrics, but as another poster mentioned, I could not use widgets using data from the table to control layers on the map even though the fields matched 1 to 1. Conversely, I could use layer selections on the map to filter the widgets created by the standalone table. So it works in one direction, but not both.
With that attempt thwarted, I decided to go the sql spatial route. I got the x,y from the points, then created an x,y from the lines based on the nearest point on a line to the centroid of the feature extent.(dealing with multiple line segments per feature). With the x,y’s, I updated the view to a spatial view of points. Same code as above, adding in the logic to pull x,y’s from both tables, then geometry::point(x,y,srid) as shape after the row_number function. In this attempt I could see the points, see the attribute table, etc..., in the map. But when trying to create a metric with the service, I got the cannot acces data icon. Same sharing as before.
third attempt, create the table view as before only adding in the x,y’s as fields. Then create a query layer in pro and generate the points in the query layer instead of the view. I used the globalid as my uniqueid in the query layer spatial properties window which then generates your esri_oid. Same issue as above when trying to create metrics only this time it said cannot access data of 1038 records(number of records in my query layer). Or something similar. Still didn’t work, but an error at least giving me the count of records in the layer. (I’ll note that even though it says it can’t access the data, if you select a drop down to select fields for statistics, etc, all the fields from the layers populate). Progress.
final attempt. for some reason, I was stuck on objectid being the issue. I was using globalid in the spatial properties window of the query layer dialog as my unique Id because it defaults to that. But I had also created a new objectid in the union view, so it was also unique. I used objectid as my unique Id in the spatial properties window and created the query layer. This time, the query layer created, but did not create an esri_oid field and instead made my objectid field the unique Id field with the field type objectid instead of integer. Plugged this layer into the dashboard and everything worked flawlessly. Success.
now, I didn’t go any further down the rabbit hole to test why it wasn’t working before. Did it need a field called objectid to be the unique identifier? Did the fact I had objectid as an integer field in my query layer throw everything off because it wasn’t the designated unique field? I’m guessing one or both of those 2 is the problem but I didn’t test.
Notes::
as stated above. Creating widgets from standalone tables and then trying to filter map layers by those tables didn’t give an action option for the map layers.
now, I still have my cip polylines. I’ve created a centroid point for them, but still want to show the associated lines when I select a point on the map.(using globalid as my connection between the 2 layers). I can filter just the lines I want when I select a point. But, when I don’t have a point selected, all the lines show up and I can’t control them with the widgets either since they were created using the points query layer. The question I have is. Is there a way to default a layer to filter out all the features and only show a feature after an action. In my case, I select a point on the map and the lines associated with that point show up. If no points are selected, all the lines are “hidden”. If not, is this functionality on the radar for dashboards?
sorry for the extremely long winded response. I was hoping to give users a workaround or ideas as to why the query layers may not be working as well as provide enough information for debugging purposes.
