Some SharePoint Columns not appearing in PowerApps functions

[blizzgirl]

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

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.

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.

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!

[C-Papa · Accepted solution]

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. 

[blizzgirl]

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]

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.