Amount Formatting in Document Tag

GertV

I cannot figure out how to format an amount that I need to print in my report.

My tag is:

SUM Invoices.Total WHERE (Invoices.Status='Send' AND Invoices.InvoiceForAgent=ThisInvoices.InvoiceForAgent AND DAY_DIFFERENCE(CURRENT_DATE,Invoices.DueDate)<30)-SUM Invoices.PaymentTotalAmount WHERE (Invoices.Status='Send' AND Invoices.InvoiceForAgent=ThisInvoices.InvoiceForAgent AND DAY_DIFFERENCE(CURRENT_DATE,Invoices.DueDate)<30),'#,###,#0.00'

This the editor doesn't accept. It works without the formatting though. I've tried to put the format in various different places in the expression but all without success. Any idea how to do this?

vinsnash

Looks to me like you need a couple of extra parenthesis.

SUM (Invoices .......) - SUM(Invoices .....)

You need to think algebraically. i.e. (a - (ab)) - (c - (de)) The quantity a - a times b minus the quantity c - d times e.

Vins Nash

GertV

Hi Vins,

Thanks for your suggestion. Unfortunately it doesn't work. If I put the parenthesis where you suggested I get a syntax error.

Besides, the formula works fine, it is just the formatting that I cannot get in.

aware_support

Yes, unfortunately, complex expressions that use "," inside them, cannot use formatting expression after. This is a bug. The only workaround is to store the result of the expression in an attribute and then display the value of this attribute with formatting

GertV

Support,

Thanks for the clarification. It's difficult to store this in an attribute as the selection is determinded by the end-user. However I can live with it for a while. Are you planning to fix this in an upcoming release?

aware_support

We hop so - it's in the TODO list

rocketman

Have you worked with Non persistent BO's yet, you could try
FIND [whatever records you want to find]

Create [non persisten BO] FOR EACH [found BO] with [Non Persistent BO.some_attribute] = SUM Invoices.Total WHERE (Invoices.Status='Send' AND Invoices.InvoiceForAgent=ThisInvoices.InvoiceForAgent AND DAY_DIFFERENCE(CURRENT_DATE,Invoices.DueDate)<30)-SUM Invoices.PaymentTotalAmount WHERE (Invoices.Status='Send' AND Invoices.InvoiceForAgent=ThisInvoices.InvoiceForAgent AND DAY_DIFFERENCE(CURRENT_DATE,Invoices.DueDate)<30)

You could then have the formatting on the non persisten BO attribute.

Using the FOR EACH statement you can create as many non persistent BO's as you like - as long as you don't move out of the process that creates them - they will be available for as long as you need them. Then you can report on the Non PBO rather than the actual BO