Showing terms in Aged Receivables

[itguymike]

I am using CR 8.5 and win 2K. I'm doing a aged receivable report and i am trying to show a customers payment history. I want to have 5 column with the headers current-30, Over 30, over 60, over 120. Right now i have a column receivable.Total_amount that gives me the total for each inv. along with a date of the inv. Not sure how to get all the other term amount. any help would be appriciated. Thank you

 

[synapsevampire]

If it "Total_amount that gives me the total for each inv. along with a date of the inv", that doesn't show you the total paid, just the total invoice amount, or did you mean that it shows the payment amount?

What does " Not sure how to get all the other term amount." mean?

You need something showing the payments, and often times payments cover multiple invoices, etc.

Try posting example data and expected output rather than describing it.

-k

 

[itguymike]

i guess what i'm trying to show is what a client has past due. i'm trying to show what invoice amounts are 30 days, 60 days, 90 days and 120 days. A customer might have 2 inv. open at this time. one inv. might be over 30 days and another might be over 60 days.i'm trying to show in columns what amounts are 30, 60,90 and 120 days. make any more sense?

 

[synapsevampire]

Ahhh, OK, easy enough.

Create formulas, as in:

whileprintingrecords;
numbervar days30;
numbervar days60;
numbervar days90;
numbervar days120;
if currentdate - {table.date} <= 30 then
days30:=days30+{table.amount}
else
if currentdate - {table.date} <= 60 then
days60:=days60+{table.amount}
else
if currentdate - {table.date} <= 90 then
days90:=days90+{table.amount}
else
if currentdate - {table.date} > 90 then
days90:=days90+{table.amount}

Now call the variables at the end in differnt formulas for display purposes:

whileprintingrecords;
numbervar days30

whileprintingrecords;
numbervar days60

etc...

-k

 

[synapsevampire]

At the end should have said in the report footer.

-k

 

[itguymike]

lost me a bit but i'll work with what you gave me and go from there. thanks for all your help.
whileprintingrecords;
numbervar days30;
numbervar days60;
numbervar days90;
numbervar days120;
if currentdate - {RECEIVABLE.INVOICE_DATE} <= 30 then
days30:=days30+ {RECEIVABLE.INVOICE_DATE}
else
if currentdate - {RECEIVABLE.INVOICE_DATE} <= 60 then
days60:=days60+ {RECEIVABLE.INVOICE_DATE}
else
if currentdate - {RECEIVABLE.INVOICE_DATE} <= 90 then
days90:=days90+{RECEIVABLE.INVOICE_DATE}
else
if currentdate - {RECEIVABLE.INVOICE_DATE} > 120 then
days120:=days120+{RECEIVABLE.INVOICE_DATE}

i am getting and error, it wants a number where i put the X

days30:=Xdays30+{RECEIVABLE.INVOICE_DATE}

 

[synapsevampire]

Your lost because you changed the formula, I'm adding in the amounts, you're attempting to add dates... You want the amounts, right, why would you try to add the date fields?

-k

 

[itguymike]

i added the date field in because the field {table.date} does not exist and i wasn't sure what you meant by the following:

Now call the variables at the end in differnt formulas for display purposes:

whileprintingrecords;
numbervar days30

whileprintingrecords;
numbervar days60

etc...
Sorry, i taught myself how to write CR and don't always know what to do. i'll work it out. thank you for all the help. it's a start

 

[synapsevampire]

Create the initial formula and place it in the details, create the aditional 2 formulas and the other 2 required and place them in the report footer.

Another approach, which might suit you better is to insert 4 Running Totals, and in the evaluate->use a formula place the appropriate filtering, as in:

currentdate - {RECEIVABLE.INVOICE_DATE} <= 30

-k

 

[synapsevampire]

Should have stated that the subsequent formulas will need additional clauses, as in:

currentdate - {RECEIVABLE.INVOICE_DATE} > 30
and
currentdate - {RECEIVABLE.INVOICE_DATE} <= 60

This will fully qualify them, it wasn't required in the pure formula appraoch.

-k

 

[itguymike]

Thanks for all your help, this is beyond my writing. I think i'm also not explaining it correctly. Not sure what you wanted running totals on since i only have 1 field that has numbers in.(inv. total) i'll have to get a friend write it for me. I do appriciate all the help you gave me.

Thank you again.

 

[synapsevampire]

Right, that would be the field that you use for the Running Totals.

You're overcomplicating this, it's not very difficult.

A running total would use the number, and in the eveaulate use a formula use the date conditions.


You say that you added in the date field because {table.date} does not exist...you never mentioned the name of the fieold, so I couyldn't very well use it, could I?

And if you check my formula, you palceed the date fields where the amount field should be, not sure why you prefer to give up, this is relatively basic stuff.

-k

 

[itguymike]

basic for a programmer, not for someone just learning CR. I understand that i need to write 4 running totals, one each for 30,60,90,120 days. The problem is that i don't know what formula to add to the "evaluate" in the running totals or if this is even where i add the formulas.
Filed names are as follows:
{RECEIVABLE.INVOICE_DATE} date off invoice
{RECEIVABLE.TOTAL_AMOUNT} Invoice total
{RECEIVABLE.PAID_AMOUNT} what they paid for that invoice

Again i thank you for all your help. Try to remember that some things that come easy to some people don't always come that easy to others.