Answered

Best way to build my app? Case specific, restricted by client

(0) Share

Report

Posted on by chrislamb2012

Hi y'all thanks again for all your help.

I'm currently trying to build a Power App that is simply a datatable that can be searched through and filtered, but Im having a difficult time trying to find the best way to link the original data to the power app.

Currently, all the information is kept in an excel sheet on a OneDrive. My client is requesting that the PowerApp/Website we're building pull data from that excel sheet continually, updating the PowerApp/Website when they update the excel. The only issue is I've had a difficult time trying to find a way to link the two together, especially that I've been informed that excel is terrible way to source data and its best to use something else like SharePoint Lists.

So far, I've tried the community's advice and attempted using SharePoint Lists, but I've only been able to connect the List to a PA through the "Integrate" tab which unfortunately creates it in a Mobile Phone layout. I also attempted to start from the ground up with the "Start with Data" option and using the link to the List, but this was unsuccessful as well even after reading multiple threads and watching multiple tutorials.

What would you all recommend? Maybe I'm just completing some step wrong? I dont have a deep understanding of PA so I easily could have missed something.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

v-jefferni on at

Like (1)

Report

Hi @chrislamb2012 ,

Could you please explain further? What will the client do to the Excel file? What purpose would you like to achieve with the product? In which step you were stuck when using Start with Data to create the App?

Anyway, you can try and go to Create -> Start from SharePoint:

Select or create a connection to SharePoint:

Select the site:

Select the list and click Connect:

Best regards,

Was this reply helpful? Yes No
chrislamb2012 30 on at

Like (0)

Report

Hi @v-jefferni, thanks for offering your help.

The client plans on using the excel file to expand upon the data set making more entries in the future. The excel file contains nothing other than a table with a header row and columns containing text information.

The purpose of the product is to function as a searchable database internally, enabling him to update the PA/Website whenever he needs to keep other users in the loop. Essentially, they want me to reproduce the table in excel, but make it searchable and easy to use for onboarding.

When it comes to which step of the SharePoint lists Im stuck on, Im having difficulty around the step you have labeled, "Select the site:". There, MPA is unable to find the site based on the link/links I've provided, so Im not quite sure how to get around the issue. The list I've made on SharePoint is enabled to anyone in my organization with the link, and I does not show up on my recent Sites.

Please let me know what other questions you have so we can work to solve this issue together, and thanks again

Was this reply helpful? Yes No
TuongDoan 573 Moderator on at

Like (0)

Report

Excel was never meant to be a database. Even if you've already done editing and closed the Excel file, it can still
remain locked for up to 6 minutes before you can do anything else.

I suggest using Power Automate (PA) to perform CRUD operations on an Excel file with a proper retry policy in place. When PA tries to write to the Excel file but it's locked, PA will automatically retry later

Learn more about retry policies here: https://learn.microsoft.com/en-us/azure/logic-apps/error-exception-handling?tabs=standard#retry-policies

---------------------------
If the solution I provided assisted you in resolving this issue, kindly select "Accept as solution" This will help others in finding the solution more expediently 😄Also, a Thumbs up would be a great compliment!

Was this reply helpful? Yes No
VishalJhaveri 1,167 Moderator on at

Like (0)

Report
Limit Data connections to not more than 30 connections in one app.
Limit the number of controls to not more than 500 controls in one app. Power Apps generates an HTML document object model to render each control. The more controls you add, the more generation time Power Apps needs.
Concurrent function:
https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-concurrent
HOW TO: Use the Concurrent Function To Make Your PowerApps Perform Better

You can't predict the order in which formulas within the Concurrent function start and end evaluation. Formulas within the Concurrent function shouldn't contain dependencies on other formulas within the same Concurrent function, and Power Apps shows an error if you try.
Cache lookup data:
Use the Set function to cache data from lookup tables locally to avoid repeatedly retrieving data from the source.
Set(CustomerOrder, Lookup(Order, id = “123-45-6789”));
Avoid control dependency between screens.
Use Delegation
Use Delayed Load:
Turn on the experimental feature for delayed load if your app has more than 10 screens, no rules, and many controls that are on multiple screens and are directly bound to the data source. If you build this type of app and don't enable this feature, app performance can suffer because the controls in all screens must be populated even on screens that aren't open. Also, all screens of the app must be updated whenever the data source changes, such as when the user adds a record.

Working with large datasets
Republish apps regularly:
Makers are encouraged to publish their apps regularly. Because the Power Apps platform is continuously optimized and deployed, your app is regenerated within the latest platform optimizations when you republish it.
Avoid repeating the same formula in multiple places
Enable DelayOutput on all Text input controls
Avoid using Form.Updates in rules and formulas
Use DelayItemLoading and Loading spinner to improve performance in Gallery

Was this reply helpful? Yes No
Verified answer

v-jefferni on at

Like (0)

Report

Hi @chrislamb2012 ,

As you already know, Excel table is not quite reliable when it's used as a data source due to a plenty of limitations. If you only need a data source for end users to search and view data, a SharePoint list is enough. It supports custom views, searching and sorting. Most of the time it's user friendly.

You can also use it as the data source to create an App, or create Power Automate flow to update something according to specific logic and needs. But based on your description, I don't think it's necessary for you to create an App.

Best regards,

Was this reply helpful? Yes No