Power App Template Leave Request integration into Excel (OneDrive)

[Anonymous]

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

[v-xida-msft]

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:

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

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,

[Anonymous]

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

[v-xida-msft]

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,

[Anonymous]

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.

[ohgee]

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.