BAQ Filter in Application Studio

Kinetic ERP
,
1

Hi all,

I’ve created a BAQ and published it as a dashboard in the Application Studio. However, I want to add additional filters through Application Studio to filter out rows where two particular columns are blank.

Something along the lines of:

(LaborDtl_LaborHrs = 0 AND Calculated_PartNum = ‘’) OR (Calculated_LaborHrs = 0 AND JobMtl1_PartNum = ‘’)

I have attempted to do this in the “Where” box of “Baq options” using this post as inspiration:

(Application Studio Dashboard BAQ Where List -- "AND"?)

But I had no luck, my syntax does not seem to be correct.

Should I create an event and add a dataview filter? But then again I have the same issue of being unsure of correct syntax. Am I missing something obvious?

Any advice welcome.

2

I would post what you have in the where box. You might get more responses.

2 Likes
3

What you have as an expression looks decent… the only caveat would be to make sure you’re using straight quotes ‘’ vs curly quotes ‘’.

Hard to say whether these got changed when you typed, or copy/pasted in here.

image
image854×145 3.29 KB

Below is the straight-quote version if you want to paste it in and test.
(LaborDtl_LaborHrs = 0 AND Calculated_PartNum = '') OR (Calculated_LaborHrs = 0 AND JobMtl1_PartNum = '')

~*~

Another thing you may need to verify is… a string may not be empty (‘’). It could be undefined.

For example, when I’m writing conditions with regards to PartNum(s), I typically include undefined in my check.

'{KeyFields.PartNum}' !== '' && '{KeyFields.PartNum}' !== 'undefined'

Technically, strings can also be null. You could try the below, which checks all (3) (i think) and see if it works?

(LaborDtl_LaborHrs = 0 AND (Calculated_PartNum IS NULL OR Calculated_PartNum = '' OR Calculated_PartNum = 'undefined')) OR (Calculated_LaborHrs = 0 AND (JobMtl1_PartNum IS NULL OR JobMtl1_PartNum = '' OR JobMtl1_PartNum = 'undefined'))
4

Hi David,

The straight-quote version you made behaves the same as mine, so it looks like it enters into the Where box as straight brackets.

I should clarify I want to remove rows where both LaborHrs = 0 and PartNum = ‘’. So I now realise it should look like this:

(LaborDtl_LaborHrs <> 0 AND Calculated_PartNum <> ‘’) OR (Calculated_LaborHrs <> 0 AND JobMtl1_PartNum <> ‘’)

Now what’s interesting, is that this overall expression does not return any rows, which is incorrect.

However, trying each expression without the AND/OR operators, the following work:

LaborDtl_LaborHrs <> 0

Calculated_PartNum <> ‘’

Adding the AND back in, this doesn’t return rows, even though I know rows that meet this condition exist:

(LaborDtl_LaborHrs <> 0 AND Calculated_PartNum <> ‘’)

My understanding was this expression would result in rows where both these conditions are met, but it seems to filter out one and then the other?

(I also used your expression to check for NULL and ‘undefined’ and there aren’t any, which was a good check)

5

Well, it could be the parenthesis are the issue?? Perhaps try the below and just see if you can get that portion to work.

LaborDtl_LaborHrs <> 0 AND Calculated_PartNum <> ''

I can’t say I’ve used them in any of my where clauses because I’ve never had a need for a compound “OR” statement like you’re attempting.

I’ll play around this morning and see if I can get it to work with one of my clauses.

6

Try:

(LaborDtl_LaborHrs <> 0 AND (Calculated_PartNum <> '' OR Calculated_PartNum IS NOT NULL)) 
AND 
(Calculated_LaborHrs <> 0 AND (JobMtl1_PartNum <> '' OR JobMtl1_PartNum IS NOT NULL))
7

So, I’m using a different BAQ, so, ignore the columns I’m using… but I ran through a bunch of variations… all seemed to work:

QuoteDtl_PartNum = '??{Part.PartNum}'
Basic: Works as intended

