cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blizzgirl
Regular Visitor

Some SharePoint Columns not appearing in PowerApps functions

I'm building an app to interact with our SharePoint list a little better plus adding some functionality with Power Automate that would be too complicated to do with the standalone SharePoint list.  With that said, I have a basic landing page that's just a header, search bar, and a gallery to display the items plus a couple other buttons.

 

I have the text input box and gallery coded correctly to search and display specific columns.  (The warning symbols are delegation warnings.)

blizzgirl_0-1617907055669.png

A: Text input box: Search('Risk Assessments',SearchInput.Text,"Title","ProductName",")

B: Gallery control: Search(SortByColumns('Risk Assessments',"DateOpen",Descending),SearchInput.Text,"Title","ProductName")

 

Those are both functioning.  However, my issue is I have another column titled "Vendor" that I'd like to add into the search parameters, but for the life of me, this column will not show up in the list of available columns to use nor will it work if I just type it in.  This column has always been named that. Its not a new column I added after the creation of the app.  I've tried refreshing the data source, removing it entirely and re-adding it, re-ordering the columns in the SharePoint list.  And it displays just fine in a form or gallery.  In fact, in the screenshot above, the middle field (example: Calabrio) is the data from the Vendor column. 

 

This column is a simple single line of text column with no special settings other than its a required field when adding a new list item.

blizzgirl_1-1617907332863.png

 

If I add "Vendor" into the functions above, I don't get any errors.  The gallery simply doesn't display anything and the search box doesn't work.

blizzgirl_2-1617907656844.png

 

I am at my wits end on why this column specifically will not work within a function.  Is there a delegation on how many columns can be used in functions kind of like how there's delegation on how many fields can be pulled into the app at a time?  Sorry for being so long winded, but wanted to add as much detail as I could think of!  Any suggestions would be much appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
C-Papa
Resolver II
Resolver II

Hi, i would remove all code from the SearchInput.Text control e.g. from your statement above; A: Text input box: Search('Risk Assessments',SearchInput.Text,"Title","ProductName",")

 

i would change the gallery items to:

 

SortByColumns(Filter('Risk Assessments', Or(SearchInput.Text in 'Title',SearchInput.Text in 'ProductName',SearchInput.Text in 'Vendor')) ,"DateOpen",Descending)

FYI - this would still give delegation warnings, you could use StartsWith() to avoid delegation.

 

If this doesn't work you may have renamed your column as SharePoint remembers the first given name to a column not the changed to name. If you highlight the 'Risk Assessments' part of the formula above a drop down will appear showing the data, you can check here that "Vendor" is the correct name for the column. 

 

View solution in original post

3 REPLIES 3
C-Papa
Resolver II
Resolver II

Hi, i would remove all code from the SearchInput.Text control e.g. from your statement above; A: Text input box: Search('Risk Assessments',SearchInput.Text,"Title","ProductName",")

 

i would change the gallery items to:

 

SortByColumns(Filter('Risk Assessments', Or(SearchInput.Text in 'Title',SearchInput.Text in 'ProductName',SearchInput.Text in 'Vendor')) ,"DateOpen",Descending)

FYI - this would still give delegation warnings, you could use StartsWith() to avoid delegation.

 

If this doesn't work you may have renamed your column as SharePoint remembers the first given name to a column not the changed to name. If you highlight the 'Risk Assessments' part of the formula above a drop down will appear showing the data, you can check here that "Vendor" is the correct name for the column. 

 

View solution in original post

blizzgirl
Regular Visitor

Thank you for the quick reply @C-Papa !  That definitely improved search to include all the columns!  It does seem to be excluding any recent list items (earliest ones I'm retrieving results on are from a created date in 2020).  I'm sure that has to do with the delegation limits.  Just curious that it sorts by the most recent using a combination of SortByColumns and Search but doesn't using other functions like Filter.  

C-Papa
Resolver II
Resolver II

hi, i think sortbycolumns() is delegable but the 'in' function isn't so the whole formula becomes not delegable . you could replace

 

Or(SearchInput.Text in 'Title',SearchInput.Text in 'ProductName',SearchInput.Text in 'Vendor')

 

with

 

Or(StartsWith('Title',SearchInput.Text) ,StartsWith('ProductName',SearchInput.Text) ,StartsWith('Vendor',SearchInput.Text))

 

This should make the whole formula delegable so you should be able to order the results by date before they are sent to PowerApps. the only issue with StartsWith() is that if the user doesn't put in the correct start it will not match it, this is a limitation of using SharePoint as a database, however, its a lot better than a few years ago so they might add this option in in the future with a bit of luck.

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

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

Top Solution Authors
Top Kudoed Authors
Users online (69,061)