topic Re: Update hosted layer view definition w topfilter to show only most recent record in ArcGIS Online Questions

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

JayJohnsonWashoeCounty — Tue, 07 Apr 2020 15:47:01 GMT

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

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

KhaledHassen — Fri, 10 Apr 2020 16:11:32 GMT

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

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

JayJohnsonWashoeCounty — Fri, 10 Apr 2020 17:38:25 GMT

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

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

KhaledHassen — Fri, 10 Apr 2020 17:53:40 GMT

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

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

JayJohnsonWashoeCounty — Fri, 10 Apr 2020 18:29:31 GMT

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

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

KhaledHassen — Fri, 10 Apr 2020 18:34:47 GMT

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

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

JayJohnsonWashoeCounty — Fri, 10 Apr 2020 20:28:43 GMT

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

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

nvdv — Thu, 07 Oct 2021 10:04:15 GMT

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.

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

kmsmikrud — Mon, 24 Jul 2023 22:09:10 GMT

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!

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

LiamHarrington-Missin — Thu, 19 Oct 2023 12:28:25 GMT

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.

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

KhaledHassen — Thu, 19 Oct 2023 12:53:49 GMT

That looks right to me. I have recently fixed an issue related to topfilter. The fix will be available next week as part of online feature service update. So please try it again with the update.

If it is the same bug I have fixed, then if you retry apply the topfilter. It will finally work. So please try it again another time and see if this is fixed. If not, please wait until next week for the fix.

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

LiamHarrington-Missin — Thu, 19 Oct 2023 13:31:01 GMT

Thanks @KhaledHassen, I'll wait a week and have another crack.

I'm curious, a work around that I attempted, given my fear of all things API, was to use the "join" analysis tool (in webmap classic not new, so I can create the feature view instead of a feature layer) and create a join to itself.

Then in subsequent settings, I'd use a one-one-join around StaffEmail and use the FixTime to determine which record is retained...

This would be a cheeky workaround admitedly and it doesn't work but I'm not sure why it doesn't work.

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

LiamHarrington-Missin — Mon, 30 Oct 2023 11:59:30 GMT

Hi @KhaledHassen,

I'm still not seeing the desired behaviour I want and can't figure it out. Help.

I've created a test feature service that shows what data architecture I have. You'll see 11 records, of which there are five unique entries in the "Email" col. I want to create a feature view that shows only the most recent records for each email address. E.g. the feature view will show 5 records, each showing the record that corresponds with the most recent "Fix Time" per email.

Everything I have tried to date is simply showing me the original 11 entries.

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

KhaledHassen — Mon, 30 Oct 2023 14:00:13 GMT

Hi Liam,

I think you should be able to create a view with the topfilter for this service. Can you pl. provide us with the steps to create the view in our side based on this data. We can try to repro it. I think the step that should work as follows:

- Create an empty view

- Call add to definition and link the view to the source

- Use update definition to change the topfilter.

Thanks

Khaled

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

LiamHarrington-Missin — Tue, 31 Oct 2023 09:15:53 GMT

Really appreciate the the prompt response @KhaledHassen, This is driving me nuts, I'm sure it's a user error.

I screen recorded the, clearly incorrect, process I am following to try and achieve this and below is the code snippet. Any insights would be greatly received.

Cheers

Liam

{
"viewLayerDefinition" : {
"sourceServiceName" : "Check_In_testDataset",
"sourceLayerId" : 0,
"sourceLayerFields" : "*",
"topFilter" : {
"orderByFields" : "Fix_Time DESC",
"groupByFields" : "Email",
"topCount" : 1
}
}
}

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

KhaledHassen — Tue, 31 Oct 2023 13:44:11 GMT

Hi Liam,

Can you pl. try only the topfilter.

{
"viewLayerDefinition" : {
"topFilter" : {
"orderByFields" : "Fix_Time DESC",
"groupByFields" : "Email",
"topCount" : 1
}
}
} 

Thanks

Khaled

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

LiamHarrington-Missin — Tue, 31 Oct 2023 13:58:52 GMT

No joy @KhaledHassen. I tried it with different combinations of "ASync" and "Format", since I'm clueless about what they mean or do. Happy to jump on a screen share if you want to fasttrack troubleshoot? Just private msg. me.

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

KhaledHassen — Tue, 31 Oct 2023 14:04:47 GMT

Liam,

I have watched the video. From what I see, you are applying updateDefinition to the feature service and not the layer. You need to go to the layer and set the topfilter on the layer using the layer updateDefinition. You cannot update topfilter in feature service updateDefinition API.

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

LiamHarrington-Missin — Thu, 02 Nov 2023 14:02:34 GMT

There it is. User error of course. Working a treat now. Thanks so much @KhaledHassen!

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

FarmersBranchBrian — Wed, 29 Nov 2023 00:13:52 GMT

@KhaledHassen This topFilter trick has been invaluable for me in various projects. It's an especially neat trick for handling parade routes and the current location of various participants in the parade. We use QuickCapture to stream point data to the underlying Feature Layer and then topFilter to limit it to the most recent feature.

I have noticed something in my most recent project that I thought I might bring to your attention. The "groupByFields" parameter seems to only work when the field provided is a text field. I recently tried using a GUID field and the definition update simply would not take. The moment I changed the groupByFields property to a text field, everything worked fine. I have not experimented with other field types. Is this limitation by design or simply a coincidence because of how topFilter is currently implemented on the backend?