4 Replies Latest reply on Apr 29, 2013 9:09 AM by philmodjunk

# replace a numberd list with letters

### Title

replace a numberd list with letters

### Post

hello everybody,

my problem is the following:

i have records with numbered index fields like:

Record: 1 Index:  10
Record: 2 Index:  20
Record: 3 Index:  25
Record: 4 Index:  27
Record: 5 Index:  30

and i need them replaced with the value of a valuelist like:

a,b,c,d,e

respecting the sort order of the numberd index field.

Basicaly my output should be

Record: 1 Index:  10 CalculatedValue: a
Record: 2 Index:  20  CalculatedValue: b
Record: 3 Index:  25  CalculatedValue: c
Record: 4 Index:  27 CalculatedValue: d
Record: 5 Index:  30 CalculatedValue: e

is there some function to help me?
To make it harder i can add that the index field can repeat across my table, as it is actually a SubIndex based on another Index. The Final Output should be something like:

Record: 1   Index: 1 SubIndex:  10 CalculatedValue: a
Record: 2   Index: 1 SubIndex:  20 CalculatedValue: b
Record: 3   Index: 1 SubIndex:  25 CalculatedValue: c
Record: 4   Index: 1 SubIndex:  27 CalculatedValue: d
Record: 5   Index: 1 SubIndex:  30 CalculatedValue: e
Record: 6   Index: 2 SubIndex:  10 CalculatedValue: a
Record: 7   Index: 2 SubIndex:  21 CalculatedValue: b
Record: 8   Index: 2 SubIndex:  25 CalculatedValue: c
Record: 9   Index: 2 SubIndex:  30 CalculatedValue: d
Record: 10 Index: 2 SubIndex:  40 CalculatedValue: e

THANKS A LOT FOR HELPING!!!!!

if i wasn't clear just asks, as it's really hard to explain.
thanks a lot!
Cisco

• ###### 1. Re: replace a numberd list with letters

I was thinking about this a little but now I see it's actually just a simple count. Where you count up untill you arrive at a new index and then you start again.

I think the simplest way to do this is to make a simple loop script that
- Sorts your records by index first and then subindex.
- Then loops trough and sets letters until a new index begins.

Try my test file here and run the script for yourself:

https://dl.dropboxusercontent.com/u/18099008/Demo_Files_FMP12/subindex.fmp12

If you have any more questions: make sure to ask. Some people have some difficulty undersdatnding what a variable is etc.

• ###### 2. Re: replace a numberd list with letters

I was going another direction, but didnt have time to finish using Replace function by calculation using Char() function and a recursive counter.  I like your way better

Jim...

• ###### 3. Re: replace a numberd list with letters

Thanks a lot!

works just perfectly, i only had to smooth out some things to make it fit my DB, but it was great!

thank you very much!

Cisco

• ###### 4. Re: replace a numberd list with letters

Nothing really wrong with using case, but there's a simpler way to get letters to substitute for numbers:

Set Variable [\$Letter ; value: Char ( Code ("a" ) + \$Counter - 1 ) ]