5 Replies Latest reply on Feb 20, 2013 8:33 AM by flybynight

Serial number, or sequence as letters instead of numbers?

We have JobTickets that have multiple "Jobs" on them, and everyone is used to each Job being lettered A, B, C, D… etc.

I have it set that each time someone creates a new Job on the JobTicket, it is assigned a number 1, 2, 3, 4… etc.

The reason behind this (besides the fact that people don't like change) is that elsewhere Jobs are stored on the server under their Job number and any given ticket may have Jobs like 1234A, 1234B, 1234C, 1234D… etc. where "1234" is the JobTicket number and A, B, C, D… etc. is the individual Job. If that letter were a number, it would be visually confusing because it would look like a different number.

Right now, I just have a calculation that concatenates the JobTicket number with a Case statement that checks for each number and changes it to a letter… of course this gets pretty lengthy:

Case ( JobPart# = 1; "A" ;

JobPart# = 2; "B" ;

JobPart# = 3; "C" ;

JobPart# = 4; "D" ;

...

JobPart# = 24; "X" ;

JobPart# = 25; "Y" ;

JobPart# = 26; "Z" ;

JobPart# = 27; "A1" ;

JobPart# = 28; "B1" ;

etc, etc, etc… )

There is no hard limit on how many Job parts might be on a JobTicket. Right now I just have it going as high as I think I might need, then have an ending value that will create an error - this will notify me that I need to add more Case options.

If anyone knows an easier way that I might be overlooking, please let me know.

Thanks!

-Shawn

• 1. Re: Serial number, or sequence as letters instead of numbers?

Try this...

Let([

job.number = JobPart#;

alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ;

mod.number = Mod ( job.number ; 26 ) ;

adj.mod.number = If ( mod.number = 0 ; 26 ; mod.number ) ;

letter = Middle ( alpha ; adj.mod.number ; 1 ) ;

div.number = Div ( job.number - 1 ; 26 ) ;

number = If ( div.number > 0 ; div.number ) ;

result = If ( job.number > 0 ;  letter & number )

];

result

)

• 2. Re: Serial number, or sequence as letters instead of numbers?

Hi Shawn

Here is my version of the formula you're looking for;

Char ( Mod ( PartNo - 1; 26 ) + 65 ) & Case ( PartNo > 26 ; Div ( PartNo - 1 ; 26 ))

This works up to PartNo '260' = 'Z9', before it adds another digit.

So PartNo '261' = 'A10' and this formula will continue to auto-increment the digits as required.

Best wishes - Alan Stirling, London UK.

• 3. Re: Serial number, or sequence as letters instead of numbers?

I trust you are not using this human readable identifier field in relationship matching.  Just a thought!

• 4. Re: Serial number, or sequence as letters instead of numbers?

Oh, goodness, no. I have a unique __pk_TableName_ID field for each table, that does not get touched. That ID field is also generally hidden from my users, because to them, it means nothing… and I have other, "human readable" numbers and identifiers that they actually interact with.

• 5. Re: Serial number, or sequence as letters instead of numbers?

That worked beautifully. Thank you!

Although I shudder at the thought of a JobTicket with 260+ parts to it… at least the solution is ready for it this way.

Thanks!

-Shawn