Help with formula - if field in and another field >= or another field in and another field >=

[cpjeffm]

I'm working on a report in Crystal 11.5 and need some assistance with a formula. I'm looking at inventory data where the manufacturer is equal to multiple values and the inventory year >= a year or where the manufacturer is equal to other multiple values and the inventory year >= a different year.

Here's what I'm trying. The formula name is {@Exclusions}:

{Inventory.Manufacturer} in ["AAA","BBB","CCC","DDD","EEE","FFF"] and {Inventory.YearEntered} >= 2015 or
{Inventory.Manufacturer} in ["GGG","HHH","III","JJJ","KKK","LLL","MMM"] and {Inventory.YearEntered} >= 2009

Then in my record expert, I want to include:

not {@Exclusions}

Any help would be greatly appreciated and I'm not married to this method if anyone has any better ideas on how to accomplish this.

Thanks!

 

[pmax9999]

Assuming I understand what you are trying to achieve, I would change the formula as follows (note where you are mixing 'and' with 'or' it is always best to use parentheses to ensure the components are grouped correctly):
[Code {@Exclusions}]
If (
{Inventory.Manufacturer} in ["AAA","BBB","CCC","DDD","EEE","FFF"] and
{Inventory.YearEntered} >= 2015
)
or
(
{Inventory.Manufacturer} in ["GGG","HHH","III","JJJ","KKK","LLL","MMM"] and
{Inventory.YearEntered} >= 2009
)
Then True
Else False
[/Code]

In your Selection formula, use the line: {@Exclusions} = False

Hope this helps.

Cheers
Pete

 

[dgillz]

You don't need to reference {@Exclusions} in the record selection formula, just use it as the record selection formula without the IF statement,

(
{Inventory.Manufacturer} in ["AAA","BBB","CCC","DDD","EEE","FFF"] and 
{Inventory.YearEntered} >= 2015
)

or

(
{Inventory.Manufacturer} in ["GGG","HHH","III","JJJ","KKK","LLL","MMM"] and 
{Inventory.YearEntered} >= 2009
)

This will evaluate to True or False all by itself and your report should be fine.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[cpjeffm]

Ok either of the above works with one exception. In some cases, we'll have inventory records without a manufacturer listed. These are not showing on the report. It's only showing records if the manufacturer is not null. Any idea as to why this is? If I remove the exclusions, all records show including the ones where the manufacturer is null.

 

[cpjeffm]

Never mind. I had to reorder the formula and put the year first.