Calculation

[trevors]

I have a calculation I can not work out. I am designing a expenses and income database for a small non profit organisation.

I have

- Due Date: this is the date the invoice is due for payment.

- Date Paid: Date the invoice is paid.

- Amount Paid: Amount paid on the invoice.

- Paid: This field shows "PAID" once the Date paid field is Valid, else "NOT PAID"

- Status - Calculation that determines if the invoice for payment is "Over Due", "Due Today" or "Not Due" (I had help form this forum with this cal .... many thanks :-) ):

Case(Due Date<Status(CurrentDate), "Overdue", Due Date = Status(CurrentDate), "Due Today", Due Date>Status(CurrentDate), "Not Due")

Here's the catch: I would like the status field to be blank (or maybe something else such as "PAID" displayed) if the "Date Paid" field is Valid. In other words if the invoice is paid you no longer need the "Over Due", "Due Today" or "Not Due" in the Status field.

I hope I have explained this clear enough. I just need an idea where to go, as I would still like to work most of it out so I can better understand. Do I use the current Case statement in Status, or something else?

Many thanks

Trevors

[cjaeger]

Case(Paid="PAID","PAID",Due Date<Status(CurrentDate), "Overdue", Due Date = Status(CurrentDate), "Due Today", Due Date>Status(CurrentDate), "Not Due")

or

If(Paid="PAID","",

Case(Due Date<Status(CurrentDate), "Overdue", Due Date = Status(CurrentDate), "Due Today", Due Date>Status(CurrentDate), "Not Due")) if you like grouping more.

[trevors]

Hi Christian .....

I tried the Case one before I posted, but it did not work .... The status displays whatever the date is, e.g Over Due, Due Today, Not Due etc.

Just tried the IF statement and the same ..... Once it chooses the status display (Date Due etc) it does not update when paid.

Trevor

[cjaeger]

Is the calc unstored?

[trevors]

Yes .......

[trevors]

Hi Christian .....

I seem to be defining the problem. The Cal you gave does work but not with the PAID field. The PAID field is a cal from the DATE PAID field:

If( IsEmpty(Date Payed),"NOT PAID", "PAID")

I creted a TEST field and it your cal works fine, but it does not work with the PAID field, could it have something to do with If( IsEmpty(Date Payed),"NOT PAID", "PAID") :??

many thanks

Trevors

[mdpres]

Trevors,

Hope this addresses your problem.

You could do a lot more with the status calc if you want.

Regards,

Dean

[cjaeger]

Trevors, check the result of your Paid field calc, it's probably set to number instead of text.

[trevors]

Hi Dean ....

Not sure what you mean here .... the attachment contained 3 text fields only? Also what did you mean by "You could do a lot more with the status calc if you want", please tell :-).

Thank for your help

Trevor

[trevors]

Hi Christian ....

I have it set to text ..... Mmmmmmm ...... I have done something wrong.

Trevor

[cjaeger]

so does it work now?

[Ugo DI LUCA]

Trevor,

There is one last question, that may be we should have asked at first....

Are all your fields coming from the same file, or are there some relationships involved here that you didn't tell us about ?

[trevors]

Yes .... many thanks Christian

[trevors]

Hi ...

All coming from the same file .... all working now thanks to Christian .... many thanks.

Trevors

[Ugo DI LUCA]

Fine. It wasn't clear if you had solved it, and I couldn't see why....