Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Suppress record if same part id extended costs sum to 0 1

Status
Not open for further replies.

NBVC

Technical User
Sep 18, 2006
80
CA
I am trying to suppress a Detail record on conditions..

What I have is:

[ul]
[li]Each record is distinct on InvoiceLineID[/li]

[li]Each InvoiceLineID can have duplicate SalesOrderLineID[/li]

[li]Each SalesOrderLineID has an ExtendedCost[/li]

[li]Each SalesOrderLineID can have a different PartID[/li]
[/ul]

What I need to do is:

Look if a there is a duplicate record with same SalesOrderLineID and same PartID = "Deposit" and suppress both records if the ExtendCost negate each other (i.e. sum to 0).

Note that the Deposit for each InvoiceID will be listed consecutively in the report...
 
Use a detail section suppression formula like this:

Not onfirstrecord and
{table.SalesOrderLineID}=previous({table.SalesOrderLineID}) and
{table.PartID}=previous({table.PartID}) and
{table.partID}=“Deposit” and
Previous({table.partID})= “Deposit” and
{table.extendedCost}+previous({table.extendedCost})=0

When you recreate this formula, be sure to replace the quotes as I think the iPad quotes I’m using will cause the formula to error out.

-LB
 
Hi LB,

Thanks very much for this.

It is partially working for me.

It is only removing one of the 2 "Deposit" records (the latter).

Is it a matter of adding to your code for a "Not onlastrecord" and using Next({table.partID}) kind of thing?
 
Hi again,

it did work as I expected when adding the "Not onlastrecord" part.

Is that the right way or is there a better way to combine them?
 
Sorry, that should have been:

(
Not onfirstrecord and
{table.SalesOrderLineID}=previous({table.SalesOrderLineID}) and
{table.PartID}=previous({table.PartID}) and
{table.partID}=“Deposit” and
Previous({table.partID})= “Deposit” and
{table.extendedCost}+previous({table.extendedCost})=0
) or
(
Not onlastrecord and
{table.SalesOrderLineID}=next({table.SalesOrderLineID}) and
{table.PartID}=next({table.PartID}) and
{table.partID}=“Deposit” and
next({table.partID})= “Deposit” and
{table.extendedCost}+next({table.extendedCost})=0
)

Another approach might be to group on Part ID (assuming group #1 on invoicelineID, group #2 on salesorderlineID, and group #3 on Part ID. Then you could use a formula like this:

sum({table.extendedcost},{tablePartID})=0

Whether this works depends upon your data, and I haven't seen any samples of it.

-LB
 
Thanks LB.

Got it working.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top