Alpha Numeric

[Gambler]

Is there a way to create a unique sequential field (primary field) that uses alpha-numeric.

Limit field length to say five characters.

For example,

AA001

AA002

AA003

up to AA999 and then flip to

AB001

AB002

AB003

up to AB999 and then flip to AC001, etc.....

[Vaughan]

An untried suggestion: enter a "normal" serial number. Create a calculation field that converts the serial number into the alpha/numeric format, and only display this a/n version. It cannot be modified by the user, but that could be good.

[Fitch]

2 fields:

1. A standard serial number (SN) that begins at 11001

2. A calculation:

Left(

Substitute(Substitute(Substitute(SN, "1", "A"),"2", "B"), "3", "C")

, 2)

& Right(SN, 3)

Just copy and paste that Substitute 23 more times and you're home!

[Gambler]

"Cut & Paste 23 more times"

I guess I will not be making many license plates with fmp.

Isn't this function built in?:

[Keith M. Davie]

Yes it is built in.

Define Fields / Options / Auto-Enter / [] Serial Number / next value

Set next value (enter) AA 001.

If you already have records there is a way to assign these incremented values, though I'd need to research that, and you can research as well as I.

Otherwise if there are no records this should be the number entered into the first record created.

You will probably need to test this to make sure the incrementations are what you want. The AA will not be the problem. But the formatting of 01 or 001 or 0001 or even 00001 needs to be examined by you to make sure you get what you want.

What ever you do, back-up first.

[danjacoby]

This could be the beginning of a solution:

Two fields: A number field that auto-enters a serial number.

A calc field:

code:

---

Int(SerialNoAuto / 676) & Mod(SerialNoAuto, 676)

The final step is converting the first part of the calc (the "Int" part) to a two-letter format. It could be done with a 676-line "Case" statement, or two 26-line "Case" statements, but there may be a more elegant method.

Any ideas?

[danjacoby]

Ignore previous telegram, only smoke damage.

Here is a long-ish code that works pretty well -- only one caveat:

code:

---

Case(Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 1,"A",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 2,"B",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 3, "C",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 4, "D",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 5, "E",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 6, "F",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 7, "G",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 8, "H",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 9, "I",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 10, "J",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 11, "K",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 12, "L",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 13, "M",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 14, "N",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 15, "O",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 16, "P",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 17, "Q",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 18, "R",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 19, "S",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 20, "T",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 21, "U",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 22, "V",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 23, "W",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 24, "X",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 25, "Y",

Int(If(Length(SerialNo) = 5, Left(SerialNo, 2) / 26, Left(SerialNo, 3) / 26)) = 26, "Z") &

Case(Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 0, "A",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 1, "B",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 2, "C",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 3, "D",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 4, "E",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 5, "F",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 6, "G",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 7, "H",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 8, "I",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 9, "J",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 10, "K",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 11, "L",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 12, "M",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 13, "N",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 14, "O",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 15, "P",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 16, "Q",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 17, "R",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 18, "S",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 19, "T",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 20, "U",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 21, "V",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 22, "W",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 23, "X",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 24, "Y",

Mod(If(Length(SerialNo) = 5, Left(SerialNo, 2), Left(SerialNo, 3)), 26) = 25, "Z") &

Right(SerialNo, 3)

Then set the field "SerialNo" to auto-enter, incrementing by 1, with the first number being 26000.

Now the caveat: It will go from AA999 to AB000, then AB001, etc.

[Fitch]

Grrrr... Look, first of all, there's no built-in way to increment by letter, only by number. So you have to do it via calculation. Second... hmmm, never mind... it turns out that my idea has a limitation, it only handles the digits 1-9 in the alpha part of the serial number, i.e. it only gets you to the equivalent of 99,999 (ii999) That would be this:

Left(

Substitute(Substitute(Substitute(Substitute(Substitute

(Substitute(Substitute(Substitute(Substitute

(SN, "1", "A"),"2", "B"), "3", "C"), "4", "D"),

"5", "E"), "6", "F"), "7", "G"), "8", "H"), "9", "I")

, 2)

& Right(SN, 3)

It wouldn't be that tough to fix this up to get you all the way to Z, IMO it's a more elegant but less geeky fun way than Dan's, and I'm sure we could even get around Dan's "000" problem (good catch!) --- but I get the feeling that you've already lost interest.

[Gambler]

Haven't lost interest. Still need a solution. I'm just overwhelmed by the degree of difficulty required to jump what should be an easy hurdle.

I need this to work -- I'll try each suggested solution. Thank god for cut & paste, especially given Dan's solution.....

Thank you.

[djgogi]

quote:

---

Originally posted by BarryZ:

Haven't lost interest. Still need a solution. I'm just overwhelmed by the degree of difficulty required to jump what should be an easy hurdle.

I need this to work -- I'll try each suggested solution. Thank god for cut & paste, especially given Dan's solution.....

Thank you.

---

Well if you don't mind 000 here's the solution

code:

---

pos1=Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Int(If(Left(serial, Length(serial)-3) != "", Left(serial, Length(serial)-3), 0)/26), 1)

