OData and multiple dynamic conditions

Imar Spaanjaars

Posted on 18/09/2023 17:12:56

Hi there,

I am trying to filter sales prices from BC that either have a specific end date in BC (0001-01-01) or that are greater than today. In standard OData I can do this:

?$filter=(Ending_Date eq 0001-01-01 or Ending_Date gt 2023-09-18)

which works fine when executed directly against BC.

How do I set that up in an end point or a job? I can add a $filter query string to the end point and assign the above filter. That also works but then I can't dynamically determine the date using @Code (I want to use DateTime.Now instead of 2023-09-18). I also tried now() but BC doesn't support that. And when I set the filters on the job itself they are ANDed together rather than ORed together and then I get no results because none of the dates matches 0001/1/1 and is greater than today (&$filter=(Ending_Date+eq+0001-01-01)+and+(Ending_Date+gt+18-09-2023+00:00:00)) :-)

Any ideas?

Imar

Replies

Matthias Sebastian Sort

Posted on 19/09/2023 10:36:04

Hi Imar,

We have not added the "@code" logic to the OData Provier (yet), else you could have done an "in" conditional, as here they will be added as "or" in the $filter, so it would look something like this .

So the only solution I could come up with right now, is that you split your job into 2, one who has the conditional for the Ending_Date eq 0001-01-01 and the other job that does delta-replication, although you are connecting to the V4 api, and our logic for delta-replication only looks for these columns to filter on (Last_Date_Modified, Order_Date, LastDateTimeModified, lastModifiedDateTime and modifiedon) who is for the most available in the default API.

So will make a Feature for the @Code-part.

Matthias Sort

Matthias Sebastian Sort

Posted on 26/09/2023 11:27:14

This post has been marked as an answer

Hi Imar,

After some talks with people, we came up with that we keep the current implementation of the @code for conditionals, so that will be added to the OData Provider very soon, but it will be with the same logic where it is added as "AND".

We have talked about the functionality of beeing able to group/select the "AND" and "OR" as you can do for repositories query expressions, but this will be a DW10 feature.

So to solve your issue in DW9, I can only see the way of having two jobs, one with the condition of "0001-01-01" and then another one with the @Code(System.DateTime.UtcNow) once the OData Provider has been released.

Matthias Sort

Votes for this answer: 1

Imar Spaanjaars

Posted on 27/09/2023 08:50:29

Thanks. I solved it for now by not solving it :-) The amount of prices that are expired are relatively low, so for now we import all of them. I'll wait for future updates to the product to improve this.

Imar

Peter Leleulya

Posted on 13/10/2023 10:15:33

+1 on this ...
batch failed issues due to 0001-01-01 values.

Matthias Sebastian Sort

Posted on 13/10/2023 12:54:33

Hi Peter,

I am not sure what you mean. Is it related to the Conditionals for the OData Provider with the use of @Code(c#-code) or is it something else, like date.min = 0001-01-01 from OData that we can't import/export?

Matthias Sort

Peter Leleulya

Posted on 13/10/2023 13:21:54

Incoming 0001-01-01 values make the import fail.
Im now implementing a tablescript in code to make the conversion from 0001-01-01 to DBNull.Value.
I hoped to be able to do this conversion by CMS configuration @Code(somthing) but that didnt seem possible, so now adding custom code to the solution ....

Lars Bo Wassini

Posted on 13/10/2023 15:27:29

You can also create a staging table where the incomming field '0001-01-01' is placed in a nvarchar and when make a view that converts the value into NULL.
You can then use this view for your actual import.

This could also solve the problem Imar is having with the filtering.

You must be logged in to post in the forum

Developer forum

OData and multiple dynamic conditions

Replies