INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Make a formual evaluate only within a group and reset.

Make a formual evaluate only within a group and reset.

(OP)
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 smile

Best Regard
Ernst



RE: Make a formual evaluate only within a group and reset.

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

RE: Make a formual evaluate only within a group and reset.

(OP)
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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close