cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mprice
Helper II
Helper II

Filter and Searching error with =

Hello,

 

I am working on a function to use a search function, after filtering through two drop downs. I have gotten similar function to work in the past. I know I have a function for filtering one drop down to work: Filter('K.E.Y.S', Software=SoftwareDD.Selected.Value) as well as Filter('K.E.Y.S', Sect=SectorDD.Selected.Value)

 

My problem is not with the conditional statements. My drop downs are SectorDD and SoftwareDD. My table is 'K.E.Y.S' and my colums are called 'Sect' and 'Software', and they are both of the Choice data type. 

 

The errors that it is showing for me is the logical operators of = and <> which will be bolded, underlined and red in the code below.

 

 Search(
If(
//All,All,All
SectorDD.Selected.Value = "-" And SoftwareDD.Selected.Value = "-",
'K.E.Y.S',
//Specific,All
SectorDD.Selected.Value <> "-" And SoftwareDD.Selected.Value = "-",
Filter('K.E.Y.S', Sect = SectorDD.Selected.Value),
//all,specific
SectorDD.Selected.Value = "-" And SoftwareDD.Selected.Value <> "-",
Filter('K.E.Y.S', Software = SoftwareDD.Selected.Value),
//specific,specific
SectorDD.Selected.Value <> "-" And SoftwareDD.Selected.Value <> "-",
Filter('K.E.Y.S', Sect = SectorDD.Selected.Value And Software = SoftwareDD.Selected.Value)
),
KeySearch.Text,
"cr0fd_name"
)

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @mprice,

Sorry for the late reply, have you solved your problem?

Actually, the Choice column in Dataverse is an option-set-value that could not be compared with the text value like "SectorDD.Selected.Value" in your scenario.

So, to avoid this, please format the option-set-value as below:

Text(SectorDD.Selected.Value) = "-" And Text(SoftwareDD.Selected.Value) = "-"

Modify your formula as below:

Search(
    If(
//All,All,All
        Text(SectorDD.Selected.Value) = "-" And Text(SoftwareDD.Selected.Value) = "-",
        'K.E.Y.S',
//Specific,All
        Text(SectorDD.Selected.Value) <> "-" And Text(SoftwareDD.Selected.Value) = "-",
        Filter(
            'K.E.Y.S',
            Sect = SectorDD.Selected.Value
        ),
//all,specific
        Text(SectorDD.Selected.Value) = "-" And Text(SoftwareDD.Selected.Value) <> "-",
        Filter(
            'K.E.Y.S',
            Software = SoftwareDD.Selected.Value
        ),
//specific,specific
        Text(SectorDD.Selected.Value) <> "-" And Text(SoftwareDD.Selected.Value) <> "-",
        Filter(
            'K.E.Y.S',
            Sect = SectorDD.Selected.Value And Software = SoftwareDD.Selected.Value
        )
    ),
    KeySearch.Text,
    "cr0fd_name"
)

Hope it could help you.

Regards,

Qi

 

Best Regards,
Qi

View solution in original post

5 REPLIES 5
v-qiaqi-msft
Community Support
Community Support

Hi @mprice,

Could you please share more details about your need?

How you set the Items property of SectorDD and SoftwareDD, Choices('K.E.Y.S'.Software)?

Is there an option like "-" existing in your Sector and Software or you just want to represent it as blank?

Please do answer the above questions.

I assume that you have a "-" option in your Choice column, and you want to filter the 'K.E.Y.S' based on different If statement.

I have a test on my side, please take a try as below.

In my scenario, SingleChoice and Projects are both Choice data type, Combo Box11 and Combo Box12 represents SingleChoice and Projects.

I set the Items property of the Combo Box as below:

Choices(SPList1.SingleChoice) // For Combo Box11 Items
Choices(SPList1.Projects) // For Combo Box12 Items

Set the Items property of the Gallery as below:

