Calculate balance of Qty Ordered 1

[ngcn]

Dear all,

I need to calculate the Balance of a material ordered through PO. The formula is (Bal:=Qty Order minus Received Qty) for the first batch of Qty received, then (Bal:=Bal minus Received Qty) for the subsequence batch of Qty received. Please refer to example below.

Report :
----------------------------------------------------
PONo Material Qty Date Received Balance
Description Order Received Qty
----------------------------------------------------
1 Concrete 200 02/05 100 100
1 03/05 50 50
1 04/05 50 0

Thank for your help !

best regards,
ngcn

 

[dgillz]

Group by PO number and perform a minimum summary on Balance. No formulas needed. Software Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[ngcn]

dgillz,

Could u pls elaborate "minimum summary on Balance" ?


regards,
ngcn

 

[dgillz]

From the design window, right click on the balance field in the details section. Select Insert, summary, make it a minimum summary and summarize by PO number. Software Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Ngolem]

sorry Dgillz...I don't follow you either.

ok...Group on the PO Number.

in the group header place an initialization formula

@initialization (PoNo group header suppressed)
WhilePrintingRecords;
numbervar OrderQty := {Table.OrderQty};
numberVar ReceivedQty := 0;


now in the detail section

@Balance (not suppressed in the detail section)

WhilePrintingRecords;
numbervar OrderQty ;
numberVar ReceivedQty ;
numberVar Balance;

ReceivedQty := ReceivedQty + {table.receivedQty};
Balabce := OrderQty - ReceivedQty ;

this should work fine.


Jim Broadbent

 

[dgillz]

I am talking about a simple insert, summary, minimum. The simplest summary there is, no formulas involved.

Since he already has a balance field in the details section, there is no need to use variables, just grab the minimum of these values by PO#. If you chose to express this as a formula, the formula would be:

Minimum({Balance},{PoNumber})
Software Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[synapsevampire]

dgillz: I think that they do not have the balance in the data source, it's being calculated.

They have the total ordered and received thus far quantities, hence Ngolem's suggestion.

A Running Total to accumulate the receive qty, subtracted from Total Ordered qty to demonstrate Balance might be another way to accomplish this, but Ngolemms' solution seems right.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

My fault, I misread his report that he wants to see as his actual raw data.

That being the case either Ngolem's formulas or a running total should work. Software Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[ngcn]

Hi guys,

When I open up my thread this morning, I was shock & glad to see so many replies, thanks for ur replies.

synapsevampire is right, Balance is not a field, it is a calculation field. I will tried out Ngolem's formula, if it works, I will tell u guys.

Thanks!

best regards,
ngcn

 

[ngcn]

ngolem,

Ur formula is a success! But I put the @Initialization in Material Code Group section, cause there are more than one material code in a PO.

Thank u very much !

best regards,
ngcn