cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Powernapper
Level: Powered On

Newbie questions

Hi

I've just recently discovered Powerapps and believe it could be helpful for a number of work processes and a personal project. My first trial of P would be to create an app to improve a journal which is currently just in an Excel spreadsheet (just numbers/text with no macros or vbs etc).
The app would need to capture dates&times at data input and numbers/text and store them in such a way that they are easily searchable. P would need to do some simple calculations using dates/times and sums of numbers, to show recent activities over the last 7/14/28 days etc.

I'm not a coder or dba, but can tinker with some basic scripts and MS access/basic db stuff etc

Do you think P might be suitable/work for me? If not can you suggest anything else?

A few other questions:

1. How can we backup data from P to our local environment? Is it possible to export some or all data to excel,csv or some other format?
2. Is using an app on iOS/android possible without data? Is it possible to design app to support some offline functionality such as capture input even if main database is unavailable?
3. When I've searched for info on P I've read quite a bit of negativity about speed of P apps. I saw aYouTube vid of an app that had appalling performance. Clearly a lot of companies are using P, we're currently not intending anything with images/video or particularly large, I'd appreciate any comments on app speeds?

I very much appreciate your ideas.

Thank you

Matt

 

1 ACCEPTED SOLUTION

Accepted Solutions
mogulman
Level 8

Re: Newbie questions

I considered using their CDS as a database environment.  There are numerous issues with CDS including no access from Azure Function apps, getting data in/out, licensing and general flexibility.  I love Excel but not as a database for a business application.  VBA makes it very powerful tool for analysis and reporting.  PowerQuery and PowerPivot are amazing.

 

I've been doing some form of office or business automation for 25+ years.  If your app is successful you'll be asked to add more and more features and capabilities.  Some of those features may require coding.  For example producing a report by reading database using Excel and creating report using VBA.

 

An SQL DB like Azure SQL DB provides tons of flexibility.  You will have to learn the basics of SQL but it isn't that difficult and there are tons of courses.  Don't sell yourself short.  If you have patience and determination you can accomplish amazing things.  There are tons of forums that will help with specific questions.  There are numerous books and online courses you can take.  I thought this was an excellent book on PowerApps.

View solution in original post

5 REPLIES 5
mogulman
Level 8

Re: Newbie questions

I've been developing a complicated business process using PowerApps (PA), Azure SQL DB and Azure  Function Apps.  I would recommend Azure SQL DB for storing data.  It provides incredible flexibility in getting data in/out and a basic plan is $5/month.  You can also use an on-premise SQL Server and data gateway.  If you have some complicated calculation you can use a stored procedure and call it from PA.  If your process is very complicated you can use Azure Function Apps.

 

I haven't done extensive testing on performance but Azure SQL DB seems to provide required performance.  I did a test using Excel spreadsheet and it was sluggish as I would expect.  Also, I don't view Excel spreadsheets to be a reliable method for a critical business process.

 

It seems like a lot of people use Sharepoint lists to store data.  For me it doesn't provide the flexibility to get data in/out or provide views or stored procedures like a db server.  Also, you can't read Sharepoint lists from Azure Function apps.

Community Support Team
Community Support Team

Re: Newbie questions

Hi @Powernapper ,

Based on the needs that mentioned, I think PowerApps could achieve your needs.

On your side, you could consider take a try to generate an app based on your Excel table data source, then you would get an app with three screens generated automatically.

After that, you could use above generated app to do some add, edit and delete operation to your Excel table data source.

More details about generating an app based on Excel table data source, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/get-started-create-from-data

Note: Please make sure that the data of your Excel spreadsheet has been formatted as a Table.

 

If you want to do some simple calculations based on dates/times within a PowerApps canvas app, I think the DateAdd and DateDiff function could achieve your needs. Please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-dateadd-datediff

If you want to do some sum calculation of number fields in your Excel table, I think the Sum function could achieve your needs. Please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-aggregates

 

For your other questions:

1. If you want to save your data within your app into your local environment, I think the Export control could achieve your needs. Please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/controls/control-export-import

