populate 2 character field with 400 unique digits

# populate 2 character field with 400 unique digits

• 26. července 2012 15:49

I have a table Dept.dbf
(NO char(2), DEPT char(15))
I built a program to extract from an employee.dbf the unique names of the departments so the dept char(15) is automatically created.
If there are up to 99 departments the code given to me by Naomi works perfectly. Using the record number I can:

My question is if I have 400 departments but I still have the 2 character field. How can I code the program to automatically create a unique 2 character field such as 1A, 1B...1Z, 2A, 2B..2Z  and so on.

Is this possble? Thanks in advance for code.

Calecrow

### Všechny reakce

• 26. července 2012 20:21
Přispěvatel

Personally, I wouldn't use a 2-character field as a primary key for any table. Just use an Integer and your problems go away.

If you really feel you need to do this, here's a function that gives you what you want:

LPARAMETERS nValue

LOCAL nNumber, nRemainder, cDigit, cLetter

nNumber = INT((nValue-1)/26) + 1
nRemainder = MOD(nValue-1, 26)

cDigit = TRANSFORM(m.nNumber)
cLetter = CHR(ASC("A") + m.nRemainder)

RETURN m.cDigit + m.cLetter

Tamar

• Označen jako odpověď 26. července 2012 21:28
•
• 26. července 2012 21:27

Thanks Tamar.

I have no choice to change the size of the 2 characters. It is a given in the existing program.

Calecrow

• Označen jako odpověď 26. července 2012 21:28
•
• 26. července 2012 22:04

The function provided by Tamar is ok, but for numbers greater than 234  will give you 3 character codes.

here's the same function modified to convert any number to a base 36 number ( 0-9 A-Z ) so you can represent 36*36-1 = 1295 with 2 characters:

function x
LPARAMETERS nValue

LOCAL nNumber, nRemainder, c1,c2

nNumber = INT(nValue/36)
nRemainder = MOD(nValue, 36)

c1 = transform(iif(nNumber>9,chr(65-10+nNumber),nNumber))
c2 = transform(iif(nRemainder>9,chr(65-10+nRemainder),nRemainder))

RETURN c1+c2

Marco Plaza

• 27. července 2012 0:01

Marco, your code is more what I had in mind but I don't know where to plug it in. Here is the simple.prg

COPY FILE employee.dbf TO temploy.dbf
USE temploy.dbf EXCLUSIVE
go top
index on dept to deptndx
go top
store dept to mdept
skip
do while .not. eof()
if dept = mdept
dele
else
store dept to mdept
endif
skip
enddo
pack
go top
sort on dept to sdept.dbf
clos data

USE dept.dbf EXCLUSIVE
zap
APPEND FROM sdept.dbf
replace all NO with padl(Recno(),2,'0') &&& this is the code for up to 99 departments

GO TOP
INDEX on No+dept TAG Dept
GO TOP
RECCOUNT()
messagebox("        Congratulations!"+CHR(13)+;
"You have "+transform(RECCOUNT())+" departments.","Department Builder",64,-1)

Can you help me with this.

Calecrow

• 27. července 2012 0:48

name the function num2base36(nValue)

and

replace all no with num2Base36(recno()) all

Marco

Marco Plaza

• 27. července 2012 1:22

Where in the simple.prg do I name the function num2base36(nValue). What is the exact line of code.

And I assume the

replace all NO with num2base36(recno()) all

is where the

replace all NO with padl(Recno(),2,'0') &&& this is the code for up to 99 departments

Yes??

• 27. července 2012 12:59

yes Cale.. copy and paste the function at the end of your program.

Function num2Base36(nValue)

LOCAL nNumber, nRemainder, c1,c2

nNumber = INT(nValue/36)
nRemainder = MOD(nValue, 36)

c1 = transform(iif(nNumber>9,chr(65-10+nNumber),nNumber))
c2 = transform(iif(nRemainder>9,chr(65-10+nRemainder),nRemainder))

RETURN c1+c2

Marco Plaza

• 27. července 2012 13:52

Hey Marco,

I placed the function after the dept.dbf was built but the replace command doesn't work- here is the err

REPLACE all NO with num2Base36(recno())all
Error in line 96: Command contains unrecognized phrase/keyword.

If I place the function at the end of the program then when it comes to the line replace all...it doesn't know what I'm referring to. I know this is what I want but it doesn't insert the characters.

```COPY FILE employee.dbf TO temploy.dbf
USE temploy.dbf EXCLUSIVE
go top
index on dept to deptndx
go top
store dept to mdept
skip
do while .not. eof()
if dept = mdept
dele
else
store dept to mdept
endif
skip
enddo
pack
go top
sort on dept to sdept.dbf
clos data
USE dept.dbf EXCLUSIVE
zap
APPEND FROM sdept.dbf
function num2Base36(nValue)
LOCAL nNumber, nRemainder, c1,c2
nNumber = INT(nValue/36)
nRemainder = MOD(nValue, 36)
c1 = transform(iif(nNumber>9,chr(65-10+nNumber),nNumber))
c2 = transform(iif(nRemainder>9,chr(65-10+nRemainder),nRemainder))
RETURN c1+c2
REPLACE all NO with num2Base36(recno())all
GO TOP
INDEX on No+dept TAG Dept
GO TOP
RECCOUNT()
messagebox("        Congratulations!"+CHR(13)+;
"You have "+transform(RECCOUNT())+" departments.","Department Builder",64,-1)
CLOSE DATABASES
erase sdept.dbf
erase deptndx.idx
ERASE temploy.dbf
quit```
Thanks
• 27. července 2012 14:03

The ALL keyword appears twice:

REPLACE all NO with num2Base36(recno())all

correct:

REPLACE all NO with num2Base36(recno())

Marco Plaza

• 27. července 2012 14:12

I deleted the all and now I do not get the error however it still doesn't insert any characters in the first field NO. Something is missing....
Also I noticed it doesn't get to the Recount to say how many records I have so it seems to stop at the replace all command.
• Upravený 27. července 2012 14:17
•
• 27. července 2012 16:08

Hi Cale, I did'nt check your prg. The functions must be placed at the bottom of the list:

```COPY FILE employee.dbf TO temploy.dbf
USE temploy.dbf EXCLUSIVE
go top
index on dept to deptndx
go top
store dept to mdept
skip
do while .not. eof()
if dept = mdept
dele
else
store dept to mdept
endif
skip
enddo
pack
go top
sort on dept to sdept.dbf
clos data
USE dept.dbf EXCLUSIVE
zap
APPEND FROM sdept.dbf
REPLACE all NO with num2Base36(recno())
INDEX on No+dept TAG Dept
RECCOUNT()
messagebox("        Congratulations!"+CHR(13)+;
"You have "+transform(RECCOUNT())+" departments.","Department Builder",64,-1)
CLOSE DATABASES
erase sdept.dbf
erase deptndx.idx
ERASE temploy.dbfquit*--------------------------------------function num2Base36(nValue)*--------------------------------------
LOCAL nNumber, nRemainder, c1,c2
nNumber = INT(nValue/36)
nRemainder = MOD(nValue, 36)
c1 = transform(iif(nNumber>9,chr(65-10+nNumber),nNumber))
c2 = transform(iif(nRemainder>9,chr(65-10+nRemainder),nRemainder))
RETURN c1+c2

```

Marco Plaza

• Upravený 27. července 2012 16:09
• Označen jako odpověď 27. července 2012 16:24
•
• 27. července 2012 16:24

Jackpot...it worked perfectly. Thank you for your time and attention.

Calecrow

• 27. července 2012 16:33