Multiplying different values

[fantasmamore]

Hello all!

I have a drop-down list with numbers (1,2,3,4,5) and another one with numbers, but could be anything else, letters, codes etc. What I want is to create a field where a result will appear when someone selects one value from one field and another from the second field.

Let me give you an example

Let's name the first field "numbers" and the second "codes". What I want is to write a script, where

numbers=1, codes=A, result=100

numbers=1, codes=B, result=125

numbers=3, codes=B, result=375

or the values from the code field will equal something like A=100, B=125, C=150 ... W=737 etc

Is it possible and if so, how?

Thank you very much!

[comment]

A=100, B=125, C=150 ... W=737 etc

Is that always the case, or is this written as data somewhere?

[fantasmamore]

No, the values will always be the same. A will be always 100, B always 125 etc...

[comment]

Try making Result a calculation field (result is Number) =

Number * ( 75 + 25 * Position ( "ABCEDEFGHIJKLMNOPQRSTUVWXYZ" ; Code ; 1 ; 1 )  )

[fantasmamore]

My problem is that not all codes are "previous +25" (arithmetic progression). So A=100, B=125 but C=278 or D=349... I want to give to every value from the codes list a specific, unique value.

[comment]

Then I believe it would be best to create a Codes table with two fields, Code and Value, define a relationship from your table to the Codes table matching on Code, and get the corresponding value from the related record in Codes.

[fantasmamore]

First of all, thank you for your help. But I am really a newbie, so could you please guide me in creating the relationship? Or give me a link of a similar tutorial?

[comment]

Just drag to connect the Code field of your table to the Code field of the Codes table.

Alternatively, if you prefer to hard-code it, you could use =

Let ( [

pos = Position ( "ABCEDEFGHIJKLMNOPQRSTUVWXYZ" ; Code ; 1 ; 1 ) ;

val = GetValue ( "100¶125¶278¶349¶472¶593..." ; pos )

] ;

Number * val

)

I am not sure what this is about and as a rule hard-coding is not good practice.