Kinetic Dashboard: Filters nulling (I think) in kinetic-baq where clause

Kinetic ERP
1

Odd thing here, and poorly described.

I have four filters attached to a dataview called FilterDV. I have this whereclause on the kinetic-baq widget. The whereclause is built in a row update because it quits working when the whereclause gets too long stuffed directly inside the Where field.

So, in the row update, FilterDV.CalculateWhere =

“InvcHead_FiscalYear >= '” + ({FilterDV.FiscalYearFrom} || “0”) + “’ AND InvcHead_FiscalYear <= '” + ({FilterDV.FiscalYearTo} || “9999”) + “’ AND InvcHead_FiscalPeriod >= '” + ({FilterDV.FiscalPeriodFrom} || “00”) + “’ AND InvcHead_FiscalPeriod <= '” + ({FilterDV.FiscalPeriodTo} || “99”) + “'”

That works great UNTIL I enter a value in a filter and then go back and clear that value and hit the refresh. The dashboard never works again, at least the baq get doesn’t work.

I’ve seen a reference or two that says sometimes when you clear data from a string field it will go null. The control doesn’t gray out, so I don’t know.

Anyway, I’ve tried to come up with a way to sanitize the filters right before the whereclause calculation. If I try to modify the value in a row update like this:

Binding: FilterDV.FiscalYearFrom Expression: String({FilterDV.FiscalYearFrom}

or

Binding: FilterDV.FiscalYearFrom Expression: ??{FilterDV.FiscalYearFrom}

The row update hangs – trying to change itself, maybe. ??

I might be forced to add an event with a bunch of condition blocks, but I have a bunch of dashboards to recreate and that’s a lot more work.

Anyone have an idea here? Surely someone’s been down this road.

And I know the search is imperfect, but it’s what they’re already using.

Thanks,

Joe

1 Like
2

This may work in place if you switch to a parameterized BAQ (it supports omitting where criteria when the input is null) - but I usually call the unfiltered BAQ from a function and overlay the where criteria on the dynamicquery tableset before executing it, then return ds.results in my function. As I recall the where criteria in app studio supports ?? notation for omitting a condition if the value is null also.

2 Likes
3

Hard to say without seeing what those fields evaluate to when emptied and what the BAQ request where object looks like. Have you proven the JavaScript expression is evaluated as expected?

The logical OR [ || ] returns first non-falsely (so 0 || 99 gives 99 because zero is falsely) while coalescing operator [ ?? ] returns first non-nullish. Perhaps try ?? Instead so it’ll only fall back when null or undefined.

Also the expression evaluation (and js in general) is bad with plus operator. Especially don’t trust them for strings of digits. Wouldn’t be surprised if it does arithmetic instead of concatenation when empty. perhaps try .concat()

Lastly could try the BAQ where list to apply your four expressions independently. InvcHead_FiscalYear >= ?{FilterDV.FiscalYearFrom}

This should use kinetic question mark logic to prevent the filter when missing possibly avoiding both your fallbacks and concatenation.

1 Like
4

Thanks, Gabe.

Some additional info.

I have a big list of queries/dashboards to upgrade from Classic to Kinetic, and I don’t want to have to copy/change every query to add parameters, so that kinds of limits using BAQ parameters where they are not already in use.

In the where clause on the widget, when I use the ‘??’ notation, Epicor kills it. You can look in the payload and the filter info is empty. It works on the provider model, but not at the widget level. I could be doing something wrong.

This is a wrestling match. :slight_smile:

Joe

1 Like
5

Thanks, Josh.

This where clause works where none of the others I’ve tried do (and I’ve tried a lot). The ‘??’ notation gums up the works at the widget. It works at the provider model level, but I’m not using the provider model. Working within my constraints here.

If I can get that last issue with the cleared filter figured out, I think I’ll be golden.

Joe

1 Like
6

Can you control with data rules to set some default values:

For example, if I have a Date Picker (bound to TransView.DatePicker), initially it has no value.

I set the value in the UI… the value changes to “2026-01-29T00:00:00”

I delete the value in the UI… the value is null.

But… with a data rule, I can say if TransView.DatePicker contains a value of null… set value of TransView.DatePicker to 1/1/1900.

image
image1856×595 21.4 KB

Seems to work.

image
image929×254 15.4 KB

Obviously 1/1/1900 is WIDE OPEN… which may or may not be ideal for your situation. But, it was just an example.

The crux of my thought was, if the user deletes a value and it then turns “null”, perhaps you can use a data rule to set a default value that won’t blow up your BAQ. Even if that default value is “”… its not null.

2 Likes
7

Interesting. . .

I’ll give it a try.

Thanks, David.

Joe

1 Like
8

Well, the data rules wouldn’t show my custom filter dataview. Dashboards don’t come with TransView, so we have a custom FilterDV. It’s not accessible from the data rules.

Dang it.

Joe

9

You should be able to manually enter your dataview/fields and click the popup to set it if it doesnt show in the list.

3 Likes