Calcsum not working properly

nverma

Hello,

I am in a bit of a problem and I have spent few hours trying to figure it out, but I havent been able to get it to work.

Background Information

On a form, I have a subform (which contains the following fields:- Transaction Type (option :- receipt, cheque), Date (date), Description (text), Amount (decimal)). Also on the form, there is a field called 'Annual Allocation'. All, I had to do is look at all the Transaction type which are off type "Cheque" and add all of there 'Amount' fields. Once you have the totalsum of the Amounts field, compare it with 'Annual Allocation' field. If 'amount' is greater than 'annual allocation'. Throw an error.

IF Rec."Transaction Type" = Rec."Transaction Type"::Cheque THEN
BEGIN
  lrecJob.GET(Rec."Job No.");
  lrecConsumerDisbursement.SETCURRENTKEY("Transaction Type");
  lrecConsumerDisbursement.SETRANGE("Job No.", Rec."Job No.");
  lrecConsumerDisbursement.SETRANGE("Transaction Type", "Transaction Type"::Cheque);

  lrecConsumerDisbursement.CALCSUMS(Amount);
  TotalAmount := lrecConsumerDisbursement.Amount+ Rec.Amount;

  IF lrecJob."Annual Allocation" <  TotalAmount THEN
    ERROR(NotEnoughFunds);
END;

Some of you guyz might be wondering why I added: rec.Amount to the Total Amount variable. That is because it never use to remember the new value that the user typed. That was just me trying to force it to remmeber the current value (and forget the last one) in the field.

This works most of the time. But Its does not have 100% success rate.

Its not working for the following case:

Lets say I added bunch of records to my subform of transaction type cheque. It will work, untill the Amount field is more than Annual Allocation, at which point it will throw me an error saying: not sufficient funds. At which point user has to change the value in the amount field so the total sum is less than annual allocation. However, even when the user changes the value, it remember the value i had previously (before the error mesage) and the value he/she just changed too, which gives me an even higher total amount, which throws an error all over again.

How can i make it forget the value i had previously (before the error message), so it only considers the new value and calculates the totalAmount all over again.

Screenshot to give you a better understanding.
http://screencast.com/t/xtrQUutmFpu3

lvanvugt

Where did you place that code? Or in other words: what is triggering the code?

nverma

I made a function, called checkallocationamount. This function contains the code i pasted above.

this function is called in OnModify(), and Amount - (onvalidate) trigger.

lvanvugt

OK, and both lrecConsumerDisbursement and Rec in your code are variables based on the same table?

nverma

yup

Savatage

nverma wrote:

However, even when the user changes the value, it remember the value i had previously (before the error mesage) and the value he/she just changed too, which gives me an even higher total amount, which throws an error all over again.

What do you want to do CLEAR(Amount) After the error Message?

Is this similar to the Payment journal's Balance & Total Balance Fields at the bottom of the form?
Here's a post about totaling up journal lines:
viewtopic.php?f=14&t=7692

lvanvugt

nverma wrote:

Some of you guyz might be wondering why I added: rec.Amount to the Total Amount variable.

To be honest: no I wasn't really; instead I was wondering why you did write down this remark as it is obvious.

Your code works fine when you are creating a new Consumer Disbursement line. The line you are creating/entering does not exist in the table yet. So the following code is only taking into account the (cheque) lines that already existed.

  lrecConsumerDisbursement.SETCURRENTKEY("Transaction Type");
  lrecConsumerDisbursement.SETRANGE("Job No.", Rec."Job No.");
  lrecConsumerDisbursement.SETRANGE("Transaction Type", "Transaction Type"::Cheque);

  lrecConsumerDisbursement.CALCSUMS(Amount);

The Amount of Rec variable therefor needs to be added like you do in this line of code

  TotalAmount := lrecConsumerDisbursement.Amount+ Rec.Amount;

nverma wrote:

Its not working for the following case:

... if the Consumer Disbursement line already existed in the table but is being modified. In that case:

  lrecConsumerDisbursement.CALCSUMS(Amount);
  TotalAmount := lrecConsumerDisbursement.Amount+ Rec.Amount;

... will, with CALCSUMS, use the original Amount value of that line and, with Rec.Amount, the new Amount value.

BernardJ

Could you alter the validate trigger like this:

local lBlnInserted
BEGIN
  IF INSERT THEN
    Inserted := TRUE
  ELSE
    MODIFY;
  YourFunction;
  IF Inserted THEN
    DELETE;
END;

I've not tested this, but came up to something similar sometime. This way the new value will be stored to the database already when you call the flowfield, and you don't even need to add the Rec.Amount to the lRecConsumerDisbursement.Amount.

Please note that the insert and modify triggers are not explicitly called. They will be called anyway after the validate is finished.