Radio Button Set HELP!

[GP inc.]

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.

[Vaughan]

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"

)

[GP inc.]

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 <.

[GP inc.]

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.

[grumbachr]

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

Hope that help.

[comment]

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.

[GP inc.]

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?

[Vaughan]

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...