Help, I've got the autoincrement blues

[michaelklein]

I'm working on my 1st Alpha 5 invoice entry form with a header/detail set defined. I can't use a standard autoincrement field for transaction ID because once the invoices are posted, I want to move all the records to history tables, then delete them from the header and detail tables. Since the next autoincrement value would restart at the default value in the field rules, it would create non-unique transaction ID's in the history set. Therefore, with the help of the code archive (Dr. Wayne), and this forum, I have "rolled my own" increment script. I added a field rule to the OnEnterRecord event of the transaction ID field in the header file to run the script. It appears to increment fine, but once there's one header/detail pair of records, I can't ADD any more records, even using the default form for the set. If I click the "Add record" toolbar icon, the form appears, but it displays the existing first pair of header/detail records along with their corresponding transaction ID's.

I'm probably doing something wrong in the script as far as assigning the next transaction # to the new record I'm trying to ADD, but I don't what. I have included the script below:

[b]----- snip ----
DIM tbl1 as p
DIM tbl2 as p
DIM xact_num as n
DIM idx2 as p
DIM tries as n

tries = 0
tbl1 = table.get("transactionheader")
on error goto no_open
tbl2 = table.open("arcontrol")
on error goto 0
tbl2.index_primary_put("Ar_Company_Code")
if tbl2.fetch_find(var->current_company) > 0
  tbl2.change_begin()
  select
    case var->current_xacttype = "1"
	tbl2.lastbillseqnum = tbl2.lastbillseqnum + 1
    case var->current_xacttype = "2" .or. var->current_xacttype = "3"
	tbl2.lastcrdbseqnum = tbl2.lastcrdbseqnum + 1	
  end select
  tbl2.change_end(.t.)
else
  ui_msg_box("Missing ARControl Record","There is no ARControl record for company " + \
				var->current_company + "!" + chr(10) + "Please contact Michael Klein " + \
				"before continuing")
  tbl2.close()
  END
end if
xact_num = if(var->current_xacttype = "1", tbl2.lastbillseqnum, tbl2.lastcrdbseqnum)
tbl2.close()

tbl1.XACTID = padl(alltrim(str(xact_num)),6,"0")
ui_msg_box("xactid assigned","The next xactid value is " + tbl1.xactid)
END

no_open:
on error goto 0
if tries 
	sleep(1)
tries = tries + 1
resume 0
else
	reply = ui_msg_box("Unable to update ARControl table","Keep trying?",UI_YES_NO)
if reply = UI_YES_SELECTED then
		tries = 0
resume 0
	else
		END
	end if
end if
----- snip ----[/b]

Thanks in advance for any help.
Michael

[Scott Emerick]

RE: Help, I've got the autoincrement blues

Michael,

I don't have an answer to your problem per se, but a possible suggestion to simplify things.

What about updating your transaction ID for history files by adding a year or something that would make the ID unique allowing you to use the built in autoincrement.

just a thought ...

Scott

[michaelklein]

RE: Help, I've got the autoincrement blues

Scott,

Thanks. That's a good idea. Right now I'm rethinking my "roll-your-own" mechanism. If I learn anything new, I'll post it here.

-- Michael

[Al Buchholz]

RE: Help, I've got the autoincrement blues

Michael

I would use table.open("arcontrol",file_rw_exclusive)

You may want the script to be in the cansaverecord event, rather than the onenter.

In addition you can use the debugger to follow what is happening.

debug(1) 'will start it.

[Peter.Wayne]

RE: Help, I've got the autoincrement blues

have you, by any chance, enabled referential integrity? that might interfere with what you are trying to do.
otherwise, your script looks OK to me, and when i tried something similar just now, it works fine for me.

[michaelklein]

RE: Help, I've got the autoincrement blues

Peter,

No, I haven't enabled referential integrity. I think I'm going to make the index in the history table cdate(date()) + xactid, the latter being an Alpha5 auto increment field.

Thanks for the help.
Michael

[michaelklein]

RE: Help, I've got the autoincrement blues

Al,

I originally tried to use file_rw_exclusive, but the arcontrol table would get caught in the script's error trap indicating I couldn't open it exclusively. I don't know why as that table isn't used often.

I put the script into OnEnter for 2 reasons:
1. That's where Dr. Wayne recommended it be placed.
2. I wanted to display the value on the form at the beginning of data entry to make sure it was updating properly.

Thanks for the help.
Michael