Baan Friends,

I have need of maintaining a 2nd table (which is basically an extension) of the main table, in a multi-occ session.

I have done some experimenting with the main.table.io sections, but there seems to be issues with multi-occ.

Has any one done this?

Thanks,
fallguyjg

What kind of problem are you running into? Maybe if you explain the problem someone can recommend a solution. I can not recall doing a mutli-occ session, but I would expect that you could do a do.occ to make sure the second table was updated correctly. Or even roll through all occurances to make sure the second table was updated.

Hi Mark,

My form (type 3) is like this.

maintable.viewfld

maintable.fld1 maintable.fld2 2ndtable.fld1
maintable.fld1 maintable.fld2 2ndtable.fld1
maintable.fld1 maintable.fld2 2ndtable.fld1
maintable.fld1 maintable.fld2 2ndtable.fld1
maintable.fld1 maintable.fld2 2ndtable.fld1

I use read.2ndtable() in main.table.io.after.read:
write.2ndtable() in main.table.io.after.write:
rewrite.2ndtable() in main.table.io.after.rewrite:
delete.2nd.table() in main.table.io.after.delete:

The issue seems to be in multi-occ part of things,
because it seems to work OK for single occ.

Standard program is handling the occurrences of the main table, but is NOT doing the same for the 2nd table.

Thanks,
fallguyjg

What I was thinking was that you could do something like this:


| Find how many are being deleted
for i = 1 to filled.occ
if(mark.table(i)) then
do.occ(i,delete.second.table)
endif
endfor


or

for i = 1 to filled.occ
do.occ(i,update.second.table)
endfor


The second routine could be used for both rewrite and write. The do.occ would pass the key fields from the form to the subroutine for a select for update on table 2. I also think for both routines you would not do the commit and the standard program would actually perform the commits.

Example:

select table2.fields
from table2
where table1._index1 = {:table1.fields)
selectdo
| would update only the table2 field on form
db.update(ttable2,db.retry)
selectempty
| Data field already on the form.
table2.keyfield = table1.field
db.insert(ttable2,db.retry)
endselect


At least I think something like this would work. I do something like this on one session where I use two baan tables to display data. The user can then select and transfer records into a new purchase order or add a line to an existing purchase order.

Mark,

I get the idea.

My experimenting has shown that the table2 form fields are not being kept in the occurrences arrays, like the main table.

When I execute do.occ.without.update(...), the table2 form fields are not the correct values.

Please see my attached file, showing screen shots and script.

Thanks,
fallguyjg

Try adding the read.addendum in the after.display of fixed sequence. What I think is happening is the after.read,after.write,etc. is hit only after all records have been processed. You can also try the after.input section of addendum description to do updating. Also in the after.read section you can always have it insert a blank record on the second table in the select empty.

When I have tried this, I seem to recall that I don't actually display the 2nd table. Instead create form fields set up to display multiple times and fill those in the manner you mention above. Then when you write, get the values out of the form field and write it to the 2nd table. But this was so long ago, I can't find an example of if to post.

-Dennis

On the one multi-occ display session I changed like this I did use the actual table field name. On all of the maintain session (seems like all are single-occ) I did like Dennis suggested and used a different form field name. I also can't remember why I did this.

After some experimenting, I got the following to work.

|******************************************************************************
|* tized9900 0 VRC B40C c4 ps
|* Maintain ECO Procedures
|* 2005-08-02
|******************************************************************************
|* Main table tiedm301 ECO Procedures, Form Type 2
|******************************************************************************

|****************************** declaration section ***************************
declaration:

table ttiedm301 | ECO Procedures
table ttized911 | Addendum

domain tcmcs.str80 zed911.edsc(20)
domain tcbool zed911.edsc.changed(20)


|****************************** before program section ************************

before.program:
clear.zed911()

|****************************** form section **********************************


|****************************** choice section ********************************

after.update.db.commit:
clear.zed911()

|****************************** field section *********************************

field.tized911.edsc:
before.display:
if not zed911.edsc.changed(actual.occ) then
read.addendum()
endif

before.input:
tized911.edsc = zed911.edsc(1,actual.occ)

when.field.changes:
zed911.edsc(1,actual.occ) = tized911.edsc
zed911.edsc.changed(actual.occ) = true

|****************************** main.table.io section *************************

main.table.io:
after.write:
write.addendum()

after.rewrite:
rewrite.addendum()

after.delete:
delete.addendum()


|****************************** function section ******************************

functions:

function read.addendum()
{
select tized911.edsc
from tized911
where tized911._index1 = {:tiedm301.prcd}
selectdo
selectempty
tized911.edsc = ""
endselect
zed911.edsc(1,actual.occ) = tized911.edsc
}

function write.addendum()
{
tized911.prcd = tiedm301.prcd
tized911.edsc = zed911.edsc(1,actual.occ)
tized911.txta = 0
db.insert(ttized911, db.retry, db.skip.dupl)
}

function rewrite.addendum()
{
select tized911.*
from tized911 for update
where tized911._index1 = {:tiedm301.prcd}
selectdo
tized911.edsc = zed911.edsc(1,actual.occ)
db.update(ttized911, db.retry)
endselect
}

function delete.addendum()
{
select tized911.*
from tized911 for update
where tized911._index1 = {:tiedm301.prcd}
selectdo
db.delete(ttized911, db.retry)
endselect
}

function clear.zed911()
{
set.mem(zed911.edsc, "")
set.mem(zed911.edsc.changed, false)
}

Thanks for all your suggestions.
fallguyjg

i have to delete record from the other tables based on the key of the selected record of main table in a multi-occ session. I am calling the fucntion for a particular occurance using do.occ in the before.choice of mark.delete section. The problem is, it deletes record from the other tables but error comes that record is not locked on the main table. What can be the solution for the same. Reply ASAP.

Thanks.

Post your code Ankita. You probably just need to get the retry points and commit transactions in the right spots.

If you put a commit.transaction() on the before.choice of the mark.delete, the main table isn't locked anymore... try moving the deletion of the secondary table in the after.choice instead.

When i put a commit.transaction() on the after.choice of the mark.delete, then the error is "transaction is on, can't continue".

Code:

choice.mark.delete:
before.choice:
long i

for i=1 to filled.occ
if mark.table(i) then
do.occ(i,delete.lines)
endif
endfor

main.table.io:
after.delete:
commit.transaction()

function delete.lines()
{
message("Deleting function: " & str$(kpoes042.orno) & " | " & str$(kpoes042.pono))

| delete from kpoes002 Lines

select kpoes002.*
from kpoes002 for update

where nbdn in
(select nbdn
from kpoes043
where kpoes043._index1 ={:kpoes042.orno})

and itmn in
(select itmn
from tdoes042
where tdoes042._index1 ={:kpoes042.orno,:kpoes042.pono})

order by kpoes002._index1
selectdo
db.delete(tkpoes002,db.retry)
message("Deleted from kpoes002")

| Deleting from kpoes042 also

select tdoes042.*
from tdoes042 for update
where tdoes042._index1 ={:kpoes042.orno,:kpoes042.pono}
selectdo
db.delete(ttdoes042,db.retry)
message("Deleted from tdoes042")
endselect
selectempty
endselect
}

Main table is kpoes042 for the multi-occ session... Once records from kpoes042 table are selected for deletion, the corresponding records should get deleted from the table kpoes002 table and tdoes042 table.

This gives me error that "Record is not locked on kpoes042". And when i kept the code in after.choice then error is "transaction is on, can't continue".
What can be the possile solution for same?

Delete the commit.transaction() in the main.table.io section the standard program will commit the transaction for you.

I am not sure you need the delete for kpoes042 - you have already selected them to be deleted and hit the delete key. That was the first thing I thought of.

wiggum,

I removed the commit.transaction() in the main.table.io section but the error is "transaction is on, can't continue". Now, how can i proceed?

mark,

there are two tables: one is kpoes042 which is the main table for the session and another one is tdoes042 from where i am deleting records corresponding to the selected records of kpoes042 table in multi-occ session.

The problem is solved by removing commit.transaction() form after.delete event of main.table.io section. I also wrote commit.transaction() and also db.retry.point in the function.

There is another interesting entry point
main.table.io/after.update.db.commit which has been discussed here (http://www.baanboard.com/baanboard/showthread.php?t=6236).

Günther

mark,

there are two tables: one is kpoes042 which is the main table for the session and another one is tdoes042 from where i am deleting records corresponding to the selected records of kpoes042 table in multi-occ session.
Okay - I could have swore yesterday that code had a kpoes042 in the last delete, but now is says tdoes042. Just too many oes042's for my old eyes. :)