Multilingual multiple choice answers

[Claudio_Brenni]

Dear Airtable Community,

I'm working in a multilingual environment (I'm based in Switzerland and working with partners in Africa, my daily job can involve up to 6 languages) . It is essential for my job to be able to collect data with multilingual forms, but then aggregate the results in the same answer field. 

For the survey part, Airtable allow very conveniently to reformulate the questions in the form view in as many languages as I want, and that is a no brainer.

Where the things get complicated is when the survey includes a single or a multiple choice question.

For the single choice, I've found a very practical solution:

Insert all the possible answers in all the languages in a single choice field, then generate as many form as the languages and for each one filter out all the answers that are not in the correct language. Finally, aggregate all the answers in another field with a SWITCH Formula, as shown in this example here: https://airtable.com/shr9d4kkhOACVv5VK. I can then this field directly to generate a Pie Chart https://airtable.com/shrlNoNM6XpLamHBN

I've then tried to use a similar strategy for a multiple choice answer, https://airtable.com/shrGFFXG1X2tGFCqw .  It was a bit more complicated because the SWITCH field formula need to contain all the possible permutations, and already with three possible answers the formula gets very long:

SWITCH(Multilingual,
"1. Testa", "1. Head",
"1. Testa, 2. Corpo", "1. Head, 2. Body",
"1. Testa, 3. Piede", "1. Head, 3. Feet",
"1. Testa, 2. Corpo, 3. Piede", "1. Head, 2. Body, 3. Feet",
"2. Corpo","2. Body",
"2. Corpo, 3. Piede", "2. Body, 3. Feet",
"3. Piede","3. Feet",
"1. Tête","1. Head",
"1. Tête, 2. Corps", "1. Head, 2. Body",
"1. Tête, 3. Pieds", "1. Head, 3. Feet",
"1. Tête, 2. Corps, 3. Pieds", "1. Head, 2. Body, 3. Feet",
"2. Corps", "2. Body",
"2. Corps, 3. Pieds", "2. Body, 3. Feet",
"3. Pieds", "3. Feet", 
"1. Kopf","1. Head", 
"1. Kopf, 2. Korper", "1. Head, 2. Body",
"1. Kopf, 3. Fuss", "1. Head, 3. Feet",
"1. Kopf, 2. Korper, 3. Fuss", "1. Head, 2. Body, 3. Feet",
"2. Korper", "2. Body", 
"2. Korper, 3. Fuss","2. Body, 3. Feet",
"3. Fuss","3. Feet")

So, here my question:

Is there a way to code a formula for the multiple choice answers that only need to translate the possible answers, without having to translate also all the possible permutations?

Many thanks ,

Claudio

PS: For those interested in a more complete explanation of the multiple choice example: The two extra fields in this view are necessary to be able to create a pie that split the multiple results cells like this one https://airtable.com/shr0kebBvCnAJztun . It was impossible to get such a chart with the Matchmaker field, so I've added the Summary field, which is filled by an automation that past the content of the Matchmaker in the Summary field. This last one is triggered by the Mismatch Checker, a formula that checks that the content of the Summary is equal to the one in the Matchmaker. When a mismatch is detected, it prints "Mismatch" and the pasting automation is triggered.

[pressGO_design · Accepted solution]

This formula should work for both instances.

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

Multilingual,

"2. Korper", "2. Body"),

"2. Corpo", "2. Body"),

"2. Corps", "2. Body"),

"1. Testa", "1. Head"),

"1. Tête", "1. Head"),

"1. Kopf", "1. Head"),

"3. Fuss", "3. Feet"),

"3. Pieds", "3. Feet"),

"3. Piede", "3. Feet")

Edit to add link to interface with multiselect forms and access to underlying data. There's an automation that powers the info getting to the Summary field for the charts.

https://airtable.com/invite/l?inviteId=invE6nCVH9a5m8Aok&inviteToken=0ff0b633b1f13d313f7d098b7f5ff32...

[Claudio_Brenni]

Many Thanks for your quick and useful reply. I'll integrate your Formula in my bases.

All the best,

Claudio

[Anna_Mule]

Hi @Claudio_Brenni can you tell me more about how you are displaying multilingual form questions?  ("For the survey part, Airtable allow very conveniently to reformulate the questions in the form view in as many languages as I want, and that is a no brainer.")

[Claudio_Brenni]

Hi @Anna_Mule !

Thanks for reaching out! 

For your question: 

I was meaning that I create a form view for each different language and then translate the questions in the form. I'll than have a link for the form in English, and then a link for the form in French,  and so on (I normally work with up to 5 languages).

For me normally the forms are sent out with an automation, so I've a single choice field for the language of the respondent, and then then Url fields corresponding to the right language. An automation then send the right form at a given time to the respondent.

But if you need to give the choice of the language and send only one link, I think that a viable solution is to create an empty interface that present your form and a button for each different form, so that the respondent can choose the form in the right language (that is also archivable with a grid view).

Let me know if this answer is of any help and otherwise don't hesitate to continue the discussion here. I'm glad to share my experience with airtable and surveying.