pos2=Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mod(If(Left(serial, Length(serial)-3) != "", Left(serial, Length(serial)-3), 0), 26)+1,1)

final=pos1&pos& Right(serial, 3)

where serial is defined as text serial 001 next 1

mathematics is not an opinion

HTH

Dj

[djgogi]

However you'll have only 676000 serials

Dj

[danjacoby]

quote:

---

Originally posted by dj:

However you'll have only 676000 serials

---

Yes, and I have so many databases with over 676000 records!

[Fitch]

dj, that is truly a super cool formula, except that 1000 = 27000, 2000 = 28000 etc. I've got 5 stars with your name on them if you can make it work properly.

[Gambler]

dj, I like your solution -- very clean -- I'm going to try it.

676,000 -- File size is actually bigger because the records will have eight digits (AA000001), not six. I think if I'm doing my factoring correctly, that jumps it to 600 million -- should be enough.

[Rigsby]

Ok, this isn’t going to get me 5 stars, LOL, but it might help me keep them….

The first thing is that using aa001 through to zz999, you will have a maximum of 675324 records. Hey, just making a point, I have worked on DBs with millions before.

However, if this is within your range, then lets stick with the 5-digit range, however, once you see this, you’ll also see that it can easily be developed to go as far as you want with the range of serials.

This solution works not on a calculation, but on a relationship. But it works, and it works well. To test this, I set the whole thing up in about 30 minutes.

In your main database, create a very normal run-of-the-mill serial number field (SerialNumber). So, starting at 1 it should auto-enter serials incrementing by 1.

Now create a secondary database, containing the following fields:

SerialNumber

Number Serial Number with Current Value: "1" Increment: "1" Indexed

TextGlobal

Global (Text)

Counter999

Global (Number)

CounterLetter1

Global (Number)

CounterLetter2

Global (Number)

CounterSerial

Text Auto-enter calculation

= TextGlobal & Right("00" & Counter999, 3)

Alphabet Global

(Text) Repeating field with 26 repetitions

(Fill this global field with the letters of the alphabet)

The trick now is to create all the possible combinations of serials in the related database. However, who wants to spend the time entering all of that?….. My advice….. use a script to do it for you. Set the thing going and go and get a coffee.

NB: If you have tested this, make sure to reset your serials to 1 or this won’t work.

So, here the script based on the above fields:

Set Field Counter999

Calculation

1

Set Field CounterLetter1

Calculation

1

Set Field CounterLetter2

Calculation

1

Set Field TextGlobal

Calculation

"AA"

New Record/Request

Loop

Set Field Counter999

Calculation

Counter999 + 1

If Calculation

Counter999 > 999

Set Field Counter999

Calculation

1

Set Field CounterLetter2

Calculation

CounterLetter2 + 1

If Calculation

CounterLetter2 = 27

Set Field CounterLetter1

Calculation

CounterLetter1 + 1

Set Field CounterLetter2

Calculation

1

If Calculation

CounterLetter1 = 27

Set Field CounterLetter1

Calculation

1

End If

End If

End If

Set Field TextGlobal

Calculation

GetRepetition( Alphabet, CounterLetter1) & GetRepetition( Alphabet,

CounterLetter2)

New Record/Request

Exit Loop If Calculation

TextGlobal = "zz" (PS I didn't let it run to the end to test this, so you might need to exit another way)

End Loop

Once you’ve done this, create a relationship between SerialNumber and SerialNumber in your main file to secondary file. Now create a field in your main file for your serials (AlphNumSerial) for your serial numbers and set this to a looked up value based on SerialNumber::SerialNumber, it should set itself to the field CounterSerial from your related DB.

Ok! I hope you’ve followed at least most of this. The main point is that it works, and very elegantly. It also uses look-ups, so the unique serials (AA0001 for example) can still be indexed, so also used as right-hand key-fields

I’ve tried to format this so that you can copy and paste, but if you want I can send you my demo. However, if you want the demo, send me an email to let me know.

This might seem a little arrogant, but I think in this case my motto (See below) has been shown to be true.

RIGSBY

PS: I kind of missed Old Advanced Man in this Forum! LOL

[BobWeaver]

Here's a slightly simpler formula:

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(SN/26000000),26)+1,1)&

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(SN/1000000),26)+1,1)&

Right("000000"&Mod(SN,1000000),6)

This will give two letters followed by 6 digits.

AA000001 through ZZ999999

[Fitch]

Mod(Int(BobWeaver/26000000),26) = god

[Gambler]

Bob

Very simple solution. Now can you explain how I would implement use. I've tried formula and I keep getting an error message "field "SN" would cause a circular defenition."

Thank you.

[Gambler]

Bob

Do I set "SN" as a hidden field (not visible to user)?? Not sure how fmp handles "hidden fields".

Thank you.

[Fitch]

The name of the field where you are pasting Bob's 5-star calculation cannot be "SN," because the field "SN" is used in the calculation. That's why you get the "circular definition" error. Name the calc field something else, like "AlphaNumeroBobWeaverCalc."

[Fitch]

