cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advok8
Frequent Visitor

Trouble with Filtering a gallery with two dependent statement

I have a Gallery connected to a collection. I want to be able to filter the database and update the collection based on two conditions.

1. I want to filter the ID column in my database table based on the ID typed in a text box and display the records that matched in my gallery

2. if the ID column in my database does not match the ID in the text box, I want to filter my database to show the ID column in my database table that is empty in the gallery.

 

I used this statement but it is not showing me the records from my database where the ID Column is empty.

ClearCollect(colSectmain, Filter(IAP_SectionMaintenaces,Sectmain_EventID=dd.SelectedText.Value || Sectmain_EventID = ""));

 

I would appreciate any help i can get.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

If(  CountRows(Filter(IAP_SectionMaintenaces,Sectmain_EventID=dd.SelectedText.Value))>0, 

ClearCollect(colSectmain, Filter(IAP_SectionMaintenaces,Sectmain_EventID=dd.SelectedText.Value)),

ClearCollect(colSpectmain,FirstN(Filter(IAP_SectionMaintenaces,IsBlank(Sectmain_EventID)),5)));

 

Try this code - what it does:

If there are any records with the first argument filter criteria (Countrows >0) then Create collection with this Filter On, Else return first 5 records which does not contain ID,

Hope I've managed to close all parenthesis and the function will work, Post screenshot with the error if it doesn't

 

BR

Zbigniew

View solution in original post

3 REPLIES 3
Z_Lukowski
Resolver I
Resolver I

Hello,

Try:

ClearCollect(colSectmain, Filter(IAP_SectionMaintenaces,Sectmain_EventID=dd.SelectedText.Value || IsBlank(Sectmain_EventID)));

It should work because the column is null, it doesn't contain an empty string yet

 

Tell me if that helped,

BR

Zbigniew

Thanks for the help. However, when I have an ID in my database it is displaying both the records that matches the ID and those Records without ID.

I only want to display records with ID when I have a match but if there is no match I only want to display the 5 default records where the ID Column is empty

If(  CountRows(Filter(IAP_SectionMaintenaces,Sectmain_EventID=dd.SelectedText.Value))>0, 

ClearCollect(colSectmain, Filter(IAP_SectionMaintenaces,Sectmain_EventID=dd.SelectedText.Value)),

ClearCollect(colSpectmain,FirstN(Filter(IAP_SectionMaintenaces,IsBlank(Sectmain_EventID)),5)));

 

Try this code - what it does:

If there are any records with the first argument filter criteria (Countrows >0) then Create collection with this Filter On, Else return first 5 records which does not contain ID,

Hope I've managed to close all parenthesis and the function will work, Post screenshot with the error if it doesn't

 

BR

Zbigniew

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.

Users online (2,134)