Filtering for Strings with Single Quotes in App Studio BAQ Filter

Kinetic ERP
, ,
1

Hello!!

I was working on a dashboard in Application Studio, and I’m having some difficulties with filtering the BAQ call when the string contains a single quote ('). For context, this is my BAQ Execute Filter:

OrderDtl_ProjectID like ‘%{TransView.ProjectID}%’

This works for when TransView.ProjectID doesn’t have any single quotes, but causes an error when there are.

I saw a similar post to this (String replace (or other string functions) in application studio - Kinetic ERP - Epicor User Help Forum), and I tried doing:

‘{TransView.ProjectID}’.replace(“'”, “‘’’”)

before sending it to the BAQ filter, but it ended up adding backslashes to my value, which the BAQ also did not like :sweat_smile:. Is there a way to fix this within App Studio, or should I switch to using parameters for filtering my BAQ?

Thank you!!

Camryn

2

Watch out for curly quotes on here when copy-pasting expressions.

not equals:

'{TransView.ProjectID}'.replace("'", "'''")

try it again with straight quotes and let us know how it goes..

Edit: But for SQL I think you want to replace singles with two singles like so:

'{TransView.ProjectID}'.replace("'", "''")

Also: I believe js only replaces the first match so for replacing all do global regex like so:

'{TransView.ProjectID}'.replace(/'/g, "''");
3

Thank you!! I’m sorry about that, I didn’t even notice.

I tried using the straight quotes and the different formulas, but they also resulted in backslashes. I tried adding some functions to replace the backslashes, but it didn’t seem to work. It looks like they might be getting added within the criteria parser.

I also tried using double quotes instead of single quotes for the BAQ filter, but that didn’t work either.

4

Not at a working example atm to confirm but try this:

#_ '{TransView.ProjectID}'.replace(/'/g, "''") _#
5

Thank you Josh!! I tried this but was getting an “unexpected number” error message in the developer tools, so I updated it to:

"#_ '{TransView.ProjectID}'.replace(/'/g, "''") _#"

This seemed to work without error! The single quotes are replaced without backslashes.

However, the BAQ is still not being filtered correctly. I am getting a “We apologize, but an unexpected internal problem occurred.” error in the developer tools, so I may have to go back to the drawing board.

Thank you for your help though!!

6

FWIW, I see you’re not using baq params, that’s good IMO. I find baq Where and WhereList easier to deal with. Although they have different behavior it seems. Also, find it helpful to row-update the where string to TransView so I can see what’s happening then just {TransView.WhereSQL} in the Baq action. Lastly, you should be able to see the actual string being sent in the ExecuteBaq request with browser devtools. Good luck!

7

Okay… this is silly, but it appeared to work for me:

I used an event to pre-format my filter. This is what @jbooker mentioned here:

What I found was that using the .replaceAll("'", "''") was causing App Studio to inject backslashes into the actual string value being passed, as also previously noted in the above chain.

So, I tried to circumvent that and went with replacing CharCodes instead:

"{TransView.MyTest}" .replaceAll(String.fromCharCode(39), String.fromCharCode(39) + String.fromCharCode(39))
  .replaceAll(String.fromCharCode(92), "")

… this seemed to work to prevent the backslashes. CharCode(39) for single quotes. CharCode(92) at the end to eliminate any generated backslashes.

I was successfully getting a test value of Bonk''s (my test case, ignore the silly name). However, it seemed like the Execute BAQ was escaping this and still passing a value of Bonk's in my Where Clause, which failed.

I found I had to DOUBLE escape… I needed a value of Bonk''''s in order for it to work.

That way, the Execute BAQ escaped once (using a value of Bonk''s)… and I’m assuming SQL escaped again and finally used a value of Bonk's.

Anyway… I had an event to take my user input of Bonk's… and row-update that into a TransView.MyFilter field with the following expression:

"{TransView.MyTest}" .replaceAll(String.fromCharCode(39), String.fromCharCode(39) + String.fromCharCode(39) + String.fromCharCode(39) + String.fromCharCode(39)) .replaceAll(String.fromCharCode(92), "")

The KEY was actually just replacing the backslashes. I tested the below as well (going back to replacing actual " ’ ") it also worked (and is much cleaner):

"{TransView.MyTest}" .replaceAll("'", "''''") .replaceAll(String.fromCharCode(92), "")

Either of the above replaces the single quote with (4) single quotes… and then eliminates backslashes.

I then was able to set my BAQ Option Where Clause to:
Vendor_Name LIKE '%?{TransView.MyFilter}%'

Grid filtered successfully.

8

Thank you and @jbooker so much for your help!! I’ve never thought of pre-formatting my where filters, I think this will be very helpful for debugging! @jbooker, I know I also find where filters a bit easier, I try to save baq params for the more complex dashboards!

I also tried this out and it worked great for me! I appreciate the help, and the silly test case name lol, thank you!!

9

Nice Dave. It really is silly. The framework seems to handle the (un)escaping inconsistenly between actions. Or at least, if there is a pattern, it’s not documented so we trial and error and don’t track the nuances. Then of course there’s the diff between js and sql escaping. If baq action is going to transform values, it should handle it better.

My preference would be: double escape is okay but we shouldn’t have to replace slashes.

10

mood GIF

This is the summation of my Kinetic experience.

Trial and error until you find what works… then forget and have to do the same trial and error again the next time you need to do it! :rofl: