Change Log Entries - DELETE using ODATA

vmanta

Is it possible to delete Change Log entries using ODATA?

We have NAV2016

I've setup an ODATA webservice for PAGE 595 (Change Log Entries)

I can query for specific change log entries using ODATA.

But when I try to use the DELETE http verb, i get "The underlying application page is not editable."

I'm trying to automatically delete specific (based on a filter) change log entries so that it doesn't grow too big.
Is there any other method I could employ (without a developer license) ?

HTTP/1.1 500 Internal Server Error
Content-Length: 440
Content-Type: application/xml;charset=utf-8
Server: Microsoft-HTTPAPI/2.0
X-Content-Type-Options: nosniff
DataServiceVersion: 1.0;
Date: Wed, 10 May 2017 21:13:57 GMT

<?xml version="1.0" encoding="utf-8"?>
<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<m:code />
<m:message xml:lang="en-US">An error occurred while processing this request.</m:message>
<m:innererror>
<m:message>[b]The underlying application page is not editable.[/b]</m:message>
<m:type>Microsoft.Dynamics.Nav.Service.ODataServiceProvider.ODataException</m:type>
<m:stacktrace></m:stacktrace>
</m:innererror>
</m:error>"

vmanta

We have a need to sync certain NAV data to an external MySql DB.

Because of the size of some tables (and because it's the right thing to do) I want to do incremental updates and avoid fetching entire tables from NAV. To fetch our Items table and Customer tables via a web service takes few hours.

So I need to detect all CRUD ops done to records in NAV and sync our external DB as fast as possible. In effect It's almost like implementing CRUD ops event handlers externally

For some large tables, I'm using the Last_Date_Time_Modified field exposed via OData/SOAP (I chose OData after some tests as I found it faster) to sync newly CREATEd and UPDATEd records. I can poll for these changes frequently.

This leaves the question how to detect DELETE ops...

So I'm using the Change Log Page web service and setup to log DELETE ops. And poll the Change Log Page web service frequently as well.

It works fine, not real time but very close.
I could poll every 15 seconds if I wanted to, but for our use case, even 5 minutes is acceptable.

For a completely automated solution, I also wanted to programmatically delete the change log entries themselves - I could set a scheduled task in NAv to do it at fixed interval, but still wanted to be able to trim it based on it's size

Juhl

Always create a new page to use with Webservice. Then you control the properties without changing standard objects.

595 is read only!

vmanta

@Juhl,
Thanks for the answer, but I don't think I can create a new page without a developer licence, can I?

The closest I can come to programmatically delete Change Log Entries, would be to write external code that would simulate a user doing it in either NAV Windows Client or NAV web client. I also don't have direct access to the MS SQL NAV DB.

Juhl

Ok, thats a problem.
Have you tried?

Else contact your partner.

vmanta

Well spent an hour and I studied what gets sent and received to/from the NAV web Client and figured out what I need to extract from each HTTP response, the control ID structure, the JSON RPC calls....
It takes 14 HTTP requests to sign in and all the way to opening the "Delete Change Log Entries..." dialog, setting a filter, and invoking a click.

Luckily I've done a lot of crazy things like this in the past

EvR

My question would be...why are you messing with change log entries? Trying to do some manipulation/erasing footprints?

vmanta

We have a need to sync certain NAV data to an external MySql DB.

Because of the size of some tables (and because it's the right thing to do) I want to do incremental updates and avoid fetching entire tables from NAV. To fetch our Items table and Customer tables via a web service takes few hours.

So I need to detect all CRUD ops done to records in NAV and sync our external DB as fast as possible. In effect It's almost like implementing CRUD ops event handlers externally

For some large tables, I'm using the Last_Date_Time_Modified field exposed via OData/SOAP (I chose OData after some tests as I found it faster) to sync newly CREATEd and UPDATEd records. I can poll for these changes frequently.

This leaves the question how to detect DELETE ops...

So I'm using the Change Log Page web service and setup to log DELETE ops. And poll the Change Log Page web service frequently as well.

It works fine, not real time but very close.
I could poll every 15 seconds if I wanted to, but for our use case, even 5 minutes is acceptable.

For a completely automated solution, I also wanted to programmatically delete the change log entries themselves - I could set a scheduled task in NAv to do it at fixed interval, but still wanted to be able to trim it based on it's size

Juhl

Use sql timestamp to detect new and updated fields. More reliable than any datefield I NAV, as these are not updated on modify(false).
Create your own deletion log table and use that with sql timestamps as well.
This can be filled from an Event Subscriber codeunit.
Cleanup deletion table from a function created for that. Call that function from WS.

Easy peasy

Juhl

I'm sorry, but my suggestions demand developer licens.