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!
You can certainly do this with Flow. My question would be, if you have information in SQL, why not use that as the datasource rather than Excel.
The template you're looking at is nice. However, the shortcoming (in my opinion) is the Excel factor. In order for it to work properly, everyone using it must have access to the Excel file. This is not usually practical.
When we played with that template, the first mission was to move the data source. In our case we went to SharePoint, but it could have equally been move to SQL (which fits your case).
Thank you for the response. Now that you mention it, it doesn't seem prudent to have to give 500 employees access to the Excel spreadsheet on OneDrive.
If I were to go through the SharePoint Online route, would that basically involve creating 3 lists? One for every Excel spreadsheet in the file? For example, a list for leave, a list for holidays, and a list for balance?
If I were to go thorugh the SQL Server route, how would I create a screen to reflect all this information that is shown by the Excel file?
So, you really have to look at the Excel file as a database (in a sense). Each Table in the Excel file would correlate to a List in SharePoint or a Table in SQL.
That was what we did with that Template app was to create 3 lists in SharePoint and then just changed the datasources in the PowerApp (also needed a little bit of changing on some of the fields/columns).
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
As I mentioned previously - if you have the information in SQL, why not just use that as the data source?
It's already there and you can consume it in your PowerApp. No other need for Flow of other sources at that point.
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.
Yes, you're on the right trail.
The tables in Excel are almost a complete representation of the tables you would have in SQL.
You would only need to change the data source/connection and the rest of the app should work. (that assumes you uses the same names for tables and fields - otherwise you need to adjust).
I've done a lot of experimenting and believe I am almost at my solution. The only collection i'm having trouble with is the RequesterBalanceCollection. As you've mentioned in your previous posts, I decided to try and manipulate my SQL Table to match the one defined by PowerApps (the collection already created by the template).
You mentioned modifying the fields/attributes to better fit your needs. I'm having trouble modifying the structure of this table to match my SQL Server table. I'll attach 2 photos, one table from each enviroment. Could you please provide some pointers as to how I can manipulate my SQL Server table to match?
Thank you for your time
So the two pictures are of different tables completely. The SQL one seems to be the one you are perhaps having trouble with.
Can you give some more details on what you are specificaly running into trouble with? Are you experiencing errors or just not able to connect to it?