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.)
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’
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
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.