data selection based on condition

[koolskins]

CR 10 against an Informix database. I need a PO commitment report for outstanding po's that have not had a final pay process against it. The tables are lined up like this:

po_line_item table
-------------------
po_nbr
po_line_item
line_item_commitment


ap_invoices
--------------------
po_nbr
invoice_nbr
final_paid

I need the sum of the individual line items for any po where there is no ap_invoice generated with a final_paid = Y. There may be none or many invoices against each po and one or more of those may or may not have the final paid flag set to Y. If any one invoice has the final_paid = Y set, then the complete value of that po should not be included.

The end result is simply:

po number po_commitment

grand total po_commitment

 

[Madawc]

You need a running total, I think.

Right-click on a field and choose Insert to get a choice of Running Total, Summary etc.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say.

Madawc Williams (East Anglia, UK)

 

[lbass]

I would create a formula {@finalpd}:

if {ap_invoices.final_paid} = "Y" then 1 else 0

Insert a group on {ap_invoices.po_nbr}. Then go to report->edit selection formula->GROUP and enter:

sum({@finalpd},{ap_invoices.po_nbr}) = 0

This will return the POs that have no "Y" in any invoice. Note that once you have used group selection, any calculations across groups will include non-selected groups, so you would need to use running totals.

-LB

 

[koolskins]

LB - the concept you described works great, however, when I attempt to run it for a three month period, it keeps building a temp file in one of my partitions that exceeds the partition's limit (well over 40 meg). The a/p file in particular is very large and this problem only came up when the ap group logic was added. I've tried narrowing the data extraction by adding additional select logic but can't seem to limit the temporary file size. Any ideas?

thanks for the assistance

 

[lbass]

I don't really know much about that sort of thing, but what surprises me is that group selection takes place AFTER records are returned, and basically subselects from those records, so I wouldn't expect the group selection itself to create extra temp files, although I could be wrong about this. Someone else will need to help you, I think.

-LB