#### Howdy, Stranger!

It looks like you're new here. Sign in or register to get started.

Options

# Making an advanced calculation in a report

Member Posts: 32
edited 2012-02-09
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
Have a Nice Day!

• Options
Member Posts: 5,504
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..
• Options
Member Posts: 32
edited 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..

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:
Have a Nice Day!
• Options
Member Posts: 32
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:
Have a Nice Day!
• Options
Member Posts: 5,504
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..
• Options
Member Posts: 32
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?
Have a Nice Day!
• Options
Member Posts: 5,504
Yes, You have to Select Blanket Order and Then set filter on Orders..

Code you written is also poor..
• Options
Member Posts: 32
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.
Code you written is also poor..
:oops: :oops: Any suggestions?
Have a Nice Day!
• Options
Member Posts: 32
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
```
Have a Nice Day!