cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hixk
Frequent Visitor

Search, Filter, If Statements, and DropDown Functionality. Hitting a wall.

I am a novice and got stuck... I have all these filters on my 2 dropdowns plus my search bar and now I can't fit in this final piece I need for my gallery. All I want to do it fit the second piece of logic into the existing formulas. Can anyone tell me where it belongs? I'm sure the answer is something obvious but I have spent a ton of time trying to figure this out. Thank you so much in advance!

 

Existing formulas:

SortByColumns(
Search(
If(
ddInvoiceType.Selected.Value = "All Invoice Types" And ddDivision.Selected.Value = "All Divisions",
'Problem Invoices',
ddInvoiceType.Selected.Value = "All Invoice Types" And ddDivision.Selected.Value <> "All Divisions",
Filter(
'Problem Invoices',
'Division'.Value = ddDivision.Selected.Value
),
ddInvoiceType.Selected.Value <> "All Invoice Types" And ddDivision.Selected.Value = "All Divisions",
Filter(
'Problem Invoices',
'Invoice Type'.Value = ddInvoiceType.Selected.Value
),
ddInvoiceType.Selected.Value <> "All Invoice Types" And ddDivision.Selected.Value <> "All Divisions",
Filter(
'Problem Invoices',
'Division'.Value = ddDivision.Selected.Value And 'Invoice Type'.Value = ddInvoiceType.Selected.Value
)
),
TextSearchBox1.Text,
"Search_x0020_Concat"
),
"InvoiceDate",
If(
SortDescending1,
Descending,
Ascending
))

 

What I'm trying to add (this should always be the case for this specific gallery):

Filter('Problem Invoices', 'Status'.Value="Purchasing")

 

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User II
Super User II

Hi @Hixk 

The formula that you have written is in the Items property of the gallery?  To simplify things, I would suggest that you create a collection by pre-filtering the 'Problem Invoices" data set first and then using that colProblemInvoices in your formula iinstated of 'Problem Invoices".  

ClearCollect(colProblemInvoices,Filter('Problem Invoices", 'Status".Value = "Purchasing")

You could put this in the OnVisible property of the Screen containing your gallery.  That way the collection would be filled as soon as you opened the screen. 

View solution in original post

5 REPLIES 5
Drrickryp
Super User II
Super User II

Hi @Hixk 

The formula that you have written is in the Items property of the gallery?  To simplify things, I would suggest that you create a collection by pre-filtering the 'Problem Invoices" data set first and then using that colProblemInvoices in your formula iinstated of 'Problem Invoices".  

ClearCollect(colProblemInvoices,Filter('Problem Invoices", 'Status".Value = "Purchasing")

You could put this in the OnVisible property of the Screen containing your gallery.  That way the collection would be filled as soon as you opened the screen. 

View solution in original post

Hixk
Frequent Visitor

You're awesome. Worked beautifully. Thanks so much. 

4e42b558fea53b8cd862f27c233b09c3.jpg

 

You are very welcome.  It should speed up the search too. 

 

Hixk
Frequent Visitor

Unfortunately adding that collection broke my pdf viewer in my detail/edit screen. I've spent the last hour trying different ways to make that work with the collection,  including trying to create a new attachment collection based on my attachment data card. The closest I got was picking up the attachment from the last record I selected from my gallery. My usual formula of First(BrowseGallery1.Selected.Attachments).Value simply does not work anymore. It's like the collection I added made my Attachment property inaccessible. Hope all that made sense. 

 

Would you happen to know of a way to do this filter without the collection? As much as I liked that option, I think I liked my pdf viewer a little better. If not, I still really appreciate your help. I will keep looking for a solution or find a workaround.

 

Thanks again!

Hixk
Frequent Visitor

Got a good night's sleep and figured it out myself :). Hopefully I'm not missing anything. 

 

SortByColumns(
Search(
If(
ddInvoiceType.Selected.Value = "All Invoice Types" And ddDivision.Selected.Value = "All Divisions",
Filter('Problem Invoices', Status.Value = "Purchasing"),
ddInvoiceType.Selected.Value = "All Invoice Types" And ddDivision.Selected.Value <> "All Divisions",
Filter(
'Problem Invoices',
Division.Value = ddDivision.Selected.Value And Status.Value = "Purchasing"
),
ddInvoiceType.Selected.Value <> "All Invoice Types" And ddDivision.Selected.Value = "All Divisions",
Filter(
'Problem Invoices',
'Invoice Type'.Value = ddInvoiceType.Selected.Value And Status.Value = "Purchasing"
),
ddInvoiceType.Selected.Value <> "All Invoice Types" And ddDivision.Selected.Value <> "All Divisions",
Filter(
'Problem Invoices',
Division.Value = ddDivision.Selected.Value And 'Invoice Type'.Value = ddInvoiceType.Selected.Value And Status.Value = "Purchasing"
)
),
TextSearchBox1.Text,
"Search_x0020_Concat"
),
"InvoiceDate",
If(
SortDescending1,
Descending,
Ascending
)
)

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (20,548)