Composite SortByColumns, Filter and Search Gallery Formula Question

[tagustin2020]

Hello,

I've been trying to filter my gallery by logged in user and status with no success so I am hoping you can help me. I have the SortByColumns, Dropdown, and Search formulas working. After we figure out the Filter portion, I was wondering if you could help me with the proper syntax to get all 3 components knitted together. I've attached screenshots of my test app OnStart variables, and the filter formula that I tried as well as pasting the formulas below. Please let me know if I have left out any key pieces of information necessary to troubleshoot this. I also need to provide an option to view the gallery in its entirety for the sake of people working the requests and managers who need a broader view. The logged in user default option should be just that. What do you recommend?

OnStart Formula

I verified that the Collections were set. Please let me know if the syntax below doesn't look right. I tried creating a "regular" Collection before introducing the Table syntax, but I can't get anything to show in the Gallery either way.

Concurrent(Set(varUser,User()),ClearCollect(colRequests,'Tool Design Support Requests'),ClearCollect(colStatus,Table({ReqStatus:"Display All"},{ReqStatus:"Open"},{ReqStatus:"Waiting on Info"},{ReqStatus:"In Design"},{ReqStatus:"Approval Pending"},{ReqStatus:"In Model Shop"},{ReqStatus:"Completed"},{ReqStatus:"On Hold"},{ReqStatus:"Cancelled"})))

Filter formula

This formula doesn't work at all. Zero results appear in the gallery. Am I even close?

If(ddStatus.Selected.Result="Display All",Filter(colRequests,'Creator Email'=varUser.Email),Filter(colRequests,'Creator Email'=varUser.Email && ReqStatus=ddStatus.Selected.Result))

The following formulas are working as expected:

Status dropdown Items formula:

Sort(Distinct(colStatus,ReqStatus),Result)

Default property is set to "Display All"

Search formula:

Filter(colRequests,StartsWith('Request Summary Title',TxtSearchBox.Text))

SortByColumns formula:

SortByColumns(colRequests,"Title")

I've spent so many hours on this it is time to ask for help. I would greatly appreciate your kind assistance.

Thanks! Teresa

[WarrenBelz · Accepted solution]

Hi @tagustin2020 ,

Your syntax is valid and varUser.Email certainly should show the logged-in user's email. A little improvement below

Filter(
   colRequests,
   'Creator Email'=varUser.Email && 
   If(
      ddStatus.Selected.Result="Display All",
      true,
      ReqStatus=ddStatus.Selected.Result
   )
)

when you are testing obviously this will be you and as long as you have records with your email address in 'Creator Email', the filter should work. Am I missing something?

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.

[tagustin2020]

Hello Warren, 

Thank you for taking a look at my post. I copy/pasted the formula you included in your reply into the Items property of the Gallery. I've attached screenshots of the errors I am getting. No results appear in the Gallery. Creator Email is one of the columns in my Collection and I set the User variable in the OnStart of the app so I'm not sure what is going sideways...

Teresa

[WarrenBelz]

Thanks @tagustin2020 ,

The error is showing on the field ReqStatus - is this a Choice Field? If so

Filter(
   colRequests,
   'Creator Email'=varUser.Email && 
   If(
      ddStatus.Selected.Result="Display All",
      true,
      ReqStatus.Value=ddStatus.Selected.Result
   )
)

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.

[tagustin2020]

Hello Warren,

In my SharePoint list, Request Status is a Choice field, but I created a Status Collection/Table in the app to sort by. Here is an excerpt from the OnStart formula mentioned in the original post. Does it look correct to you? 

ClearCollect(colStatus,Table({ReqStatus:"Display All"},{ReqStatus:"Open"},{ReqStatus:"Waiting on Info"},{ReqStatus:"In Design"},{ReqStatus:"Approval Pending"},{ReqStatus:"In Model Shop"},{ReqStatus:"Completed"},{ReqStatus:"On Hold"},{ReqStatus:"Cancelled"}))

I copy/pasted your latest formula suggestion into the Items property of the Gallery, but unfortunately it is still throwing errors. I've attached screenshots for your review. Kind regards, Teresa

[WarrenBelz]

Thanks @tagustin2020 ,

There are only two things you need to consider with Filters: -

• The valid syntax for the output of the control
• The same for the field that is being filtered on.

A Choice field is a complex field type and needs to be referred to as xxxxx.Value, so ReqStatus.Value is how you would refer to it in a filter. The next is the output of the drop-down. If you type in 

ddStatus.Selected.

(Note second dot), the valid values will be displayed immediately underneath. The same goes for ReqStatus.
If your dropdown items are based on colStatus (I assume they are), then you need

Filter(
   colRequests,
   'Creator Email'=varUser.Email && 
   If(
      ddStatus.Selected.Result="Display All",
      true,
      ReqStatus.Value=ddStatus.Selected.ReqStatus
   )
)

Once you get your mind around this, Filters and LookUps  will become far easier for you.

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.

[tagustin2020]

Hello Warren,

Thank you for this explanation. I gave your formula suggestions a try, but unfortunately, I'm getting more errors (screenshots are attached).  When I type in ddStatus.Selected. no options appear in Intellisense for me to select. Did I miss a step when assembling my Collection? "Display All" is not one of the choices in my Choice drop down in my SharePoint list. I added it to the colStatus Collection as an extra record. Does PowerApps not know what to do when "Display All" is selected from the dropdown menu? If not, what do I need to do to convey that all of colRequests should be shown both by default when the user first lands on the screen (where the results match the logged in user) and any other time "Display All" is selected from the dropdown?

This original status dropdown Items formula was working for the dropdown menu so in a second attempt I tried it in combination with your Gallery formula suggestion, but sadly it was still a no-go.

Sort(Distinct(colStatus,ReqStatus),Result)

Default property is set to "Display All"

Thanks for your patience. I can't wait until all this starts making sense since I'm sure it is a skill I will need to apply to each and every app. I appreciate your help. Have a nice evening.

Kind regards,

Teresa

[WarrenBelz]

@tagustin2020 ,

This really should not be so difficult - what are the Items of your drop-down?
If it is colStatus, then "Display All" is the first item and should display (I assume it is a Drop-Down, not a ComboBox). Try taking this out and put Parent.Default.

Can you please summarise this in one spot so we don't have to go back through this growing post

• The Items of your drop-down 
• The Update of the DataCard
• The Default of the drop-down
• The Items of the Gallery

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.

[tagustin2020]

Hello Warren,

Thanks so much for your help! As I reexamined my app in order to respond to your questions from yesterday evening, I realized I need to restructure my data to include request phases as well as statuses so I am going to go ahead and mark your first suggestion as the solution as I'm sure it is solid. I will copy/paste it to my OneNote for future reference. I appreciate your time. Have a great day!

Teresa