You can access the JSON from the ArcGIS Online Assistant: ArcGIS Online Assistant
The query would look like something that you have done in a definition query but using some of the advanced query functions. You also have to know the full name of the data table, not just what might show up in a connection.
Example: CreationDate IN (SELECT MAX(CreationDate) FROM db_193.user_193.Collector_Tracking_Layer_Location_Tracking GROUP BY Creator)
Your query would have to go in a definition expression for that layer (if you are doing it in a map) or as part of the layer properties for an item in Portal/ArcGIS Online. A way to figure out where that location is, add a filter to the layer in the map and look at where that shows up in the JSON.
Please keep in mind that editing the JSON is entirely at your own risk and not supported by Esri.
Thank you Leah. I see where the query is, but not sure how to reference the data table in the query i.e. the db_193 stuff - is this information in here?
"id": "Aerial_Tracking_5587",
"layerType": "ArcGISFeatureLayer",
"url": "http://services2.arcgis.com/iXA1dC6ldRMKRwra/arcgis/rest/services/Aerial_Tracking/FeatureServer/0",
"visibility": true,
"opacity": 1,
"title": "Aerial Tracking",
"itemId": "507623ea30c341ecb26b3652df53bd1a",
"layerDefinition": {
"definitionExpression": "Accuracy < 25"
Thanks! cob
If it is an enterprise geodatabase, you will need to go and look at the full name including the database, user, etc. Sometimes you can search through the rest api to find the table name as well. This is likely the trickiest part.
Well, it's just a feature service created in AGOL based on the location tracking template from ESRI. Is the info I need contained in the rest from the template file or from the one I created based on it? If it's ESRI's file is should be easy to come up with a boilerplate because most are using that template to create the tracking layer and we want to do the same thing (plot current location of collector). I'm sorry, it's likely I'm not following you too well - my understanding of database infrastructure is limited. Thanks much, cob
If it is a hosted layer you can go to the admin url and use the JSON to determine enough information to make an assumption.
For example:
1. My hosted layer url is: http://services.arcgis.com/2zRtyrQ6q4mGrLJK/arcgis/rest/services/Test_Tracking_Layer/FeatureServer/0
2. Modify this url to get to the rest admin and view the JSON: http://services.arcgis.com/2zRtyrQ6q4mGrLJK/arcgis/rest/admin/services/Test_Tracking_Layer/FeatureSe... (notice the "admin" in the url and the json request at the end)
3. In the JSON search for "user"
4. You should find something with "user_<some number>"
5. Now you should have enough information to build the query: CreationDate IN (MAX (CreationDate) FROM db_<some number>.user_<some number>.<name of the layer>
So mine might look like this if I test it by adding my hosted feature layer to ArcMap: "CreationDate" IN (SELECT MAX ( "CreationDate") FROM db_123.user_123.Test_Tracking_Layer_Location_Tracking)
The number you found in the "user" information is the same as the db for these hosted services as far as I can tell.
Test the query in ArcMap by adding the layer to your map from your ArcGIS Online organization (either through Add Data or through the My Hosted Services node in Catalog. Then create a query in the definition query properties of the layer to see if it works.
Hope that this helps!
I think I'm getting close with this, Leah. I have the query working in
Arcmap using the hosted feature service, but I can't get the AGO-assistant
to accept the json for the query. I guess the syntax is a little different;
I tried several variations to no avail. Can you send me the exact query
syntax that worked for you in AGOL? Thanks for your help, time and
patience...cob
I used
"created_date IN (SELECT MAX( created_date) FROM Tracking.DBO.TrackingData GROUP BY created_user)" in one layer.
Make sure your fields are correct. One other issue is that you must be able to use advanced queries in your ArcGIS Online organization. If you are an admin you can change this by going to the Organization Settings > Security > Policies Make sure that the "Allow only standard SQL queries" is unchecked.
Got it working, hooray! At first it wouldn't hold the setting in AGO assistant, then it stopped tracking, and I removed the layer from all maps and created another tracking layer based on it, put it in a new map and saved the layer, then the map. It still feels kinda shaky but we will see. I never would have figured this out in a hundred years without your help...thanks!
I have had it working great for quite a while, so hopefully you will have success! Glad I could help!