I have a sample invoice with radio buttons that display >30 days >60 days ...... etc. I having trouble figuring the calculation based off of the most recent date of the customers payment. This is how I see it in my head but I can't figure out the calculation correctly. This is not correctly formatted, just pseudo.

IF Balance < 0 and (payment_date >30 but <61 ) THEN past_due ">30 days"

IF Balance < 0 and (payment_date >60 but <91 ) THEN past_due ">60 days"

and so on........

Any help would be appreciated.

There are many ways to skin the cat...

Case(

Balance < 0 and payment_date < 31 ; "" ;

Balance < 0 and payment_date < 61 ; ">30 days" ;

Balance < 0 and payment_date < 91 ; ">60 days" ;

Balance < 0 and payment_date < 121 ; ">90 days" ;

">120 days"

)

Thanks that worked!

I tried the case statement but I used >. What I've learned is that case checks in order and once it finds a true statement it stops. At least that's what I believe happened when I was using > to evaluate instead of <.

It's beeb been bogging my brain but I'm still working on a way to get it to select the most recent date in the portal. It seems to be using the date from the first creating record.

You can set a sort order of the related table in the relationship graph.

Hope that help.

Or use the Max() function. The Last() function is also an option, provided records are created in chronological order and the relationship is not sorted in a contradicting order.

I've tried both suggestions but it still isn't correctly so I tried to add another variable to eliminate the date problem. I've added a value list with "Billed" and Paid. My plan is to add this to the case statement but when adding another "AND" statement it evaluate this statement.

[AND payment_status NOTEQUAL "Paid"]

Is it that you can only do two calculations in a CASE statement?

It's possible to turn the logic around a bit so that the calc does not display anything of the payment_status is paid.

Case(

payment_status = "paid" ; "" ;

Balance < 0 and payment_date < 31 ; "" ;

Balance < 0 and payment_date < 61 ; ">30 days" ;

Balance < 0 and payment_date < 91 ; ">60 days" ;

Balance < 0 and payment_date < 121 ; ">90 days" ;

">120 days"

)

Alternatively, I prefer looking at whether the field is empty or not; this makes the calc more robust if the text string is ever changed.

Case(

not isempty( payment_status ) ; "" ;

etc...