How do I make my auto-increment field bigger?

[EngrDan]

Hi All,
History:
I am new to Alpha�s forum and to Alpha Five Version 7.
Experience programming in C, BASIC, ForTran, Easytrieve and Datatrieve.
Have read most of Pace Training, Nurture Build levels one and two.
Project:
This company has a database developed by a person no longer with us. I have been given the project to upgrade the database because the field assigning RGA�s (return goods authorization) has auto incremented from RGA-9999 to RGB-0000 to RGB-0001�
I have tried editing in the Default Browser using Allow edits with no luck. I would export change and import but that has to be done in code not in the Alpha environment. I have tried Edit Field Rules using the Simple default expression with a new default value and Auto advance after field filled selected with some luck however after it enter that record it increment to the next RGB number. Anyhow the books say you can edit in browser mode � Where do I go from here, books. On-line help...?

[Garry Flanigan]

Re: How do I make my auto-increment field bigger?

From what you describe, in table design you need to increase field width by one character (or more if you like) but I assume 1 for the steps given below.
Then the existing records need to be fixed up.
I assume the field was set to autoincrement from RGA-0001. If not then please provide extra details.
The steps I suggest are:
1. Backup just in case something goes wrong.
2. Turn off auto-inc
3. Change field to one character wider
4. Make an Operation to modify (Update) the field. The field in question called YourFieldName for the sake of explanation will be calculated as:
if(left(YourFieldName,3) = "RGA", "RGA-0" + right(alltrim(YourFieldName),4), "RGA-1" + right(alltrim(YourFieldName),4))
5. Run the Operation.
6. Check that all is ok and if so change the field rule for YourFieldName to auto-inc from "RGA-10001"

Is this the only table with the field? If it is in a set then the related records also need to be modified to preserve integrity.

[EngrDan]

Re: How do I make my auto-increment field bigger?

Thanks for your fix but I have a question.
How do I reset the counter to pick up the RGA-XXXX last number in the file?

[EngrDan]

Re: How do I make my auto-increment field bigger?

When I use the below expression in Expression Builder I get an Error "Expression does not evaluate to a character value. Invalid or incomplete expression.




You�ll need to edit the table where that contains the field and increase the width (if you haven�t done so already).



Then you need to edit your existing data to insert some zeros in the middle.



Left(alltrim(FIELDNAME),4)+�000000000000�+right(alltrim(FIELDNAME),4)

This expression should work (you�ll need to substitute FIELDNAME with the name of your field)



Basically we�re saying take the left four characters of the field, add it to it a bunch of zeros, then add to that the right four characters of the field. The alltrim() just removes blank spaces from the beginning and end - it may not be necessary but it is a good practice.



You�ll do that using an Update Operation. Make a copy of your data first because you cannot undo an Update operation � if you make a mistake you can mess up your data.

[EngrDan]

Re: How do I make my auto-increment field bigger?

if(left(Rga_number,3) = "RGA", "RGA-0" + right(alltrim(Rga_number),4), "RGA-1" + right(alltrim(Rga_number),4))

The field is already 20 characters long. the problem now is its counting RGA-9999 -> RGB-0001, RGB-0002... I tried changing the data entry by adding an initial value which did inter into the filed but when a new record was called for it pickup where it had left off: RGB-0003 instead of RGA-00000000000000010100.

[EngrDan]

Re: How do I make my auto-increment field bigger?

Gary, Dave,
Since I have RGB in some of the fields I trying to use this statement: if(left(Rga_Number,3) = "RGB", "RGA")
"I keep getting Expression does not evaluate to a character expression"
Its suppose to change all "RGB"s to "RGA"s. then I plan to remove the RGA using:
if(left(Rga_Number,3) = "RGA", "000"+”000000000000”+right(alltrim(Rga_number),4))
to add zeros or do I have to add zeros? How can I keep accumulating without adding a bunch of number? Example when the numbers reach accumulate from 10000 to 99999 they will turn over to 100000 and on and on.
Also in the expression builder do I need a start and end statement?

[Mike Wilson]

Re: How do I make my auto-increment field bigger?

Daniel,
I believe the if statement requires three parameters:
if(expression to evaluate, value if true, value if false)

dim rga_number as C="RGX123"

? if(left(Rga_Number,3) = "RGB", "RGA")
ERROR: Too few parameters

try this:

dim rga_number as C="RGA-9999"
dim vnew as C
vnew=if(left(Rga_number,3) = "RGA", "RGA-" + padl(alltrim(right(Rga_number,4)),16,"0"), "RGA-" +padl(alltrim(str(val(right(alltrim(Rga_number),4))+1)),16,"0"))
? vnew
= "RGA-0000000000009999"

rga_number="RGB-0002"
vnew=if(left(Rga_number,3) = "RGA", "RGA-" + padl(alltrim(right(Rga_number,4)),16,"0"), "RGA-" +padl(alltrim(str(val(right(alltrim(Rga_number),4))+1)),16,"0"))
? vnew
= "RGA-0000000000000003"

[EngrDan]

Re: How do I make my auto-increment field bigger?

Mike,
Thanks for answering my message.
I have many questions first after reviewing your message I picked up on the true false, its been awhile since I have programmed. So I ran this operation in the table operations to change "RGB" to "RGA" why didn't this code work: if(left(Rga_Number,3) = "RGB", "RGA", "RGA")

But reconsidering maybe I want to first add 10000 to all "RGB" then go back and remove all "RGA-" so we just have a number. So I would first get
"RGB-1001"...through "RGB-10078" which is the last number thats been add so far with a "B" instead of an "A". then change all "RGA" and all "RGB" to zeros in addition to adding more zeros so I would get:
"00000000000000010001" through "00000000000000010078".

