Cannot compare OptionSetValue

(0) Share

Report

Posted on by bugescape

I have a model driven app using dataverse, there are 2 tables:

"Company", with columns:

- company name

- GST registered, this is a Yes/No OptionSetValue data type column

"Tax Invoice", with columns:

- company, this look up the "company" table

- Amount Excl GST

- GST amount

I want it so that if a company is GST registered, the "GST registered" column would be tick to be "Yes', and then when I input a new record of tax invoice in the "Tax Invoice", if the record below to a GST registered company, the "GST amount" column will be calculated using below power fx (preview) function:

If(Company.'GST Registered' = "Yes",'Amount Excl GST'*0.1,0)

This gives error saying that OptionSetValue cannot be compared. See attached screenshot

I have tried many alternative ways, like adding .value behind, doesnt work and says no such value. Adding text(Company.'GST Registered') = "Yes", then it says this is not supported.

Can someone please have a look and give a solution?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

LaurenWithers 82 on at

Like (0)

Report
Assuming that you are trying to compare an option set with a text input - and this is why you are getting the error. You could modify the formula to use "OptionSetValue" to convert the yes value into its OptionSetValue value. Say if "Yes" in your option sets value is 1 then you could do:
If(Company.'GST Registered' = OptionSetValue(1),'Amount Excl GST'*0.1,0)
OptionSetValue(1) represents the OptionSetValue value for "Yes" but this might be different for your optionset, just check the field properties and change the OptionSetValue(x) accordingly!

Please thumbs up if it helped or mark as solution if it worked 🙂

Was this reply helpful? Yes No
bugescape 41 on at

Like (0)

Report

Sorry this doesnt work, it says OptionSetValue is an unknown function

Was this reply helpful? Yes No
Verified answer

Ami K 15,679 Super User 2024 Season 1 on at

Like (0)

Report
Hi @bugescape, try the following pattern:

If('Yes or No Choice' = 'Yes or No Choice (Sample Data)'.Yes,Decimal*0.1,0)

Note that because the Power FX option remains in preview, it is quite unwieldy. Please refer to a similar solution I provided a few days ago for detailed steps:

https://powerusers.microsoft.com/t5/Microsoft-Dataverse/Formular-PowerFx-Column-doesn-t-compare-Input-of-a-YES-NO-Column/m-p/2092460#M28887

-----------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.

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

Was this reply helpful? Yes No
bugescape 41 on at

Like (0)

Report

Bruh thanks! it's finally working!

what even is these reference...I want to learn how to understand the difference between:

'Yes or No Choice'
‘Sample Data’.'Yes or No Choice'
'Yes or No Choice'(‘Sample Data’)
‘Sample Data’.'Yes or No Choice' (Delegate)
‘Sample Data’.'Yes or No Choice' (Base)

My head is blowing up

Was this reply helpful? Yes No
Ami K 15,679 Super User 2024 Season 1 on at

Like (1)

Report

Glad it got sorted.

'Yes or No Choice'
‘Sample Data’.'Yes or No Choice'
'Yes or No Choice'(‘Sample Data’)

The Choice/Option Set data type returns a table of values for the column. In order to reference a specific choice, the pattern is to display the choice name followed by the data source name in parenthesis, and then the choice value. This is different from the PowerFX experience in Canvas Apps. Which just require the Choice field name + the choice value. E.g. ChoiceColumn.Choice1

No idea why this is but given this is still in Preview I just accept this is the way it currently works.

Base

When you add a currency column, a duplicate column with "Base" appended at the end of the column name is added and it refers to the base currency currently set for the organisation.

Delegate

Delegate as far I am aware only relates to the Modified by column. If a user does not have permission to update a record but still manages to update a record (e.g. via a plugin or some other route), this field will be populated by that user's name.

Was this reply helpful? Yes No