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.
