I have a multi-occurrence form for table cxcsb010. I allow the user to do multiple row updates on the table which is my goal.

I have related tables cxcsb005 and cxcsb020 which status are dependent on the values entered in cxcsb010 . I have a DAL for cxcsb010 to handle the updates of the status of the related tables which is illustrated below via function check.update.ctl.wo.status and check.update.ctl.header.status.


If I put the update functions for the in after.save.object section I get Error 206 for cxcsb010 table but if I put the update functions in after.commit.transaction() it only update the last record that was modified in MOC. Can somebody help me where do I need to up the update functions in my DAL so it updates the relevant tables for every record modified in cxcsb010 MOC form?


function extern long after.save.object(long mode)
{
if mode = DAL_NEW then
| this is code from after.write subsection
else
| this is code from after.rewrite subsection
check.update.ctl.wo.status(cxcsb010.wono)
check.update.ctl.header.status(cxcsb010.ctln, cxcsb010.tqty)
endif

}




function check.update.ctl.wo.status(domain tcorno l.wono)
{
long l.nrec | Number of Records
long l.nrcc | Number of Line completed

|Update tcxcsb020 as Partially completed and/or completed

|Count the number of Records in a Work Order
select count(cxcsb010.wono):l.nrec
from cxcsb010
where cxcsb010._index3 = {:l.wono}
selectdo
endselect

|Count the number of Records for WO completed or Outbounded
select count(cxcsb010.wono):l.nrcc
from cxcsb010
where cxcsb010._index3 = {:l.wono}
and cxcsb010.stat >= 50 |or and cxcsb010.stat = 70 | Completed WO or Outbounded
selectdo
endselect

|db.retry.point()
select cxcsb020.*
from cxcsb020 for update
where cxcsb020._index1 = {:l.wono}
selectdo
if l.nrec = l.nrcc and l.nrcc > 0 then
cxcsb020.cpdt = utc.num()
cxcsb020.cpby = logname$
cxcsb020.stat = ltoe(40) | Completed WO
else
if l.nrec <> l.nrcc and l.nrcc > 0 then
cxcsb020.stat = ltoe(30) | Partially Completed WO
else
cxcsb020.stat = ltoe(20) | Released
endif
endif
db.update(tcxcsb020, db.retry, elocked)
commit.transaction()
endselect

|return(l.nrcc)
}


function check.update.ctl.header.status(domain tcmcs.long l.ctln, domain tcmcs.long l.tqty )
{
|Update tcxcsb020 as Partially completed and/or completed
long l.nrcc | Number of Line completed or Outbounded
|db.set.to.default(tcxcsb010)
select count(cxcsb010.wono):l.nrcc
from cxcsb010
where cxcsb010.ctln = {:l.ctln}
and cxcsb010.stat >= 50
selectdo
endselect

|db.retry.point()
select cxcsb005.*
from cxcsb005 for update
where cxcsb005._index1 = {:l.ctln}
selectdo
if l.nrcc > 0 and (l.nrcc = l.tqty) then
cxcsb005.stat = ltoe(50) | Completed
else
cxcsb005.stat = ltoe(40) | Released
endif
db.update(tcxcsb005, db.retry, elocked)
commit.transaction()
endselect

}

I would think a 'commit.transaction' within a DAL is a bad thing, since DAL is called while updating the main record and before an automatic commit is done. If you prematurely commit in the DAL, I would think that some issues may occur when the standard processing tries its own commit. I never put 'commit.transaction' within a DAL and try to do all updates of other tables or records in the "after.after" section.

Thanks BaanInOhio, that did it for me. Nice tip for my issue.

Hi,

Also keep in mind, when you are using commit.transaction, do it out of the select loop to avoid other possible errors.

Thanks bushhandchanda, that actually make sense that I think of it.

A few other remarks about the code examples:
1. Instead of using ltoe(nn) you can better use the enum constant name.
2. The 3rd argument of db.update should not be an error value (like elocked) but an error action (like db.skip.dupl). See db.update() function in the programmers manual.