Service Layer API filter - operator 'like' does not work properly

[MatLakaemper]

Hi,

i am encountering a Problem in SAP-BusinessOne 10 FP2502

The like-Expression does not work.

I think this is a bug.

kind Regards,
Matthias Lakämper

----------------------------------------------------------------------------------------------------------

GET {{SboServicelayerHost}}Orders?$select=CardCode,DocNum,CardName&$filter=CardName like '002%'

Content-Type: application/json

Cookie: : {{SboServicelayerSessionId}}

Cache-Control: no-cache

Accept: application/json

###

{ "error": { "code": "201", "details": [ { "code": "", "message": "" } ], "message": "Query string error - Invalid filter condition" } }

[Johan_Hakkesteegt]

This is a wild guess, but it may be due to the spaces. Can you escape spaces in the filter string?

[MatLakaemper]

Hi,

[williamwachholz]

Hello,

You can use the SQLQueries entity, you have more possibilites to work with queries.

Here is a sample:

You send a POST to the endpoint b1s/v1/SQLQueries

The payload:

{
"SqlCode": "Demo_Q1",
"SqlName": "Demo - list of Salesorder",
"SqlText": "SELECT T0.[DocNum],T0.[DocDate], T0.[DocDueDate], T0.[CreateDate] FROM ORDR T0 WHERE T0.[CardName] like '002%' order by T0.[Createdate] desc"
}

It will create the query with ID "Demo_Q1. You can then list the result by sending a POST to the endpoint  b1s/v1/SQLQueries('Demo_Q1')/List

Best regards

[MatLakaemper]

Hello William,

thank you for your Answer, i know that possibility.

But i need the functionality within CAP-Sql

eg. cds-repl-Command: (Standard for Odata).

qx = B1Svl.run(SELECT.columns('CardCode', 'DocNum').from('B1Svl.Orders').where `CardName like '001%' `

and here i get the error...

So my opinion is, that is a bug and should be fixed.

Kind Regards,
Matthias

[Johan_Hakkesteegt]

This has a missing quote, can that be it?: qx = B1Svl.run(SELECT.columns('CardCode', 'DocNum').from('B1Svl.Orders').where `CardName like '001%' `

[MatLakaemper]

Hi,

there is no missing quote, it was on the next line... but indeed the closing bracket was missing.

kind Regards
Matthnias

qx = B1Svl.run(SELECT.columns('CardCode', 'DocNum').from('B1Svl.Orders').where `CardName like '001%' `)

[ANKIT_CHAUHAN]

Hi MatLakaemper,

It appears that Service Layer does not support like operator.

https://help.sap.com/docs/SAP_BUSINESS_ONE/f110a154dd0f4c20bf7f3ebca9eeb794/3e5b7d980d99493f92718474... 

Kind regards,

ANKIT CHAUHAN

SAP Business One Support

[MatLakaemper]

Hi Ankit,

thanks for the link,

so the solution is:

GET {{SboServicelayerHost}}Orders?$select=CardCode,DocNum,CardName&$filter=startswith(CardName, 'W')
GET {{SboServicelayerHost}}Orders?$select=CardCode,DocNum,CardName&$filter=contains(CardName, 'Way')

that works for me.

kind Regards,
Matthias