Power Apps - Building Power Apps

Evaluate choice column/ option set using Fx expression in dataverse Formula column

Like (0) Share

Report

Posted on 5 Feb 2023 16:15:28 by sryzm

Hi,

Here is my scenario in a model driven app with a Dataverse table.

There is a Choice column in Table A called FoodType. User has the option to pick one of three choices: Fruit, Fruit & Veggies, Veggies from the global choice/optionset called FoodTypeCategoryOptions. The corresponding number values are 1001,1002,1003.

There is second column - a formula column (preview feature) on the same table A named FoodAction, in which I want to implement the following example logic:

If( 'Food Type'= 1001, "F", 'Food Type'= 1002, "FV", 'Food Type'= 1003, "V")

Receiving the following error on the evaluative portion of a simple expression like: If('FoodType'= 1001, "F", "Empty")

Incompatible types for comparison. These types can't be compared :
OptionSetValue(global_cr123_foodtypecategoryoptions), Value.

Note: cr123 is the publisher in this solution.

Need help with the appropriate syntax for extracting and referencing the selected Value and Text values, stored in the FoodType column. I can then use the true/false result in second part of an IF or Switch statement.

It is probably something obvious, but I can't find the right answer in google searches and chatGPT.

thanks in advance!

Categories:

Using Apps Using Formulas

All responses (5)

Answers (0)

Suggested answer

Richard Mamabolo 2 on 11 Dec 2024 at 05:28:12

Like (2)

Report

Evaluate choice column/ option set using Fx expression in dataverse Formula column

Syntax should look like: @FoodType = [@FoodType].'Fruit'

In order to achieve this perform the following actions:

1. In the Fx Formula field, type out the optionset name

2. Type out the equals sign

3. Type out the optionset name followed by Ctrl + Spacebar (this will display the [@FoodType] option)

4. Add a period after [@FoodType] like so: [@FoodType]. (This will show all the optionset names associated with the option set i.e Fruits, Fruits &Veggies and Veggies)

5. Select the relevant one. Done.
Suggested answer

ankit_singhal 481 Super User 2025 Season 1 on 04 Oct 2024 at 07:41:17

Like (0)

Report

Evaluate choice column/ option set using Fx expression in dataverse Formula column

Please check below blog
https://www.matthewdevaney.com/25-power-apps-if-function-examples/#IF-Choice-Column-Value-Equals

Note: Mark verified, if it useful for you
Suggested answer

CU04100730-0 2 on 04 Oct 2024 at 07:33:34

Like (1)

Report

Evaluate choice column/ option set using Fx expression in dataverse Formula column

I have tested these 3 different formulas and they all seem to work. You need to refer to the choice values using the choice display name followed by the value.

Go to your Choices and check the display name. My choice display name is chProductVisibility, my values are Private, Public and Invite.

In my table, I have created a formula to check the value on Product Visibility Column ('Product Visibility')

1st test column created was TestChoicePrivate Column - result returned Yes or No

'Product Visibility' = chProductVisibility.Private

2nd test column created used IF - column name TestChoice

If('Product Visibility' = chProductVisibility.Private, "PR",

If('Product Visibility' = chProductVisibility.Public, "PU",

If('Product Visibility' = chProductVisibility.Invite, "IN", "")
)
)

3rd test column created used SWITCH - column name TestChoiceSwitch

Switch(

'Product Visibility',

  chProductVisibility.Invite, "IN",

  chProductVisibility.Private, "PR",

  chProductVisibility.Public, "PU"

)

I hope this helps!

Melina Louzada

Access Analytic

www.accessanalytic.com.au
sryzm 6 on 16 Feb 2023 at 16:18:27

Like (0)

Report

Re: Evaluate choice column/ option set using Fx expression in dataverse Formula column

Thanks. I hope this capability is on the roadmap.
v-liwei-msft on 06 Feb 2023 at 03:33:43

Like (0)

Report

Re: Evaluate choice column/ option set using Fx expression in dataverse Formula column

Hi @yozu

Unfortunately, this preview column doesn't support this data type now.

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/formula-columns#data-types

Hope it saves your time.

You can create a text column and then update the column through PowerApps or Flow.

Best Regards,

Levi