cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

If Statement filtering multiple columns

Hello,

 

I've looked at about every IF and OR statement thread there is on this site and cannot find the exact answer to my issue. We had an app developer build the structure of an app for us and we are now making modifications based on requests from the users of the app. I currently have a gallery that searches (filters, but I'll use search in my thread) in two different text boxes for data in order to produce the results in the gallery. However, we need to be able to search multiple columns/fields in the SharePoint data source as part of this search. I will list the current formula that is only filtering from the ScanTag# column in the data source.

 

If(IsBlank(SearchPopupBarcodeScanLbl.Text),Filter('All Asset List', 'ScanTag#'=SearchTextBox.Text),Filter('All Asset List','ScanTag#'=SearchPopupBarcodeScanLbl.Text))

 

As you can see, it's currently looking at a text box (SearchPopupBarcodeScanLbl.Text) that is populated when scanning a barcode in and produces a result in the gallery if the scan result is found in the ScanTag# column within our data source (All Asset List). However, if the scan text box is blank, it searches for exact data that is manually entered into a separate text box (SearchTextBox.Text), where it will search the same column in our data source, and produce the desired result in the gallery.

 

The problem I'm having is that if the SearchPopupBarcodeScanLbl.Text box is empty, I actually need to search 2 additional columns if exact data is manually entered into the SearchTextBox.Text box. I'm not really sure if this would be accomplished by an Or statement, by stringing multiple arguments within the If statement, or something all together?  I need it to search the ScanTag#, Serial#, and Holder Columns in our data source. I gave it a go with a couple awful attempts at using the Or function with no luck. PowerApps actually accepted the statement but said it might produce inconsistent data with large data sets, but didn't work nonetheless. I'm hoping someone can see my logic behind this, let me know if this can be accomplished, and how.

 

FIRST ATTEMPT If(IsBlank(SearchPopupBarcodeScanLbl.Text),Filter('AllAssetList','Holder'Or'ScanTag#'Or'Serial#'=SearchTextBox.Text),Filter('AllAssetList','ScanTag#'=SearchPopupBarcodeScanLbl.Text))

 

SECOND ATTEMPT

If(Or(IsBlank(SearchPopupBarcodeScanLbl.Text),Filter('All Asset List', 'ScanTag#'=SearchTextBox.Text);Filter('All Asset List', 'Serial#'=SearchTextBox.Text);Filter('AllAssetList','Holder'=SearchTextBox.Text),Filter('AllAssetList','ScanTag#'=SearchPopupBarcodeScanLbl.Text))

 

I will be more than happy to give additional info if needed, thanks.  

Sean

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

@forbudt4u 

I apologize that I missed that key part of your original post.  In re-reading I see where that point is made.

The example I gave was mostly just an example to show the technique as opposed to a pure solution.

 

Your image was very helpful in driving home what you are trying to do.

 

So, let's get more specific.

To cover for both the scan and the search being empty, we just inject a "false" into the formula:

   !(IsBlank(SearchTextBox.Text) && IsBlank(SearchPopupBarcodeScanLbl.Text)) 

This will inject a false into our series of AND's in the formula.  Any false in an AND will result in a false - and thus no record produced.

 

Now, to incorporate the 'ScanTag#', Holder, and 'Serial#', The formula would be this:

Filter('All Asset List', 
    !(IsBlank(SearchTextBox.Text) && IsBlank(SearchPopupBarcodeScanLbl.Text)) &&
    (IsBlank(SearchPopupBarcodeScanLbl.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl.Text) &&
    (IsBlank(SearchTextBox.Text) || 
     StartsWith('ScanTag#', SearchTextBox.Text) ||
     StartsWith(Holder, SearchTextBox.Text) ||
     StartsWith('Serial#', SearchTextBox.Text)
    )
)

 

If I am understanding everything properly now, the above should work for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

5 REPLIES 5
Super User III
Super User III

@forbudt4u 

Avoid using If statement for your filters like that - you end up repeating the filter criteria over and over and it gets very confusing.

Please consider changing your Formula to the following:

Filter('All Asset List', 
    (IsBlank(SearchTextBox.Text) || 'ScanTag#'=SearchTextBox.Text) &&
    (IsBlank(SearchPopupBarcodeScanLbl.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl.Text)
)

 

I hope this is helpful for you. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Ryan,

 

I appreciate the reply and the formula.  However, I don't think I clarified something.  Due to the sensitivity of assets within our company, we need the gallery to start from a blank slate.  Although your formula will search for and produce the desired search result, the gallery actually starts with all records from the record source... which is what we don't want.  I appreciate you mentioning the way in which the current IF statement is poorly written, but it was written from the standpoint of starting with a blank gallery.  

 

I'm also not sure if you were attempting to produce a formula that took care of my original question, but it does not address the formula filtering the two additional columns (Serial# & Holder).  I have attached a screenshot of what I'm trying to do and have included the result I get with your formula for reference.  

 

Thanks again, and I hope this relays some information I might not have explained thoroughly enough.  

 

Sean

Super User III
Super User III

@forbudt4u 

I apologize that I missed that key part of your original post.  In re-reading I see where that point is made.

The example I gave was mostly just an example to show the technique as opposed to a pure solution.

 

Your image was very helpful in driving home what you are trying to do.

 

So, let's get more specific.

To cover for both the scan and the search being empty, we just inject a "false" into the formula:

   !(IsBlank(SearchTextBox.Text) && IsBlank(SearchPopupBarcodeScanLbl.Text)) 

This will inject a false into our series of AND's in the formula.  Any false in an AND will result in a false - and thus no record produced.

 

Now, to incorporate the 'ScanTag#', Holder, and 'Serial#', The formula would be this:

Filter('All Asset List', 
    !(IsBlank(SearchTextBox.Text) && IsBlank(SearchPopupBarcodeScanLbl.Text)) &&
    (IsBlank(SearchPopupBarcodeScanLbl.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl.Text) &&
    (IsBlank(SearchTextBox.Text) || 
     StartsWith('ScanTag#', SearchTextBox.Text) ||
     StartsWith(Holder, SearchTextBox.Text) ||
     StartsWith('Serial#', SearchTextBox.Text)
    )
)

 

If I am understanding everything properly now, the above should work for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

New Member

@RandyHayes 

 

You are awesome... this worked flawlessly!  It actually doesn't even lag, which I was anticipating for Holders with large pools of assets.  

 

Thank you so much for your help.  Have a great rest of your week!

 

Thanks,

 

Sean

Super User III
Super User III

@forbudt4u 

Excellent!  Glad that worked out and sorry to miss those key points in your original post.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

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

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (81,972)