Search(
    If(
//All,All,All
        ComboBox11.Selected.Value = "-" And ComboBox12.Selected.Value = "-",
        SPList1,
//Specific,All
        ComboBox11.Selected.Value <> "-" And ComboBox12.Selected.Value = "-",
        Filter(
            SPList1,
            SingleChoice.Value = ComboBox11.Selected.Value
        ),
//all,specific
        ComboBox11.Selected.Value = "-" And ComboBox12.Selected.Value <> "-",
        Filter(
            SPList1,
            Projects.Value = ComboBox12.Selected.Value
        ),
//specific,specific
        ComboBox11.Selected.Value <> "-" And ComboBox12.Selected.Value <> "-",
        Filter(
            SPList1,
            SingleChoice.Value = ComboBox11.Selected.Value And Projects.Value = ComboBox12.Selected.Value
        )
    ),
    TextInput6.Text,
    "Title"
)

Note that I have a SP list as my data source and you should refer to Choice column as Sect.Value.

On your side, please modify as below:

Search(
If(
//All,All,All
SectorDD.Selected.Value = "-" And SoftwareDD.Selected.Value = "-",
'K.E.Y.S',
//Specific,All
SectorDD.Selected.Value <> "-" And SoftwareDD.Selected.Value = "-",
Filter('K.E.Y.S', Sect.Value = SectorDD.Selected.Value),
//all,specific
SectorDD.Selected.Value = "-" And SoftwareDD.Selected.Value <> "-",
Filter('K.E.Y.S', Software.Value = SoftwareDD.Selected.Value),
//specific,specific
SectorDD.Selected.Value <> "-" And SoftwareDD.Selected.Value <> "-",
Filter('K.E.Y.S', Sect.Value= SectorDD.Selected.Value And Software.Value = SoftwareDD.Selected.Value)
),
KeySearch.Text,
"cr0fd_name"
)

Check if this could help you.

Regards,

Qi

Best Regards,
Qi

Choices('K.E.Y.S'.Sect), (same for Software). Are Choice columns in the table, and yes "-" is an option. I could not figure out how to get "-" or "All" any other way. 

 

Again, the problem I am running into is with the = and <> in this line of code.

SectorDD.Selected.Value <> "-" And SoftwareDD.Selected.Value = "-",

Hi @mprice

Would you please tell me that if your data source is Dataverse or SharePoint list, from the context, I think it is a table including Choice field in Dataverse, right?

In addition, you tried my formula in your scenario and the error is still, right?

Please provide a screenshot to describe your error message, not only a red-line error but also the error message.

Regards,

QI

Best Regards,
Qi

Choice field in the data verse and 

mprice_0-1623376685104.png

 

Hi @mprice,

Sorry for the late reply, have you solved your problem?

Actually, the Choice column in Dataverse is an option-set-value that could not be compared with the text value like "SectorDD.Selected.Value" in your scenario.

So, to avoid this, please format the option-set-value as below:

Text(SectorDD.Selected.Value) = "-" And Text(SoftwareDD.Selected.Value) = "-"

Modify your formula as below:

Search(
    If(
//All,All,All
        Text(SectorDD.Selected.Value) = "-" And Text(SoftwareDD.Selected.Value) = "-",
        'K.E.Y.S',
//Specific,All
        Text(SectorDD.Selected.Value) <> "-" And Text(SoftwareDD.Selected.Value) = "-",
        Filter(
            'K.E.Y.S',
            Sect = SectorDD.Selected.Value
        ),
//all,specific
        Text(SectorDD.Selected.Value) = "-" And Text(SoftwareDD.Selected.Value) <> "-",
        Filter(
            'K.E.Y.S',
            Software = SoftwareDD.Selected.Value
        ),
//specific,specific
        Text(SectorDD.Selected.Value) <> "-" And Text(SoftwareDD.Selected.Value) <> "-",
        Filter(
            'K.E.Y.S',
            Sect = SectorDD.Selected.Value And Software = SoftwareDD.Selected.Value
        )
    ),
    KeySearch.Text,
    "cr0fd_name"
)

Hope it could help you.

Regards,

Qi

 

Best Regards,
Qi

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,192)