×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

If OnLastRecord?

If OnLastRecord?

If OnLastRecord?

(OP)
Hello,
There's this report and on Suppress Freight and InvTotal (so not to repeat when there are multiples lines on an invoice)

CODE

if OnLastRecord = False then
Next({AR_InvoiceHistoryDetail.DetailSeqNo}) <> '000000' Else False 

How do I modify it to also handle if the 1st record doesn't have 000000 but 000001 instead?
I tried this

CODE

[code]if OnLastRecord = False then
Next({AR_InvoiceHistoryDetail.DetailSeqNo}) <> '000000' ({AR_InvoiceHistoryDetail.DetailSeqNo}) <> '000001'  and Else False 
But still getting incorrect information. The report is exported out into Excel then sent to Customers.

So it's ItemCodes and sometimes the same itemcodes repeats per invoice, hence using the DetailSeqNo (Line item no).

However it appears on some invoices the 1st line item starts with 000000 and some starts with 000001.

Why the original OnLastRecord doesn't always work.

The reason for the suppress is that there are header records and detail records so the header record repeats for every detail record. Just want to show it once on the 1st detail record of the header record.

Hope that made sense?

Example:
Invoice = 1
DetailSeqNo = 000000
Itemcode = X
DetailSeqNo = 000001
ItemCode = X
DetailSeqNo = 000002
ItemCode = Y

Invoice = 2
DetailSeqNo = 000001
ItemCode = Y
DetailSeqNo = 000002
ItemCode = Y
DetailSeqNo = 000003
ItemCode = Z

Invoice = 3
DetailSeqNo = 000000
ItemCode = X
DetailSeqNo = 000001
ItemCode = Y
DetailSeqNo = 000002
ItemCode = Z




WHY it did that (re: assign the 1st line sometimes with 000000 and sometimes with 000001, do not know but that's what's in the system (tables) and dealing with that.

Need to NOT repeat where highlighted, it's the "header" info, freight amount and invoice total and should show only once per invoice (change on invoice).

Please help.

Thank you

RE: If OnLastRecord?

First you should have a group on invoice and then suppress the group sections. Then select the fields you want to conditionally suppress and right click->format field->suppress->x+2 and enter:

{table.detailseqno}<>minimum({table.detailseqno},{table.invoiceno})

This will make the header amounts only show on the first detail record of the invoice, assuming you are sorting on detailseqno.

It is unclear whether you also are trying to suppress duplicate detail records. If you are, then go to section expert->details->suppress (do not check)->x+2 and use a section suppression formula like this:

Not onfirstrecord and
{table.invoiceno}=previous({table.invoiceno}) and
{tabledetailseqno}=previous({table.detailseqno})

-LB

RE: If OnLastRecord?

(OP)
Thank you!

The original formula was on DetailSeqNo (Group #2, Group #1 is InvoiceNo) on conditional suppress | Suppress |x2

I removed the DetailSeqNo, added yours

also did the Not OnLastRecord on Select Expert on Detail section.

This seems to work. I have a slight descrepancy
will be working through that but looks right overall

How do you do a report total with these conditions so they are correct?

I exported it out to Excel to sum them after the newly modified settings and your formulas

4852 36137.27 15237.42 51345.73

4849 36115.38 15234.72 51321.14
Variance 3 21.89 2.70 24.59

Again Thanks so much!!!

RE: If OnLastRecord?

Not enough information to troubleshoot this. Sorry.

-LB

RE: If OnLastRecord?

(OP)
Sorry, basically how to do a report total with the formulas you've provided so the totals make sense?

RE: If OnLastRecord?

The formulas I provided have nothing to do with creating summaries except that anytime you suppress rows, you need to use running totals to exclude them, since they are still IN the report, just not visible. You would have to use running totals that evaluate on change of group (invoice number). That might be all you need to do. You will need to test this by observing the value of the running total in the detail section. If correct, place in the report footer.

-LB

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! Already a Member? Login

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