Unanswered

Tangled in syntax (filter a gallery)

(0) Share

Report

Posted on by GraemeNZ

235

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
)

Categories:

Building Power Apps

I have the same question (0)

All responses (16)

Answers (1)

WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report
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

Was this reply helpful? Yes No
GraemeNZ 235 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
GinjaFlowNinja 7 on at

Like (0)

Report

Have you tried using the "Run Onstart" command?

Was this reply helpful? Yes No
GraemeNZ 235 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
GraemeNZ 235 on at

Like (0)

Report

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
)

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report
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 ?

Was this reply helpful? Yes No
GraemeNZ 235 on at

Like (0)

Report
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

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report
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.

Was this reply helpful? Yes No
GraemeNZ 235 on at

Like (0)

Report
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

Was this reply helpful? Yes No