Filter query on gallery (filtering multiple columns)

[Doddy83]

Hi All,

After a little help again.

I've got a Gallery with a search box, I need people to enter a number into the search box and this bring back the matching result. It was working fine when I used the following formula:

Filter('LIST_NAME', 'CA_Complete?' = false && CAR_No = inp_SearchBox.Text)

However, I've now been asked to also filter on a column called Initial_Approval, so if it's blank it doesnt bring back the result so they cant progress the CA until it's been initially approved. But when I add this it fails to bring back any results. This is the formula I've tried:

Filter('LIST_NAME', (IsBlank(Initial_Approval)) &&'CA_Complete?' = false && CAR_No = inp_SearchBox.Text).

I've also tried:

Filter('LIST_NAME', (!IsBlank(Initial_Approval)) &&'CA_Complete?' = false && CAR_No = inp_SearchBox.Text) (Which I understand should bring back results that are not blank)

What am I missing here as the formula doesnt error, it just doesnt bring back any results at all when I enter a CAR No into the search box after adding the filter for (!IsBlank(Initial_Approval)) ?

[Doddy83]

@eka24   I've attached a pic of the SP list columns I need to filter on and the search page of the app, CA_Complete needs to be false and there has to be text in the Initial_Approval column for them to appear the the search results. They enter a CAR_No into the search box (inp_SearchBox.Text) to find the Corrective Action so they can amend or progress it.

I don’t want them to see once that have been completed or haven’t been initially approved yet.

I probably wasn’t too clear with what I was after at the start and I've caused confusion!

[eka24]

You didn't say anything whether is working or not. Can you take your time and restate what you want again. You want to filter two columns? Should all the column be filtered at same time or any of them should filter

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[Doddy83]

Ok, so what I need is the following:

To filter two columns at the same time (CA_Complete? & Initial_Approval) using a search text input box.

CA_Complete is a Yes/No choice

Initial_Approval is multiple lines of text and will include the approval comments from an approval flow

So on the search page of the app, CA_Complete needs to be false and there has to be text in the Initial_Approval column for them to appear the the search results. They enter a CAR_No into the search box (inp_SearchBox.Text) to find the Corrective Action so they can amend or progress it.

I don’t want them to see ones that have been completed already or haven’t been initially approved yet.

So far nothing has worked to get both column filtered at the same time, unless I dont use the search box.

If I use the following it DOES filter both colums correctly but this doesnt use the Search Box:

Filter('SHEQ Test Corrective Actions TEST List', 'CA_Complete?' = false, !IsBlank(Initial_Approval))

When I include the search box, regardless of what CA number I enter, it brings back no results. I can confirm that there is an entry in the list that should be showing as this appears when I use the first formula above that doesnt include the search box.

This is the formula I tried that DOESNT work but includes the search box:

Filter('SHEQ Test Corrective Actions TEST List', 'CA_Complete?' = false, !IsBlank(Initial_Approval) && CAR_No = inp_SearchBox.Text)

There are no errors in the formula so I'm happy it's not a simple spelling mistake or anything like that. From my testing, I can use any of the following and they work, but I cant combine them so it all works together.

Works to filter CA_Complete & uses the search box - Filter('SHEQ Test Corrective Actions TEST List', 'CA_Complete?' = false && CAR_No = inp_SearchBox.Text)

Also works but doesnt include the search box -Filter('SHEQ Test Corrective Actions TEST List', 'CA_Complete?' = false && !IsBlank(Initial_Approval))

Interestingly this formula doesnt bring back any results either - Filter('SHEQ Test Corrective Actions TEST List', !IsBlank(Initial_Approval) && CAR_No = inp_SearchBox.Text) So is it a combination of using the search box and filtering on !IsBlank(Initial_Approval) that causes the issue?

Reallly scratching my head here so any help would be much appreciated. I've tried to be as clear as I can but let me know if you need more information.

[Doddy83]

Ok, so I need to be able to filter on multiple columns at the same time (Initial_Approval, CA_Complete? & CAR_No).

Initial_Approval - Multiple lines of text

CA_Complete? - Yes/No

CAR_No - Auto ID

Users will enter their CAR No into the search box and this brings back the Corrective Actions that has not yet been completed and have no text in the Initial Approval column. This is so they cannot see actions that have already been completed and cannot see actions that have not yet been initially approved. They do need to be able to see actions that have text in Initial Approval but have not yet been completed.

Formulas I've tried already:

Doesn’t Work:
Filter('SHEQ Test Corrective Actions TEST List', (!IsBlank(Initial_Approval) && CAR_No = inp_SearchBox.Text))

Filter('SHEQ Test Corrective Actions TEST List','CA_Complete?' = false && (!IsBlank(Initial_Approval) && CAR_No = inp_SearchBox.Text))

Works but doesn’t include the Initial Approval Filter:

Filter('SHEQ Test Corrective Actions TEST List', 'CA_Complete?' = false && CAR_No = inp_SearchBox.Text)

Works but doesn’t include the Search Box:

Filter('SHEQ Test Corrective Actions TEST List','CA_Complete?' = false && (!IsBlank(Initial_Approval)))

None of the above formulas produce errors in PA and unfortunately none of the formulas mentioned previously in this post have worked either. It seems like the combination of filtering the Initial_Approval & using the Search Box causing the issue?

Any help would be much appreciated as this is the final step before I can make this app live for users and it's eagerly awaited but this filter has completely got me!

[Doddy83]

Grrrrr, sorry, the previous reply took ages to show up so I figured it didnt post, so I re-wrote it all again!

I've made this a very messy subject so sorry everyone!

[Doddy83 · Accepted solution]

Used Filter('LIST_NAME', CA_Work_Complete = 0 && 'CAR No' = inp_SearchBox.Text) = Note the =false is changed to =0. This allowed the formula to filter on multiple colums and solved my issue.