cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Power App Template Leave Request integration into Excel (OneDrive)

Hallo Guys,

 

I'm trying to use the Leave Request app in Power Apps. 

It is working sofar that I can publish it and use it. 

Also the Excel is created in my OneDrive, but the App is not writing the requests into the Excel.

 

Maybe the point is that when I create the App there is no Connection to OneDrive.

After I created "my own App" it creat the Excel, but when I look at the Sources there is no integration with OneDrive.

So I added the Connection with OneDrive for Business and added the Tables. 

 

Still I think I just cant finde the place where the System updates the Excel. 

 

Maybe someone of you have a idea. 

 

KR

George

5 REPLIES 5
Community Support
Community Support

Hi @Anonymous ,

Actually, within the Leave Request tempate app, it does not use Excel table as a data source to store the Leave Request record. Instead, it use a Collection (LeaveCollection) to store the Leave Request record in this app.

 

The LeaveCollection is initialized within the OnStart property of the App control:8.JPG

 

Within the Confirmation screen, save Leave request record into the LeaveCollection:9.JPG

 

If you want to save your Leave Request record into your Excel table rather than above Collection, you need to create a Excel table within your cloud service (e.g. OneDrive, OneDrive for  Business, etc), and your Excel table must contain the following fields:

 Title
 Detail
 StartDate
 EndDate
 LeaveType
 Approver
 Status
 Requester
 LeaveID

you need also to format your Excel table as a Table.

Within your app, create a connection to your Excel table, then replace the LeaveCollection within above Patch formula (mentioned in second screenshot) with your Excel table, when you click "Submit"

, the Leave request record would be saved into your Excel table

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Kris Dai,

 

thanks for your respons. 

 

The problem is, when I just copy all Requests into the Excel, the LeaveCollection will not be updated. 

So there will be no overview of requests, cause they look up in the LeaveCollection. 

 

I'm going to try to update both and keep you updated. 

 

Still I'm confused regarding collections. Where are they saved? 

Are those persistent ?

 

KR

George

Hi @Anonymous ,

Actually, the Collection data you created within your app is stored within your app's memory, which is a temporary data (not persistent).

Please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearcollect#collect

 

When you close your app, the Collection would be destroyed. You could use SaveData function to store the Collection data into your local device, then use LoadData function to load your local store data.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi,   

I decided to use Excel to save my Data. 

And it works perfectly. I update both Excel and Collections, so that I don't have to adjust to many things in the App.  

 

Still, I would like to separate the Balance Collection into Excel.  So that the newest Data is in the Excel Document and can be adjusted manually. And that the App is not overwriting the Content.

 

I thought about using the existing ClearCollect and add a function to add it to excel.  But before that I would like to check if there is already a record in the Excel Document.   

 

I thought about using something like

 

If (

LookUp(

Balance(MyExcelTable); EmployeeEmail = Office365Users.MyProfile().Mail));

 

And if it is true, I would just add the Data from the Excel to the Balance Collection, if not create / update the excel document. But at the moment I run into problems to compare if there is already an existing Email.

They must have changed the template.  I'm messing with it now and it is saving balances to Excel, as well as some other data.  It does start out with an arbitrary amount of days for each bucket if you don't have the Excel file in place already, but you can easily tweak those initial values as well..  or just change them once the Excel file is set up.

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Check out the Power Apps Community Call for January 2021

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (3,631)