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

[NBVC]

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

 

[lbass]

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

 

[NBVC]

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?

 

[NBVC]

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?

 

[lbass]

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

 

[NBVC]

Thanks LB.

Got it working.