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

SQL Server CSV export to OneDrive Excel to PowerApps

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!

12 REPLIES 12
Super User
Super User

Re: SQL Server CSV export to OneDrive Excel to PowerApps

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).

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
akim91
Level: Powered On

Re: SQL Server CSV export to OneDrive Excel to PowerApps

Hi Randy,

 

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?

 

Thank you

Super User
Super User

Re: SQL Server CSV export to OneDrive Excel to PowerApps

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).

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
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

Super User
Super User

Re: SQL Server CSV export to OneDrive Excel to PowerApps

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
akim91
Level: Powered On

Re: SQL Server CSV export to OneDrive Excel to PowerApps

Hi Randy,

 

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.

Super User
Super User

Re: SQL Server CSV export to OneDrive Excel to PowerApps

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).

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
akim91
Level: Powered On

Re: SQL Server CSV export to OneDrive Excel to PowerApps

Hi Randy,

 

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 

Super User
Super User

Re: SQL Server CSV export to OneDrive Excel to PowerApps

@akim91 

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?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 42 members 3,919 guests
Please welcome our newest community members: