Showing totals on a report for some records based on a condition but when all are requested 1

[Steve-vfp9user]

Hi all

I have a report that lists invoices. I can choose whether I want to show "paid", "unpaid" or "all".

A field in my table called INVOICEPYN is a character field C(1) which either stores a "Y" for paid or "N" for unpaid.

Examples:

*  Invoices "paid"

cPrinter=GETPRINTER()
IF NOT EMPTY(cPrinter)
  SET PRINTER TO NAME(cPrinter)
  REPORT FORM STATEMENTREPORTDENT NOCONSOLE TO PRINTER FOR INVOICEPYN="Y"
  cPrinter=""
  SET PRINTER TO DEFAULT
ENDIF

*  Invoices "unpaid"

cPrinter=GETPRINTER()
IF NOT EMPTY(cPrinter)
  SET PRINTER TO NAME(cPrinter)
  REPORT FORM STATEMENTREPORTDENT NOCONSOLE TO PRINTER FOR INVOICEPYN="N"
  cPrinter=""
  SET PRINTER TO DEFAULT
ENDIF

*  Invoices "all"

cPrinter=GETPRINTER()
IF NOT EMPTY(cPrinter)
  SET PRINTER TO NAME(cPrinter)
  REPORT FORM STATEMENTREPORTDENT NOCONSOLE TO PRINTER
  cPrinter=""
  SET PRINTER TO DEFAULT
ENDIF

In the footer of the report I have a numeric field called TOTAL which has a calculation type: Sum

All this works ok however, if I create the report for "all" invoices, the TOTAL adds up all the invoices whether they are paid or not.

Question: What do I need to do to differentiate between "paid" and "unpaid" so the sum total only adds up the "paid" invoices with the condition INVOICEPYN="Y" and ignores the total of the "unpaid" invoices with the condition INVOICEPYN="N" when I produce a report for "all" invoices.

In other words, invoices:
123 - paid - 150.00
124 - paid - 200.00
125 - unpaid - 150.00
Total of paid reports would be 350.00

I hope the above makes sense!

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Chris Miller]

1. You create a new field that's 0 for unpaid invoices and the paid amount for paid invoices, or even a partial payment when that's done.
2. You don't sum total but you sum IIF(INVOICEPYN='Y',total,0)

I would perhaps go for 1 as it gives you the option to copy the value of the invoice on the condition INVOICEPYN='Y' or set it to 0 or any partial amount paid for INVOICEPYN='N', so you may get better information about what money you actually got paid so far not just fully paid nor what you could potentially get if everything is paid.

Chriss

 

[Mike Lewis]

Steve,

You mentioned that invoices can either be paid or unpaid. But don't you also need to cater for partly-paid invoices, which is surely not all that unusual?

If so, instead of a boolean (yes/no) flag, consider having a field for amount paid. For an unpaid invoice it would be zero; for a paid invoice it would equal the invoice amount; and for all others it would contain the actual amount paid.

Your report footer would then simply contain the total for the amount paid field.

Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Same thought. If all invoices are quite small Steve might only have the Y/N case, but you never know when your first job allows for partial payment just due to the fact you'll take months and need a monthly compensation for your time. So it would pay to add that field even if you will usually not have the case of an amount in there being something between 0 and invoice total price. You could also take the chance to get rid of the Y/N field because IIF(invoice.amountpaid>=invoice.total,'Y','N') can give you that.

Chriss

 

[Steve-vfp9user]

Chriss / Mike

I'll look into your suggestions but in relation to part payments, it will only ever be paid or unpaid!

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Steve-vfp9user]

Just an update on this thread:

As Chriss suggested, I placed IIF(INVOICEPYN='Y',total,0) in the field properties. I took it one step further by duplicating the field on the form and changing 'Y' to 'N'.

So when the report is run it now shows paid and unpaid invoices on two separate fields.

Appreciate the posts all.

Thank you

Steve Williams
VFP9, SP2, Windows 10