Make a formual evaluate only within a group and reset.

[ErnstNot]

Hi!

I'm working on a report to evaluate a numberserie for several different client accounts. I simply have a group for each client nbr. Withtin each group there is a invoice serie, and my goal is to identify missing numbers in a number series within each group.

I have then first created the following formula:

if {@Numeric} = true
then if {@09_Invoice_To_nbr} + 1 = Next ({@09_Invoice_To_nbr}) then ""
else if {@09_Invoice_To_nbr}+2 = Next ({@09_Invoice_To_nbr}) then
"Missing " + totext({@09_Invoice_To_nbr}+1,"0000")
else "Missing " + totext({@09_Invoice_To_nbr}+1,"0000") + " to " +
totext(next({@09_Invoice_To_nbr})-1,"0000") else {Cust__Ledger_Entry.External Document No_}

This works fine for one continous numberseries and will give a result such as this: "Missing 10600 to 10606"

however, when grouping by client nbr, the formula will then also evaluate the difference between the numberseries from one group to the other

GroupHeader Client 1
invoice 1
Invoice 2
Invoice 3
Invoice 4
GroupFooter Client 1
GroupHeader Client 2
invoice 10 The formula as is will give a result here such as: "Missing 5 to 9"
Invoice 11
Invoice 12
Invoice 13

I would like it to reset before every group so it only evaluates the numbers within each group. Also notice that the field is a string field. The numeric field in the formula is another formula so I can disregard non-numeric values.

Attached is a picture of the result I get.

Looking forward to suggestions

Best Regard
Ernst

 

[IanWaterman]

Use a number variable as your result.

You can then reset the number var in the client group header

Create 2 formulae, please note : ; are very important as is the order

@Reset// place this in client group header

Whileprintingrecords;

Global numbervar result:=0

@Eval// place this in detail

Whileprintingrecords;

Global booleanvar FirstrecordTest;

Global Numbervar result;

result:= your formula

Ian

 

[ErnstNot]

Hi!

Thank you Ian for the suggestion. However, this wont work either. The problem is actually not resetting. The formula I have made will look to the next nbr, and add and subtract 1 to find the next or previous number. But as it does this, the problem is not resetting, it is actually that the last line in every group will always get a result.

I Solved it by making a max summary on the invoice numbers and then I changed my formula like this:

if {Ledger_Entry.External Document No_} = Maximum ({Ledger_Entry.External Document No_}, {Ledger_Entry.Client No_}) then ""
else
if {@Numeric} = true
then if {@09_Invoice_To_nbr} + 1 = Next ({@09_Invoice_To_nbr}) then ""
else
if {@09_Invoice_To_nbr}+2 = Next ({@09_Invoice_To_nbr}) then
"Missing " + totext({@09_Invoice_To_nbr}+1,"0000")
else
"Missing " + totext({@09_Invoice_To_nbr}+1,"0000") + " to " +
totext(next({@09_Invoice_To_nbr})-1,"0000") else {Ledger_Entry.External Document No_}

Ernst