Dynamically translate dropdown options based on a separate mapping table

[Amik]

Scenario

I came across the following scenario posted on the forums and I moved my response to this blog.

We have an EditForm control with a DataCard that is bound to a single-select SharePoint Choice or Dataverse Choice field.

Based on the selected value from the ComboBox control inside the DataCard, we want to display a different value to the end user, but still save the selected Choice with the actual value configured for that Choice behind the scenes.

Suppose for example we have a Choice field which contains a list of colours. For a French speaking user, we want to display the choice options in French, but save the choice selection in English back to our data source.

Set up a sample mapping table

We need a mapping table which records the display value shown to our end user and the underlying value we will save to our data source. Typically this would be a separate table we will add as a data source into our app, but for this example, we will create a Collection.

1. Create a Button control and on the OnSelect property, enter:

ClearCollect(
    col_translated,
    Table(
        {
            ChoiceValue: "Red",
            ChoiceTranslated: "Rogue",
            Lang: "French"
        },
        {
            ChoiceValue: "Blue",
            ChoiceTranslated: "Bleu",
            Lang: "French"
        },
        {
            ChoiceValue: "Green",
            ChoiceTranslated: "Verte",
            Lang: "French"
        },
        {
            ChoiceValue: "Red",
            ChoiceTranslated: "Roja",
            Lang: "Spanish"
        },
        {
            ChoiceValue: "Blue",
            ChoiceTranslated: "Azul",
            Lang: "Spanish"
        },
        {
            ChoiceValue: "Green",
            ChoiceTranslated: "Verde",
            Lang: "Spanish"
        }
    )
)
//take note the ChoiceValue section should be populated with the actual choice values in your choice field

2. Select the Button to initiate the Collection.

App Set Up

1. Create an EditForm control and update the DataSource property with your data source

2. Insert the DataCard for your Choice field

3. Create a Gallery control and set the Items property to your data source

4. On the OnSelect property of the Gallery control, enter:

EditForm(Form1)

5. On the Item property of the EditForm control, enter:

Gallery1.Selected

6. Create a Button control and on the OnSelect property, enter:

SubmitForm(Form1)

7. Create a Button control and on the OnSelect property, enter:

UpdateContext({ctx_language:"French"});

8. Create another Button control and on the OnSelect property, enter:

UpdateContext({ctx_language:"Spanish"});

Configure the DataCard and ComboBox properties

1. Unlock the DataCard for your Choice field

2. In the Items property of the Combobox control embedded into the DataCard, enter:

Filter(
    col_translated,
    Lang = ctx_language
)

3. Select the ComboBox control and on the properties pane, select "ChoiceTranslated" under Primary text.

4. In the DefaultSelectedItems property of the Combobox control, enter the below if your data source is SharePoint:

LookUp(
    Filter(
        col_translated,
        Lang = ctx_language
    ),
    'Your Choice Field' = ThisItem.'Your Choice Field'.Value
)

Or enter the below if your data source is Dataverse:

LookUp(
    Filter(
        col_translated,
        Lang = ctx_language
    ),
    ChoiceValue = Text(ThisItem.'Your Choice Field')
)

5. On the Update property of the DataCard, enter the below if your data source is SharePoint:

LookUp(
    Choices([@'Your List'].'Your Choice Field'),
    Value = DataCardValue1.Selected.ChoiceValue
)

Or enter the below if your data source is Dataverse and your Choice field is a Global Choice:

LookUp(
    AddColumns(
        Choices('Your Global Choice Field'),
        TextChoice,
        Text(Value)
    ),
    TextChoice = DataCardValue1.Selected.ChoiceValue,
    ThisRecord.Value
)

Or enter the below if your data source is Dataverse and your Choice field is a Local Choice:

LookUp(
    AddColumns(
        Choices([@'Your Table'].'Your Local Choice Field'),
        TextChoice,
        Text(Value)
    ),
    TextChoice = DataCardValue1.Selected.ChoiceValue,
    ThisRecord.Value
)

 

Multi-choice fields

1. If your Choice field has been configured to allow multiple selections, then in the DefaultSelectedItems property of the Combobox control, enter the below if your data source is SharePoint:

ForAll(
    ThisItem.'Your Multi Choice Field' As _selections,
    LookUp(
        Filter(
            col_translated,
            Lang = ctx_language
        ),
        _selections.Value = ChoiceValue
    )
)

Or enter the below if your data source is Dataverse:

ForAll(
    ThisItem.'Your Multi Choice Field' As _selections,
    LookUp(
        Filter(
            col_translated,
            Lang = ctx_language
        ),
        Text(_selections.Value) = ChoiceValue
    )
)

2. On the Update property of the DataCard, enter the below if your data source is SharePoint:

ForAll(
    'Your ComboBox'.SelectedItems As _selections,
    LookUp(
        Choices([@'Your List'].'Your Choice Field'),
        Value = _selections.ChoiceValue
    )
)

Or enter the below if your data source is Dataverse:

ForAll(
    'Your ComboBox'.SelectedItems As _selections,
    LookUp(
        AddColumns(
            Choices([@'Your Table'].'Your Multi Choice Field'),
            TextChoice,
            Text(Value)
        ),
        Text(Value) = _selections.ChoiceValue
    )
)

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan