Adding rows according to field value 1

[woodyinoz]

I am trying to read the value of a field and add the appropriate number of rows.

For example, if my quantity field reads 2 then I want to add another row so that there are actually 2 rows with the same properties. The quantity should then change to 1 in each row. Likewise, if the quantity is 3 then 2 rows should be copied down or added and the quantity in the 3 rows should reset to 1.

I hope someone can help,

Woody.

 

[Langley]

I recommend that you look at the copyToArray() and copyFromArray() procedures. The example under the copyToArray() proc is almost exactly what you want. Let me know if you need help with the coding.

Mac

 

[woodyinoz]

How would I specify that the value in quantity is to be read in order to insert the correct number of rows? The below code is merely and example of what I am trying to do and I know that it would not work.

If Quantity >1 then
Quantity -1 = {No of rows to be inserted}

 

[Langley]

Okay, let's make a couple of assumptions. First you have queried the table and got an answer table containing only those entries with multiple quantities. Second, you've got a form with a pushbutton on it. For simplicity let's assume that your data table is called Stock.db and that the table has 3 fields: StockNum*(key) Increment*(key) and Quantity. The answer table has the same fields, as generated by the query. The pushbutton event code should be as follows:

var
  tcAns, tcQuan    tCursor
  cntr             number  
  StkAr            Array[] Anytype
endvar

; your query code to generate the answer table should go here


if not tcAns.open(":Priv:answer.db")
   then errorShow()
endif

if not tcQuan.open(":myAlias:Stock.db")
   then errorShow()
endif

tcQuan.edit()

scan tcAns:

   tcQuan.locate("StockNum",tcAns."StockNum")
   cntr = tcQuan."Quantity" - 1
   tcQuan."Quantity" = 1
   tcAns.copyToArray(StkAr)
   while cntr <> 0
      tcQuan.insertRecord()
      tcQuan.copyFromArray(StkAr)
      cntr = (cntr-1)
   endwhile

endScan

tcQuan.endEdit()
tcQuan.close()
tcAns.close()

This steps through the answer table one record at a time, finds the corresponding record in the stock table. It's important that the Stock table have a two field primary key (StockNum and Increment), since you are going to duplicate StockNum and you don't want key violations. You can use an autoincrement field for the Increment field, but it is better to use your own numbering scheme. If you use you own, you will have to add the code in the scan loop for it. Anyway, once the record is found it sets the counter (cntr) value to the number of items in stock (minus the 1 found record), copies the record to an array, and sets the first records quantity value to '1'. A while loop then adds the correct number of rows in the Stock.db table, copies the information from the array so there will be x number of rows, each having a quantity value of '1'.

This is off the top of my head and I have not had my coffee yet, so make sure to test it on copied tables.

Mac