Locking records

[madwill]

My homegrown (homegroan?) invoicing solution includes a calculation that simultaneously applies a discount to the invoices of clients who pay in X days and a surcharge to those of clients who fail to pay in under Y days. This has worked well until today, when one pesky client has slipped over the threshold and will now incur a surcharge on his invoices. However, I realise that the surcharge has been applied to all the client's past invoices, whether paid or pending, and this is definitely not OK.

How can I lock the records to prevent the calculation from modifying these invoices?

I appreciate any help, though I'm reluctant to send the entire db for perusal.

Many thanks,

Madwill

[Inky Phil]

Hi madwill

You can use the case function within your calc field to do what you want.

It goes along these line

Case(Condition1;calc1;condition2;calc2;default result for all other conditions).

You can have as many conditions as you like. The resultant calculation will be the first one where a condition is met or the default one

Yours in Pseudo code would be:

Case(paid in x days;discount;paid in y days;surcharge;already paid or pending;"")

This is 3 conditions with no default.

HTH

Phil

[madwill]

Thanks very much, Phil. I'll try this straight away. However, it isn't a "record lock" solution. Is that because there is no way, or because it's trickier to implement, or what?

Many thanks,

Madwill

[madwill]

Hi, Phil,

I think this has solved the problem. In fact, the calc was already a Case function, but I've now modified it to (hopefully) only apply discount/surcharge on new invoices. It now looks like this (my FMP is half in Spanish):

Case(

IsEmpty(Timing) or

Timing < "1"; "";

Timing >= 130 and Fecha >= Today; Sum(ImporteEuros) * 0,05;

Timing <= 45 and Fecha >= Today; -Sum(ImporteEuros) * 0,08;

"" )

with Timing being the average number of days client takes to pay, Fecha being Date and ImporteEuros being net cost.

I may have to get back to you should this fail, but it seems to work for now. Thanks again.

Madwill

[David Jondreau]

Sounds like the calculation is in the Client record, not in the Invoice record where it should be.

With this new calculation it looks like you're using the average time to pay, but really shouldn't you be assessing a surcharge per invoice? If a client pays one invoice within 30 days but another in 360 days, you are assessing a fee on both invoices, when really you want to be assessing a fee on just the second.

[madwill]

Hi, David,

Thanks for your reply. In fact the above solution has not worked, maybe because, as you say, the calc is in the Job (not client) file, whereas it should be in the invoice file. I'll experiment a little but I don't want to screw all 2000 invoices, so I'll have to go easy. Either way, I certainly don't want to have to reconsider the general premise for applying discounts and surcharges, and doing so on individual invoices is not an option. Clients in Spain are very slow to pay in general and it is in my interests to pre-apply a discount to those who regularly pay quickly, just as the surcharge penalises those who regularly take over 130 days to pay. I need to set the calc so that when a client whose average was 125 days slips over into surcharge zone, the surcharge is applied only to invoices issued from that point on.

Maybe this explanation should have been given in the first place. If so, I apologise for not being altogether clear. Hope you or someone else can help.

Many thanks

Madwill

[David Jondreau]

I think you need to firmly establish the criteria for adding a surcharge to invoices in order to set up a good calculation (and communicate this criteria to your clients).

From a business perspective, I think it's best to charge based on how long an individual invoice has taken to pay. But I hear that's not how you want to do it. But average time to pay isn't very efficient.

If a client has 9 invoices average 130 days overdue and generates a new one, now he has 10 invoices, average 117 days. So, every new invoice skews the average dramatically.

What about if an account has more than 5 invoices which are 130 past due or some such?

[madwill]

I can concede that average time to pay is not efficient, but I cannot see beyond the impracticalities of applying a discount or surcharge to a particular invoice depending on when it is paid (I have to send out a discount/surcharge notice? All clients pay either by banker's draft or cheque by post - I cannot alter either. I would need to re-charge or refund - altogether too complicated). Tigers do not tend to change their stripes. And slow-paying clients are not going to be swayed by the lure of a 5% discount.

BTW, a client averaging 130 days over 9 paid invoices can generate any number of new invoices without skewing the average. Only paid invoices enter the equation. Number of invoices would not be too useful to me. The calc could include a surcharge on invoices over a certain sum (say $1000) 130 past due; that would seem far more logical to me.

Thanks for your comments,

Madwill

[David Jondreau]

I didn't realze you were using the average length of Paid invoices.

Your original question was about "locking" a record. There's two ways of doing this that I see.

1) Make the total field a number field, not a calculation field. Write a script that will set that field to the total due. When the invoice has all its line items, click on that button.

2) Keep it a calc field, but embed the calculation in a Case statement that checks to see if the invoice has been sent.

Total = Case(isEmpty(InvoiceSent); yourCalc; Total)

[madwill]

Sounds excellent, David. I shall initially go with solution 2) as I can grasp the logic better and already know and use the technique.

Many thanks for your help.

Madwill