Calulation Output

[amypaulsmom]

I have a field that is supposed to auto calculate a finance charge.

But - I am having diffuliculties with the order that I need to write the calculation in.

I want the finance charge to be calulated if Today's Date > DateDue +30 or if a field (FCMarkOff) is not checked.

Here was my thought:

If (TodaysDate>DateDue+30 and FCMarkOff = "=",AmountDue*.015,0)

I get a little confused on the logical operators (or, and, xor). Also - I am not sure if this is the correct way to do the entry for a checkbox in a formula.

I have the calc entered this way in my current database and the Finance Charge field is not being calcualted when it should.

Thanks for any help.

[Kurt Knippel]

quote:

---

Originally posted by amysmom2000:

If (TodaysDate > DateDue+30 and FCMarkOff = "=", AmountDue * .015,0)

---

Looks correct to me. Make sure that you set this as UNSTORED in the Indexing. This is most likely why it is not calculating as you expect.

AND means just that "This AND That". OR means "This OR That or both". XOR (Exclusive OR) means "This or That but not both".

[andygaunt]

quote:

---

I want the finance charge to be calulated if Today's Date > DateDue +30 or if a field (FCMarkOff) is not checked.

Here was my thought:

If (TodaysDate>DateDue+30 and FCMarkOff = "=",AmountDue*.015,0)

---

OK, so today > dateDue+30 or FCMarkoff is empty.

Try this instead.

If (Status(CurrentDate) > DateDue + 30 and IsEmpty(FCMarkOff),AmountDue*.015,0)

Set the calc to unstored so the current date updates every day.

[amypaulsmom]

The above calculation works pretty well.

I realized thought - that I need to focus on the datepaid field and not the FCmarkoff field.

I need the calc to add the finance charge if todaysdate>datedue+30, but then if they have entered a datepaid and it is less then the datedue+30 the finance charge should be 0.

so - should this work:

If(todaysdate>datedue+30 or datepaid > datedue+30,amountdue*.015,0)

I think this is what I had to begin with, the problem was - once today's date went past the datedue+30 date it added a finance charge even though the datepaid was entered.

That's why I was wondering about the significance of the AND,OR, and XOR operators.

Thanks for the advice.

[amypaulsmom]

Back to this - thought I had it working - but it is giving me charges when they shouldn't be there.

Here is my initial calculation -

If(DatePaid>DateDue+30 or TodayDate>DateDue+30,AmountDue*.015,0)

This calculation doesn't work because - anything past 30 days overdue gets a finance charge. Even if they have paid - that's not good.

So - I tried -

If(DatePaid>DateDue+30 and IsEmpty(DatePaid),AmountDue*.015,0)

But this only seems to catch the ones that have something inputted in the DatePaid field.

What I need is for the Finance Charge to be added to the account if the Date Paid is 30 days past the Date due or if the DatePaid field is empty and the Due Date is 30 days past due.

Does this make sense to someone out there?

Thanks

[Kurt Knippel]

I think that your problem is in relaying on the DatePaid field too much, since if they have paid then they cannot be past due anymore.

Try:

If( Status( CurrentDate ) > DateDue + 30 and IsEmpty( DatePaid ), AmountDue * 0.015, 0 )

[amypaulsmom]

Thank you -

That worked liked a charm!!!!

[amypaulsmom]

AHHHH -

It worked like a charm - except that . . .

when the user enters a date in the date paid field - the finance charge now goes to 0.00 - no matter what the date paid field is - meaning - if it's way past the current due date - and the user enters a date paid - then the finance charge still goes to zero.

The finance charge should stay at it's calculated value, if the date paid is greater then the date due +30.

Here is my current calculation:

If(Status(CurrentDate)>DateDue+30 and IsEmpty(DatePaid), AmountDue*.015,0)

I hope that makes sense and you can all help me out one more time.

Thanks

Okay - I have been looking at my calculation - what I need is an additional criteria in my if. If Date Paid is full but is greater then DateDue + 30. How can I add a third criteria to my if statment? Can I do a nested IF?

Thanks again for all your help

[amypaulsmom]

No ideas from anyone?? You guys are usually so quick with a great helpful idea.

[BobWeaver]

By any chance do you have a line items file for customer charges? If so, why not also enter customer payments as related records. Then at the end of the month, you can run a script that calculates the balances for all customers (total of charges minus total of payments). Any customer that has an outstanding balance (not including current month charges) will have a finance charge item (line item record) added to their account. This way it is added into the balance, and interest will automatically compound every month. It also allows for the situation where a customer makes a partial payment.

Setting it up this way makes accounting simple, because all charges (including finance charges) and payments are individual records which you can itemize neatly on customer statements.

Hope this doesn't just muddy the waters.

[amypaulsmom]

Hi -

Bob - I wanted to thank you for your post. I do have a Customer Charges db for this Invoice database. But - this part deals with Payment Plans and runs a little differently with varying due dates.

But - you gave me some good thoughts and ideas. I am pulling from them and think i am heading in a positive direction.

Thank you - I will try to remember and write back if this works.