Solved: Re: Switching from Excel in Onedrive to Sharepoint...

MartySt · ‎06-02-2020

Hello,

I am building an app that was pulling data from an excel file in onedrive. The issue I ran into is that any users that are not logged in under my account cannot access any of the data since the app is pulling from my personal one drive. In order to fix this, I have created a sharepoint list that way the data can be accessed by anyone. I'm having a bit of trouble after connecting to the new data source (sharepoint list), I need to redo a lot of code but for some reason the sharepoint list is not working with the code I'm used to. For example here is the data source in a gallery:

Sort(Filter(Table1,NO=ID,QOrder,SortOrder.Ascending)

This filters through the excel list for any data in the column 'NO' that matches with a specific ID (a variable). It then sorts that data by QOrder, and sorts ascending. This works just fine with onedrive/excel, and my entire app is built off of this gallery.

Now here is what I have tried with the sharepoint list with no luck.

Sort(Filter('SharepointList',NO=ID),QOrder,SortOrder.Ascending)

The sharepoint list is identical to the excel list in it's column names, and even trying different column names did not help, no data is returned but there is no error either, just a delegation warning (my data will be less than the limit of I think 500?) I currently only have 30 some rows, so I doubt it's due to a delegation error.

I have tried also:

Sort(Filter('SharepointList',ID in NO,QOrder,SortOrder.Ascending)

Which DOES return a single row of data, instead of ALL of the data with that specific ID. But this does indicate to me that its able to pull data from the correct place.

Is there something wrong with my syntax? Any ideas?

Thanks a ton!

timl · ‎06-02-2020

Hi @MartySt

I wouldn't worry too much yet - there isn't anything unusual about what you want to do, so I'm sure that someone here can answer this.

The comment I would make is that all SharePoint lists include a built-in field called ID.

Therefore, the following function may be attempting to return rows where the value of the ID field matches the value of the NO field. This would explain why you don't see any matching rows.

Sort(Filter('SharepointList',NO=ID),
     QOrder,
     SortOrder.Ascending
)

To confirm that this isn't due to a naming conflict, could you rename your variable to a different name (for example varID) and see if that makes a difference?

View solution in original post

KrishnaV · ‎06-02-2020

Hi @MartySt,

Few things I want to suggest here to speedup your productivity:

You don't have to copy the excel to SharePoint List
Copy the Excel to a document library in SharePoint
make that document library as readonly to all the team memebers
Now come back to your PowerApp and change the connection from OneDrive to SharePoint
That's all you are set no further changes needed
you will also see a major performance improvement with Excel Vs SharePoint list, unfortunately SharePoint API take long time to read/update time when you compare with Excel operations.

I hope this resolved your issue if you see any challenge/need further help let me know I am always happy to help.

Regards,
Krishna
If this post helps give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP, Microsoft Certified Trainer
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a THUMS-UP and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

eka24 · ‎06-02-2020

Can you give details of the the variable ID

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

MartySt · ‎06-02-2020

Hi @KrishnaV ,

I would absolutely love to be able do this, I have tried this method earlier and I run into the following problem:

on your step 4 "Now come back to your PowerApp and change the connection from OneDrive to SharePoint", there is no connector to do this. Unfortunately the sharepoint connector only allows me to connect to a sharepoint list, not to anything in documents. Is there another connector that can do this?

I've searched online and some people seem to have made flows that do this manually, but that's quite a bit of work and I'd rather just make the sharepoint list work for me instead of messing around with flows for hours.

Thanks

KrishnaV · ‎06-02-2020

Hi @MartySt ,

Yes, all we need to do is only add a SharePoint connection, but there is no difference in the operations. I mean when you refere the excel from OneDrive or from a SharePoint library they both are same. all we need to do is assign the appropriate data source to gallery.

If you see any challenge/need further help let me know I am always happy to help. Let me see if I can create a working model for you.

Regards,
Krishna
If this post helps give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP, Microsoft Certified Trainer
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a THUMS-UP and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

MartySt · ‎06-02-2020

Hi @eka24 ,

A bit of background info, in my excel table I have a number of questions related to a piece of equipment for a pre-use checklist to be filled out. These questions are grouped together by the NO column, here is a very reduced example of what my excel data contains:

NO	Question	QOrder
1	Is the forklift damaged	1
1	Is the forklift working	2
2	Is the crane damaged	1
2	Is the crane working	2

So in a separate section of the app, you would click on "forklift", which would set an ID=1, and you would then filter the table above for NO=ID, so it will return the first two rows of the example table above. This works well in practice, but not with the sharepoint list, for some reason.

Thanks!

MartySt · ‎06-02-2020

@KrishnaV ,

The issue is I cannot add the sharepoint connection, it does not allow me to add the excel file that is on sharepoint. It only allows sharepoint lists.

eka24 · ‎06-02-2020

If you set a variable with the ID, then the formula should contain the variable name and not the ID column:

Then filter:

Sort(Filter('SharepointList',NO=VariableName),QOrder,SortOrder.Ascending)

That's why I requested for the exact formula used in the variable.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

MartySt · ‎06-02-2020

Sorry I may have misspoke, ID is not a column, NO is the column. So the way I see it is that using the filter function, it searches every row of data and returns any true conditions, where the logic tests column NO for a match with variable ID (which is set prior). In practice this works with no issues in excel, but does not work for the sharepoint list, I'm not sure if I'm misunderstanding what you are saying.

MartySt · ‎06-02-2020

@eka24 Update: It looks like this may be a delegation issue? I've found a similar thread to mine, where someone is trying to use the lookup function with a sharepoint list with no success. A community support member replied with: Issue confirmed, lookup field is not supported delegation when working with Data Sources, like CDS, SharePoint List.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-function-become-non-delegable-when-us...

So it looks like this was a content request created almost 3 years ago...Am I just doomed here? If sharepoint lists wont work for me and I cant use an excel file on sharepoint, what are my options?