Filtering a Combo-Box within an EditForm based on a Yes/No column stored in a Lookup...

[tellerj]

---> How do I filter the options within a combo-box control based on a Yes/No column stored with the records being filtered? <--

I have a "Gradesheets" DataVerse table, which includes columns:
**Date** (Type:DateTime),
**Assignment** (Type:Text),
**StudentName** (Type:Lookup),
**InstructorName** Type:Lookup)

I also have a "Personnel" DataVerse table which includes columns:
**Fname** (type:Text),
**Lname** (type:Text), and
**isStudent** (type:Yes/No)

I'm trying to use the "EditForm" input control to let me add records to the "GradeSheets" table by editing all four of the fields mentioned. When I add the "StudentName" and "InstructorName" fields to the form, PowerApps creates a DataCard which includes a label and a combo-box control. So I have:

**Form1**
--> DateDataCard
--> AssignmentDataCard
--> InstructorNameDataCard
--> **StudentNameDataCard**
---> DataCardKey (label)
---> **DataCardValue** (combo-box)

The newly created "Form1" EditForm control has the `DataSource` field automatically set to my "GradeSheets" table. Then, the StudentName_DataCard component has: {DataField: ["cr4e5_StudentName"]}, and within THAT, the DataCardValue combo-box control has "Items" automatically set to "Choices([@GradeSheets].cr4e5StudentName" and the DisplayFields and SearchFields set to "[cr4e5_fname]"

Since Choices() returns a Table, I want to be able to Filter() that table based on the value of isStudent--another column within the Personnel table. I've tried a bunch of ways, but I don't understand what I'm doing wrong with the syntax. What it seems like it should be, is:

`Filter(Choices([@GradeSheets].cr4e5_studentname), Text(isStudent) = "Yes")`

But that doesn't work. Hovering over "Choices" confirms that I am indeed accessing a table of records (since StudentName is a lookup to the Personnel table), but why can't I filter off of a column within that table of returned records???? I think it has to do with confusion about scopes, OptionSet types, or something else entirely.

Thank you in advance for any assistance.

[v-jefferni]

Hi @tellerj ,

The reason is the Y/N column is an option-set column, and the way to filter option-set column is as follows:

Filter(Choices([@GradeSheets].cr4e5_studentname),isStudent.Yes)

Best regards,

[rampprakash]

Hi @tellerj 

What ever @v-jefferni will help your solution

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

[tellerj]

Hi @v-jefferni, 

That doesn't work, and gives an error. Specifically, "The function 'Filter' has some invalid arguments."

Additional variations I've tried with their errors: 
`Filter(Choices([@GradeSheet].cr4e5_StudentName), isStudent.Yes = "Yes")`  --> Error. "Incompatible types for comparisson. These types can't be compated: Error, Text."

`Filter(Choices([@GradeSheet].cr4e5_StudentName), Text(isStudent.Yes) = "Yes")`   --> Doesn't give an error, but zero items populate in the dropdown.

`Filter(Choices([@GradeSheet].cr4e5_StudentName), Text(ThisItem.StudentName.isStudent) = "Yes")`  --> No error, and no items populate

`Filter(Choices([@GradeSheet].cr4e5_StudentName), Text(ThisItem.StudentName.isStudent.Yes) = "Yes")`  --> Error. "Name isn't valid. 'Yes' isn't recognized."

`Filter(Choices([@GradeSheet].cr4e5_StudentName), Boolean(Text(ThisItem.StudentName.isStudent)))`  --> No error, and ALL items populate, without being filtered

There is one way that does solve my problem, but doesn't answer why this isn't working. That's when I just cut out the "GradeSheets" table from the combo-box options and point it straight at the "Personnel" table: 

`Filter(Personnel, Text(isStudent) = "Yes")

^I don't really like this because it nullifies the 'StudentName' lookup column in the "GradeSheet" table, and just patches in the value from a different table (which just happens to be the same table that the lookup is pointing to.)

I don't know if it's relevant, but most of the time I get this message as well: 

"This Item.StudentName.isStudent = This formula uses scope, which is not presently supported for evaluation." | Data Type:  "

[v-jefferni · Accepted solution]

Hi @tellerj ,

Please try below instead:

Filter(Choices([@GradeSheet].cr4e5_StudentName), isStudent)

Best regareds,

[tellerj]

That works! Thank you! The OptionSet type is really annoying. The documentation is poor about how to interact with it, and in what situations it acts like 'Text' and what situations it acts like 'Boolean'. Thank you again.