update field

nverma · 2012-04-10

I am not sure how to do this. If you look at the attached picture. There is a field called "Remaining Balance". I want this field to be updated whenever the amount field in the subform is changed.

Remaning Balance will be calculated by (sum of all the cheques - sum of all the receipts).

IF "Consumer Disbursement"."Transaction Type" = "Consumer Disbursement"."Transaction Type"::Receipt THEN
  BEGIN
    TotalAmountReceipt := "Consumer Disbursement".Amount + TotalAmountReceipt;
  END
ELSE
IF "Consumer Disbursement"."Transaction Type" = "Consumer Disbursement"."Transaction Type"::Cheque THEN
  BEGIN
    TotalAmountCheque := "Consumer Disbursement".Amount + TotalAmountCheque;
  END;

"Remaining Balance" := TotalAmountCheque + TotalAmountReceipt;

I am just not sure what to do next or in which trigger this code should be put in.

Savatage · 2012-04-10

nverma wrote:

I am not sure how to do this.

:shock: <-

Looks like you would like to sum up some lines.
Here's how we did it in 2005.
viewtopic.php?f=14&t=7692

Did you look at the Deposits. I'm sure it acts just as you wish & you can see how that was accomplished.

There also appears to be no need to distinguish between the types. They are already either (+/-) so just totaling the amount field & subtracting the header value should do the trick.

nverma · 2012-04-10

I created the function in form called: UpdateBalance

"Consumer Disbursement".COPY(REC);
"Consumer Disbursement".CALCSUMS(Amount);
Remaining := "Consumer Disbursement".Amount;

It compiles perfectly, but when i try to run the form it gives me the error: Both target and source must refer to the same table.

Any idea what I might be doing wrong..

The error seems to be caused by this line:

"Consumer Disbursement".COPY(REC);

Savatage · 2012-04-10

is consumer dispersment refering to the Lines or the header, it appears the header by your error?
You have to figure out which table is holding the "lines".

you need to call the function from the amount field.

Forget about the header for now & subtracting the "Annual allocation"

Just try to total the fields and see how that goes.

I know in my deposits - I enter a total amount received and I can allocated that full amount to different customers and it subtracts from the Deposit total to show you how much is left. It's so easy to review how it was already done somewhere else in nav and follw that as an example.

Savatage · 2012-04-10

Try this explanation:
viewtopic.php?f=23&t=26411

this is based on the General Journal Line table - just substitute you line table.

nverma · 2012-04-10

Consumer Disbusrement table is the subform table (lines).

I am not sure why its still throwing the error. Also, would I have to call this function from the OnAfterGetCurrRecord trigger ????

Savatage · 2012-04-10

see above 2 posts

& http://msdn.microsoft.com/en-us/library/dd301396.aspx

nverma · 2012-04-11

Update:- I found a workaround that error message.

The only Issue that I am having is that whenever I add a new line to my subform, I have to click on the ES Card (header) for it to update the Remaining Balance. Is there a way I can have it update itself, without having to click anywhere else.

Savatage · 2012-04-11

nverma wrote:

"Consumer Disbursement".COPY(REC);

Just as a point you don't seem to follow examples.
In the example a new Global Record was used "GenJnlLine" notice it was not "Gen. Journal Line".
you've been given all you need to complete the task. Have you even taken a look at how deposits work?
just sayin

nverma · 2012-04-11

I got it to work, now it calculates the remaining balance. But I want it to update as soon the user changes the values or add a new line in the subform. Right now, I have to click on the header for it to update the values.

What can I do, so that it would update the remaining balance right away.

David_Singleton · 2012-04-11

nverma wrote:

What can I do, so that it would update the remaining balance right away.

Simple.

Delete everything you have done so far then go back and read the help that was provided to you. If you don't follow the help that people give you have can you expect to benefit form that help.

nverma · 2012-04-11

Savatage wrote:
click on a sales line->Tools-Designer
Select the table box and drag it up to create just enuf space at the bottom or top to fit a textbox.

View->Globals
-Add a function called CalcRunningTotal (or something)
-Add a Variable: recSalesLine-Record-Sales Line

Add this code to the function
CalcRunningTotal()
recSalesLine.RESET;
recSalesLine.SETCURRENTKEY("Document Type", "Document No.", "Line No.");
recSalesLine.SETRANGE("Document Type", "Document Type");
recSalesLine.SETRANGE("Document No.","Document No.");
recSalesLine.CALCSUMS("Line Amount");
Now add a textbox to the space created when moving up the Table Box.
Add to it's sourceExp: recSalesLine."Line Amount"
Make sure the property VertGlue is Bottom

It should look like this:
http://savatage99.googlepages.com/Textb ... bleBox.JPG

finally add
OnAfterGetCurrRecord()
CalcRunningTotal;

I followed the way you suggested, with minor changes. Now it calculates the remaning balance, but I have to go the next line before it updates the field. Is there any other trigger that I need to call the function from so that it would update it right away (as soon as i press enter). So far the function is being called from OnAfterGetCurrRecord trigger.

BernardJ · 2012-04-11

That will be hard, since the record is not actually written to the database as long as the user is editing it. That means you are not abled to get the new data in other record variables at the same time.
If this is a serious requirement, you can try to extend the amount validate with a MODIFY statement, as described in viewtopic.php?f=23&t=52091

update field

Answers

Categories