Using Sharepoint List for Leave Request Template

[kdougherty]

Hi All

Very  green here so go easy on me.

I am using the Leave Request template and trying to convert it over to using sharepoint lists, rather than one drive excel file. Maybe I am missing something, but it seems without loading the data necessary to track these values over time is useless since they are gone after app is closed. 

I have connected a sharepoint list with all of the necessary columns. I want to edit the "On Start" so that the ClearCollect loads data from the sharepoint list rather than inserting values manually. How do I accomplish this. See attached as reference. 

OnStartBalance

Instead of the numbers and text manually inserted here how do I insert the values from my sharepoint list that is tracking this data. FYI I already have most of the patching functions working so the data is posted to the list on approving. But I haven't figured out loading it on app start. Thank you!

[WarrenBelz · Accepted solution]

Hi @kdougherty ,

I may have missed something here, but if you simply want to Collect a complete SharePoint List (bear in mind the maximum records of standard 500 items which can be increased to 2000 in Settings), you simply need to at App OnStart (replace with your list name)

ClearCollect(BalanceCollection,YourSharePointListName)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @kdougherty ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[kdougherty]

Hi,

Thank you for the reply! It's definitely got me started on the right track I think. I didn't know you could grab everything all at once like that. So now my problem is working with the collection later on in the app. A number of errors have come up now that I changed the OnStart... See below as one example:

Set(_requesterBalanceRecord, LookUp(LeaveBalances, Year = Text(Year(Now())) && EmployeeEmail = _myProfile.UserPrincipalName));

It is now telling me "Year" and "EmployeeEmail" not recognized identifiers. 

[WarrenBelz]

Hi @kdougherty ,

I will cover these separately.

Year is a reserved work in PowerApps, so you need to change your field name to something else - this may fix it.

If not, what is _myProfile.UserPrincipalName?

Set(
   _requesterBalanceRecord, 
   LookUp(
      LeaveBalances, 
      NewYearField = Text(Year(Now())) && 
      EmployeeEmail = _myProfile.UserPrincipalName
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[kdougherty]

Thanks again. I may try and create an app from scratch. Start simple and build from it. 

When I edit this template to use sharepoint data it throws like 87 errors. It will most likely be easier for me to start from scratch and get a better understanding of how this all goes together. 

[WarrenBelz]

@kdougherty ,

If you have 87 errors, maybe so. Happy to assist when you get this number down a way.

[kdougherty]

So I have made some progress in understanding how this all works. 

I cleaned up all my errors. These errors are happening because when I do ClearCollect(BalanceCollection, LeaveBalances), the columns do not line up.

If I analyse BalanceCollection a couple of columns simply don't line up. For example. Lots of formulas referenced "BalanceCollection, EmployeeName". Nowhere in the balance collection did the Employee Name column exist. However all of the data does come accross it just seems to be in random columns that don't even exist in the sharepoint list. 

Right now I have the app working but I've had to use the column names that the collection seemed to create rather than actual column names. Note the same issue persists if I try to do a lookup on the sharepoint list directly. It's like the list has old or unused column names that are coming accross. 

I have refreshed the data sources with no success. Any ideas? 

[WarrenBelz]

Hi @kdougherty ,

Some questions initially

• Have you renamed any columns in SharePoint from their original name (particularly Title)?
• Do you have any calculated columns in SharePoint you expect to be collecting?
• Is  _myProfile.UserPrincipalName still relevant in this - if so what is it?

[kdougherty]

I definitely renamed the title when I created the list and I may have renamed others.. No calculated values. 

As for _myProfile. It is a global variable that is set in OnStart. This existed in the template already and is used frequently. I don't believe it's relevant here in my current question though. 

Set(_myProfile,Office365Users.MyProfile()