populate 2 character field with 400 unique digits

Question

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:

replace all NO with padl(Recno(),2,'0')

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

Answer 1

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

Answer 2

Thanks Tamar.

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

Calecrow

Answer 3

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

Answer 4

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

Answer 5

name the function num2base36(nValue)

and

replace all no with num2Base36(recno()) all

Marco

---

Marco Plaza

Answer 6

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??

Answer 7

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

Answer 8

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
&&replace all NO with padl(Recno(),2,'0')
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

Answer 9

The ALL keyword appears twice:

REPLACE all NO with num2Base36(recno())all

correct:

REPLACE all NO with num2Base36(recno())

---

Marco Plaza

Answer 10

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.

Answer 11

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.dbf
quit
*--------------------------------------
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

Answer 12

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

Calecrow

Answer 13

Mark the answer / helpful ;-)

---

Marco Plaza