Greetings,

I have a recursive procedure to break out bill of materials. I'm using recursion because low level sub assemblies must have their costs 'rolled up' into higher level assemblies. Because it is recursion, I'm pretty much forced to resort to "INTO ARRAY" clause in SQL instead of using cursors or tables.

Ironically, all the pricing and cost rollup works just fine. That was the part I expected to be the biggest headache when I started.

Instead the array returned from the "into array" for some reason seems volatile for my character fields. Sometimes there will be a logical in one of the columns of the array that are defined as character in the table structure. To make it more confusing the type just doesn't seem consistent through all rows. In other words things maybe just fine until row 150 and then my 3rd column has type logical and no data. But at the same time the third column, unit of measure field for example, has data in every single record. It also seems that in some results that I get nothing but logical (.F.) in every row of that array despite knowing data should be in every field.

Is there known glitches or workarounds that I need to do when using the "INTO ARRAY" clause? Am I missing something obvious here? If I wasn't using recursion I would just switch to cursors.


My basic code looks like this:

PROCEDURE ProcessBOM
LPARAMETERS lcBomNo

LOCAL lnArrayLen, lnCounter, lnTotalCost
LOCAL ARRAY aBomDetail(1,5)  && scope array to local procedure

* Select data into array
SELECT pebmdt01.item,pebmdt01.begdate, pebmdt01.bqtyusd, pebmdt01.umeasurusd, pebmdt01.operid, icitem01.stdcost ;
  from pebmdt01, icitem01 ;
  WHERE  pebmdt01.bomno = lcBomno ;
      AND pebmdt01.item = icitem01.item ;
  ORDER BY icitem01.itmdesc ;
  INTO ARRAY aBomDetail

lnTotalCost = 0  && Initialize cost
IF TYPE("aBomDetail") = "C"  && Array created will be "U" if no records returned?

   * Get count of items/assemblies returned		
   lnArrayLen = ALEN(aBomDetail,1)

   * Assign item field to temorary variable
   lcItem = aBomDetail(lncounter,1)
   
   * Loop through all items/assemblies
   FOR lnCounter = 1 TO lnArrayLen   && Loop through assembly

       SELECT pebmhd01 && Check for sub assembly
       locate for pebmhd01.bomno = aBomDetail(lnCounter,1)  
       IF !EOF()  && There is a subassembly so process it
           lnTotalCost = lnTotalCost + ProcessBOM(lcItem)  && Recursive call
           Do UpdateCost with lcItem, lnTotalCost  && update cost after rollup
       ELSE
           lnTotalCost = lnTotalCost + aBomDetail(lnCounter,6) && add stdcost to total
       ENDIF
       * add item to BOM cursor for the report
       Do BOMAdd lcitem, aBomDetail(lncounter,4)  && This "C" field appears volatile
   ENDFOR
ENDIF
RETURN lnTotalCost

Thanks in advance for any help on this. It's making me pull my hair out! :)