How to Filter Based on a Boolean Choice Column in DataVerse?

[rprahalad]

I have a Choice Column on a table  in Dataverse called 'isActive', which is of a 'Boolean Yes Or No' type.  Of Course within the DataVerse it is termed as 'Allowed' and 'Not Allowed'. 

I have a Dropdown within my Canvas App wherein I am trying to filter and show data where isActive = 'Yes' . But I have tried everything to get the syntax right of the Filter function on a Boolean Yes/No Choice but nothing works. The syntax shown for SP Lists does not work on the DataVerse table. 

Can you please let me know how I can filter a Boolean Yes/No Choice within my Canvas app? This is DataVerse table/column again and not SP list. @RezaDorrani had suggested that he will create a video of it. So, awaiting it as well.

Thanks

[Drrickryp]

HI @rprahalad 

I had a lot of trouble with this too.  Rather than continuing to aggravate myself,  I solved it by creating a view in Dataverse and used that as the filter. 

[rprahalad]

Its amazing how I thought of that @Drrickryp ! But my only concern is, if I use the View on a drop down on a Transactional form where I would have to actually save the data to the main transactional table, to obviously a LookUp column, would I be able to Save the picked dropdown data, since I have used the View on form to bind it? Thanks!

[Drrickryp]

@rprahalad 

I would be out of my depth to answer that.  I have been playing with this to figure out how to answer your question.  I could only suggest that you try it and see if it works.  I found a video from @Shanescows  on option set in Dataverse and how to use them in canvas apps.  Take a look and see if it helps.  In the meantime, I will keep playing around with this. https://www.youtube.com/watch?v=1L633HIvNGE 

[rprahalad]

Appreciate it! It somehow weirdly feels good when others are also having the same issue as you. Lol. Sorry but I did spend 45 minutes thinking that "I" am doing something wrong here and I felt good ONLY after reading your comments:-). 

I sure will. I will watch the video and will play around with it as well. Thanks again!

[Drrickryp · Accepted solution]

@rprahalad 

I know I am an old fogey and am very old school database designer but I have learned to simplify my life by avoiding complex type column types and use single line of text, numbers and date/times almost exclusively.  For Lookup type fields, I use a separate table and set a foreign key to reference it. I create all of my table relationships manually.  It's a habit I learned from SharePoint and it has served me well. I know that I am losing some of the features of Dataverse, but mostly I like Dataverse because virtually all PowerApps functions are delegatable and I deal with very large datasets. 

[rprahalad]

Oops I didn't mean to mark that as a solution:-). But same here! I just posted another question on many-many junction tables. I think one should STOP thinking SQL Server way and STOP normalizing the heck out of the database tables. I will denormalize my data structure as much as possible, just to avoid writing lines of LookUp and this and that functions. Denormalization = Low Code. So, there goes it. Just killed a few relationships that I had created. Many-Many is definitely out of my books from here on.

[Spamrancher]

Not sure if you ever got a good resolution to this. If you're using Dataverse and trying to filter in Power Automate, then you can use XRMToolBox - FetchXML Builder tool and copy/paste your result into the Fetch XML query. If you're trying to filter in a Canvas App, then the format is: 'Is Active (entity name)'.Yes or 'Is Active (entity name)'.No

Flow solution below.