Update hosted layer view definition w topfilter to show only most recent record

[JayJohnsonWashoeCounty]

I have a hosted table with many records and would like to create a view layer from this that only shows the most RECENT record. 

I've created a view layer and attempted to update the view definition using the TOPFILTER via the admin API.

Original Layer Definition:

{
"adminLayerInfo" : {
"tableName" : "db_4176.user_4176.CASES_TABLE_CASES_TABLE", "geometryField" : null,
"xssTrustedFields" : "",
"viewLayerDefinition" : {
"sourceServiceName" : "Cases_Table",
"sourceLayerId" : 0,
"sourceId" : 984,
"sourceLayerFields" : "*"
}
},
"id" : 0,
"name" : "Cases_Table",
"type" : "Table",
"isView" : true,
"isUpdatableView" : true,
"sourceSchemaChangesAllowed" : true,
"displayField" : "",
"description" : "",
"copyrightText" : "",
"defaultVisibility" : true,

ETC...

I think I need to insert something like the following, but can't get it to work successfully:

"viewLayerDefinition" : {
"topFilter" : {
"orderByFields" : "reportdt ASC",
"topCount" : 1
}

}

Does anyone have an example of successfully using topFilter (or some other method) to filter a layer view down to just the most recent record?  This is in support of a COVID-19 project.

Thanks

Jay

[KhaledHassen]

Yes esri internally using the topFilter to view most recent record in view as well and in similar projects related to COVID-19.  Not aware of any issues. This should work. Let me know if it does not or having issues.

Khaled Hassen

Online Feature Service Software Dev Lead

[JayJohnsonWashoeCounty]

Khaled, Thansk for responding.

When I open the admin api for the view layer I want to restrict to the most recently updated row of the data I see this:

I attempt to update the ViewLayerDefinition like as shown in yellow highlight:

When I click Update Layer Definition it looks like it worked, but if I look at the ViewLayer data there is no filtering occurring - ALL the records are still shown. I also tried changing the Format to JSON and when I click UPDATE LAYER DEFINITION button I get a good-looking message:

{ "success" : true }

If I go back to look at the admin api again, it appears that my changes didn't stick:

Any suggestions where I'm going wrong?

Thanks,

Jay

[KhaledHassen]

You are using the wrong json. I should be something like:

{

    "viewLayerDefinition" : {

       "sourceServiceName" : "...",

       "sourceLayerId" : 0,

      "sourceLayerFields" : "*",

      "topFilter" : {

        ...

     }

 }

}

You do not need to set all these props.

Can you pl try it out and let me know. This should work.

Khaled

[JayJohnsonWashoeCounty]

Making some progress - I didn't realize I could just put in only the portion I want to change.

So I cleared the box and just put in this:

{
  "viewLayerDefinition" : {
    "sourceServiceName" : "CaseTable",
    "sourceLayerId" : 0,
    "sourceId" : 1033,
    "sourceLayerFields" : "*",
    "topFilter" : {
   "orderByFields" : "reportdt ASC",
   "topCount" : 1
    }
  }
}

That change actually "stuck", which is progress.  Now when I go back to the admin api it looks like this:

I'm still not getting filtering in the view layer though.  I still see all the records, not just the top one.

Jay

[KhaledHassen · Accepted solution]

Not sure why you stuck. are you using async=true? try to avoid that for quick update.

At any case, I do not see you are passing groupby. you need to pass a group by field. This is how we group by records and pick the record. Do you have any issue passing groupby.

Khaled

[JayJohnsonWashoeCounty]

Khaled, you are my hero today.  Issue resolved!  

For others who might come across this question, I ended up going to the admin API, deleting all the text that is there by default and pasting in only what is shown in this screen shot:

"reportdt" is my field for the datetime the record was entered in the table.

"name" is another of my fields that was suitable for grouping by.

Clicked the Update Layer Definition button and got a confirmation message.

The view layer now shows only the most recent record in the table.  

Totally awesome, thanks again Khaled!

Jay

[nvdv]

Thanks for sharing Jay! 

We ran into some issues applying the filter above. In the end, the solution was to select ASync = false and format = HTML in the form while updating the service definition. 

[kmsmikrud]

Thank-you @JayJohnsonWashoeCounty  your post definitely made the win for the day in accomplishing a task of showing the last 2 records in a feature layer. I have been going over different solutions (that haven't worked) and so excited to see your solution. thank-you for posting!

[LiamHarrington-Missin]

Hi @KhaledHassen ,

I feel like I'm so close to getting this right, but it's just eluding me.  I have a database where my groupby fields is StaffEmail and I want to return the latest position (using the date field "esrignss_fixdatetime").  

I've created the update and successfully updated but my view still shows multiple entries for each email address.  What am I missing?

{
   "viewLayerDefinition" : {
   "sourceServiceName" : "CheckIn",
   "sourceLayerId" : 0,
   "sourceLayerFields" : "*",
   "topFilter" : {
      "orderByFields" : "esrignss_fixdatetime DESC",
      "groupByFields" : "StaffEmail",
      "topCount" : 1
}
}
}

Is it ASync? I don't know what that does.  Is it Format?  I've tried both.  I'm not getting error messages when I update and the update seems to hold BUT again, I'm still not seeing a view layer where each user (email address) is linked to a single point feature.