Changing a "Sum" flowfield into code

amadman114

Hello,

I'm trying to write a part of codeunit that performs what this flowfield calcformula does:

Sum("Sales Invoice Line".Amount WHERE (Document No.=FIELD(No.)))

"Why can't you just put that in table?" I'm sure you're thinking... I've been tasked with optimising the system and I've thought of a pretty solid way of doing it (but I don't want to/need to write an essay explaining it =P~ )



I guess I could do this in about 20 lines of code, where I set multiple 'temporary' variables in a loop, but I'm sure there's a much more streamlined way of doing this. I'm trying to learn C/AL so I want to learn best practice where I can.

geordie

Sorry but I didn't understand properly the "temporary variables" part of your post, anyway the total amount calculation can be achieved in this way:

CLEAR(InvAmount);
SalesInvLine.RESET;
SalesInvLine.SETRANGE("Document No.",SalesInvHeader."No.");
IF SalesInvLine.FINDSET THEN
  REPEAT
    InvAmount += SalesInvLine.Amount;
  UNTIL SalesInvLine.NEXT = 0;

amadman114

Don't worry about the temporary variable thing, and thanks for your code.

I don't really understand it, however. When I try and implement it, it seems to not do anything (though no error is returned).

I don't understand what is happening in the loop. Could you (or another reader) inform me how this works?

*** Edit ***
I think there may be a misunderstanding of what I would like:

I would like this codeunit to go through and write a value to the "Amount" field of the Sales Invoice Header in every line. This value is calculated by adding up the relevant Sales Invoice Lines records.

Miklos_Hollender

This is really the absolute basics, so if you have trouble understanding Geordie's example you either need to go to a training course or at least look at this: http://mibuso.com/dlinfo.asp?FileID=744

amadman114

Thanks for that resource, I'll definitely take a look at that

Any chance of a solution for the short- term problem though?

geordie

amadman114 wrote:

Any chance of a solution for the short- term problem though?

Something like this?

SalesInvHeader.CALCFIELDS(Amount);
SalesInvLine.RESET;
SalesInvLine.SETRANGE("Document No.",SalesInvHeader."No.");
IF SalesInvLine.FINDSET(TRUE,FALSE) THEN
  REPEAT
    SalesInvLine."Total Amount" := SalesInvHeader.Amount;  //New Sales Invoice Line field to create
    SalesInvLine.MODIFY;
  UNTIL SalesInvLine.NEXT = 0;

But to be honest it doesn't make much sense (moreover because should be more practical using a new FlowField on Sales Invoice Line table)...

amadman114

Hi Geordie, I tried what you suggested but it wouldn't work.
Inspired by the first post, I wrote my own code, as follows

GrecSalesInvLine.RESET;
GrecSalesInvHeader.RESET;
GrecSalesInvHeader.FINDFIRST;


REPEAT

CLEAR(InvAmount);
GrecSalesInvLine.SETRANGE("Document No.",GrecSalesInvHeader."No.");

REPEAT

InvAmount := InvAmount + GrecSalesInvLine.Amount;

UNTIL GrecSalesInvLine.NEXT = 0;

GrecSalesInvHeader.Amount := InvAmount;
GrecSalesInvHeader.NEXT;
UNTIL GrecSalesInvHeader.NEXT = 0;

This works