QuoteDtl_PartNum = '??{Part.PartNum}' AND QuoteDtl_QuoteLine = 7
Basic ‘AND’: Works as intended

QuoteDtl_PartNum = '??{Part.PartNum}' AND QuoteDtl_QuoteLine = 7 OR QuoteDtl_QuoteLine = 12
‘AND’ + ‘OR’
Returned… almost as if I had parenthesis around the first two arguments.
I get the correct combination of PartNum & QuoteLine = 7
I then also get ALL PartNums where QuoteLine is 12.

QuoteDtl_PartNum = '??{Part.PartNum}' AND (QuoteDtl_QuoteLine = 7 OR QuoteDtl_QuoteLine = 12)
Added parenthesis around the 2nd & 3rd arguments:
Worked as you would expect… only returned combinations of PartNum and 7 AND PartNum and 12

QuoteDtl_PartNum = '??{Part.PartNum}' AND (QuoteDtl_QuoteLine = 7 OR QuoteDtl_QuoteLine = 12) OR QuoteDtl_QuoteNum = '36986'
Returned:
Combinations of PartNum and 7 AND PartNum and 12
AND all QuoteLines for QuoteNum ‘36986’

QuoteDtl_PartNum = '??{Part.PartNum}' AND (QuoteDtl_QuoteLine = 7 OR QuoteDtl_QuoteLine = 12) OR (QuoteDtl_PartNum = '??{Part.PartNum}' AND QuoteDtl_QuoteNum = '36986')
Returned:
Combinations of PartNum and 7 AND PartNum and 12
AND ONLY the QuoteLine for QuoteNum ‘36986’ where PartNum was correct.

(QuoteDtl_PartNum = '??{Part.PartNum}' AND QuoteDtl_QuoteLine = 7) OR QuoteDtl_QuoteLine = 12 OR (QuoteDtl_PartNum = '??{Part.PartNum}' AND QuoteDtl_QuoteNum = '36986')
Returned as expected:
PartNum & 7
All QuoteLines from all Quotes where QuoteLine = 12
PartNum & Quote 36986

(QuoteDtl_PartNum = '??{Part.PartNum}' AND QuoteDtl_QuoteLine = 7) OR (QuoteDtl_QuoteNum = '36986' AND QuoteDtl_QuoteLine = 2)

It seems to have filtered correctly:
018-01618 is the PartNum I called… and it only appears as Line 7 on Quote 52004.
It also returned QuoteNum 36986 Line 2, even though it is a different PartNum
image

8

Thanks, I gave it a go but it didn’t seem to work.

The first line works as intended:

(LaborDtl_LaborHrs <> 0 AND (Calculated_PartNum <> ‘’ OR Calculated_PartNum IS NOT NULL))

But the final line returns 0 rows:

(Calculated_LaborHrs <> 0 AND (JobMtl1_PartNum <> ‘’ OR JobMtl1_PartNum IS NOT NULL))

9

try:

((Calculated_LaborHrs <> 0 AND Calculated_LaborHrs IS NOT NULL) AND (JobMtl1_PartNum <> '' AND JobMtl1_PartNum IS NOT NULL))

as David said, pay close attn to your single quotes. If they are pasting into Kinetic as wavy like they are here that’s a problem. single quotes must be apostrophe.

10

Thank you for testing all those, that’s pretty comprehensive.

My suspicion now is the fact that this is a Union All BAQ may be causing the filter to not apply correctly.

11

I’m afraid that hasn’t worked either, but thank you again.

I have a feeling that this issue may be due to the BAQ being Union All, With a top level and three union all subquery’s.

12

yeah you definately wanna break it down to simple query so you know your seeing expected results. Many correct WHERE stmts on here but hard to say what’s what in a complex query.

1 Like
13

PS - see if you can make it work in BAQ designer - with subquery tests then look at the WHERE clause(s) from the sql display. Maybe gleen some hints on how our suggestions apply to your complex circumstance