multiple selections - Crystal reports 1

[tonyvee1973]

Hi

Running a report which shows whether a stock code needs to be replenished.
i have the formula below:

if {Master.balance} <= {@Trigger} then 'yes' else 'no'

But i also need the decision below integrated:

If {category} = 'h' and (master.code} is one of 1,15,122,400 then yes else no

Can anyone help?

 

[MCuthill]

TonyVee1973,

I beleive the following will work for your scenario:

IF {Master.balance} <= {@Trigger} THEN 'yes' ELSE
IF {category} = 'h' AND {master.code} in [1,15,122,400] THEN "yes" ELSE "no"

If {master.code} is a string, place quotes around each number listed. Namely {master.code} in ["1","15"...


If you wanted to know which reason caused the replenish to return "yes", you could create a formula for each reason, then an evaluative formula to return yes if either formula returns yes. The third formula would be the one used for business decisions, but #1 & #2 could be on the report for reference.

Formula 1:

IF {Master.balance} <= {@Trigger} THEN 'yes' ELSE "no"

Formula 2:

IF {category} = 'h' AND {master.code} in [1,15,122,400] THEN "yes" ELSE "no"

Formula 3:

IF {@Formula 1} = "yes" OR {@Formula 2} = "yes" THEN "yes" ELSE "no"

Hope this helps!


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[tonyvee1973]

Hi Mike, thanks for the advice.

I've applied the formula and it doesnt seem to take the 2nd line (IF {category} = 'h' AND {master.code} in [1,15,122,400] THEN "yes" ELSE "no") into consideration.

I have looked at code 2,9,11 etc and they are all still saying "Yes"

Any ideas?

 

[MCuthill]

TonyVee1973,

The formula I provided means either criteria will result in a "yes". Is the {master.balance} for these < {@trigger}? (I would assume yes).

If this is not the result you are seeking, can you please clarify the results you are seeking (with a few examples of records and the outcome sought). If you are looking for "yes" only when the balance < trigger AND the category is "h" AND the code is one of those listed, the formula will certainly need changed.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[tonyvee1973]

Hiya, sorry i think i didnt explain myself clearly.

Yes, the {master.balance} for these category 'h' are < {@trigger}
so therefore returning a 'yes' even if they are not one of the codes listed (1,15,122,400)

I need to look at code 2-14 for example and see a 'no' regardless of them being < {@trigger}

I do hope i make sence, its getting late now though and this has been on me all afternoon

Appreciate the help Mike

 

[MCuthill]

No Problem TonyVee!

If these code are to be "no" regardless of the other criteria, I think the following should work:

IF {master.code] in [2 to 14] THEN "no" ELSE
IF {Master.balance} <= {@Trigger} THEN 'yes' ELSE "no"

(this will return no for all codes 2 through 14, regardless of category)


If it is ONLY for the category "h" items these codes return "no" then please change the formula to as follows:

IF {category} = 'h' AND {master.code] in [2 to 14] THEN "no" ELSE
IF {Master.balance} <= {@Trigger} THEN 'yes' ELSE "no"

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

oops!

Correction, as I am sure it isn't just 2 though 14, but rather all those you didn't list.

IF {category} = 'h' AND Not({master.code] in [1,15,122,400]) THEN "no" ELSE
IF {Master.balance} <= {@Trigger} THEN 'yes' ELSE "no"

My apologies for the oversight.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[tonyvee1973]

Thanks Mike, it was the latter of the two that i needed (nice one for decyphering my gabbling) I've applied and this works a treat so thank you.

The last part i have to figure out now is how to get 160 x {master.code} in the formula. The 2 to 14 i gave you was for example, i have many numbers from 1 to 1250.

I think maybe another formula referencing the 160 {master.code} then amend the formula you sent me to below - you think it'd work?

IF {category} = 'h' AND {master.code] in [@newformula] THEN "no" ELSEIF {Master.balance} <= {@Trigger} THEN 'yes' ELSE "no

 

[MCuthill]

Hi TonyVee,

I don't beleive this part will work:
"{master.code} in [{@newformula}]"

for the in function, I beleive you need to list the options (or perhaps build an array formula? though I have not used such). if you have 1,250 codes to look at, I would suggest looking at the lesser of the two groups and analyze ranges and such to create the smaller "in" listing (either the yes's or the no's).

For Example:

Not({master.code} in [1,15,122 to 250,266,300 to 350,500,750])

you can use combinations of individual values and ranges to minimize the maintenance of this formula.


Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[tonyvee1973]

Thanks Mike, i'll have a go
Once again, appreciate the help, enjoy the rest of the day!
Tony

 

[tonyvee1973]

Hi Mike

Just wanted to say thanks again, i had to mod slightly but ended up working a charm! I have posted the formula i used below so you can see.

IF {@category} = 'H' AND not ({master.code} in [***a range of 160+ numbers]) THEN "no" ELSE
IF {Master.balance} <= {@Trigger} THEN 'yes' ELSE "no"

You're a star! Kudos!
Tony
(P.S how do i close this post as solved?)

 

[MCuthill]

Happy to help Tony!

The thread will close to new postings after a set period of time, and as far as I know cannot be manually closed.

Egad, one hundred and sixty items in the list. My fingers are crossed that the listing stays constant and doesn't need revisited often.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."