Answered

Multiple criteria filter with all text

(0) Share

Report

Posted on by MV-30041934-0

I'm trying to filter some dataverse tables using only text fields. These records contain names and birthdays stored as text. Each of month, day, and year are separate columns. The searches users need to do might not have all of the pieces of the date or names. I don't have a problem with the name fields since I'm only using the "in" operator. But when attempting to filter on each date field I end up getting zero results on test data I know exists. My goal is to avoid using a big If statement to skip the date fields that the user hasn't selected. What is wrong with my formula? all of the variables are being set with a search button.

Filter('Datasource',

(varLastName in 'Last Name' Or IsBlank(varLastName)),

(varFirstName in 'First Name' Or IsBlank(varFirstName)),

(varBirthMonth = 'BirthMonth' Or IsBlank(varBirthMonth)),

(varBirthDay = 'BirthDay' Or IsBlank(varBirthDay)),

(varBirthYear='BirthYear' Or IsBlank(varBirthYear))

)

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

WarrenBelz 154,540 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @dsagfhjj ,

What type of fields are BirthDay, BirthMonth and BirthYear ? Also how and what to do you set the relevant Variables ?

Was this reply helpful? Yes No
MV-30041934-0 97 on at

Like (0)

Report
Copy link

Link copied!

Those fields are text fields in the database. The variables are gathered from text boxes and specifically saved as type Text in order to match to the database field type. If the user doesn't use all of the text boxes, the variable is set to blank. When everything is blank, this formula works fine. Whenever a date is searched, no results are ever returned. I've validated the types do match. I'm sure something is wrong with how I'm approaching the idea of "if they didn't fill out the field, don't filter on that field".

Was this reply helpful? Yes No
WarrenBelz 154,540 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@dsagfhjj ,

OK - just checking the obvious. If they are all Text fields and Variables, you might try this (not sure it will help)

Filter( 'Datasource', (varLastName in 'Last Name' Or Len(varLastName) = 0), (varFirstName in 'First Name' Or Len(varFirstName) = 0), ('BirthMonth' = varBirthMonth Or Len(varBirthMonth) = 0), ('BirthDay' = varBirthDay Or Len(varBirthDay) = 0), ('BirthYear' = varBirthYear Or Len(varBirthYear) = 0) )

Also note that = is case sensitive, so ensure both the field and the Variable are identical.

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.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Verified answer

MV-30041934-0 97 on at

Like (0)

Report
Copy link

Link copied!

Thank you for the suggestion. Changing the order of the comparison solved the problem.
Filter('Datasource',
(varLastName in 'Last Name' Or IsBlank(varLastName)),
(varFirstName in 'First Name' Or IsBlank(varFirstName)),
('BirthMonth'=varBirthMonth Or IsBlank(varBirthMonth)),
( 'BirthDay=varBirthDay ' Or IsBlank(varBirthDay)),
('BirthYear'=varBirthYear Or IsBlank(varBirthYear))
)

Was this reply helpful? Yes No
WarrenBelz 154,540 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@dsagfhjj ,

Seems to be the same code you originally posted . . .

Was this reply helpful? Yes No