Unanswered

How to filter records on an option set against a local variable

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have an entity in Dynamics 365 named Screen Progress Data with a field named "Screen Progress Status" of type option set. This field has following 3 options:

Not Started
In Progress
Completed

In a canvas app I am selecting the records of this entity by running a filter on "Screen Progress Status" field using the following statement:

Set(varProgressStatus, Filter('Screen Progress Data', 'Screen Progress Status' = 'Screen Progress Status (Screen Progress Data)'.'Not Started'))

This works fine but I want this filter to run against a value stored in a variable. How can I change the query like below:

Set(varProgressStatus, Filter('Screen Progress Data', 'Screen Progress Status' = <value_from_a_variable>))

<value_from_a_variable> is a local variable which holds text "Not Started".

Any suggestion/help will be greatly appreciated.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

WarrenBelz 156,100 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous,

Have you tried

Set( varProgressStatus, Filter( 'Screen Progress Data', 'Screen Progress Status'.'Not Started' = Variable ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Warren,
When I put a . (dot) after 'Screen Progress Status', it says "No Suggestion". Suggestions are available when I put a . (dot) after 'Screen Progress Status (Screen Progress Data)' and sorry but I don't know what is the difference between 'Screen Progress Status' and 'Screen Progress Status (Screen Progress Data)' and which one should be used on the left hand side of the equal sign (=) in Filter formula. It would be great if you could let me know this.

However, I have tried the way you have suggested but it says "Incompatible type. We can't evaluate your formula because the values being compared in the formula aren't the same type." I have tried with Set(varStatus, "Not Started") as well but the error remained same.

Thanks

Was this reply helpful? Yes No
WarrenBelz 156,100 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous ,

What does it say the types are - you need to get them the same on both sides of the filter.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz
In Dynamics CRM, data type of "Screen Progress Status" field is optionset which is similar to a dropdown. It stores a text and a numeric value for each of the options.
I am new to PowerApps and I am not sure how to check the data type of a field. But I have experienced that if we select a field, it's data type appears an the bottom of the formula panel. I have done that and as you can see in the images below, PowerApps is encountering an error while determining the data type of 'Screen Progress Status (Screen Progress Data)' .'Not Started' whereas for 'Screen Progress Status' it's not even saying anything.
Thanks

Was this reply helpful? Yes No
WarrenBelz 156,100 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

'Screen Progress Data' is your data table and you are looking for records where the contents of 'Screen Progress Status' equals your Variable varStatus. I now notice that you are setting varStatus to a numeric value. If you do a test collection

ClearCollect( colTest, 'Screen Progress Data' )

then look in View > Collections > colTest what do you see in the field 'Screen Progress Status' ?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz
It only shows the schema name of all the fields of "Screen Progress Data" table. Schema name of field "Screen Progress Status" is lg_screenprogresssttaus in Dynamics CRM.
Thanks

Was this reply helpful? Yes No
WarrenBelz 156,100 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous ,

I am not a Dynamics user, so I will tag @RandyHayes on this one.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Sorry - just seeing this notice from @WarrenBelz about this thread.

Are you still experiencing an issue?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @RandyHayes
I have used some other way to fulfil my requirement but I still don't know the answer of my question. It would be great if you could answer.

Thanks

Was this reply helpful? Yes No