Solved: Re: Static excel data vs dynamic excel data

Anonymous · ‎08-15-2019

Hi guys,

i am trying now to experiment a little bit in PowerApps combined with SharePoint.

Right now I'm working on the Service Desk Template. This templates uses an Excel file for retrieving the data.This excel file is located in my OneDrive folder.

If I now share this app to my friend. He just sees the UI without any content. Then I thought to move the file to a public SharePoint site and redirect PowerApps to this excel file. But right now the only data source I can choose from is static excel data.

But this will be contradictory with my functionality. I also implemented a flow that when a item is created on a SharePoint this item will be added to the xls file that is the base for PowerApps. This "Dynamic" version was only working in the case that I stored the xls file on my OneDrive, but then I was the only to see it.

Maybe you get with what I'm struggling right now. Hope that someone can help me 🙂

If you need more information please feel free to ask.

Thank you very much in advance.

Pitfrog

timl · ‎09-06-2019

Hi @Anonymous

Apologies for the delay in replying. In answer to your first question, I'm not exactly sure why you need to navigate away to another page and back again to see the items.

I see that in the Service Desk template, the main list is bound to a collection called TicketsCollect1 and that this collection is populated from a timer. I would try copying this formula to the OnStart property of your app so that it runs when your app starts. This might help.

I'm not sure about the second issue. Could you screenshot this error? Something elses that's worth noting is that in the origanl Excel version, I couldn't actually find formula that saves the local collection back to the orginal Excel data source.

View solution in original post

timl · ‎08-15-2019

Hi @Anonymous

For both you and your friend to access your Excel file, it's necessary to configure the OneDrive settings to share the Excel file with your friend. I think this is step you're missing. There are more details here.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/share-app-data

If this isn't what you're looking for, feel free to ask.

Anonymous · ‎08-19-2019

Hi @timl ,

thank you very much for your quick reply. That sounds like a decent solution. But what if I want to share this app in almost my whole organization? Do I have to manually pick all the stakeholders and grant them access to my particular excel file? Do you have any idea how to tackle this specific case?

Anonymous · ‎08-19-2019

And now I imported all the excel tabs in several lists on sharepoint and connected them with powerapps.

To be precise, I changed the data source from the excel file (on OneDrive) to Sharepoint lists.

The names of the sharepoint list are the same as the excel tabs from from the service desk template.

After this change the template doesn't work anymore.

I assume that I have no refeence problems. But what is the difference between referencing sharepoint lists and/ or excel tables? I've imported the SP lists with the "Import spreadhseet" app, so they should have the same structure.

This is one of the most common error I receive.

And this one:

Anonymous · ‎08-21-2019

Can someone help me?

timl · ‎08-21-2019

Hi @Anonymous Sorry for the delay in responding. In answer to your first question, unfortunately yes, it's necessary to share your Excel file with all of your stakeholders.

In answer to your second question, for more complex larger apps like the one you're looking at, it isn't straightforward to change a data source from Excel to SharePoint.

SharePoint provides a wider range of data types than Excel. When you imported your spreadsheet into SharePoint, a field that might have been interpreted as a string/text value in Excel could have been imported into a SharePoint numeric column (or vice versa). This discrepancy in data types is the cause of the incompatible type error that you see. To resolve this, you'll have to click into the error and identify the values that the code in the OnSelect property is trying to set. It'll be calling the Set or UpdateContext functions to set variable values, so it'll be necessary to check that for each variable, the data type of the value that the code is trying to set is consistent.

The delegation warning that you see would probably have applied to Excel too. Depending on the data source, PowerApps isn't able to filter data on the server. In these instances, it'll have to filter the data on the client/browser. For performance reasons, PowerApps imposes a maximum data limit of 2000 rows so the result of the query may not include all of the results that you would expect to see.

Hope that provides a bit more clarity.

Anonymous · ‎08-21-2019

Hi @timl ,

thank you for your reply.

Alright, but if I shift the database to SP, assuming that everyone has access to the SP site, everyone should see? So I don't need to share the excel file if all my data is stored on SP lists, right?

Thanks for that information with the reference problem. I figured it kinda out how to change the variables to SP

Regarding the delegation problem. Could you explain that again? What do you mean exactly by filtering? Every PowerApp is basing on some kind of data to show soemthing or whatever. So shouldn't veryone has delegation warnings? And what does this limitations means in my case? If I have more than 2k SP list entries, the app won't show me the 2001 list entry?

I am not very familiar with that and I didnt understand it 😛 Sorry for that. Need further clarification ^^

BR

Pitfrog

timl · ‎08-21-2019

Hi @Anonymous

In answer to your first question, you're right - you don't need to share the Excel file if your data is stored in SharePoint Lists.

In answer to your second question, an example of filtering would be where you want to want to display all records with a create date greater than 1st Aug 2019. With the SharePoint connector, the Filter function and the >,>= operators are not delegable against DateTime columns. Because of this limit, PowerApps will download the first 2000 records locally onto the client and apply the filter to only that subset of data. If record 2001 matched your filter criteria by having a create date greater than 1st Aug 2019, this result will not be shown in your app. All users of your app will be affected by this behaviour.

This page in the documentation includes a table that summarises the delegable operators.

https://docs.microsoft.com/en-gb/connectors/sharepointonline/

For further info, this page here describes delegation in more detail.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Hope that provides a bit more clarity. If not, feel free to post back any further questions.

Anonymous · ‎08-22-2019

Hmmm ok I understood. I see now where i have filter functions and where this might create a slight problem.

What would be your suggestion? I mean I am not sure if in my case we will ever have 2000 open tickets. Because this app should only function as an overview for the dispatcher/ service employee.

Or do you think this won't create any problems? Otherwise i will use another data source but i think it will be the same for the excel connector, right?

BR

Pitfrog

timl · ‎08-22-2019

Hi @Anonymous

In my opinion - if you're unlikely to have more than 2000 records, this won't be an issue for you.

Last week, the PowerApps team added delegation support for complex objects. Therefore, it could be the case that there will be datetime support by the time your data exceeds 2000 rows.

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

You can also find workarounds on this forum which generally overcome this problem by loading data in batches of 2000 inside a ForAll loop.

The Excel connector also suffers from this problem but in general, the SQL and CDS datasources offer much better delegation support.