In my FileMaker solution, I would like to automatically translate certain fields from my "Products" table into fields in my "Products_2" table using a dictionary stored in my "Dictionary" table.

I suppose I should somehow use the Substitute function, but how do I retrieve (all) the values from the "Dictionary" table?

I don't think you need all the values from the "Dictionary" table. You only need the matching value, which you can get through a relationship.

However, I would question the setup you describe. Why would you have two Products tables? And what are these "certain fields" that need translation? I suspect the real problem is somewhere else.

 

Edited January 11, 201610 yr by comment

The fields that need translation are fields with a lot of text, like for example the "Description" field. Several words in that field will need to be translated, so there is more than just one matching value. I might be able to keep it all in one table, but for clarity I have separated my data into a danish table (Products) and a norwegian table (Products_2). This shouldn't affect the translation challenge, I suppose.

3 hours ago, LoisLane said:

Several words in that field will need to be translated, so there is more than just one matching value.

I see. Well, you could use Substitute() in a recursive custom function - but you need to keep in mind that (a) the Substitute() function is case-sensitive, (b) it works with patterns, not words, and (c) cumulative application can have unexpected results (as each iteration works on the result of previous ones).

If these limitations do not affect you, try a custom function such as:

 MultiSubstitute ( text ; searchValues ; replaceValues ) =

Let ( [
countValues = ValueCount ( searchValues ) ;
searchString = GetValue ( searchValues ; 1 ) ;
replaceString = GetValue ( replaceValues ; 1 ) ;
result = Substitute ( text ; searchString ; replaceString )
] ;
Case (
countValues > 1 ; MultiSubstitute ( result ; RightValues ( searchValues ; countValues - 1 ) ;  MiddleValues ( replaceValues ; 2 ; countValues - 1 ) ) ;
result
)
)

To use the function, define a relationship to the Dictionary table using the x relational operator, then make your calculation =

MultiSubstitute ( YourField ; List ( Dictionary::SearchString ) ; List ( Dictionary::ReplaceString ) )

 

Alternatively consider a scripted approach using the Perform Find/Replace script step in a loop.

Thanks a lot for your quick, thorough and very useful answer. It works perfectly! I am aware of the challenges you mention, but I have my ways to work around them

 

So far, so good... but a new challenge has come up:

The records in the "Dictionary" table need to be inserted to the MultiSubstitute function in a certain order. Unfortunately, the sort order I have made in the "Dictionary" table is ignored which leads to errors. How do I solve this problem?

20 minutes ago, LoisLane said:

the sort order I have made in the "Dictionary" table is ignored

Sort the related Dictionary records, in the relationship definition.

Of course! Thanks again

Is it possible you give us an example basis, I aim my test in my same topic that want to Lois Lane.
My subject here