auto increment (AGAIN)

[JerryBrightbill]

RE: auto increment (AGAIN)

Rose,

One thing I do with some reports is run them from a script rather than using OnPrintInit or running them directly. You could follow the script segment you have with :Report.preview("report name") (or :report.print) and have the report detail filtered on the global variable.

Another method would be to use the variable to create a query and run that before the report and set the report to "base report on current selection of records". This is more flexible.

I have a couple reports that run from fairly lenghtly scripts to set variables, select which report to run, and then filter the records.

Jerry

RE: auto increment (AGAIN)

Jerry...thanks so much...I used the following and it works great! Hats off to you and Bill.
dim global fnum as c
parentform.index_set("f_num")
FNUM=ui_get_text("File Number", "Enter File Number",fnum)
If fnum=""
End
End If
fnum = padl(alltrim(fnum),5,"0")
Parentform.find(fnum)

How would I use this to print a report? I used the aksc variable to ask the user which f_num to print but I have to use the leading zeros.
This is the last piece for this app. I could not have done it without people like you, Bill, Cal, etc.
Even though I have learned a great deal, I will be glad to put this one into action. Rose

[JerryBrightbill]

RE: auto increment (AGAIN)

Rose,

They are the events available for a specific field. Scroll up to the name of the table. Thats where the other events are listed.

Jerry

RE: auto increment (AGAIN)

Jerry, I looked in field rules for the parent table. The only options are CanEditField, CanWriteField, OnEditField, OnWroteField. There is not a CanSaveRecord event.
That is why I put it on the form.

[JerryBrightbill]

RE: auto increment (AGAIN)

Rose,

Glad you got it working. Your difficulty with find is exactly the same issue I am facing on an app. Check out the new tips section on the Alpha Software site. Bill Warner posted a neat way to set up a customized find. This should work well for your case with one addition. After the user enters "jobno" (change to f_num on you app) add the following line before the find()

jobno = padl(alltrim(jobno),5,"0")

This will add the leading zeros to whatever the user entered.

Jerry

[JerryBrightbill]

RE: auto increment (AGAIN)

Rose,

From your message, it looks like you want to autoincrement the f_num field in Mainfile, the parent. Rather than putting the script on the CanSave event for the form, try it on the CanSaveRecord event in field rules for the parent table. When you are entering parent and then child records, the parent is saved when you go to enter a child record. You could also put it on the OnEnterRecord event in field rules. The only difference is when the script fires. I prefer the CanSaveRecord event since it saves it only if the parent is actually saved. The CanSave event for the form fires first when the parent is saved and then again when a child is saved. This could cause an error.

Also, be sure you have an index for Mainfile named "f_num" which uses "f_num" as the indexing expression.

