Adding filter for one table in OData connector

Hi @juriaan.hensbroek 

I think you can add a filter like you would if it was a SQL table.

CData has this written about how this works with client side processing. cdn.cdata.com/help/RDH/ado/pg_advancedqueryproc.htm

Depending on how you set up the OData data source it will just give a bunch of tables when you synchronize. Each one will have an option to filter on it then.

For this issue you could maybe use incremental load mixed with history. Then you will not loose the data if you had to do a full load. You could set up a filter that only gives a years worth of data and then update it until you got all of it. Then following that using incremental load would only add new additions from these tables.

Hi Thomas,

The filter seems to be required in the connection string, Topdesk otherwise blocks the connection to the table. All other tables I extract work just perfect, but this is something new they did for 1 big table. If I use filtering on the existing connection I still get the error I mentioned. There is no option in the connector for advanced query processing or filtering per table. Connector needs to be created first before I can filter like with SQL.

So what I did now is creating a new connection, just for this one table, with a filter included in the url.

Basic url: https://sakura.topdesk.net/services/reporting/v2/odata

New url with filter: https://sakura.topdesk.net/services/reporting/v2/odata/IncidentDurationBlocks?$filter=start gt 2023-06-26T00:00:00Z

Now I get another error message. The types 'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible. I'm checking this with Topdesk. Or do you recognize this error?

Not really, maybe something about html encoding.

Topdesk doesn't recognize the error and say they don't support external applications.

In the meantime I see I can retrieve data in Postman with the same url (including filter in url). Should be possible to get it working in TX right? 

In Postman I have a base url: https://sakura.topdesk.net/services/reporting/v2/odata/IncidentDurationBlocks

query params: key=filter value=start gt 2023-06-26T00:00:00Z

Use Basic authentication

Resulting in url: https://sakura.topdesk.net/services/reporting/v2/odata/IncidentDurationBlocks?$filter=start gt 2023-06-26T00:00:00Z

Where can I add the query params in the connector in TX? I keep getting the same error: The types 'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible.

Hi @juriaan.hensbroek

You can add URL parameters in the Custom Url Params field.

Add this to the field to get the same.

filter=start gt 2023-06-26T00:00:00Z

Tried this already. But the same error: The types 'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible. Could it be something with the datatype or date format?

I tried to load it in PowerBI. This works.

Would this option do something? It refers to the edm.datetime.

Hi @juriaan.hensbroek 

Well Microsoft does not have a Time Offset option, but you can set a reset in the Other field like 

ConvertDateTimeToGMT=True

or try to do it in the field you showed with CET as the value representing Central European Time.

Maybe you even have to do both.

I would also consider turning on the Use Client Side Paging and Support Formulas options.

I changed it but still the same error. I also tried a boolean filter instead of a date filter. This also gave an error “Edm.Boolean” not compatible (instead of boolean AND datetime).

Hi @juriaan.hensbroek 

Did you try the Server Time Zone field?

It should be attempted both with and without the Other option set I think.

If it makes no difference either way.

Try to see if I could convert these data types to strings using the data type override feature.

Ok, I tried Server Time Zone with value UTC+2 and GMT with and without the Other field filled with ConvertDateTimeToGMT=True. This didn't do anything, still the same error.

I also added data type override to datetime and filter rows like I would do with a normal ODX connection. Looks like these overrides only is looked at after the connection is made (first a sync is required and this fails already). 

Just had a word with my colleague Rory Smith. He says it's notable TX doesn't generate logfiles while logging is on Verbose 3. So it's not an issue between DataSource and CData, but between CData and TimeXtender.

Could it be a bug in TX/CData OData connector that the Custom Url Params with value $filter=start gt 2023-06-26T00:00:00Z can't interpret the “gt” as a > (greater than) evaluation? This could be causing the error we get: The types 'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible. This because the field “start” is a datetime field and with “greater than” it should be evaluated as a boolean.

Do you have experience with this OData connector in which this filter function succesfully operates? 

Hi @juriaan.hensbroek 

It should not matter it is not our program that requires this but the API.

If you add

$filter=start 2023-06-26

and then if it does not work.

$filter=start 2023-06-26T00:00:00Z

and then this.

$filter=start%202023-06-26T00:00:00Z

Is there a way to run it without the filter and does that work?

Hi Thomas,

Tried these 3 suggestions, but all give a  'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible error.

It's not possible to run the api for this table without a filter, because it's actively refused by the source. Then I get a 500 error - the URI is malformed. Or 503 error - unexpected response.

Something I just tried in Postman is to run the table without a filter. It seems to give results also without the filter. I made a screenshot of the headers I got. I see a date in GMT timezone. In TX using the same URL without a filter, the sync and transfer both fail with a 500 error - URI is malformed.

Hi Thomas,

Good news. Changing for this table to a REST API works. We will see for how long it will work, because we don't use any filtering now while we are informed by Topdesk this is obligatory for retrieving data.

Thanks for all your efforts Thomas, it's appreciated!

Kind regards, Juriaan

@juriaan.hensbroek 

The REST provider should be better at handling filtering, you can even use the RSD file to make it dynamic.

• Advanced RSD file options Tutorial