Sales Invoice Report

imurphy · 2008-06-27

I'm in the middle of writing my first sales invoice report - a modified invoice - and am seeing something I can't explain.

I've taken the standard 206 report and copied it.

I've then added a line to the 'Sales Invoice Header - OnAfterGetRecord' trigger which calls a function.

Before it calls the function I take a look at the 'Sales Invoice Header' variable and can see that the Amount column contains 0. However if I open the Sales Invoice Headers table, the row in question has an amount of 42000. My db only has 3 rows at the moment so I'm not mistaking the row.

Whats going on?

DaveT · 2008-06-27

Hi Ian,

Amount is a flowfield so you will need to do a calcfields on it first

imurphy · 2008-06-27

Ok, there is a calculation trigger associated with the field.. but the record in the db contains a value!

I can understand having to recalculate the amount after some change, but its there.

If you always have to calculate it, why does the field exist at all?

imurphy · 2008-06-27

Just realised - stupid of me. Of course its calculating the field when I open table from the object viewer, its not really stored.

Ian

DaveT · 2008-06-27

Hi Ian,

Yes Nav calculates flowfields when it's displayed (on forms and reports). If it is not displayed you can use the calcfields command or propery on the dataitem / form.

imurphy · 2008-06-27

The amount field on the sales line has the following formula associated with it.

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

Is there any way of doing something similar from C/AL??

I've been browsing throught he help and nothing especially springs out as likely to do this.

garak · 2008-06-27

with the Nav functions calcfield and calcsums you can do this.

calcfield(Field,Field2,...,FieldX) you use to calculate a flowfield (before set the filter on fields which are used as Filterfields in flowfieldformula)

calcsums(Field,Field2,...,FieldX) you use to calculate a SumIndexField in a base table to a specific key. Like Quantity on table Item Ledger Entry

regards

imurphy · 2008-06-27

I should have explained myself better. I didn't mean how to do a calcfields, I meant how could I do a Sum on a range of rows.

Something like:
MyTotal := Sum("Sales Line".Amount WHERE (Document Type=SalesLine."Document Type"::Order,"Document No."='1234567'));

But the Sum function doesn't exist

At the moment I'm doing this by writing a loop to run through each record and adding up the sum. I'm thinking that if the function exists for use in the column definitions, then maybe it exists for use from C/AL

imurphy · 2008-06-27

or maybe I've misunderstood your response completely...

I've done little or nothing with filterfields and flowfields so far.

I'll have to do a little searching on the forum for these keywords and see what I can find.

Thanks

DaveT · 2008-06-27

Hi Ian,

The quick answer is to do

Salesline.setrange( "document type", SalesLine."Document Type"::Order );
Salesline.setrange( "document no.", '1234567');
if salesline.findfirst then
  repeat
    Mytotal += salesline.amount;
  until salesline.next = 0;

This will alway work whether the amount is calculated in a sumindex field or not. Sumindex fields are an efficent way to calculate values and can be seen in the key of a table. You will be guaranteed that a value used in a flowfield is a sumindex field (or the flowfield will give an error).

Thus in this example you can use this to your advantage. So (as Garak suggests)

Salesline.setrange( "document type", SalesLine."Document Type"::Order );
Salesline.setrange( "document no.", '1234567');
salesline.calcsums(amount);

will work.

One thing - you started the post with Sales Invoice Header and are now talking about Sales Line. Sales Line is linked to the Sales header table and the amount field on the sales line table is only calculated when an order, etc is released so be careful.

Hope this clears it up for you.

imurphy · 2008-07-01

Thanks dave, got it working - I've spent the last couple of days writing a report that indicates how much of an order has been billed/paid/is waiting to be billed....

Ian

Savatage · 2008-07-01

Using the customer ledger entries is the way I would go.

Remaining Amount is a very useful field

DaveT · 2008-07-01

Savatage wrote:

Using the customer ledger entries is the way I would go.

Remaining Amount is a very useful field

....Especially when you set the date filter flowfilter

Sales Invoice Report

Comments

Categories