Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

cpjeffm

IS-IT--Management
Mar 9, 2015
73
US
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!
 
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
 
You don't need to reference {@Exclusions} in the record selection formula, just use it as the record selection formula without the IF statement,

Code:
(
{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:

 
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.
 
Never mind. I had to reorder the formula and put the year first.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top