cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering with a Nested If statement

I am trying to create a dropbox that filters my gallery based on the custom value selected. I have 4 Options that I would like them to choose from. 

Option 1: Active Waivers

Option 2: Expired Waivers

Option 3: Waivers Expiring within 12 Months

Option 4: Active Waivers with Conditions

 

I am having problems getting the functionality to work. Here is my formula located in the Items part of Gallery 1: 

Filter('Waiver Master List',

If(Dropdown1.SelectedText.Value= 'Active Waivers', 'Is it Active?'= "yes", 'Waiver Master List'),

If(Dropdown1.SelectedText.Value = 'Expired Waivers', 'Waiver Expiration Date' < Today(), 'Waiver Master List'), If(Dropdown1.SelectedText.Value = 'Waivers Expiring in 12 Months', 'Waiver Expiration Date' > Today()+ 365, 'Waiver Master List'), If(Dropdown1.SelectedText.Value = 'Active Waivers w/ Conditions', Conditions= (Not(IsBlank(Conditions))) & 'Is it Active?' = 'yes', 'Waiver Master List'))

 

To further explain, I essentially want the dropbox to return the value that matches the if statement. For example if they select 'Active Waivers', all the items where the column 'Is it Active?' = yes should show up and if not return the full Sharepoint list and so on for each. I am not sure if I am in the right direction and would greatly appreciate some assistance. Thanks. 

 

This is in the items part of my dropbox: ["","Active Waivers", "Expired Waivers","Waivers Expiring in 12 Months", "Active Waivers w/ Conditions"] These are custom names not pulled from a source. 

9 REPLIES 9
8BitWarrior
Advocate III
Advocate III

@Anonymous  - I believe your issue lies with using SelectedText.

Change all of these values in your logic Dropdown1.SelectedText.Value  to Dropdown1.Selected.Value .

@Anonymous 

Your Gallery item logic should be written like this:

If(
   Dropdown1.Selected.Value= 'Active Waivers',Filter('Waiver Master List', 'Is it Active?'= "yes"),
Dropdown1.Selected.Value = 'Expired Waivers',Filter('Waiver Master List', 'Waiver Expiration Date' < Today()),
Dropdown1.Selected.Value = 'Waivers Expiring in 12 Months',Filter('Waiver Master List', 'Waiver Expiration Date' > (Today()+ 365)),
Dropdown1.Selected.Value = 'Active Waivers w/ Conditions',Filter('Waiver Master List', Not(IsBlank(Conditions)) = true && 'Is it Active?' = 'yes'),
'Waiver Master List'
)

A Drop Down input is a good case to use a switch statement, It checks the value from one source against multiple inputs, with a default value at the end.

Switch(
   Dropdown1.Selected.Value, 
'Active Waivers',Filter('Waiver Master List', 'Is it Active?'= "yes"),
'Expired Waivers',Filter('Waiver Master List', 'Waiver Expiration Date' < Today()),
'Waivers Expiring in 12 Months',Filter('Waiver Master List', 'Waiver Expiration Date' > (Today()+ 365)),
'Active Waivers w/ Conditions',Filter('Waiver Master List', Not(IsBlank(Conditions)) = true && 'Is it Active?' = 'yes'),
'Waiver Master List'
)

 

Anonymous
Not applicable

I am getting a lot of 'Name isn't valid' errors and Invalid argument types. Do you think I am getting these errors because the drop down options are custom, instead of being pulled from the SharePoint? 

Anonymous
Not applicable

Would I put this on the dropdown item or gallery item? Either way, they both produced a lot of errors and did not work for me

@Anonymous 

When comparing text values you need to wrap the text in double quotes --> example:  "text".
Also, when referencing SharePoint column names with spaces you need to enter the correct syntax. 

For example:
If you SharePoint column name is Field 1 you would need to reference it as --> Field_x0020_1

It's good practice to not use spaces in SharePoint columns, instead use a combination of upper and lower case characters or underlines:
ColumnExample1
Column_Example_2

@Anonymous 

Your code would then be modified to the following:

If(
   Dropdown1.Selected.Value= "Active Waivers",Filter('Waiver Master List', Is_x0020_it_x0020_Active?= "yes"),
Dropdown1.Selected.Value = "Expired Waivers",Filter('Waiver Master List', Waiver_x0020_Expiration_x0020_Date < Today()),
Dropdown1.Selected.Value = "Waivers Expiring within 12 Months",Filter('Waiver Master List', Waiver_x0020_Expiration_x0020_Date > (Today()+ 365)),
Dropdown1.Selected.Value = "Active Waivers with Conditions",Filter('Waiver Master List', Not(IsBlank(Conditions)) = true && Is_x0020_it_x0020_Active? = "yes"),
'Waiver Master List'
)

 

Also, just a note on your code - when you are using a statement like this Dropdown1.Selected.Value = "Active Waivers with Conditions" you need to make sure that the text value is exactly the same as the value in the dropdown list (case sensitive):

 

For Example:
This --> Dropdown1.Selected.Value = "Active Waivers w/Conditions" will result in never having a match for that condition
This --> Dropdown1.Selected.Value = "Active Waivers with Conditions" can result in a match for that condition

 

Anonymous
Not applicable

Thank you, 

 

I changed the column names so nothing is containing spaces. However, the statements containing "WaiverExpirationDate" is giving me the error "Invalid Argument Type" expected a number value. 

The values in the excel appears as follows'8/24/2000' sharepoint list looks like the following '42089'

@Anonymous 

 

This appears to be a common issue between Excel and SharePoint. This post offers some suggestions on how to tackle that situation:
https://powerusers.microsoft.com/t5/Building-Power-Apps/PowerApps-Excel-Date-Filtering-Problem/td-p/124587

 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (20,539)