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

SQL Server CSV export to OneDrive Excel to PowerApps

Hi

 

We are currently looking to implement an request for leave PowerApp within our organization. With the template PowerApps provided, the leave balances are stored in a OneDrive Excel file. However, the leave balances are dynamic and change biweekly so we're currently looking for a way to have the SQL Server information with current balances be exported and updated on the OneDrive Excel file to correctly reflect each individual's balance.

 

The scenario would be getting the SQL Server data (correct employee balances) --> Update OneDrive Excel file --> PowerApps would reflect this information. Would anyone know if this is possible with Microsoft Flow? If not, would Skyvia be a viable tool? Thank you!

7 REPLIES 7
mogulman
Level 8

Re: SQL Server CSV export to OneDrive Excel to PowerApps

I'm not understanding.  Why don't you do everything in SQL Server.  You can connect to SQL Server from PowerApps. I don't view saving key business data in Excel as reliable.  With SQL Server you can schedule regular backups.  Are you using SQL Server on-premise or Azure SQL DB?

akim91
Level: Powered On

Re: SQL Server CSV export to OneDrive Excel to PowerApps

I'm modifying the template, and it was configured so that it pulled data from the OneDrive Excel file. How should I go about storing leave balance information for the employees? Would it be better to try and create a SQL table?

 

We are using on-prem SQL Server by the way.

mogulman
Level 8

Re: SQL Server CSV export to OneDrive Excel to PowerApps

I would create a table and avoid saving data in Excel.  If you want to do a leave report report in Excel it is easy to connect Excel to SQL Server using VBA and create a report.  This is what I do.  Using an on-premise SQL Server is good for debugging.  You can see what PowerApps is doing using the SQL Profiler.

akim91
Level: Powered On

Re: SQL Server CSV export to OneDrive Excel to PowerApps

I appreciate your input. From some of the information I've gathered, a lot seem to believe it isn't smart to have a three step process where the SQL Server data gets ported to the OneDrive Excel sheet, and then reflected on the PowerApp. Do you believe it would be better to have the SQL Server PTO balances update the PowerApps directly?

I think my next step would be to figure out how I can show individual balances just to the person that's using the PowerApp via SQL Server updates.. Any more input/information would be highly appreciated.

Thank you,

Alex

Re: SQL Server CSV export to OneDrive Excel to PowerApps

Hey Alex,

Grab the excel file and create a table in Sql with the same headers, you'll need a primary key to edit records in powerapps. Then go through your app and 'redo all the connections to excel. Won't take long.

The only sticking point is the Sql connector is not as strait forward as rewiring excel to say SharePoint.

Keith
mogulman
Level 8

Re: SQL Server CSV export to OneDrive Excel to PowerApps

I would have PowerApps directly connect to SQL Server and have PowerApps edit all records.  I don't know your schema but I assume you have an employee table and a PTO table.  The PTO table has a EmployeeID field that links to employee table So you have employee name and email address.  When a user runs your app you can get their name (User().FullName) or email (User().Email).  You can filter PTO table so only their records show.  I do something similar so only certain people can delete records.

akim91
Level: Powered On

Re: SQL Server CSV export to OneDrive Excel to PowerApps

Hi @mogulman @KeithWhatling

 

I'm having a bit of trouble understanding how I can directly use SQL Server as our datasource. I understand that all the pertinent information is there, it's just the presentation of that data directly into PowerApps that i'm having trouble grasping. 

 

My team was able to create a SQL query that listed every employee with their current and variously different (sick, vacation, personal, etc.) balances. My questions is would this be adequate to create a table based on this query, and have that table information pulled into my PowerApp where the PowerApp can then filter a user's personal data?

In the template provided, they provided 3 Excel spreadsheets in 1 workbook. Should I create 3 tables in SQL Server that reflect the 3 Excel spreadsheets? I'd like to thank you for your patience, as I'm eager to learn but having a bit of difficulty doing so.

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 (Last 30 Days)
Users online (4,721)