finding exact amount

[Ugo DI LUCA]

I often look to the amount due in my invoice file to search for late payments, but also to match received cheques.

But, in Search Mode, as the amount due is a calculation of the total invoice - total accounts, FM takes a long time to recalculate the lines before showing the good results.

And it is quite impossible to find it because my client cheque is of 1.250,50 $ while the amount due (because of taxes) is of 1.250,5002 $.

So I enter 1.249,99...1.250,51 to find out, with same delay.

What should be the solution for this problem ?

[andygaunt]

So, are you looking for an amount due > 0

If so, can you create a field that is

If (Amount Due > 0, 1, "")

Then you can use this to find on, or use the constant relationship to this field to go to related records. A hell of a lot faster than finding

DTH ?

[Ugo DI LUCA]

No, I do not want to see amount due >0.

I have an accountant that sometimes tells me. Well Ugo, who made a check of 10,550,00 $ in April 2002 ?

Now, assuming I do not have (for the moment) any Receivable File to register the different entries and dates, I need to find in the Invoice file for this amount.

If this record is an account, it would be easy as this is a classic number field. But if it is the amount due, as this is a calc, I must set the search for 10,549,99...10,550,01.

And it is so long...

[jasonwood]

who made a check of 10,550,00 $ in April 2002 ?

If you're looking for a payment, why are you searching balance due? why not just search the payment field (wouldn't that be a normal field in the invoices database). If it is regular for there to be multiple payments for one invoice, you should probably have a separate payments database.

Msg Edited: I just reread your post a few times and I think I understand now. Sorry I don't have a solution.

Andy, you answered a question I was probably about to ask, because I recently encountered this problem. I was thinking about having a field as you suggest, but I wonder what would cause it to update? What if I apply a payment to an invoice without actually going to that record (eg: I apply it through a script on another database)... will the calculation take the change into effect?

I'm still a bit sketchy on storage/indexing of calculation fields.

[Razumovsky]

Let me take a stab at it.

g_CheckAmount

c_CheckRelate Case(c_BalDue > (g_CheckAmount - 2) and c_BalDue < (g_CheckAmount + 2),1)

c_One (universal calc field that always returns the # 1)

if you type 10,550,00 into g_CheckAmount, and then script a go to related records (show) based on relationship c_One::c_CheckRelate, you will pull up all balance dues from 10,549,98 to 10,550,02, and fast.

problem is that you most likely calculate BalDue based on a related line items file. in this case you need to script a replace or relookup to insert the value of c_checkrelate into number field n_checkrelate first.

Might be of help though. I feel your pain regarding the search time.

[BobWeaver]

It seems to me that the problem is that when you add in the tax, you should be rounding the amount to the smallest unit of your currency. Then, your amount due would match the actual cheque amount.

Example:

AmountDue = Round(SaleTotal * (1+ TaxRate), 2)

[Ugo DI LUCA]

Oh Bob, you got it !

I turned round and round with that irritating feature. Round !!

For sure...