Alert! Condition - How to exclude a result when a Detail record is true. 1

[jpstrzoch]

Greetings,

I am trying to construct an Alert for one of my reports. I am using Crystal XI connecting to an SQL DB. The report is constructed such that Item ID's, Detail Records, are returned in the detail section of the report grouped by their respective Order Number, Order Header records. The formula I entered into the Alert Condition is stated below.

{ORDRHDR.SOLDTOCUSTID} = "VRTX" and
{ORDRHDR.CLIENTDIVISION} = "GPS" and
{ORDRHDR.CLIENTGLACCT} = "11" and
Not({ORDRDET.ITEMID} in ["GPS-ENV-MED","GPS-ENV-LRG"])

My desire is ultimately to identify only those orders which meet the first three criteria and that do not contain Item ID's, "GPS-ENV-MED" or "GPS-ENV-LRG", as Detail records of the order. I know this is written incorrectly as the Alert will return an order meeting the first three criteria and the Order Details of such an order yet simply minus the Detail records of either "GPS-ENV-MED" or "GPS-ENV-LRG" even when "GPS-ENV-MED" or "GPS-ENV-LRG" are a part of the detail records of an order.

If you can advise me on how to properly write such a formula I would greatly appreciate it!

Regards,
James Pstrzoch

 

[kenhamady]

Yes you are writing a record level condition when you need a group level condition. Instead write a separate formula field called EnvCounter that says:

if {ORDRDET.ITEMID} in ["GPS-ENV-MED","GPS-ENV-LRG"]
then 1
else 0

Then change your alert condition to be:

{ORDRHDR.SOLDTOCUSTID} = "VRTX" and
{ORDRHDR.CLIENTDIVISION} = "GPS" and
{ORDRHDR.CLIENTGLACCT} = "11" and
Sum({@EnvCounter},{ORDRHDR.ORDERID}) = 0

Note that the field {ORDRHDR.ORDERID} is my guess for the name of the group field for the order level group.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[jpstrzoch]

Good morning Mr. Hamady!

Thank you for your time and your reply post! I appreciate it very much especially during the holiday weekend!

Continued success and I wish you and your family a Happy New Year's!

Warm regards!
James J. Pstrzoch

 

[jpstrzoch]

Good morning Mr. Hamady,

I just now had the chance to exercise your recommendation above and I'm having a little difficulty. Let me provide additional clarification regarding the report structure in hopes that it may help to decipher what the cause of my difficulty may be.

The report has three group headers as shown below:

GH1 = {ORDRHDR.CREATEDATE}, Date the order was created.
GH2 = {ORDRHDR.SOLDTOCUSTID}, Customer for which the order was created.
GH3 = {ORDRHDR.ORDERNUMBER}, Order number.

The Detail section of the report is where the Item IDs, {ORDRDET.ITEMID} are returned.

When I execute the report and get a return of order numbers with detail records that meet the criteria of {ORDRDET.ITEMID} in ["GPS-ENV-MED","GPS-ENV-LRG"] the result is 0.00 when placing the formula into the Group Headers. Only when I add the formula to the Detail section of the report do I get a return of 1.00 next to the Item ID that is equal to either "GPS-ENV-MED" or "GPS-ENV-LRG".

When I add the formula expression shown below to the Alert as you had recommended, I am prompted with the message, "One of the previous fields is not constant over group level of this field."

{ORDRHDR.SOLDTOCUSTID} = "VRTX" and
{ORDRHDR.CLIENTDIVISION} = "GPS" and
{ORDRHDR.CLIENTGLACCT} = "11" and
Sum({@EnvCounter},{ORDRHDR.ORDERNUMBER}) = 0

Let me know your opinion of what I may be doing incorrectly.

Thank you in advance for your time!

James J. Pstrzoch

 

[kenhamady]

I have never seen this message before so I tried one here. It seems that an alert will allow fields and subtotals in the condition if it is a NEW alert but not if you try to modify an existing alert. Try creating a new alert with the same condition and see if it works. This may be a bug which means that anytime you want to change an alert like this you would need to recreate it.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[kenhamady]

I can get it to work with 2 detail rules and one subtotal rule, but if I try an alert with three detail rules and one subtotal rule the alert stops working. No error message, but the alert won't appear even though there are records that meet the criteria. I tried using formulas and couldn't get around it. It may be that the software is not designed to mix these rules, and my workaround can only go so far.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[jpstrzoch]

Sir,

Can you clarify when you say 2 detail rules and one subtotal rule.....restated, are you saying that the below would work:

{ORDRHDR.SOLDTOCUSTID} = "VRTX" and
{ORDRHDR.CLIENTDIVISION} = "GPS" and
Sum({@EnvCounter},{ORDRHDR.ORDERNUMBER}) = 0

But not the following:

{ORDRHDR.SOLDTOCUSTID} = "VRTX" and
{ORDRHDR.CLIENTDIVISION} = "GPS" and
{ORDRHDR.CLIENTGLACCT} = "11" and
Sum({@EnvCounter},{ORDRHDR.ORDERNUMBER}) = 0

Thanks again for your time!
James Pstrzoch

 

[kenhamady]

Yes, based on my environment and my data, that is the result I see. I can add 5 detail (single field) rules with no problem. But as soon as I mix field and subtotal rules I am limited to 3 rules and I have to create the alert as a new alert to avoid the previous error message. Very strange. Do you get a similar result?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[jpstrzoch]

Good afternoon Mr. Hamady!

That did the trick! I was able to add a new Alert!, but you're correct in stating that I had to create the brand new Alert! to avoid the error message! I was able to add two and three detail rules alerts along with a single subtotal without the error but again, it had to be a new Alert! each time.

Thank you very much again for your expertise! I would have never figured this one out on my own!

Have a safe and Happy New Years!

James J. Pstrzoch

 

[kenhamady]

Make sure you confirm that the alert actually works once it is saved. Like I said, I could save a condition with mixed rules without an error when it was now. But if there were more than 3 rules it would never show that alert.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[jpstrzoch]

Will do! Thanks again! It is working properly!

James J. Pstrzoch