hello

i have a piece of software that contains a lot of data which i bring into filemaker. The problem is that i need to tell the software through a quickey to look up the next value. i am not sure how to make a field in filemaker that will;

get the next value of the alphabet in other words i need it to calculate the following values , i am just not sure how to script it to go through the numbers and the alphabet.

a1,a2,a3,a4,a5,a6,a7,a8,a9,aa,ab,ac,ad,ae,af,ag,ah,ai,aj,ak,al,am,an,ao,ap,aq,ar,as,at,au,av,aw,ax,ay,az,b1,b2,b3 etc

So when filemaker reaches the last record from the other database of

'aa' it then takes the next value to be calculated in a field called 'lookupvalue' and using a quickey goes to the other application and would then paste in 'ab' . Can anyone help me understand how to either get the next value or calculate the next value in the series i have outlined above?

Thanks Very very much

Dave

Hello Dave,

If the current alphanumeric value (eg 'aj') is in a text field in FileMaker called 'CurrentAlpha', you will be able to calculate the next alphanumeric value (ie 'ak') according to the schema you outlined in your post, using the following formula:

Middle("0123456789abcdefghijklmnopqrstuvwxyz", Int(Mod(((Position("0123456789abcdefghijklmnopqrstuvwxyz", Left(CurrentAlpha, 1), 1, 1) - 1) * 36) + (Position("0123456789abcdefghijklmnopqrstuvwxyz", Right(CurrentAlpha, 1), 1, 1)), 36^2)/36^1) + 1, 1) & Middle("0123456789abcdefghijklmnopqrstuvwxyz", Int(Mod(((Position("0123456789abcdefghijklmnopqrstuvwxyz", Left(CurrentAlpha, 1), 1, 1) - 1) * 36) + (Position("0123456789abcdefghijklmnopqrstuvwxyz", Right(CurrentAlpha, 1), 1, 1)), 36^1)/36^0) + 1, 1)

Essentially this is a manipulation of base 36 numbering, and will produce up to 1295 unique sequential values from 01 to zz within the confines of the two digit limitation.

Thank you so much Ray. This is just great. it has helped my understanding tremendously. you have been so helpful to me. Thanks again

Dave