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!
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?
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.
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.
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.
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.
Hi @mogulman @Anonymous
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.