OneDrive Excel data connection not refreshing

[sa11]

Our app was working without problems until today, when new records created using the app interface stopped showing up on the gallery search. This previously worked fine, and we have made no updates / changes to the app since.

Records created via the app appear in the linked Excel spreadsheet, but these do not appear in the app search. Previously created records do appear.

Refreshing data connections to all tables in the Excel sheet has no effect.

[chsanche]

Hi @sa11 

Do you have any filtering in your gallery that might be affecting? Also, how many rows are you retrieving from the excel file? If you have more than 500 records you might be facing delegation issues: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Best Regards

--

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[newk1991]

Unfortunately search is not a delegable function for an Excel data source. 

You may set the values for the data row limit from 1 to 2000  if you need more there are work arounds to collect all the data in the power app onstart.  Another option is to use a delegable function like startswith()

I would recommend moving the data to a SharePoint list. If you need assistance please feel free to book a free 15 minute consult with me --> digitalms365.com/help 

[sa11]

I don't think filtering is the issue, because for some searches it still shows older versions of entries that have since been changed - the changes are updated in the spreadsheet but just not reflected in the versions shown in the search.

We just went over 500 entries, but I don't get any delegation warnings in the editor. Do Excel connections not support delegation? I thought I was using delegable functions for my search expressions (given below for reference - everything here seems to be allowed). This particular search is only returning 2 entries in the gallery.

SortByColumns(
    Search(
        If(
            And(
                chkInclGone.Value=false,
                chkInclNonChem.Value=false
                ),
            Filter(
                Inventory,
                Status<>"Gone",
                Chemical<>"No"
                ),
            chkInclGone.Value=false,
            Filter(
                Inventory,
                Status<>"Gone"
                ),
            chkInclNonChem.Value=false,
            Filter(
                Inventory,
                Chemical<>"No"
                ),
            Inventory
            ),
        TextInput1.Text,
        "Name",
        "CAS",
        "Barcode",
        "Supplier"
        ),
    "Name"
    )

[newk1991]

Oh I think I know what's going on now. If anyone in the organization has that Excel document open and active anywhere. the power app will not function correctly. 

this does go back to my recommendation to have this in a better data source such as a SharePoint list, the common data service, or SQL

[sa11]

Thanks @newk1991 , I just saw your reply.

So if I want to use search with over 2000 entries I have to use a different data source?

I assume Sharepoint lists work. I tried reworking using those previously, but it wasn't very user friendly.

[newk1991]

I hate to say but SharePoint lists have very similar delegation issues as an Excel workbook. I would either filter the data with a delegate herbal function then wrap that in your search if you need to use the function search. Another option is to do a clear collect on the data source. However again it's only going to pull in the first 2000 records. If you want I can send you a formula that collects the first 2000 then the next two thousand and so on. There are obvious performance issues if you are to take this route. Let me know I'm more than happy to help. 

[sa11]

So if I rework my search instead using Filter with In operator to match search terms within the string for a given column, will this be delegable?

If not I can't see I will solve this problem. The app needs some form of search function.

I may just have to trash this project and use a simple Excel spreadsheet - the whole reason I used PowerApps in the first place was that it offered a simple connection to an Excel sheet that could be used if the app could no longer be maintained or became unmanageable. I do not have resources or dedicated IT support to solve complex problems or establish a SQL server etc.

[newk1991]

--Collect over 2000 records--


This is a common issue. Unfortunately due to delegation issues specific to SharePoint list the search function is not delegable. You can use the function startswith(). However for most people that is not exact solution there looking for.

You can use a clearcollect() to collect in a local cashed table in PowerApps. This will only bring in the first 2000 record. There is a work around...

Basically you will do a filter 2 only show the first 2000 records and put that into a collection. Then you will collect the next 2000 records and so on. Each of these collections must have a different name. Once you have all the collections you can do a clearcollect() on all of the collections you make one massive table. You then will not have any delegation issues when referencing the local collection/table.

Here's an example:

Step 1.)

Concurrent(


ClearCollect(CollectionA,Filter(NameofTable,ID#<2000)),
ClearCollect(CollectionB,Filter(NameofTable,ID# >=2000 And ID<4000)),
ClearCollect(CollectionC,Filter(NameofTable,ID# >=4000 And ID<6000)),


ClearCollect(CollectionD,Filter(NameofTable,ID# >=6000 And ID<8000)),


ClearCollect(CollectionE,Filter(NameofTable,ID# >=8000 And ID<10000)),


ClearCollect(CollectionF,Filter(NameofTable,ID# >=10000 And ID<12000)),


ClearCollect(CollectionG,Filter(NameofTable,ID# >=12000 And ID<14000)),


ClearCollect(CollectionH,Filter(NameofTable,ID# >=14000 And ID<16000))


)


 


Step 2.)


Then Collect your Collections together=)

;ClearCollect(NameofCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF,CollectionG,CollectionH)


 


You should now be able to Filter your NameofCollection with all 15000 records.  It just takes a bit of preloading first to get there.