Newbie questions

[Anonymous]

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

[mogulman]

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.

[v-xida-msft]

Hi @Anonymous ,

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,

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

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