6 Replies Latest reply on Dec 6, 2011 5:41 AM by Jason_Farnsworth

# Counting

Is there a way to count using letter rather than numbers?

What I need is

A,B,C,D.....,AA,BB,CC,DD....,AAA,BBB,CCC,DDD....ETC.

And then once I have the numbers skip all the I's and O's ..

Its a strange need

Thanks

Jason

• ###### 1. Re: Counting

Hey Jason,

If you know there will never be more than 26, then you can do this:

Middle ( "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ; whereverYouGetTheNumberFrom ; 1 )

If it is more than 26, but you know the top limit of number, you could do some hard-coding with the GetValues or Choose function. If not, then we'll need to get clever. Someone may have already written a custom function for this on Brian Dunning's site.

By the way, after Z, your pattern should go like this: AA, AB, AC … AZ, BA, BB, BC …

Then it won't get so wide so fast.

Paul

--

Paul Spafford

FileMaker Database Superhero,

Spafford Data Solutions

Tel: 613 838 9956

www.paulspafford.com

www.ottawafilemakerdeveloper.com

www.fmlayoutmode.com

www.fmscripts.com

FileMaker User Group in Ottawa:

www.meetup.com/fudgeo

1 of 1 people found this helpful
• ###### 2. Re: Counting

This will work in a general case:

~num should be whatever number you try to convert.

~string is the regular alphabet here, but if you remove the I and the O from the string, it will still return correct results.

Let([

~num = 25 ;

~string = Upper ( "abcdefghijklmnopqrstuvwxyz" ) ;

~length = Length ( ~string )  ;

~position =  Mod ( ~num ; ~length )  ;

~last =  ( ~position = 0 ) ;

~position = If ( ~last ; ~length ; ~position ) ;

~value = Middle ( ~string ; ~position ; 1  ) ;

//reps will determine the number of 0s

~reps = Div ( ~num ; ~length ) - ( ~last ) ;

~rep0 = 10^ ( ~reps + 1 ) ;

~repValue = Substitute ( ~rep0 ; [ 0 ; ~value ] ; [ 1 ; "" ] ) ;

~result = ~repValue

];

~result

)

Message was edited by: DavidJondreau to change variable names for clarity.

Message was edited by: DavidJondreau. Corrected error in calc.

• ###### 3. Re: Counting

Hi Jason,

The purpose determines the solution and we do not yet know the purpose of this alpha number.  Should these numbers change according to the found set?  If you delete a record, should the numbers adjust?

As Paul suggests, the standard method of enumerating alpha past the Z is then AA, AB, AC.  By using the AA, BB, CC structure in your opening post, the 160th record will be RRRRRRR whereas standard alpha counting would produce FR.  You will very quickly increment into very large text strings using your counting method.

I would like to offer an alternate solution (if only for others who may read this and want standard count method).  Here is one such custom function by Michael Horak (DecToAlpha): http://www.briandunning.com/cf/399.  Modify the custom function by adding a comment similar to "12/3/2011 Joe removed I and O from Alpha."  In this way, the author's original custom function and intent is preserved.  Then create a calculation (result is text) with:

DecToAlpha ( Get ( RecordNumber ) - 1 )

Be sure to uncheck 'do not store calculation results' in the calculation Storage Options.  This is for display purposes only and it will change with the found set.

ADDED:  BTW, this CF only lists a four-character code (AAZZ) by record 15,000 so the enumeration method is more concise and it also shows this custom function will not break regardless of size.

Opps, typo. Calc should be unstored of course!

Message was edited by: LaRetta

• ###### 4. Re: Counting

Hi David!

I may have something wrong in trying out your calculation.  To use it, I removed the I and O and changed ~num = Get ( RecordNumber ) instead of 29.  It does not produce the single Z on record 24 but instead produces AA on records 24 and 25.  And by record 192, which normally would produce GZ, I get .Ae+9.  By record 9696, I only get ? whereas standard methods produce RUZ.

This method of counting is a good alternative and I am sure you can either tell me how to adjust how I implement your calculation or you can fix it.

• ###### 5. Re: Counting

Good catch.

1) I wasn't accounting for Mod() = 0 correctly. I've made a change to the calc below...

2) at values somewhere about 175, the calc breaks down because Filemaker's scientific notation kicks in and my trick of subbing zeroes on 10^~reps breaks down. Don't know how to get around that. But unless the OP needs it to work on AAAAAA, I'm not going to bother.

Let([

~num = Get ( RecordNumber ) ;

~string = Upper ( "abcdefghjklmnpqrstuvwxyz" ) ;

~length = Length ( ~string )  ;

~position =  Mod ( ~num ; ~length )  ;

~last =  ( ~position = 0 ) ;

~position = If ( ~last ; ~length ; ~position ) ;

~value = Middle ( ~string ; ~position ; 1  ) ;

//reps will determine the number of 0s

~reps = Div ( ~num ; ~length ) - ( ~last ) ;

~rep0 = 10^ ( ~reps + 1 ) ;

~repValue = Substitute ( ~rep0 ; [ 0 ; ~value ] ; [ 1 ; "" ] ) ;

~result = ~repValue

];

~result

)

Message was edited by: DavidJondreau

• ###### 6. Re: Counting

Hi,

LaRetta,

Ok the purpose of these alpha numbers are for counting visualy a "tube" count on a document that is full of numbers. It breaks up the numerical madness! The reason for omitting I and O is that the closely resemble 1 and 0. In this case I have also have a numerical row count, I do all the resetting for deleting under that row then just refer to that row for this field.  David's formula works really well in this case. As for the number of records that get counted it depends but on the high side it wont exceed 100.

I really thank you guys for helping me out with it.

Jason