Making an advanced calculation in a report

Lalaka · 2012-02-08

I have a report which is created by two data items. Sales Header and Sales Line.
This report runs perfectly sorted by Due Date. 8)

The clients requirement was to create a pending Sales order report. :?

However he wants the amount of sales orders subtracted from the respective blanket order, (First they create a blanket order for one or more items and out of that they click 'make to order' and publish new sales orders for one or more items.)

Ex:
You create a Blanket Order for Item I001 and specify the quantity as 100. Then you create a sales order S001 with 30 items of the specified item, and another Sales Order S002 with 25 items, of the same item.
When the report runs,
It should display the sales orders:
S001 30
S002 25
then the Blanket Orders:
BO0001 45 (100 - (30+25))
:oops:
Any idea how I can tackle this? :-k :-k
Thanks in advance! :roll:

mohana_cse06 · 2012-02-08

You want to show the Remaining Blanket Order qty in report or update in Blanket sales order itself?

You can find Blanket Order No. and Blanket Order line No. in sales Line Table and use it in your calculation..

Lalaka · 2012-02-08

mohana_cse06 wrote:

You want to show the Remaining Blanket Order qty in report or update in Blanket sales order itself?

You can find Blanket Order No. and Blanket Order line No. in sales Line Table and use it in your calculation..

Only in the report

?

How do I apply the filters you specified because there are instances where there are more than one Blanket Order Line No. in the Sales Line table. :shock:

Lalaka · 2012-02-08

mohana_cse06 wrote:

You want to show the Remaining Blanket Order qty in report or update in Blanket sales order itself?

You can find Blanket Order No. and Blanket Order line No. in sales Line Table and use it in your calculation..

Yes, Only in the report

!

How do I apply the filters you specified because there are instances where there are more than one Blanket Order Line No. in the Sales Line table. :shock:

mohana_cse06 · 2012-02-08

If you filter on both Blanket Order No. and Blanket Order Line No. then you will get correct blanket order and qty..

Please try and let us know if not worked with what you tried..

Lalaka · 2012-02-08

mohana_cse06 wrote:

If you filter on both Blanket Order No. and Blanket Order Line No. then you will get correct blanket order and qty..

Please try and let us know if not worked with what you tried..

GRecNewSalesLine.SETRANGE("Document Type",GRecNewSalesLine."Document Type"::Order);
GRecNewSalesLine.SETRANGE("Blanket Order No.",GRecNewSalesLine."Document No.");
GRecNewSalesLine.SETRANGE("Blanket Order Line No.",GRecNewSalesLine."Line No.");
GDecDisplayingQuantity:=0;
REPEAT
    BEGIN
        GDecDisplayingQuantity := GDecDisplayingQuantity + GRecNewSalesLine.Quantity;

    END;
UNTIL GRecNewSalesLine.NEXT = 0;

Quantity := GDecDisplayingQuantity;
MESSAGE('Quantity');

I used this code in the onAfterGetRecord event at the Sales Line data Item.
But cannot get the correct amount in the message box.

The way I see, the problem is in the way we are filtering, isn't it?

mohana_cse06 · 2012-02-08

Yes, You have to Select Blanket Order and Then set filter on Orders..

Code you written is also poor..

Lalaka · 2012-02-08

mohana_cse06 wrote:

Yes, You have to Select Blanket Order and Then set filter on Orders..

Sales Header gives only the Blanket Order No. Then I select it store it in a variable. and use it to filter the Sales Order to find the Sales Order with that blanket Order No in the line.

mohana_cse06 wrote:

Code you written is also poor..

:oops: :oops: Any suggestions?

Lalaka · 2012-02-09

Currently I have the data items aligned as follows.

Sales Header
|->Sales Line

What If I use a data structure like this?

Sales Header
|->Sales Line
|-&gt;Sales Line

Making an advanced calculation in a report

Comments

Categories