help duplicating script for multiple records

[Becky McNichols]

I have the following script attached to the CanDeleteRecord field event of a parent table in a set to undo a post on three child fields. The parent,"Salesheader", has a one to many link with "Treatments". "Qty1","Qty2","Qty3" are three Treatment fields which post to "Inventory".

The script works fine if there is only one child record attached to the parent, but if there is more than one, it only works on the first record. How do I modify it to keep going until all associated children are taken care of? Thanks for any help.

dim oldvala as n
dim oldvalb as n
dim oldvalc as n
dim invent as p
dim ida as n
dim idb as n
dim idc as n
dim rec as n
dim tbl as p

tbl=table.get("treatments")
invent=table.open("inventory")
oldvala=tbl.qty1
oldvalb=tbl.qty2
oldvalc=tbl.qty3
ida=tbl.id1
idb=tbl.id2
idc=tbl.id3

invent.index_primary_put("id")
rec=invent.fetch_find(ida)
if rec

[JohnZaleski]

RE: help duplicating script for multiple records

Betsy,
page 63 of xbasic manual explains this method of fetching thru the child records related to the current parent on your form. Something like this:

'Get pointer to primary table of your set
tbl = table.current()' this would be your sales header
'Get pointer to one to many child table in the set
child = table.get("treatments")
' now fetch thru all child records for the current parent
child.fetch_first()
WHILE .NOT. child.fetch_EOF()
' this is where your update inventory logic should go
' child.qty1 child.qty2 child.qty3
' take the "end" statements out of your program
child.fetch_next()
END WHILE

[Peter.Wayne]

RE: help duplicating script for multiple records

try this:

dim oldvala as n 
 dim oldvalb as n 
 dim oldvalc as n 
 dim invent as p 
 dim ida as n 
 dim idb as n 
 dim idc as n 
 dim rec as n 
 dim tbl as p 

 tbl=table.get("treatments") 
 invent=table.open("inventory") 
 oldvala=tbl.qty1 
 oldvalb=tbl.qty2 
 oldvalc=tbl.qty3 
 ida=tbl.id1 
 idb=tbl.id2 
 idc=tbl.id3 

 invent.index_primary_put("id") 
 rec=invent.fetch_find(ida) 
if rec>0 then 
 'found matching chemical in inventory 
  invent.change_begin() 
  invent.qty_on_hand=invent.qty_on_hand+oldvala 
  invent.change_end(.t.) 
end if

 rec=invent.fetch_find(idb) 
 if rec>0 then 
 ' matching chemical in inventory 
 invent.change_begin() 
  invent.qty_on_hand=invent.qty_on_hand+oldvalb 
 invent.change_end(.t.) 
 end if

 rec=invent.fetch_find(idc) 
 if rec>0 then 
 'matching chemical in inventory 
  invent.change_begin() 
 invent.qty_on_hand=invent.qty_on_hand+oldvalc 
 invent.change_end(.t.) 
 end if

invent.close()
end

You don't have to keep opening and closing the inventory table, and resetting the index - those are very time-consuming operations.

[Becky McNichols]

RE: help duplicating script for multiple records

Thank you both. I'll get on it as soon as I can.

[Becky McNichols]

RE: help duplicating script for multiple records

Dr. Wayne-
Your suggestions certainly streamlined my script, but the problem remains of just acting on the first child record and ignoring the rest. I trying to implement John's suggestions, but I'm not sure where to insert what (I'm obviously a novice at this!).

[JohnZaleski]

RE: help duplicating script for multiple records

Becky,

I did a little cutting and pasting from Dr. Wayne's, yours and mine. See if the following works.

'Get pointer to primary table of your set
tbc = table.current()' this would be your sales header
'Get pointer to one to many child table in the set
tb1 = table.get("treatments")
invent=table.open("inventory")
invent.index_primary_put("id")
' now fetch thru all child records for the current parent
tb1.fetch_first()
WHILE .NOT. tb1.fetch_EOF()
oldvala=tbl.qty1
oldvalb=tbl.qty2
oldvalc=tbl.qty3
ida=tbl.id1
idb=tbl.id2
idc=tbl.id3

'rem pass 1
rec=invent.fetch_find(ida)
if rec>0 then
'found matching chemical in inventory
invent.change_begin()
invent.qty_on_hand=invent.qty_on_hand+oldvala
invent.change_end(.t.)
end if

'rem pass 2
rec=invent.fetch_find(idb)
if rec>0 then
' matching chemical in inventory
invent.change_begin()
invent.qty_on_hand=invent.qty_on_hand+oldvalb
invent.change_end(.t.)
end if

'rem pass 3
rec=invent.fetch_find(idc)
if rec>0 then
'matching chemical in inventory
invent.change_begin()
invent.qty_on_hand=invent.qty_on_hand+oldvalc
invent.change_end(.t.)
end if

tb1.fetch_next()
END WHILE
invent.close()
end

[JohnZaleski]

RE: help duplicating script for multiple records

Becky,
In case you don't see it, I made attempt at this. Look several message above this on: msg#34348 same thread

[Becky McNichols]

RE: help duplicating script for multiple records

John-
Thanks for your response. I haven't put it through very rigorous testing yet, but it seems to work fine. Thanks a lot - I really appreciate it!