How do I do a Like Clause in the Where List in Application Studio?

Kinetic ERP
,
1

Hello,

I am trying to do a Like clause in the Where List in BAQ Options in Application Studio.

Can anybody point me in the right direction please?

2

Where expression must evaluate to a valid T-SQL Where Clause.

Something like:

"Part_PartNum LIKE '{myDataView.mySearchTerm}%'"

{} contains a binding expression that gets evaluated at runtime.
myDataview is the name of a dataview in your app, ie: Transview
mySearchTerm is a field of that dataview containing the search value
% is the T-SQL wildcard character (so the above example is ‘starts-with’, you can put it at the beginning for ends-with or both for ‘contains’)

so the above evaluates to

"Part_PartNum LIKE 'somevalue%'"

where myDataView.mySearchTerm = ‘somevalue’

1 Like
3

Everything I have been told was to add the two question marks in there.

Are those not needed?

My original Value before wanting to do Like: ‘??{SearchFilters.txtGLAcct}’

4

Hey,

I got it figures out!

You comment got me thinking and I finally found the way to do it was:

‘%??{SearchFilters.txtGLAcct}%’

I appreciate your help!

3 Likes
5

Glad you got it.

The ?? is a null coalescing operator. It’s typically used with a fallback like

if value is null then fallback else value

In the case of kinetic expressions, there’s no fallback so I’m not certain, but assume it prevents an error if the expression evals to null or undefined.

So with ?? , if SearchFilters.txtGLAcct is undefined, then the eval probably returns ‘’ empty string.

What does it do with a LIKE filter? Who knows? If it goes LIKE ‘%%’ (which is valid SQL), the query will return everything.

Meanwhile without the ?? you get an error, so again, who knows. :man_shrugging:

6

Saw this in learning center today:

A single question mark prefixing the field value means that a REST request to execute a BAQ will go through only if the value is NOT empty or null. For example, OrderNum = ?{OrderHed.OrderNum}.

A double question mark prefixing the field value means that a REST request to execute a BAQ will go through even if the value is empty or null. For example, CustNum = ??{Customer.CustNum}.

Values of string data type (including constants) are put in single quote marks. For example, CustID = ‘??{Customer.CustID}’ or ReqDate > ‘??{Constant.Yesterday}’.

1 Like