Dear,

With this script I’m able to update the item master for any date. I am also able to insert the new date(ticpr300.indt)and new amount(ticpr300.amnt)

Problem:
I’m unable to update the new amount in ticpr300.amnt for same date but able to update tiitm001 table smoothly.
For ex:
If I update tiitm001.copr = 36 tiitm001.ltcp = 03/03/09 the record get updated in tiitm001 and inserted in ticpr300 respective tables.
If I update again tiitm001.copr = 40 tiitm001.ltcp = 03/03/09 the records get updated in tiitm001 table but the record is not updated in ticpr300 table.
My script:
form.1:
init.form:
get.screen.defaults()
|****************************** choice section ********************************
choice.cont.process:

on.choice:

read.main.table1()


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

|****************************** function section ******************************
functions:


function read.main.table1()
{

item = ""
itemc = ""
db.retry.point()
select ticpr300.*
from ticpr300 for update
where ticpr300.item = :item.f
|and ticpr300.amnt <> 0.0
order by ticpr300.item, ticpr300.indt desc
as set with 1 rows
selectdo


if item = ticpr300.item then
else
itemf = ""
itemnf = ""
endif


if ticpr300.indt = date.num() then
itemf = ticpr300.item

db.retry.point()
select tiitm001.*
from tiitm001 for update
where tiitm001.item = :itemf

selectdo

|tiitm001.copr = ticpr300.amnt
|tiitm001.ltcp = ticpr300.indt
|tiitm001.matc = ticpr300.amnt
tiitm001.kitm =tckitm.purchase
tiitm001.copr = copr.f
tiitm001.matc = copr.f
tiitm001.ltcp = date.num()

db.update(ttiitm001,db.retry)
commit.transaction()

endselect
endif

if ticpr300.indt <> date.num() and isspace(itemf) then
itemnf = ticpr300.item

db.retry.point()
select tiitm001.*
from tiitm001 for update
where tiitm001.item = :itemnf
and tiitm001.stoc = 0.0
selectdo

tiitm001.kitm =tckitm.purchase
tiitm001.copr = copr.f
tiitm001.matc = copr.f
tiitm001.ltcp = date.num()

db.update(ttiitm001,db.retry)
commit.transaction()

ticpr300.item = item.f
ticpr300.cntr = tiitm001.cntr
ticpr300.indt = tiitm001.ltcp
ticpr300.cpcp = "001"
ticpr300.nuni = 1.00
ticpr300.actf = 1.00
ticpr300.amnt = tiitm001.copr
if ticpr300.indt=date.num() then

db.update(tticpr300,db.retry)
else
db.insert(tticpr300,db.retry)
endif
commit.transaction()

endselect
endif




item = ticpr300.item

endselect
|message ("Item is updated now")
}

Hi,

Did you try to debug the code and see if it gets into the selectdo or not (in both SQL)?

- George

Dear,
I have tried to debug the above script and have changed the script.
Now,Just help me in updating Ticpr300 table when the tiitm001.copr changes in same date.
I get the above error(attached file) when I execute the items for same date and works smoothly for different dates
My form inputs are item.f (item)and copr.f(cost price)


|******************************* form section *********************************
form.1:
init.form:
get.screen.defaults()
|****************************** choice section ********************************
choice.cont.process:

on.choice:

read.main.table1()


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

|****************************** function section ******************************
functions:
function read.main.table1()
{

item = ""
itemc = ""
db.retry.point()
select ticpr300.*
from ticpr300 for update
where ticpr300.item = :item.f
order by ticpr300.item, ticpr300.indt desc
as set with 1 rows
selectdo


if ticpr300.indt = date.num() then
itemf = ticpr300.item
db.retry.point()
select tiitm001.*
from tiitm001 for update
where tiitm001.item = :item.f
selectdo
tiitm001.kitm =tckitm.purchase
tiitm001.copr = copr.f
tiitm001.matc = copr.f
tiitm001.ltcp = date.num()
db.update(ttiitm001,db.retry)
commit.transaction()
|selectempty
ticpr300.item = item.f
ticpr300.cntr = tiitm001.cntr
ticpr300.indt = tiitm001.ltcp
ticpr300.cpcp = "001"
ticpr300.nuni = 1.00
ticpr300.actf = 1.00
ticpr300.amnt = tiitm001.copr
db.update(tticpr300,db.retry)
commit.transaction()
endselect

endif

if ticpr300.indt <> date.num() then
itemnf = ticpr300.item
db.retry.point()
select tiitm001.*
from tiitm001 for update
where tiitm001.item = :item.f
and tiitm001.stoc = 0.0
selectdo
tiitm001.kitm =tckitm.purchase
tiitm001.copr = copr.f
tiitm001.matc = copr.f
tiitm001.ltcp = date.num()
db.update(ttiitm001,db.retry)
commit.transaction()

ticpr300.item = item.f
ticpr300.cntr = tiitm001.cntr
ticpr300.indt = tiitm001.ltcp
ticpr300.cpcp = "001"
ticpr300.nuni = 1.00
ticpr300.actf = 1.00
ticpr300.amnt = tiitm001.copr
db.insert(tticpr300,db.retry)
commit.transaction()

endselect
endif
item = ticpr300.item
endselect

}

Hi,

i do not understand your script. you're doing 2 db.update and 2 commit.transaction() in the same selectdo.

Also, you have 1 db.update and 1 db.insert in the same selectdo.

If you fix your script, it's going to work.

PB.

Dear,

Thank You for your help. The script worked succesfully.