Now since I am not sure how not to print all the leading zeros in a form change them from character to numeric values so the leading zeros will be suppressed in the forms.

Can you help with this? Can you see were I am going with this. The program was poorly designed from the beginning and if I can grasp this I will either change or re-due the program. The more steps might be better for me to learn the programming and maybe not.

Daniel,
I believe the if statement requires three parameters:
if(expression to evaluate, value if true, value if false)

dim rga_number as C="RGX123"

? if(left(Rga_Number,3) = "RGB", "RGA")
ERROR: Too few parameters

try this:

dim rga_number as C="RGA-9999"
dim vnew as C
vnew=if(left(Rga_number,3) = "RGA", "RGA-" + padl(alltrim(right(Rga_number,4)),16,"0"), "RGA-" +padl(alltrim(str(val(right(alltrim(Rga_number),4))+1)),16,"0"))
? vnew
= "RGA-0000000000009999"

rga_number="RGB-0002"
vnew=if(left(Rga_number,3) = "RGA", "RGA-" + padl(alltrim(right(Rga_number,4)),16,"0"), "RGA-" +padl(alltrim(str(val(right(alltrim(Rga_number),4))+1)),16,"0"))
? vnew
= "RGA-0000000000000003"

[Mike Wilson]

Re: How do I make my auto-increment field bigger?

I would not convert the field to numeric. Leave it, duplicate your table giving it another name, paste this code into the interactive window, fill in your table name in the following code where it says "yourtablename", highlight the code and run it (toolbar lightning bolt) against the duplicated table. It might take a minute or two, so I added the progress bar for your viewing pleasure. Let me know how it worked.

	
' set record count and starting numbers of records
        dim tcount as N
	dim vcycle as N=1
	tcount = TABLECOUNT("[COLOR="red"]yourtablename[/COLOR]",".T.")

' open a wait dialog to show the script progress
	DIM GLOBAL pDlg1 as {waitdialog}
	pDlg1.Set_Title("Please Wait")
	pDlg1.Create(1,"Percent")
	pDlg1.Set_Message("")
	pDlg1.Set_Bottom_Message("")
	pDlg1.Set_Percent(Var->vcycle,Var->tcount)

' open table and loop through each record evaluating and changing the rma_number value
	dim t as p
	t=table.open("[COLOR="Red"]yourtablename[/COLOR]")
	t.fetch_first()
	while .not. t.fetch_eof()
	t.change_begin()
		' if RGA is contained in the RGA number, this, else that
		if "RGA" $ t.Rga_number
			t.rga_number="RMA-"+padl(alltrim(str(val(right(alltrim(Rga_number),4))+1)),16,"0")
		else
			t.rga_number="RMA-"+padl(alltrim(str(val(right(alltrim(Rga_number),4))+1001)),16,"0")
		end if
	t.change_end(.t.)

'increment wait dialog counter
	vcycle=vcycle+1
	pDlg1.Set_Percent(Var->vcycle,Var->tcount)
' get next record
	t.fetch_next()
	end while
	t.close()
'close wait dialog
	pDlg1.close()

'completed message
	msgbox("completed")

[EngrDan]

Re: How do I make my auto-increment field bigger?

I renamed the “rgastart.dbt” “reworked rgastart.dbt”
Moved it to another directory
Opened alpha program and selecting “reworked rgastart.dbt”
Hilighted "reworked rgastart" and selected code tab
Right clicked mouse and selected "?Interactive Code"
A Code Window opened
Pasted code
Tried entering four names 1. “reworked rgastart.dbt” , 2.“reworked rgastart”, 3. "reworked_rgastart.dbf" and 4. "reworked_rgastart"
Hilighted all the code pressed the lightening bolt button and received:

Script:Template line:4
tcount = TABLECOUNT("reworked rgastart.dbf",".T.")
Unable to find or open memo file


After posting this I will try a few other thinkings, let me know what I am doing wrong.

[Mike Wilson]

Re: How do I make my auto-increment field bigger?

Dan,
I can't follow what you did, or why you tried to do what you did.

There are no .dbt files in alpha.

What you should have done is go to the control panel->tables. highlight the table of interest, right mouse click Utilities->duplicate. Use that table to run the script.

[Al Buchholz]

Re: How do I make my auto-increment field bigger?

I renamed the �rgastart.dbt� �reworked rgastart.dbt�

After posting this I will try a few other thinkings, let me know what I am doing wrong.

a .dbt is an Alpha 4 file that holds memo data...

Are you working in DOS or Windows or converting?

[EngrDan]

Re: How do I make my auto-increment field bigger?

Mike,
I made a typo, it should have read ".dbf" sorry for the confusion. I will try duplicating as you mentioned above. Thanks I'll let you know what happens today.

[EngrDan]

Re: How do I make my auto-increment field bigger?

Mike,
I made a typo, it should have read ".dbf" sorry for the confusion. I will try duplicating as you mentioned above. Thanks I'll let you know what happens today.

Mike,
Thanks for your patience. Please review the attached jpg. It pictures Rga_number not found and the file with Rga_number in it.
Dan

[Mike Wilson]

Re: How do I make my auto-increment field bigger?

Dan,
Are you sure you put in the correct table name? The one in your photo reads: reworked_rgastart_dbf.dbf. Therefore, the table pointer should be:

.....
dim t as p
t=table.open("reworked_rgastart_dbf")
.....

Is that what you have? Also, my code mistakenly has the first letters being changed to RMA instead of RGA. You might want the correct that.