Answered

Filter by user and search results with searchbox

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I'm running into errors when trying to add a search box to my filtered data. Can anyone see where I am messing up in my formula? My current formula in the browse gallery is pasted below and works without an issue. I get errors when I go to add the search function. The dataset is a SharePoint list.

Filter('Actuals (WRFP-1)',Or('GWA ID L/U:Account Manager_TXT' .Value=CurrentUser, 'GWA ID L/U:Sales Manager_TXT' .Value=CurrentUser))

I tried adding the search function with the below formula but I'm getting errors.

Search(Filter('Actuals (WRFP-1)',Or('GWA ID L/U:Account Manager_TXT' .Value=CurrentUser, 'GWA ID L/U:Sales Manager_TXT' .Value=CurrentUser)), 'Search Box'.Text,'GWA ID L/U:BillingID_Name_DataCard1')

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

Please consider changing your Formula to the following:

With({_items: Filter('Actuals (WRFP-1)', 'GWA ID L/U:Account Manager_TXT'.Value=CurrentUser || 'GWA ID L/U:Sales Manager_TXT'.Value=CurrentUser) ) }, Search( AddColumns(_items, "_billID", 'GWA ID L/U:BillingID_Name_DataCard1'.Value ), 'Search Box'.Text, _billID ) )

I hope this is helpful for you.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes The filter function is throwing the below errors. Did I not input it correctly? I was able to get the formula to work with the following changes. Do you see any issues with the new code?

With({_items:
Filter('Actuals (WRFP-1)',Or(
'GWA ID L/U:Account Manager_TXT'.Value=CurrentUser,
'GWA ID L/U:Sales Manager_TXT'.Value=CurrentUser)
)
},
Search(
AddColumns(_items, "_billID", 'GWA ID L/U:BillingID_Name'.Value
), 'Search Box'.Text, "_billID"))

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

The only difference is that you are using the long-hand version of OR whereas mine was using the short-hand version.

And the problem was that I had an additional closing paren.

Should have been:

With({_items: Filter('Actuals (WRFP-1)', 'GWA ID L/U:Account Manager_TXT'.Value=CurrentUser || 'GWA ID L/U:Sales Manager_TXT'.Value=CurrentUser ) }, Search( AddColumns(_items, "_billID", 'GWA ID L/U:BillingID_Name_DataCard1'.Value ), 'Search Box'.Text, _billID ) )

Either will work.

Sorry for the typo...extra parens are free!! 🤣

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes lmao no worries about the typo. I checked them and counted myself and didn't catch it. One final question. Once the user selects the account they want it takes them to the next screen where I have BrowseGallery1.Selected as my formula. This page is now blank and the selected is throwing an error with the new formula on the previous screen. Do you know what is needed to resolve this?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@Anonymous

Yes, we added a column in the process of your search. If you have schema dependencies in other places, then it will cause some issues.

The need is to just drop the column.

Here is that formula:

With({_items: Filter('Actuals (WRFP-1)', 'GWA ID L/U:Account Manager_TXT'.Value=CurrentUser || 'GWA ID L/U:Sales Manager_TXT'.Value=CurrentUser ) }, DropColumns( Search( AddColumns(_items, "_billID", 'GWA ID L/U:BillingID_Name_DataCard1'.Value ), 'Search Box'.Text, _billID ), "_billID" ) )

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes Thank you for all of your help!! I'm migrating an InfoPath form and working through the learning curve.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Very good! Just keep in mind that InfoPath is more "development" like. PowerApps is a no-code, Excel-like platform. Once that is embraced, it is pretty simple. 😁

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes Does the SP experience matter for Powerapps? All of our lists are in classic vs new. Also is there a trick to getting the information to write back to SP? I searched and read responses and have tried some of the responses with no luck. I did see a response you gave about 4 or 5 things that could be it and I tried them all except deleting and creating a new form.

I have onselect set to Submitform(Form1) and in the Item of form one I have '2022 Forecast Gallery'.Selected
I do not have any required fields in this list that would be causing the issue.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Fairly good working knowledge of SharePoint is a real plus if you are going to work with that data source (in fact knowledge about any datasource you work with would be highly beneficial.)

You appear on the right track...you should have a form. Its Datasource should be set to your list. The Item property will be the record you are trying to work with.

In your case, the '2022 Forecast Gallery' should have its items based on the same datasource as your Form.

Then, SubmitForm(formx) is all you need to submit it.

If you create a form and modify only those above properties, then it should work without any issue. If there are modifications done, then that is where issues can creep in.

Explain more about the problem you are experiencing and what you have done up to that point.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes Thank you, my item field was blank and that was the problem. Your message triggered me to look at it and make sure it was set up properly. Thank you again for all the help you've provided. I'm proficient in SP and have been working in it for 3 years. This site that I started managing about a year ago was set up in classic due to workflows being utilized prior to that function being discontinued. I have set up other informational sites in the new look but have been hesitant to migrate this one over. This site I currently manage has a lengthy list of permissions so that each sales team can only see their customers and not others. This is why I needed the filter formula in place in the app.

If you don't mind I have one final question. Those fields I have my filter set up for are lookup fields in SP. I get a delegation error but since my list is sub 2K records it seems to be running ok. On initial startup of the app there is about a 20 second load time for accounts to populate. Would this load time be sped up if I were to create text columns for the filter to reference instead of lookups?

Was this reply helpful? Yes No