Finding an elements position in Array 1

[simpelli]

I'm creating a report that will display potential product shortages as the product appears on orders.
I have a sub report that calculates the item's available quantity (based on several conditions - inventory, damaged, ordered, on hold, etc) Because the SKUs do not appear in the report sequentially, I need to maintain
a running count of available quantity per SKU and calculate against ordered quantity.
(Ref SKU "B" below)

Order# SKU Available Qty Ordered Bal
001 A 0 375 -375
001 B 775 25 75
001 C 100 193 - 93
001 D 0 5 - 5
002 B 750 75 - 25
002 F 200 200 0

I attempted to create two "synchronized" shared arrays in the subreport - one to hold the SKU and the other to hold the quantity. (Below)


evaluateafter({@Available});
stringvar array SKU_Array;
numbervar array Qty_Array;
shared numbervar NewQty;
numbervar max;
numbervar sCounter;

if not({tbl.sku} in SKU_Array )then
(sCounter := sCounter +1;
(Redim Preserve SKU_Array[sCounter];
(Redim Preserve Qty_Array[sCounter];
(SKU_Array[sCounter] := {tbl.sku};
(NewQty := {@Available};
(Qty_Array[sCounter] := NewQty)))))
else
max := ubound(SKU_Array);
While sCounter < max Do
(sCounter:=sCounter+1;
(if{tbl.sku} = SKU_Array[sCounter] then
(NewQty := Qty_Array[sCounter]-{tbl.order_qty};
(Redim Preserve Qty_Array[sCounter];
(Qty_Array[sCounter] := NewQty)))));

NewQty;

What I've got here is: If the SKU is not in the SKU array, I want to add it, and add the Available quantity to the Quantity array at the same position. If the SKU IS in the array, I want to find its position, then look up the quantity in the Quantity Array and change its value. Then use the variable &quot;NewQty&quot; in the report as the new Available Quantity. The problem is with the lookup array. Even if I run the formula after &quot;else&quot; in a separate formula, it returns 0 and the position value (sCounter) returns the incremental counter value.

So, after all of that... Am I missing something? Anyone have an alternative method of achieving this?

Using Informix Database, Crystal 9, Windows 2000

 

[simpelli]

That Order list should read:
Order# SKU Available Qty Ordered Bal
001 A 0 375 -375
001 B 775 25 750
001 C 100 193 - 93
001 D 0 5 - 5
002 B 750 775 - 25
002 F 200 200 0

 

[Ngolem]

I'll take a crack at it...you seem to have a WAY too many brackets in your formula...here is my version

evaluateafter({@Available});
stringvar array SKU_Array;
numbervar array Qty_Array;
shared numbervar NewQty;
numbervar max;
numbervar sCounter;

if not({tbl.sku} in SKU_Array )then
(
sCounter := sCounter +1;
Redim Preserve SKU_Array[sCounter];
Redim Preserve Qty_Array[sCounter];
SKU_Array[sCounter] := {tbl.sku};
NewQty := {@Available};
Qty_Array[sCounter] := NewQty;
)
else

//you need brackets here since the If-then block ,as-written ends after the MAX statement and would execute for the first part of the IF-then block as well.
(
max := ubound(SKU_Array);
BooleanVar Flag := False;
While sCounter < max Do
(
sCounter:=sCounter+1;
if{tbl.sku} = SKU_Array[sCounter] then
(
NewQty := Qty_Array[sCounter]-{tbl.order_qty};
Qty_Array[sCounter] := NewQty;
Flag := True;
);
if Flag then exit do;
);
);

NewQty;


Several things I have done other than mess with the brackets

I removed this line from the ELSE

(Redim Preserve Qty_Array[sCounter];

If you found the correct SKU on the first record you have just lost all your array data through this redim I think...or else at best it does nothing

I added a flag to allow you to kick-out of the While - do loop .

I think that is all you need.


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[simpelli]

Thanks, I'll give it try.

yeah, I got carried away on the brackets, I'll admit. And that last ReDim was thrown in as an attempt to make something happen (which didn't)

I was able to get a little closer by changing the

&quot;While sCounter < max Do &quot;

To

&quot;For sCounter := 1 to ubound(SKU_Array) do&quot;

I'll give your revision a shot & let you know

Thanks
-SI

 

[simpelli]

Thanks Jim for the code revision. I had to make a slight adjustment but it did the trick.
It threw an error at the line:

if Flag then exit do;

Was expecting a While after the exit do, so I changed
While sCounter < max Do to
While sCounter < max and Flag=False Do

I feel pretty good that I was at least on the right path to the solution. Just need to study some on Crystal Syntax...

Thanks again!

-SI

 

[Ngolem]

haha...

It is like Dr. Ruth says &quot;Practise...Practise...Practise!&quot;

I like including the boolean in the While condition BTW...better idea

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!