cancel
Showing results for 
Search instead for 
Did you mean: 

BTP CI-Filter condtion set up on oDATA API ( API_SALES_ORDER_SRV) using Date values

satsunros
Explorer
0 Kudos

Hello Community,

Good day.
I need to  set up a filter condition on oData API (API_SALES_ORDER_SRV)

(CreatedByUser eq 'BTPDEVUSER' and (CreationDate ge datetimeoffset'${date:now:yyyy-MM-dd'T'00:00:00.0000000'Z'}- ${property.p_daysInPast}' and CreationDate le datetimeoffset'${date:now:yyyy-MM-dd'T'00:00:00.0000000'Z'}'))

however this filter condition could not help me to get the records at all.
Could you please provide some hints, how this filter condition would be set up

Thanks & Regards,
Sateesh N

Ryan-Crosby
Active Contributor
0 Kudos
It looks like you are trying to retrieve all orders created by BTPDEVUSER in which case the easiest filter to use would be CreatedByUser eq 'BTPDEVUSER'
satsunros
Explorer
0 Kudos
Hello Ryan, Thank you. yes, it works with CreatedByUser eq 'BTPDEVUSER However, I must use date filter in order to retreive particular date period.
View Entire Topic
Ryan-Crosby
Active Contributor

The reason you are not getting results is because your query filter is incorrectly formatted. A datetimeoffset does not include math regarding number of days, but rather a difference between UTC... e.g. eastern time zone is -05:00. Based on your expression it would be easier if you were to use datetime and reference KBA 2933028 for the proper syntax.

 

Regards,

Ryan Crosby

satsunros
Explorer
0 Kudos
Hello Ryan,
satsunros
Explorer
0 Kudos

Hello Ryan, I tried with several formats suggested by SAP notes.
with the note KBA 2933028 , I also tried with my first attempt with this filter condition
$filter=(CreatedByUser eq 'BTP_DEV_USER' and (CreationDate eq datetimeoffset'2021-01-05T15:16:23'))
it didn't work out.

 

Ryan-Crosby
Active Contributor
@satsunros your filter is incorrect because you are using datetimeoffset without an offset, and you also used eq which means only if an order has that exact timestamp. Change your filter to $filter=CreatedByUser eq 'RFC_BTP_DEV' and CreationDate ge datetime'2021-01-05T:00:00:00' and you would get results for all orders created after 01/05/2021
satsunros
Explorer
0 Kudos

Hi Ryan,
Great. my first query is running with this filter
$filter=(CreatedByUser eq 'RFC_BTP_DEV' and (CreationDate ge datetime'2024-06-10T00:00:00'))

But my final query should be like mentioned in my first question, how to be it look like?
$filter=(CreatedByUser eq 'RFC_BTP_DEV' and (CreationDate ge datetime'${date:now:yyyy-MM-dd'T'00:00:00}'))- ${property.p_daysInPast}' and CreationDate le datetime'${date:now:yyyy-MM-dd'T'00:00:00}')

note: ${property.p_daysInPast} value should be in days ( ex: 3 or 5..)

Could you please also suggest how it would be like?

Thank you 

Sateesh

Ryan-Crosby
Active Contributor
0 Kudos
@satsunros it would look something like this, but you would have to figure out how to determine your starting date because I don't know what bounds you are trying to use. The following expression would give all orders between the beginning of the year and today - $filter=CreatedByUser eq 'RFC_BTP_DEV' and (CreationDate ge datetime'2024-01-01T:00:00:00' and CreationDate le datetime'${date:now:yyyy-MM-dd}T00:00:00')
satsunros
Explorer
0 Kudos

Hello Ryan, Thank you your suggestion. I think I have solution now instead of after read all your inputs
I simplly go with this filter and get all the records the date given in the filter. so this works and solution also for me. 
$filter=(CreatedByUser eq 'RFC_BTP_DEV' and (CreationDate ge datetime'2024-06-10T00:00:00'))

One last thing which I want to address to you about other issue in the same filter conditions, usually I must use the filter like below ( CreatedByUser not equals to RFC_BTP_DEV).

when I given 'ne' in filter condition I get HTTP 500 Internal server , could you please also give me your hint on this topic. it really saves my time

$filter=(CreatedByUser ne 'RFC_BTP_DEV' and (CreationDate ge datetime'2024-06-10T00:00:00'))

Ryan-Crosby
Active Contributor
0 Kudos
@satsunros you will need to look at the error logs to determine the cause because ne is correct for not equals.
satsunros
Explorer
0 Kudos

Hello Ryan,
I received Runtime Error: 'DDIC_TYPE_INCONSISTENCY'. The OData request processing has been abnormally terminated when I used 'ne'

Ryan-Crosby
Active Contributor
0 Kudos

@satsunros you would need to check the real issue in the ST22 dump because ne is the appropriate logical operator and I have no issue using it in our system.

Screenshot 2024-06-11 at 12.43.16.png

satsunros
Explorer
Thank you Ryan. yes I found error analysis in ST22. I escalate this issue SAP technical team. Thank you very much for your great support