How to serialize with Hexidecimal #'s

[MarkFuller]

I would like to create an auto incrementing serial number field to print some sheets of labels that are Mac Addresses (Media Access Control) numbers.

The format is 00-00-00-AA-AA-AA

each pair of numbers are hexidecimal numbers so it will increment from 00 thru 09 then 0A, 0B, 0C, 0D, 0E, 0F, 10, etc.

Say I want to have a sheet of labels with 10 labels per sheet.

Each label on a sheet will be its own field which will increment by 10 (10 per sheet)

Is there any easy way to have filemaker use Hexidecimal numbers in the autoentry serial number field?

It seems like someone would have run across this already?

Thanks in advance,

Mark

[BobWeaver]

I realize that you are working with FM6, but perhaps you can split out the following calculation Let() function into separate intermediate calculation fields.

This formula adds together two 4 digit hexadecimal fields A and B. In your case field A would be previous value, and field B would be the increment (constant = 1). The result is a 4 or 5 digit hex number. You would have to expand it to handle the extra digits (which should be self-evident from the formula), and then do the text formatting to insert the dashes.

Let([

S="0123456789ABCDEF";

N1=Substitute(A;["0";"00"];["1";"01"];["2";"02"];["3";"03"];["4";"04"];["5";"05"];["6";"06"];["7";"07"];

["8";"08"];["9";"09"];["A";"10"];["B";"11"];["C";"12"];["D";"13"];["E";"14"];["F";"15"]);

N2=Substitute(B;["0";"00"];["1";"01"];["2";"02"];["3";"03"];["4";"04"];["5";"05"];["6";"06"];["7";"07"];

["8";"08"];["9";"09"];["A";"10"];["B";"11"];["C";"12"];["D";"13"];["E";"14"];["F";"15"]);

N3=N1+N2;

N4=N3+(Mod(n3;100)>15)*84;

N5=N4+(Mod(n4;10000)>1599)*8400;

N6=N5+(Mod(n5;1000000)>159999)*840000;

N7=N6+(Mod(n6;100000000)>15999999)*84000000;

X=Right("0000000000"&N7;10)

];

Middle(S;1+Middle(X;1;2);1)&Middle(S;1+Middle(X;3;2);1)&Middle(S;1+Middle(X;5;2);1)&Middle(S;1+Middle(X;7;2);1)&Middle(S;1+Middle(X;9;2);1)

)

There are of course simpler auto-increment formula that will likely be easier to implement in version 6, but I just happened to have this one handy, so it may give you some ideas.

Edited May 1, 2006 by Guest

[comment]

It's not too difficult, but rather tedious. First, use Filemaker's serial number generator to generate the numbers (as regular decimal numbers). Then use a calculation field to convert the serials to the MAC address format.

Now this is where it gets cumbersome. In version 7D or 8A, you could use a custom function to do a recursive calculation. Without it, you need to compute each one of the 12 bits, convert each to an alphanumeric character, and combine them into pairs (octets).

To compute the bits:

bit1 = Mod ( Serial ; 16 )

bit2 = Int ( Mod ( Serial ; 16^2 ) / 16 )

bit3 = Int ( Mod ( Serial ; 16^3 ) / 16^2 )

bit4 = Int ( Mod ( Serial ; 16^4 ) / 16^3 )

bit5 = Int ( Mod ( Serial ; 16^5 ) / 16^4 )

...

To convert to alphanumeric:

char1 = Middle ( "0123456789ABCDEF" ; bit1 + 1 ; 1 )

char2 = Middle ( "0123456789ABCDEF" ; bit2 + 1 ; 1 )

char3 = Middle ( "0123456789ABCDEF" ; bit3 + 1 ; 1 )

char4 = Middle ( "0123456789ABCDEF" ; bit4 + 1 ; 1 )

char5 = Middle ( "0123456789ABCDEF" ; bit5 + 1 ; 1 )

...

To construct the final address:

char1 & char2 & "-" & char3 & char4 & "-" & char5 & ...

---

I didn't get the 'increment by 10' part.

[MarkFuller]

Thanks very much to you both. I think I can put it toghether with your help.

---

I didn't get the 'increment by 10' part.

The increment by 10 is because there will be 10 labels printed on each page (in this example) so each field should increment by 10 every time a new record is made, to have multiple pages increment in sequence. Hopefully that makes sense

Thanks again, this site is a great resource.

Mark

[comment]

Hopefully that makes sense

Not really. I'd think each label should be a record, with only one number. Print them using a label layout.