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
)

[GraemeNZ]

Hi @WarrenBelz , thanks for your perseverance. Let me go back a bit and give you the back story...

We are a contract manufacturer, where our customers provide us with a recipe and ingredients, and we manufacture for them. Previously it was the customer's responsibility to approve ingredients, but recently our regulatory environment changed so we as the manufacturer need to approve the ingredients. We have a very small team, so the app is a portal that allows our customers to enter their ingredients directly into our system. They state if they have approved the ingredient for use, then we review the supplied information and state if we approve it for use. 

We've been running this app for just about a year now with our biggest customer, and are now rolling it out to our other customers. As we've been doing this, we've found that often our customer's approval requirements aren't as strict as our approval requirements, so we've introduced a new status of Conditional Approval. This means we need to test samples of the ingredient(s) to ensure they meet our requirements before they can be fully approved. This in turn means we need some easy way to review which ingredients have extra testing requirements, and whether or not the extra testing has been done.

The new screen I'm trying to develop takes ingredient data from the existing list and presents it in a different way. The intent is that our ingredient coordinator can quickly access the app, and look up a particular ingredient from a specific customer to see the testing required. 

Note: I don't expect our total ingredient list to exceed 300-400 items, so I'm not really worried about delegation errors.

Where is the filter for ingredients?

• The name of the list is Master Ingredients List, everything here is an ingredient. The filter is whether the item has an FIWApproval (our approval) of Conditional Approval or Approved.

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.

• I realise the OR statement is a true/false test
• The way I want it to work is the same as you have stated here: "If the Combo Box is empty, it will not filter (show all records), if there is something selected in the Combo Box..." then it should filter by what I have selected in the combo box. However, the code you gave me to try had this line: 
  ( IsBlank(cbCustFilter_Ing.Selected.Value) || Customer.Value = gvCustomer ) ),
  Doesn't this mean that you are using the global variable to populate Customer.Value, instead of the choice in the Customer column? 
  
• For this screen/gallery, gvCustomer will be blank - see snip of my test screen:

• Customer is a 'choice' from the column of the same name within the SP list - see snip:

I hope this helps.

GraemeNZ

[WarrenBelz]

@GraemeNZ ,

As you can understand I cannot see your data or logic and there have been multiple changing iterations of the code in the thread - for your Combo Box logic, you need

IsBlank(cbCustFilter_Ing.Selected.Value) ||
Customer.Value = gcbCustFilter_Ing.Selected.Value

in your original code, you had 

Customer.Value = gvCustomer

as part of the filter, so that is probably where it came from.

Can we please deal with this one first and then see if there is anything else not working.

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]

@WarrenBelz , combo box works as expected, thank you.

[WarrenBelz]

@GraemeNZ ,

OK what is not working now ?

[GraemeNZ]

@WarrenBelz The gallery is showing all ingredients, not just the ones where: 

(FIWApproval.Value = "Conditional Approval" || FIWApproval.Value = "Approved") && CustApproval.Value <> "Discontinued"))

[WarrenBelz · Accepted solution]

@GraemeNZ ,

OK - we need to get all this into one piece of code again as I am having difficulty keeping track of what is current here - so how far is this from what you need ?

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

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]

@WarrenBelz you are a champion, that's exactly what I need. Thanks again for your perseverance. I can now see how you've structured this code to achieve the outcome of the embedded If statements within a filter.

Cheers, GraemeNZ