As for "hidden" fields, simply don't put the field on your layout. A field does not necessarily have to appear on ANY layout in order to be evaluated by a calculation. In this case I would say you are correct, you probably should remove the SN field from the layout. (There are other ways to "hide" a field, e.g., remove it from the tab order and/or disallow entry and set it to transparent fill, and make the text the color of the background.)

[djgogi]

quote:

---

Originally posted by Fitch:

dj, that is truly a super cool formula, except that 1000 = 27000, 2000 = 28000 etc.

I've got 5 stars with your name on them if you can make it work properly.

---

Sorry gys I was out for weekend:

I knew it wouldn't work, cause I didn't test it:

But now here comes the true one.

First, to explain the procedur.

So basicly we have to transform an decimal number to base 26.

i've implemented here the formula wich would work for up to three letters.

Also there's a flag "allowZeros" for allowing/dissallowing trailing zeros, ie AB000->AB001.

The fields we need are:

code:

---

serial, serial number, text starting from 0000000000 with step 1

digits, global number // cicling number ie AA001..AA999

corrector=

Truncate(serial/ Right("9999999999", digits),0)+1 //, calc num

serialNoZeros= Right(10 ^ digits, digits-Length(serial +corrector) ) & (serial +corrector) // calc text

num=Int(If(allowZeros,serial,serialNoZeros)/10 ^ digits) // calc text

Now we have to simulate the iterative procedure to convert decimal to n-base number.

you should extend this part for more than tree characters

code:

---

First fields

step1=Int(num/26) //less significant

step2=Int(step1/26)

step3=Int(step2/26,0)

continue if you need more...

pot0=num-step1*26

pot1=step1-step2*26

pot2=step2-step3*26

continue if you need more...

alfaPos0=Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ", pot0+1, 1)

alfaPos1=Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ", pot1+1, 1)

alfaPos2=Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ", pot2+1, 1)

continue if you need more...

Of course you can put it together

alfaPos0=Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ", num-Int(num/26,0)*26+1, 1)

ect.

Finally

alfa=alfaPos2&alfaPos1&alfaPos0

and our alfanumeric serial

alfa&Right((If(allowZeros,serial,serialNoZeros), digits)

[ April 02, 2002, 11:18 PM: Message edited by: dj ]

[djgogi]

Forgot to mention

digits=0 only alfa ex. AAA, AAB ect

digits=1 AAA1 , AAA2 ... AAA9,AAB1 ect

digits=2 AAA01..AAA99, AAB01 ect

HTH

Dj

[ April 02, 2002, 05:40 AM: Message edited by: dj ]

[djgogi]

If you don't want to use all 26 letters than create an global text field "mychaes" for ex, and put there only letters you want. Than create an calc field like this

code:

---

count=Length(myChars)

Now in the code replace number 26 with count.

Ie you can use part of this formula to convert an decimal number to any base you want.

HTH

Dj

[BobWeaver]

Barry,

Sorry, as Fitch pointed out, and I neglected to mention SN is set up as a basic auto-enter serial number field, and the formula that I gave should be assigned to a calculation field (result type text) with some other name like "AlphaSN". The original SN field doesn't need to be on the layout, just the calculated field.

[djgogi]

quote:

---

Originally posted by BobWeaver:

Barry,

Sorry, as Fitch pointed out, and I neglected to mention SN is set up as a basic auto-enter serial number field, and the formula that I gave should be assigned to a calculation field (result type text) with some other name like "AlphaSN". The original SN field doesn't need to be on the layout, just the calculated field.

---

To bad a didn't give attention to your post before writing my reply. Basiclally I gave the same formula which already you have posted.

Well, I have to say that your solution is more elegant that one I posted. So here's what I'd wrote after reading first your posting.

As I said it's a number base convertion problem.

To refresh our memory:

given an base b>0 than an number num can be presented as an finite serie like this.

code:

---

num=An*b^n +An-1*b^n-1 + ...+ A1*b^1 + A0*b^0

where An=Mode(Int(num/b^n),:.

So in our case we have to convert an decimal number to base 26.

code:

---

Assuming we have fields

SN defined as text, init value="000000000" with step 1

digits, global number specifying number of trailing digits

chars, global number specifying number of leading chars

allowZeros, global number , flag which allow/disallow all zeros

corrector=Int(SN/ Right("999999999", digits))+1

serialNoZeros= Right(10 ^ digits, digits-Length(SN +corrector) ) & (SN +corrector) // calc text

num=Int(If(allowZeros,SN,serialNoZeros)/10 ^ digits) // calc text part of SN whish we have to convert

alfanumericSN named alfa is given (assumed max 10 chars)

Right

(

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^9),26)+1,1) &

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^8),26)+1,1) &

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^7),26)+1,1) &

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^6),26)+1,1) &

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^5),26)+1,1) &

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^4),26)+1,1) &

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^3),26)+1,1) &

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^2),26)+1,1) &

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^1),26)+1,1) &

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mod(Int(num/26^0),26)+1,1) ,

chars)

&

Right

(

If(allowZeros,SN,serialNoZeros), digits

)

Good job Bob

Dj

[ April 03, 2002, 01:33 PM: Message edited by: dj ]