Patching a dataverse table choice field from a collection

[AngusWalker]

I know this question has been asked many times before, and I've looked through all the answers, but none of the suggested solutions work for me.
My case is slightly different in that I have a pre-built collection (in offline mode) that I am transferring to Dataverse (when back online).
I've reduced my code to just the choice field that is causing the problem.
I have a text column in my 'ncr' collection (ncrClassification) that contains "Low", "Medium" or "High" that I'm trying to transfer to a Choice column (Non Conformance Classification) in my 'Non Conformance Classification' table  .
I've got it working with the below code, but this is definitely not the way to do it:

    ForAll(
        Filter(
            ncr,
            ncrAudID = Upload_Gal.Selected.audID
        ),
        Patch(
            'Non Conformance Reports',
            Defaults('Non Conformance Reports'),
            {
                'Non Conformance Classification': Switch(
                    ncrClassification,
                    "High",
                    'Non Conformance Classification (Non Conformance Reports)'.High,
                    "Medium",
                    'Non Conformance Classification (Non Conformance Reports)'.Medium,
                    "Low",
                    'Non Conformance Classification (Non Conformance Reports)'.Low
                ),
            }
        )
    );

 Any recomendations on how I should do this without the Switch command?

[M_Ali_SZ365]

HI @AngusWalker 

Try this

Create a local collection to map the text values to their corresponding choice value numbers when the app starts.

// Assuming the choice values for 'Non Conformance Classification' are 1 for Low, 2 for Medium, and 3 for High.
ClearCollect(
    LocalChoiceMapping,
    { TextValue: "Low", ChoiceValue: 1 },
    { TextValue: "Medium", ChoiceValue: 2 },
    { TextValue: "High", ChoiceValue: 3 }
);

Use the LookUp function to retrieve the correct choice value from your mapping collection during the patch operation.

ForAll(
    Filter(
        ncr,
        ncrAudID = Upload_Gal.Selected.audID
    ),
    Patch(
        'Non Conformance Reports',
        Defaults('Non Conformance Reports'),
        {
            'Non Conformance Classification': {
                // Assuming 'Value' is the schema name of the actual choice field
                Value: LookUp(LocalChoiceMapping, TextValue = ncrClassification).ChoiceValue
            }
        }
    )
);

Please accept this solution if it resolves the issue. ✅
Best regards,
Muhammad Ali

[AngusWalker]

Hi @M_Ali_SZ365,
Thanks for the reply.
I do define the collection before collecting to it, but this just moves the issue from the patch statement to the ClearCollect statement.
I don't want to have to define all the options of the choice.
If I add to the choices in the table later, I don't want to have to change the code.

[M_Ali_SZ365]

@AngusWalker 

Let’s simplify the process. You can directly match text values from your collection to the choice field in Dataverse. When your app starts, fetch the choices for 'Non Conformance Classification' with a simple Lookup in the patch. Here’s your updated code:

ForAll(
    ncr,
    If(
        ncrAudID = Upload_Gal.Selected.audID,
        Patch(
            'Non Conformance Reports',
            Defaults('Non Conformance Reports'),
            {
                'Non Conformance Classification': LookUp(
                    Choices('Non Conformance Reports'.'Non Conformance Classification'),
                    Value = ncrClassification
                )
            }
        )
    )
);

Remember to replace Value with the property name of the choice in your Dataverse table that holds the option labels.

This way, the code dynamically adapts to any new options you add to your choice field in Dataverse without needing to change anything in your app’s code. It’s neat, efficient, and future-proof."

Please accept this solution if it resolves the issue. ✅
Best regards,
Muhammad Ali

[AngusWalker]

Unfortunaly this returns a record, not an option set, so I get the error Expected type OptionSetValue(Non Conformance Classification (Non Conformance Reports)) Found Type Record.

I also tried:

                'Non Conformance Classification': LookUp(
                    Choices('Non Conformance Reports'.'Non Conformance Classification'),
                    Value = ncrClassification
                ).Value

But Value just returns a Text value. What I need is an OptionSetValue.
I can still only get this to work by using a hard coded value, e.g.

'Non Conformance Classification (Non Conformance Reports)'.High

If only you could add the variable at the end e.g.

'Non Conformance Classification (Non Conformance Reports)'.ncrClassification

 but that doesn't work either 

[AngusWalker]

I found similar posts saying this can't be done and giving work arounds similar to that in my original question.
e.g: https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-set-text-variable-value-to-an-Options...
Thanks for trying @M_Ali_SZ365