Hello,

I have a session which is used to capture forecase data. The script will automatically assign a reference number each time a new record is inserted. However, when copying an existing record a new ref number does not get allocated which results in "Record already exists" error.

This is my current code to allocate the ref number what changes do I need to make to also allocate a new number when copying records?

|****************************** MAIN TABLE SECTION ***************************

main.table.io:
before.write:
select tssma950.*
from tssma950 for update
selectdo
tssma951.budref = tssma950.ffno + 1
tssma950.ffno = tssma950.ffno + 1
db.update(ttssma950, db.retry)
commit.transaction()
endselect

Thanks in advance

Hello,

I have a session which is used to capture forecase data. The script will automatically assign a reference number each time a new record is inserted. However, when copying an existing record a new ref number does not get allocated which results in "Record already exists" error.

This is my current code to allocate the ref number what changes do I need to make to also allocate a new number when copying records?

|****************************** MAIN TABLE SECTION ***************************

main.table.io:
before.write:
select tssma950.*
from tssma950 for update
selectdo
tssma951.budref = tssma950.ffno + 1
tssma950.ffno = tssma950.ffno + 1
db.update(ttssma950, db.retry)
commit.transaction()
endselect

Thanks in advance

Hello,

no where clause ?

select ......
from .......
where ......
selectdo
update
selectempty
insert
endselect


Regards

Hello Bernd,

My tssma950 table only holds one record which is the last used reference number.

My current logic works fine for allocating the next sequential reference number in the tssma951 table. It just fails when creating a new record via a copy. So I'm not really sure what you are meaning when you say "no where clause?"

When you copy a record is there a way to remove the reference number and then when you save it will allocate the next sequential number using the existing logic?

I'm no expert at programming so please excuse my ignorance.

Thanks

Hello Bernd,

My tssma950 table only holds one record which is the last used reference number.

My current logic works fine for allocating the next sequential reference number in the tssma951 table. It just fails when creating a new record via a copy. So I'm not really sure what you are meaning when you say "no where clause?"

When you copy a record is there a way to remove the reference number and then when you save it will allocate the next sequential number using the existing logic?

I'm no expert at programming so please excuse my ignorance.

Thanks

Hello John,

as i understand you right, only one record ist stored in the table.

Based on the reference of tssma951 try :

before.write: and before.rewrite:

The actions programmed in this subsection are executed before each (re)write action on the main table.
A write action occurs when a record is inserted.
A rewrite action occurs when a record is changed.
You can use this subsection to call skip.io() in order to undo the (re)write action.

Try to use rewrite if you modify the only one existing record.

Regards

I just tested this on a 4c4 session I developed recently - see the simple code below. In the case of the copy record it hits the before.write. The thing that worries me is that commit.transaction in the before.write - not sure why you do not get an error. In my below session I had to comment out the commits - or get record not locked errors. Running your session thru debug mode - add an after write section - just put a message or something there. Check the tssma951.budref field and the table tssma950. So just me and I am not 100% sure, but I would remove the commit and run in debug mode to trace through it.



|******************************************************************************
|* tcudi0112 0 VRC B40T c4 dev
|* Maintain Commodity Code Extension table
|* Mark Holland
|* 2013-07-27
|******************************************************************************
|* Main table tcmcs028 Commodity Code Extension table, Form Type 2
|******************************************************************************
|****************************** declaration section ***************************
declaration:
table ttcudi012 | Commodity extension table.
table ttcmcs028 | Commodity Code Extension table



|****************************** tables ****************************************
|****************************** variables *************************************
|****************************** functions *************************************
|****************************** include ***************************************
|****************************** program ***************************************
|****************************** main table io *******************************
main.table.io:
before.write:
message("here")

after.delete:
delete_fields()

after.rewrite:
update_fields()

after.write:
update_fields()
|****************************** zoom ******************************************
|****************************** fields ****************************************
|****************************** choices ***************************************
|****************************** functions *************************************
functions:
function update_fields()
{
select tcmcs028.*
from tcmcs028 for update
where tcmcs028._index1 = {:tcudi012.ccde}
selectdo
tcmcs028.ccde = tcudi012.ccde
tcmcs028.dsca = tcudi012.dsca
db.update(ttcmcs028,db.retry)
selectempty
tcmcs028.ccde = tcudi012.ccde
tcmcs028.dsca = tcudi012.dsca
db.insert(ttcmcs028,db.retry)
endselect
|commit.transaction()
}
function delete_fields()
{
select tcmcs028.*
from tcmcs028 for update
where tcmcs028._index1 = {:tcudi012.ccde}
selectdo
db.delete(ttcmcs028,db.retry)
endselect
|commit.transaction()
}

Didn't have much luck in progressing with this.

Tried a before.rewrite and debugging as suggested but couldn't get it to work.

Didn't make any difference with or without the commit.transaction.

I guess what I want to happen is when you click the copy button the ref field is set to zero so when you save, it automatically gets incremented by th existing logic.

I'll have to sleep on it and look again tomorrow with a fresh head.

Thanks

I think I see what you are saying - on my session (maintain commodity codes) if I tab out of the commodity code field I get the record already exists. So I have to change the key index field before the save. What you can try is setting the value to zero in the "choice.dupl.occur:" and "before.choice:" event. And then hopefully the save runs and updates the fields.

Well there has to be a better way. Here is one solution I found that works for my session. When you click copy it sets a status of true and then in the before display of the index field(tcudi012.ccde) I check the status and set the field to "0". Then I can update the field. In this case I think you can set the field to 0. So what happens is the cchoice.dupl.occur happens when you hit the copy button, then the before display field gets executed, then after you hit the save button(the record saves) and the after choice of choice.dupl.occur resets the copy.status value. I did not do a lot of testing, but for copying one record it worked.

field.tcudi012.ccde:
before.display:
if copy.status then
tcudi012.ccde = "0"
endif

|****************************** choices ***************************************
choice.dupl.occur:
before.choice:
copy.status = true
after.choice:
copy.status = false

Hi Mark,

Spot on! Your solution worked for me.

Thank you very much for your help.

My code ended up as this -

|******************************************************************************
|* tssma9151 0 VRC B40O c4 liv0
|* Maintain SSA Modification Forecast
|* bsp
|* 17-09-13 [16:41]
|******************************************************************************
|* Script Type: 123
|******************************************************************************

|****************************** DECLARATION SECTION ***************************

declaration:

table ttssma950 |SSA Mod Forecast First Free Number
table ttssma951 |SSA Mod Forecast Data
extern domain tcmcs.str2 bu |String value of bu enumerated field
long copy.status

|****************************** CHOICE SECTION ***************************

choice.dupl.occur:
before.choice:

copy.status = true
after.choice:
copy.status = false

|****************************** FIELD SECTION ***************************

field.tssma951.bupo:

before.display:
bu = enum.descr$("tssma.bu", tssma951.bu)

tssma951.bupo = bu & "." & tssma951.custpo

field.tssma951.budref:

before.display:
if copy.status then
tssma951.budref = 0
endif


|****************************** MAIN TABLE SECTION ***************************

main.table.io:
before.write:

select tssma950.*
from tssma950 for update
selectdo
tssma951.budref = tssma950.ffno + 1
tssma950.ffno = tssma950.ffno + 1
db.update(ttssma950, db.retry)
commit.transaction()
endselect