convert text to number

[CR3T4N]

Hello all

i am a novice user of fmp 12.

i have this question in my mind can't figure out

i have 2 tables as follows:

table A named "Alphabet" with 2 fields ----> "Letters" , "Value"

table B named "Data" with 2 fields ----> "Words" , "Value"

in table A i have all the letters of the alphabet in the "Letters" field , and in the field "Value" a number for each letter.

I want in table B to enter words in the "Words" field , and when i do that , i want in the "Value" field of table B to automatically filled with the sum of the letters of the word i enter.

for example:

table A

A=1

B=2

C=3

D=4

E=5

F=6

G=7

table B

CAB=6

BAD=7

FADE=16

is this possible to be done?

thanks very much in advance for any answers

[eos]

I hope this is what you wanted; CF isn't too elegant (could do with a helper CF for counter initialization),

but does the job.

EDIT: not quite …

[CR3T4N]

I hope this is what you wanted; CF isn't too elegant (could do with a helper CF for counter initialization),

but does the job.

yes something like this ...only problem is it doesn't do the sum correct . i tried some words and gives me incorrect result.

but it is very close to that i want thanks

i will study your example to see how you make it

[eos]

You're right, I had forgotten that multiple instances of the same letter still only give one match result, so the sum is too low.

(Also there was an extraneous record for letter “H” in the alphabet list; teaches you to always check your data first …)

Here's an improved version which uses a script to loop over the individual letters and sum up their values. Hopefully works

correctly now (unless I've overlooked something else ;-)

SumLetterValuesv02_eos.fmp12.zip

[Vaughan]

No need for custom functions or scripts...

Let(



[

fld = fieldname ; // <--- field here

txt = Filter( Upper( fld ) ; "ABCDEFG" ) ;

calc = Substitute( txt ;

  [ "A" ; "1+" ] ;

  [ "B" ; "2+" ] ;

  [ "C" ; "3+" ] ;

  [ "D" ; "4+" ] ;

  [ "E" ; "5+" ] ;

  [ "F" ; "6+" ] ;

  [ "G" ; "7+" ]

  ) & "0"

] ;



Evaluate( calc )



)

[eos]

Very nice! I keep forgetting about Evaluate [ ]

[Vaughan]

Be careful with Evaluate(). Notice that I process what gets sent to the function with the Filter() function.

If that wasn't there, what would happen if these values was entered into the calculation:

A3*2*BC

1/0+CAD

[comment]

It would be helpful to know what this is about. It seems like the individual letters are (or should be) records in a related table; converting each letter individually, then summing the results would be a more robust solution.

[eos]

Be careful with Evaluate(). Notice that I process what gets sent to the function with the Filter() function.

Yes, I did notice

It would be helpful to know what this is about. It seems like the individual letters are (or should be) records in a related table; converting each letter individually, then summing the results would be a more robust solution.

This was my approach with a triggered looping script.

[comment]

No, I meant convert each letter while it still stands alone in its own record. There would be no looping script and no triggering required, just a simple Sum() calculation.

[eos]

No, I meant convert each letter while it still stands alone in its own record. There would be no looping script and no triggering required, just a simple Sum() calculation.

Can you be a bit more concrete? My first idea was to generate a multi-key from the string to match on an alphabet table - where each letter has its own record with an associated value - and Sum () up the results, but (as I noted above) this approach doesn't account for multiple instances of the same letter.

[comment]

You are starting from the string; I am assuming the string is the result of concatenating individual letters. I am suggesting the letters should be converted to their numerical equivalents before (or instead) being concatenated into a string.

[eos]

The OP said he/she wanted to “to enter words in the "Words" field”, that why I was starting from the string. Without knowing the actual purpose and/or workflow involved, it is hard to tell which method is best suited; but as usual, the nice thing about FileMaker is that there is more than one way to skin a cat.

[comment]

The OP said he/she wanted to “to enter words in the "Words" field”

So they did - and I said it would be helpful to know what this is about.

[CR3T4N]

woww i see more answers today i am very glad

well to give you a hint what i want to do is that i want to convert words that i enter into a lexarithmic value. Also i want the words i enter to be stored so i can process them later.

btw i am "he" you can call me Tasos (short name from Anastasios) and i am Greek.

[Lee Smith]

I hope this is what you wanted; CF isn't too elegant (could do with a helper CF for counter initialization),

but does the job.

EDIT: not quite …

Please do not Edit / Delete your Reply once they have been replied to. Whether or not it was a correct answer, doesn't matter, it still helped to move the process along, and by removing it now, it leaves a hole in the thread.

Lee

[comment]

convert words that i enter into a lexarithmic value.

I am not quite sure what that means; it seems something very close to gematria. If so, have a look at the attached.

GematriaCF.fp7.zip

[CR3T4N]

I am not quite sure what that means; it seems something very close to gematria. If so, have a look at the attached.

thank you it does the job fine