Need help building an app to write multiple records to SharePoint List

[Anonymous]

I am trying to replace an existing business process with SharePoint but need help building a PowerApp to do so. In order to manage project work & team availability our managers use an Excel spreadsheet, which they fill out every Monday for the next 4 weeks. I have attached a sample of that data. 

The problem is that the data is collected at an employee level where individual dates are recorded as fields. In order to truly use this data we would prefer it be formatted as a row per employee per date with a field for Time Code (Project Work, Leave, Holiday, Available) and a field for Hours. We have created a SharePoint list that is formatted correctly, but asking managers to fill out/edit 20 rows of data for every employee every Monday is too much work. As such, we would like to create a PowerApp that allows the manager to fill in the Employee Name, Team, Location, Service Line & Project Information (Client, Project Type, Project Code, Project Description) once and then to fill in just the time code and hours for each date. Ideally 4 weeks worth of weekdays would automatically populate and 3 of those weeks would always already be filled out with the option to edit. 

I have never worked with PowerApps before so any help anyone could provide would be greatly appreciated. 

Thanks in advance! 

[WarrenBelz]

Hi @Anonymous ,

Yes all of that would be possible with a Collection Patched back to the list.

I am not sure how to start describing the process other than 

1. The List is presented to the manager
2. The manager fills out the bits they need to
3. A collection is made with the list adding the columns the manager created (the same for each item)
4. The collection is Patched using ForAll back to the list.

Happy to help once you have a go at this process.

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.

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

Do you want to fill Employee Name, Team, Location, Service Line & Project Information once time, and enter time code and hours for each date (multiple records), then save the data back to your SP List?

Based on the needs that you mentioned, I think a Edit form and a Gallery could achieve your needs. You could consider generate an app based on your SP List data source, then within the Edit form, only enable the Employee Name, Team, Location, Service Line & Project Information field for managers to fill in.

Then within the Edit form screen, add a Gallery, configure it as a "Repeating" Table. Within this repeating table, add several Text Input boxes or Dropdown box for choosing value for time code and enter data for hours (for each date).

More details about configuring Repeating Table in PowerApps using Gallery, please check the following article:

https://www.youtube.com/watch?v=xgznk4XlPCo&t=1525s

https://www.youtube.com/watch?v=DylxsXIUyDc&t=8s

After that, you could use the combination of ForAll function and Patch function to achieve your needs. Set the OnSelect property of the "Submit" button to following:

ForAll(
       RepeatingTableGallery.AllItems,
       Patch(
             'SP List',
             Defaults('SP List'),
             EditForm1.Updates,
             {
                'Time Code': TimeCodeDropdownBox.Selected.Value,
                 Hour: HourTextInputBox.Text
             }
       )
)

Note: The TimeCodeDropdownBox and HourTextInputBox are both controls in your repeating Table gallery.

Best regards,

[Anonymous]

Edit: Never mind - Figured it out! Thanks everyone for your help!

Thank you, this is super helpful. I can figure out how to do this if I go to the SharePoint list and create a PowerApp through the drop down available. However, I want to create the app in Tablet form, so I need to start from a blank app. When I do that the form refuses to connect to my SharePoint list. Am I missing something obvious here?

[Anonymous]

I managed to get the app set up exactly how I want it, but I cant get all of the rows to write back to the list. Instead it seems like it only writes back one row and it forgets to include the time spent data. Any thoughts? Thanks so much for all of your help so far, it has been a god send.