cancel
Showing results for 
Search instead for 
Did you mean: 

Stock Running Total

Former Member
0 Kudos

Hi,

Hoping someone can help me....I have an order report which shows all our open orders specific to a customer and date range specified in a cascading parameter. The report is grouped on Part number.

What I want is to be able to see the available stock which is a running total from our current stock and the running total is based on the open qty on each line. To achieve this I created a formula field that is current.stock-open.qty. I then created a running total which is based on the formula I just wrote and that resets on the group.

This appears to be working perfectly when there is 0 stock but when there is stock, the figures produced don't add up - though they are close. See below:

As you can see, the first 3 lines show my issue (within the red box). The totals for these lines should read -117 (so first line is ok), -130 & -131.

Any help you can give would be greatly appreciated.

Thanks, Emily

Accepted Solutions (0)

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Emily,

You'd need a manual running total.

Something like this:

1) Create a formula with this code and place this on the Details Section:

shared numbervar avl;

shared numbervar x := x + 1;

if x = 1 then

     avl := {Current Stock} - {Open Qty Line}

Else

     avl := avl + {Open Qty Line};

2) Create a formula with this code and place this on the Group Header:

shared numbervar avl := 0;

shared numbervar x := 0;

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

Thanks for responding. This looks almost perfect - it has certainly sorted out the first set of records. It seems however to be getting mixed up - I think when the stock is 0 and there are multiple lines - please see below:

Any ideas?

Emily

abhilash_kumar
Active Contributor
0 Kudos

Hi Emily,

And, what should happen when the stock is zero and there are multiple lines?

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

Actually sorry looking at it again, the first of records are not right either as they are showing  -117,-104, -103 - when they should be saying -117, -130, -131. Likewise with my highlighted example above it is showing -70 which is correct but then on the next line down it is showing 23 as it has obviously added the open qty of 93 - what I want it to be showing is -163 (I want to see how much stock I would need to make to achieve the open  quantities).

Thanks again,

Emily

abhilash_kumar
Active Contributor
0 Kudos

Try changing formula 1 to:

shared numbervar avl;

shared numbervar x := x + 1;

if x = 1 then

     avl := {Current Stock} - {Open Qty Line}

Else

     avl := avl - {Open Qty Line};


-Abhilash

Former Member
0 Kudos

Hi Abhilash,

Thanks that's perfect! Would you mind explaining what you have written so that I can try and understand for if I need something like this in future?

Thanks again, Emily.

abhilash_kumar
Active Contributor
0 Kudos

Glad it works!

The code subtracts Current Stock from the Open Qty for the first line of each group.

For all the other lines in the group, it subtracts the value carried forward (from the first line's calcuation) from the Open Qty.

-Abhilash