"On change of field ..." - how would I write that in a formula? 1

[CarolineS]

Hi,

I have a running total field which worked fine as

Field to summarize: table.commissions
Evaluate on change of field: table.Invoice#
Reset: on change of group SalesRep

I need it to evaluate on change of Invoice# because there may be more than one item on an invoice, and a sales rep's total would be incorrectly inflated as a result.

Now I'm bringing down a whole range of posting dates so that I can calculate YTD Sales (in a different field; that's working fine). But now in my running total described above, I have to add the criteria that the invoices fall in a date range established by a couple of parameter fields.

What my running total now says is

Field to summarize: table.commissions
Evaluate [use a formula]:
onfirstrecord = false and
table.Invoice# <> Previous(table.Invoice#) and
PostingDate in DateTime({?Start Date}) to DateTime({?End Date}
Reset: on change of group SalesRep


....It's working, but I'm just not sure it's a foolproof way to write this. Is there some syntax to say &quot;on change of field&quot; that I could add into the formula instead?

Thanks,
Caro

 

[kenhamady]

That should work, as long as the same invoice# can't appear in two different groups.

But a safer approach is to write the running total against a formula field that says:

If {table.PostingDate} in
DateTime({?Start Date}) to DateTime({?End Date}
then {table.commissions}
else 0

Then use this as your running total's 'field to summarize'. Then you can use the evaluate on change of group as you did before. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CarolineS]

Great, that's perfect. Thanks, Ken.