Dashboard view rule question

Hi all,

I’m trying to color code a dashboard based on lot expiration dates.

I created the calc fields that I’m using as my rule values in the BAQ, but the expiration date field is null for many parts, and the null fields are for some reason satisfying the criteria. I can create another calc field and assign a bool value to check for null, but I don’t see a way to use multiple conditions in the view rules.

Is there a way to create multiple conditions that I’m unaware of, or maybe a creative work around that someone can share?

Hi Ryan,
You can add a new calculated field to your BAQ with as many conditions as you need on one or multiple fields. Then you can return the column as an integer value instead of a boolean. That will give you the flexibility you are looking for. On the Dashboard RowRule, use the integer value to decide what to do (if calcfield = 0 green, if calcfield = 1 red, if = 2 yellow, etc.)

An example here:


Awesome, thank you for that link, Carlos! Much appreciated.

A few hours of cussing at my monitor and I’m still unable to come up with a calc field that works.

I finally came up with something that the editor doesn’t reject, but the query will no longer run with the calc field. When I test the query I get this: Table: , Level:, Type: , RowID: , Text: Business Query Execution error. Please contact your System Administrator.

I couldn’t get anything to work using isNull, so I went with the following:

if ((PartLot.ExpirationDate) < Today and (PartLot.ExpirationDate) <> ‘’) then ‘1’
else if ((PartLot.ExpirationDate) >= Today and (PartLot.ExpirationDate) <= (Today + 29) and (PartLot.ExpirationDate) <> ‘’) then ‘2’
else if ((PartLot.ExpirationDate) >= (today + 30) and (PartLot.ExpirationDate) <> ‘’) then ‘3’
else ‘4’

Hi Ryan,

I did it like this based on your formula and it seems to work, or at least it doesn’t fail when I Test the BAQ. I don’t have dates, so you need to confirm date conditions are met.

if ( PartLot.ExpirationDate = '' ) then 4
else if ( IsNull(PartLot.ExpirationDate) ) then 5
else if (PartLot.ExpirationDate < Today) then 1
else if ((PartLot.ExpirationDate >= Today) and (PartLot.ExpirationDate <= (Today + 29))) then 2
else if (PartLot.ExpirationDate >= (today + 30)) then 3
else 6

and the test gives:


Thank you for the continued help, Carlos.

Well I don’t know what could be going on here, but even with your example, my query will not run.

I started with a fresh query as well to test.

PartLot is my only table, PartNum, LotNum, ExpirationDate and the calc field are the only things I’m displaying, yet I’m still getting the same error.

If I remove the calc field, the query completes as expected.


Since PartLot.ExpirationDate is a date field, it can’t be equal to a blank… try taking that first expression out and see if it at least runs.

That was it, Ernie. Thank you!

Thank you again, Carlos. I have a feeling that since you aren’t using that field, and have no dates in that table, that troublesome expression didn’t rear it’s ugly head.