CALCFIELDS and uncommitted data

FlowerBiz

I'm experiencing a problem that I didn't think would occur. I'm using NAV 2009 SP1 with SQL 2008 and wrote a dataport that reads data and inserts it into the Sales Line table after inserting the Sales Header record. When I finish inserting my data, I run the following code:

SalesHeader.RESET;
SalesHeader.SETRANGE("Document Type",SalesHeader."Document Type"::Invoice);
SalesHeader.SETRANGE("Posting Date",BusinessDate);
SalesHeader.SETFILTER("No.","Store ID" + '*');
IF SalesHeader.FINDSET THEN REPEAT
  SalesHeader.CALCFIELDS(Amount);
  IF SalesHeader.Amount <> 0 THEN BEGIN
    // do some stuff to bring the invoice balance to zero
  END;
UNTIL SalesHeader.NEXT = 0;

The problem is that the SalesHeader.Amount is always zero within the dataport but can be non-zero after the dataport finishes. So I suspect that the CALCFIELDS is not able to work on uncommitted data. I was under the impression that I did not need a COMMIT to read data that I previously wrote in my own code. Is this not true?

I have not tested my theory by adding a COMMIT yet because I don't have an up-to-date test database prepared.

Thanks.

Savatage

I've always found the sales header amount to be zero on open orders. If you release the order the amount field is updated

matttrax

I often find that it is still zero even after release :? But if you then look at statistics the correct amount is shown.

Alex_Chow

You should use the Line Amount field instead.

Alex_Chow

matttrax wrote:

I often find that it is still zero even after release :?

Really? Under what circumstance do you see that? :-k

FlowerBiz

Alex Chow wrote:

You should use the Line Amount field instead.

Hmmm...is this a flowfield in the Sales Header table? I don't see it on my version.

Or are you recommending that I manually add up all of the detail lines (using Line Amount)?

jglathe

Hello FlowerBiz,

to calculate the document amount without releasing it I figured that you have to do what the statistics window does do calculate the amount.

For example, the following function calculates the amounts including VAT:

CalcSalesValues(VAR SalesHeader : Record "Sales Header";VAR AmountInclVAT : Decimal;VAR AmountInclVATLCY : Decimal)

TempSalesHeader:=SalesHeader;
TempSalesHeader.INSERT;
TempSalesHeader.SETRANGE("No.",TempSalesHeader."No.");
CLEAR(SalesLine);
CLEAR(TotalSalesLine);
CLEAR(TotalSalesLineLCY);
CLEAR(SalesPost);

SalesPost.GetSalesLines(TempSalesHeader,TempSalesLine,0);
CLEAR(SalesPost);
SalesPost.SumSalesLinesTemp(
  TempSalesHeader,TempSalesLine,0,TotalSalesLine,TotalSalesLineLCY,VATAmount,VATAmountText,ProfitLCY,ProfitPct,TotalAdjCostLCY);

IF TempSalesHeader."Document Type" = TempSalesHeader."Document Type"::"Credit Memo" THEN BEGIN
  TotalSalesLine.Amount := TotalSalesLine.Amount * (-1);
  TotalSalesLine."Line Amount" := TotalSalesLine."Line Amount" * (-1);
  TotalSalesLine."Amount Including VAT" := TotalSalesLine."Amount Including VAT" * (-1);
  TotalSalesLine."Inv. Discount Amount" := TotalSalesLine."Inv. Discount Amount" * (-1);
  TotalSalesLineLCY.Amount := TotalSalesLineLCY.Amount * (-1);
  TotalSalesLineLCY."Amount Including VAT" := TotalSalesLineLCY."Amount Including VAT" * (-1);
  VATAmount := VATAmount * (-1);
END;

IF TempSalesHeader."Prices Including VAT" THEN BEGIN
  TotalAmount2 := TotalSalesLine.Amount;
  TotalAmount1 := TotalAmount2 + VATAmount;
  TotalSalesLine."Line Amount" := TotalAmount1 + TotalSalesLine."Inv. Discount Amount";
END ELSE BEGIN
  TotalAmount1 := TotalSalesLine.Amount;
  TotalAmount2 := TotalSalesLine."Amount Including VAT";
END;

SalesLine.CalcVATAmountLines(1,TempSalesHeader,TempSalesLine,TempVATAmountLine);
TempVATAmountLine.MODIFYALL(Modified,FALSE);

AmountInclVAT:=TotalAmount2;
AmountInclVATLCY:=CurrExchRate.ExchangeAmtFCYToLCY(TempSalesHeader."Posting Date",TempSalesHeader."Currency Code",TotalAmount2,
  TempSalesHeader."Currency Factor");

TempSalesHeader.RESET;
TempSalesHeader.DELETEALL;

That's more or less what the statistics window is doing in OnOpenForm(). Works fine when you need to show the total amount of all documents in a list form. The problem you experience with the Sales Header.Amount field has nothing to do with uncommited data.

with best regards

Jens

Alex_Chow

FlowerBiz wrote:

Alex Chow wrote:

You should use the Line Amount field instead.

Hmmm...is this a flowfield in the Sales Header table? I don't see it on my version.

Or are you recommending that I manually add up all of the detail lines (using Line Amount)?

Sorry, no. I didn't see you were only grabbing the information from the Sales Header table.

DenSter

The Amount field in the Sales Header table is a flowfield that sums the Amount field of all related records in the Sales Line table. There is no additional filter, it only looks at Document Type and Document Number.

When an order is released, the system populates the Amount field on the lines, and when the order is re-opened, the system sets those fields back to 0, at least that is what is *supposed* to happen.

My guess is that orders that show 0 Amount on the header are simple not released, and the ones that do, are. If that's not the case, or if the totals do not add up correctly, then there is something wrong with your release process.

I also doubt that this has anything to do with (un)committed data.

FlowerBiz

WOW! You guys are awesome! I never bothered to look at the statistics form...I assumed that it was just showing me the flowfields from the Sales Header table.

I am not releasing the document so, naturally, the Amount field is going to be zero. What I want to look at in this case is "Outstanding Amount ($)", which gives me what I need.

Thanks again!