Eliminate or comment out the line (put ' in from of the line)

parentform:f_Num.Refresh()

This is designed to refresh an object named f_num on the form. If you don't have a object name f_num on the form, this will generate an error.

I ran a test file and tried the script on both field rule events and it works without error.

Jerry

RE: auto increment (AGAIN)

Jerry...I figured out what I did wrong....thanks so much. However when I do a find by key, I have to enter the leading zeros because I changed F_Num to and alpha field. Is there anyway around this...thanks and thanks for solving the numbering problem!!!!

RE: auto increment (AGAIN)

Thanks for trying to help me. I would like the user to enter new records on a form based on a set called file with a one to many link based on F_Num. The child browse is embedded in the form
The Parent is called Mainfile, the fields are:
F_Num 5 in length, character field
Location
File Name
The child is called Subfile, the fields are:
F_Num 5 in length, character field
Subfile
Subject
Document

On the CanSave event for the form I put:
tbl1 = table.current()
if tbl1.f_num "".or.tbl1.mode_get() = 0 'Skips loop if already entered or not in enter or change mode.
else
num = ""
for i = 1 to 10000
num = padl(alltrim(str(i,5)),5,"0")
if key_exist("f_num",num)
else
tbl1.f_num = num
parentform:f_Num.Refresh()
exit for
end if
next
end if

I cannot get this to work. Do you have any idea what I am doing wrong.
Problem 1:If I add a parent record it will generate this error
trapped error in action command [1]
Command Inline Xbasic
Error: functioned not recoginized.
It will then assign the number correctly
Problem 2: I want to be able to add child records at the same time.
Sorry this is so long...wanted to explain in detail. Thand you so much for all your help...Rose

[JerryBrightbill]

RE: auto increment (AGAIN)

Rose,
That script you posted seems a complicated way to get an autoincremented number. Since it has to fetch thru every record, I suspect it is slow too. You can place the script I posted on the CanSaveRecord event in field rules for the table. You will have to change the field names to whatever you are using. The only problem you may have is if the table is a child in a set and you are saving a new record. Then table.current() will point to the parent and not the child. I have a work around for that issue if you need it.

Jerry

RE: auto increment (AGAIN)

I am trying to use your auto increment script to assign a file number.
I am using the following:
t=table.current()
t.fetch_first()
vprev_fileno = 1
if t.F_num vprev_fileno
goto EndOfLoop
end if
t.fetch_next()
WHILE .not. t.fetch_eof()
vprev_fileno=vprev_fileno+1
if t.fetch_eof() = .T. then
Goto EndOfLoop
end if
if t.F_numvprev_fileno
goto EndOfLoop
end if
t.fetch_next()
END WHILE
EndOfLoop:
'vprev_fileno has next usable file number
'use it for next file number
end
Question: Where do I attach this script?
I am using a data entry/update form based on a set with a one to many link using f_num, with an embedded browse for the child records.
I have tried numerous things but cannot get it to work. I would really appreciate any insight. Thanks in advance...Rose

RE: auto increment (AGAIN)

Looks good Jerry, I'll have to give it a whirl!
Jeff

[JerryBrightbill]

RE: auto increment (AGAIN)

Jeff, There is a simpler way to get the same result. I use the same concept in one app to resuse deleted values. In this case I am tracking application numbers which is in the field app_num. I have the following attached to an event on the data entry form. (In my app, it is on the OnDepart event for a field, but could be placed on the CanSave event for the form). I have an index for the app_num called app_num and the field name for the app_num is again app_num

tbl1 = table.current()
if tbl1.app_num "".or.tbl1.mode_get() = 0 'Skips loop if already entered or not in enter or change mode.
else
num = ""
for i = 1 to 1000000
num = padl(alltrim(str(i,7)),7,"0")
if key_exist("app_no",num)
else
tbl1.app_num = num
parentform:App_Num.Refresh()
exit for
end if
next
end if

As you can see, I just keep looping until a blank number is found or the loop hits 1000000. You could determine how many records exist in the table and limit the loop to the number of records +1 also. This method also allows the app_num to be a combination of a specified value and an incremented value.

Jerry

RE: auto increment (AGAIN)

This is the way the dbase 4 program was written "They" wan't to continue with this numbering scheme. I orginally made deleted records inactive but they insisted I reuse the numbers...

RE: auto increment (AGAIN)

Thanks Jeff, I will give it a try.
Rose

RE: auto increment (AGAIN)

Rose, I have a similar situation in our one of our apps. You can do this by first: Making sure that your records are indexed A to Z on the file number. Second: Fetch the first record and see if it the first possible file number. If it is not, use the first possible file number for the next new record because the record that used to hold it was obviously deleted. If the first record is using the first possible file number then third: Using a WHILE statement to fetch through the records one at a time get the value of the current record's file number and store it in a variable (lets name it vprev_fileno). Then fetch the next record. On the next line you will add one to vprev_fileno and then compare it to the file number in the current record. If it does not match then use it for the next new file number. If it does match, then continue fetching through the records until the script finds a record that does not match the vprev_fileno + 1. If you script reaches the end of the table then just use the last records file number + 1 for the next new record. This system will go back and fill any skipped file numbers until all have been filled. From that point on it will always use the first available file number in order of A to Z. I�m going to take a quick shot at the script off the top of my head but it will not be tested. You should get the general idea. I�m assuming the table will be the current table and that the first possible file number is �1000�. If not, you can modify the first line to open the proper table and set the first possible file number in the third line.

t=table.current()
t.fetch_first()
vprev_fileno = 1000
if t.F_num vprev_fileno
goto EndOfLoop
end if
t.fetch_next()
WHILE .not. t.fetch_eof()
vprev_fileno=vprev_file_no+1
if t.fetch_eof() = .T. then
Goto EndOfLoop
end if
if t.F_numvprev_fileno
goto EndOfLoop
end if
t.fetch_next()
END WHILE
EndOfLoop:
'vprev_fileno has next usable file number
'use it for next file number
end

Hope this helps?
Jeff