Tangled in syntax (filter a gallery)

[GraemeNZ]

Hi - I have an app with several different screens which acts as a portal by which our customers can enter relevant information directly into our SharePoint lists. The need has arisen to add a new screen to the existing functionality, with a gallery which I need to filter based on if the item has "Conditional Approval" or is "Approved", and the Customer status isn't "Discontinued". I got this to work like this:

SortByColumns(
Filter(
'Master Ingredients List',
(FIWApproval.Value = "Conditional Approval" || FIWApproval.Value = "Approved") && CustApproval.Value <> "Discontinued"
),
"Title"
)

Since this list will likely become quite large, I want to be able to filter the new gallery by the ingredient name (text input box ) and/or customer name (combox box based on the choices in the SP list). In an existing gallery, this works as below, but note that this gallery also has a filter based on the customer logged on. For the new gallery, I don't need the functionality to filter by the customer logged, as the customers won't be able to see the control to access this screen. 

I tried the following, but it doesn't like the formula and I can't see what I'm doing wrong.  The text input is called TxtIngTestFilter, and the combo box is called cbCustFilter_IngTest.

SortByColumns(
Search(
Filter(
'Master Ingredients List',
(FIWApproval.Value = "Conditional Approval" || HalalStatus.Value = "Approved") && CustApproval.Value <> "Discontinued",
If(IsBlank(cbCustFilter_Ing.Selected.Value),
true,
Customer.Value = cbCustFilter_Ing.Selected.Value
),
),
"Title",
TxtIngFilter.Text,
"Title"
),
"Title",
Ascending
)

[WarrenBelz]

Hi @GraemeNZ ,

You have Title twice in your Search and also an extra comma that would be invalid - try this

SortByColumns(
   Search(
      Filter(
         'Master Ingredients List',
         (
            FIWApproval.Value = "Conditional Approval" || 
            HalalStatus.Value = "Approved"
         ) && 
         CustApproval.Value <> "Discontinued" &&
         (
            IsBlank(cbCustFilter_Ing.Selected.Value) ||
            Customer.Value = cbCustFilter_Ing.Selected.Value
         )
      ),
      TxtIngFilter.Text,
      "Title"
   ),
   "Title"
)

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

[GraemeNZ]

Hi @WarrenBelz 

I copied and pasted your formula and it was accepted as valid by PowerApps, but the gallery is empty. Then I realised that some of the control names are wrong:

- HalalStatus.Value should be FIWApprovalStatus.Value

- cbCustFilterIng should be cbCustFilterIngTest

- txtIngFilter should be txtIngTestFilter

I changed the Halal one first, and the formula is still valid, but as soon as I change the name of the combo box to the correct name, it errors - even when I select the control name from the list in PowerApps.

Any other ideas?

[GinjaFlowNinja]

Have you tried using the "Run Onstart" command?

[GraemeNZ]

Hi @GinjaFlowNinja 

OnStart applies to the whole app doesn't it, rather than to an individual gallery? In my app I have 11 screens with 8 different galleries on 8 of those screens. 

[WarrenBelz]

Hi @GraemeNZ ,

This is where the "boring debugging" comes in and you have started this. You obviously have one or more mismatches between the data type in the List and the value or control output you are needing to compare it with. So keep taking out and putting in pieces until you get everything working - then you can see the error/s.

Looking at your currwnt code (and please post your code in Text to save re-typing), what type of fields are CustApproval and Customer and what are the Items and field displayed of cmbCustFilter_IngTest

[GraemeNZ]

Yep @WarrenBelz - debugging (sigh!). I haven't included the code here because I wanted to take snips showing the code and the effect on the gallery, but I have kept a diary(?) in Notepad++ of what I've tried if that's helpful. (I find Notepad++ very helpful especially with ensuring I have the correct number of brackets in the right places.)

I have no concerns about FIWApproval and CustApproval, cos that bit works fine if I only have SortByColumns and Filter.

My difficulty is when I try to further filter this gallery by customer and/or ingredient name.

Customer: Combo box and associated SP list

SP list settings:

Ingredient Name: Text input,

In SP, this column type is a single line of text.

I know all the other controls etc. work, because they are working on a different screen. I duplicated the screen, renamed the controls, and left the properties the same. The source gallery is a bit different, in that customers using the portal are only able to see their own items, so there are 2 global variables (which are set when the app starts).

If I type "cal" in the text input, only ingredients with "cal" in their name will display, no matter which Customer or which approval status.

If I also filter by customer using the combo box, the gallery view is further refined.

Source gallery code (which works just fine):

/If current user is approver show all, else filter the data based on the customer logged in
SortByColumns(
Search(
Filter(
'Master Ingredients List',
If(
gvCurrentUser = "FIW Approver",
If(IsBlank(cbCustFilter_Ing.Selected.Value),
true,
Customer.Value = cbCustFilter_Ing.Selected.Value
),
Customer.Value = gvCustomer
)
),
TxtIngFilter.Text,
"Title"
),
"Title",
Ascending
)

[WarrenBelz]

Hi @GraemeNZ ,
We need to drill down on the actual issue here I think - this is the way I would write the code (so I am starting with something I know works - If statements inside a filter are never a good idea) so

SortByColumns(
   Search(
      Filter(
         'Master Ingredients List',
         gvCurrentUser = "FIW Approver" &&
         (
            IsBlank(cbCustFilter_Ing.Selected.Value) ||
            Customer.Value = gvCustomer
         )
      ),
      TxtIngFilter.Text,
      "Title"
   ),
   "Title",
   Ascending
)

Assumes only

• gvCustomer is a Text Variable
• Customer is a (single) Choice field in 'Master Ingredients List'
• cbCustFilter_Ing has the output of .Value
• TxtIngFilter.Text is a Text Input control
• Title is a Text field in  'Master Ingredients List'

What is incorrect here and/or what is not working ?

[GraemeNZ]

Hi @WarrenBelz , thanks for trying a completely different way of thinking. When I first tried this, it showed all ingredients, and neither the combo or text input boxes worked. I wrote a reply and was about to shut down PowerApps when I noticed the control names were wrong for the combo and text input.

• Correct combo name is: cbCustFilter_IngTest
• Correct text input name is: TxtIngTestFilter

(Edited reply)

Once I corrected these names I got the following outcomes:

• All ingredients are listed, not just Approved or Conditional Approval ❌
• Text input filter works ✔️
• Customer combo box will return no results, regardless of which customer is selected. I suspected this was because of the OR statement reverting to the value of gvCustomer, because if an approver is logged on then this variable is blank. However, I used the test buttons to manually set the gvCustomer value and the gallery is still empty. ❌

In this screen I don't want to have the Customer value revert to gvCustomer, since this screen won't ever be accessed by users who trigger that variable. 

Cheers, GraemeNZ

[WarrenBelz]

Hi @GraemeNZ ,

I am getting a little lost here 

• All ingredients are listed, not just Approved or Conditional Approval 
  Where is this filter - there is nothing about ingredients in the last bit of code
• Text input filter works 
• Customer combo box will return no results, regardless of which customer is selected. I suspected this was because of the OR statement reverting to the value of gvCustomer, because if an approver is logged on then this variable is blank. However, I used the test buttons to manually set the gvCustomer value and the gallery is still empty. 
  

  The || Or statement is a true/false test - if the Combo Box is empty, it will not filter (show all records), if there is something selected in the Combo Box (why are you not using the value chosen?) then it will filter the Customer (I assume a Lookup column - not the best things to use) looking for the value you have in gvCustomer.