FileMaker 7 only how to calculate the value in a field from a 'related' field in another table when the relationship depends on the calculation fieldname ?

I have a no records table <<Settings>> containing a global field <Language> with 3 possible values : 'DU', 'FR' and 'EN'.

I have a no records table <<Labels>> containing the global fields <CtctName>, <CtctFirstName> and <CtctZip>.

I have a table <<Translations>> with a textfield for each language and containing a record for each field in <<Labels>> :

.....record...<LabelName>.......<Label_DU>........<Label_FR>.........<Label_EN>

.....1............CtctName...............Naam......................Nom........................Name

.....2............CtctFirstName.......Voornaam..............Prenom...................First Name

.....3............CtctZip....................Postnummer..........Code postale........ZIP

If Settings::Language = 'FR', how can I calculate for each field in <<Labels>> the value from <Label_FR> when the fieldname in <<Labels>> matches the value in Translation::LabelName ? The results should be stored as globals.

I know a way of setting the fields in <<Labels>> by a loop script, but I was wondering if it was possible with a calculation.

Keep in mind that in my solution I have hundreds of global fields in <<Labels>> (and by this hundreds of records in <<Translations>>).

It is used in the development of a multi-language user interface where users can switch between 3 languages.

I would miss out the "Labels", I don't see this is doing too much ( unless you wanted to add the versatility of easily adding additional languages ).

Define a new field in Translations

Case( Settings::Language = DU, Label_DU, Settings::Language = FR, Label_FR, Settings::Language = EN, Label_EN, "Error")

Use this new field on your layouts.

You could make the Case faster by using something like

Case( PatternCount( "_DU_FR_EN_"; "_" & Settings::Language & "_" ); GetField("Label_" & Settings::Language); "Error" )

I need <<Labels>> because

- I need global fields (they keep content in find mode)

- I like to have the translations of the labels as records in a table making it easy to import the translations from other environments (like Excel); this way the translations could be done by people not having access to the FileMaker solution or even not having FileMaker ...

You could make the Case faster by using something like

Case( PatternCount( "_DU_FR_EN_"; "_" & Settings::Language & "_" ); GetField("Label_" & Settings::Language); "Error" )

This is smart !!

I assume this is faster because there is only one condition to evaluate and FileMaker is still evaluating all conditions in a case statement.

Case short-circuits now, so it isn't evaluating all conditions, unless the only true condition is the last one. So for EN it may be faster, while for DU, it may be slower. However, I would think, overall, that the calculation is more evenly efficient, because it takes the same amount of time to determine any of the results. Using Position instead of PatternCount may speed up the process, but the difference may be negligible if you only have two letters in the field.