4 Replies Latest reply on Oct 25, 2012 2:49 PM by Mitch

# Convert Number Code to Letters and process the result

### Title

Convert Number Code to Letters and process the result

### Post

I am trying to write a script which includes a Loop.  I think a Loop is what is required?

In my database I have one table that has a number of fields,  I one field [Main_Table::Input] is where a user will input a series of characters.  Lets say in this case the user enters the numbers 1234 into this field.

I need the Loop to go to the first character in the field, in this case the number 1, then look up the table for the number 1 in the field [Main_Table::Number] and find the corresponding letter in the field [Main_Table::Letter]  In this case would be the letter A.

1 = A

2 = B

3 = C

4 = D

5 = E

6 = F

Then the Loop would need to determine the corresponding letter for the second number, third number and so on.  The Loop then stopping when all the corresponding characters have been determined.

I then need the result of the script to set the field [Main_Table::Result] with the corresponding letters in this case, using the tale below, the result would be ABCD.

Field [Main_Table::Input] can have anywhere between 1 and 100 characters entered.  If the user enters the numbers 2214321313, the reuslt would produce BBADCBACAC,  just as the numbers 21 would produce BA.

I assume a counter will also be need based on a character count of [Main_Table::Input] to somehow control the Loop?

• ###### 1. Re: Convert Number Code to Letters and process the result

I don't think you'd need a loop if this letter for number encoding is exactly the same each time.

Substitute ( YourTable::NumberField ; [1 ; A ] ; [ 2 ; B ] ; [ 3 ; C ] ; //and so forth for each possible digit )

would replace the digits with letters without any loop.

A more sophisticated version of this might even work if you stored the subsitution letters in a related table with one record for each digit/letter pairing.

• ###### 2. Re: Convert Number Code to Letters and process the result

That is my issue, the letter figures may not  always be the same.  I also need to look up corresponding values for each number, in addtion to the corrsponding letters so I can make some caluculations, but one step at a time...

• ###### 3. Re: Convert Number Code to Letters and process the result

If you had a table, codes, defined like this:

Letter   |     Code
A         |        3
B         |        1
C         |        2

and so forth....

You can set up this relationship:

Maintable::anyfield X Codes::anyfield

and specify that the relationship be sorted by Code in ascending order.

Then your subsitute function could be defined as:

Let ( LetterList = List ( Codes::Letter ) ; Substitute ( YourTable::NumberField ; [1 ;  Getvalue ( LetterList ; 1 ) ] ; [ 2 ; Getvalue ( LetterList ; 2 ) ] ; [ 3 ; Getvalue ( LetterList ; 3 ) ] ; //and so forth for each possible digit )

It's also possible to define a recursive custom function that does this one letter at a time if you have FileMaker Advanced

• ###### 4. Re: Convert Number Code to Letters and process the result

Thanks. Will give it a try.