On your side, you could consider save your data within your app into a Collection, and then export the Collection into your local environment as a ZIP file using Export control. Unzip the exported zip file, the exported data would be existed within a .json file.

2. If you use an app on iOS/android device without data, this is not meaningful. If you want to create an offline app within PowerApps, please check the following blog:

https://powerapps.microsoft.com/en-us/blog/build-offline-apps-with-new-powerapps-capabilities/

3. If you want to improve your canvas app's performance, please check the following tips:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/performance-tips

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Powernapper
Level: Powered On

Re: Newbie questions


hi @mogulman, thank you for your informative reply. Great to hear your recommendation on Azure SQL. Having looked it appears a 12 month free trial may be possible, which is great, but if as mentioned it's only 5/month it seems like a good solution.

I did a quick test using Excel but a SQL or other DB will be the way forward. Did you consider any other alternatives? It makes sense to use MS cloud (with MS PA) but I see PA offers many integrations.

I wonder if you (or anyone) might have any suggestions or direction how I choose a backend (SQL or other etc). I'm not a Coder or DBA, and am attracted to PowerApps as I'm under the impression a non coder/dba can create a mobile app. Is this fair and accurate?
I've 20 years in tech roles (support/qa/projects/mgmt), have created websites and played with many other peoples scripts, understand basic db structure etc., I have some technical nous.

For the first mini project I want to replace an existing spreadsheet. As an Excel spreadsheet, it contains many formula and calculations which provide useful information. Any comment on how easy or hard it might be to accomplish similar excel type formula calculations with a PA & SQL (or other db) solution?
My thought is that to a decent coder anything would be possible, but to a non coder like me (competent with Excel formula type stuff and playing with/modifying other peoples codes/scripts), is it feasible or I wonder if my expectations are unreasonable??? If I need to seek a freelancer for some or all of the project, that's a possibility. I have googled PA freelancers and didn't find many.


hi @v-xida-msft , thanks for your comprehensive reply. I created a quick app based on a spreadsheet. It was interesting to see and trial that. A database solution will most likely be required, perhaps Azure SQL as @mogulman suggested.

I will look further at the date/sum/export links you provided.

Yes, the link for offline PA usage was what I was seeking.

Thank you also for the performance tips.

PA could be very helpful and meet requirements. I'm just slightly unsure on what level of tech skills are required. Do you think there are many non coders/non dbas creating apps with PA?

I'm wondering is PA a similar tool that allows me as a non coder to create an app, like Microsoft Frontpage 1.0 allowed me to make a website 25 years ago, or is PA a much more complex beast that really requires more specialist skills and knowledge. If it's the latter, that's OK, PA may still be the system but use of a freelancer would be the method!

 

Cheers

 

Matt

mogulman
Level 8

Re: Newbie questions

I considered using their CDS as a database environment.  There are numerous issues with CDS including no access from Azure Function apps, getting data in/out, licensing and general flexibility.  I love Excel but not as a database for a business application.  VBA makes it very powerful tool for analysis and reporting.  PowerQuery and PowerPivot are amazing.

 

I've been doing some form of office or business automation for 25+ years.  If your app is successful you'll be asked to add more and more features and capabilities.  Some of those features may require coding.  For example producing a report by reading database using Excel and creating report using VBA.

 

An SQL DB like Azure SQL DB provides tons of flexibility.  You will have to learn the basics of SQL but it isn't that difficult and there are tons of courses.  Don't sell yourself short.  If you have patience and determination you can accomplish amazing things.  There are tons of forums that will help with specific questions.  There are numerous books and online courses you can take.  I thought this was an excellent book on PowerApps.

View solution in original post

Powernapper
Level: Powered On

Re: Newbie questions

I got an email notification that @mogulman replied, but this thread doesn't show his response (just shows 4 messages). I've refreshed the page multiple times and it still doesn't show. Very strange.

 

@mogulmanthank you for your reply and information. The book that you suggested looks great and pitched perfectly for my needs! 

 

Cheers

 

Matt

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 324 members 5,624 guests
Please welcome our newest community members: