Unanswered

Save a collection or table as a record in SharePoint list

(0) Share

Report

Posted on by d3ell

276

I have started building an app where employees build up a time sheet by logging on their jobs they have done on a certain day. Based on what they input from several fields and drop downs my app creates a collection of what they have done on a particular day. Sometimes this can just be one job that lasts all day and sometimes this could be 10-15 small jobs. I’m wanting to write this to a DataSource. (Currently using SharePoint lists.) but I can’t configure a a list layout because every time an employee does a days work there could potential be 40-50 records that would need saving to the list (currently these records are saved in SharePoint in a nice neat collection table),

My question is, is there a way to save the whole collection that I’ve created into one record and then be able to call upon this collection again in this app or another app if I needed too? This way for my DataSource I could just have column names as each day of the week and each row could just be an employee!

Your thoughts? Or if there is another way to do this that I’m missing, please advise.

Thanks.

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

v-xida-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @d3ell ,

Could you please share a bit more about your scenario?

Do you want to save the whole collection records into a single one record in your SP list?

Could you please share More details about the data structure of your Collection and your SP list?

I assume that the data structure of your SP list as below:

Note: The Mon, Tue, Wes, ... Sun columns are all Multiple line text type column, which has enabled the "Use enhanced rich text" option. In addition, these columns are used to store the Job records for current week day.

the data structure of your Collection as below:

The JobTitle column used to store the Job name, the Duration column is used to store the time (minutes) you spent within the corresponding job, the EmployeeName column is used to store the employee name.

I suppose that you want to store above Job reocrds in the JobCollection as a Single one record into your SP list, is it true?

Based on the needs that you mentioned, I afraid that there is no direct way to achieve your needs in PowerApps currently. As an alternative solution, you could cosider concatenate the column values of above Collection into a single one text string, then save the concanated string value into your SP list.

I have made a test on my side, please take a try with the following workaround:

The data structure of the Job Collection as below:

The App's configuration as below:

Set the OnSelect property of the "Log Job" button to following:

Patch( '20190422_case3', /* <-- '20190422_case3' represents my SP list data source */ Defaults('20190422_case3'), { Title: First(JobCollection).EmployeeName, /* <-- Store the employee name into the Title column in my SP list */ Mon: If(Weekday(Now())=2, Concat(JobCollection, "Job Name:"& JobTitle &", Duration:"&Duration&" ")), Tue: If(Weekday(Now())=3, Concat(JobCollection, "Job Name:"& JobTitle &", Duration:"&Duration&" ")), Wes: If(Weekday(Now())=4, Concat(JobCollection, "Job Name:"& JobTitle &", Duration:"&Duration&" ")), Thu: If(Weekday(Now())=5, Concat(JobCollection, "Job Name:"& JobTitle &", Duration:"&Duration&" ")), Fri: If(Weekday(Now())=6, Concat(JobCollection, "Job Name:"& JobTitle &", Duration:"&Duration&" ")), Sat: If(Weekday(Now())=7, Concat(JobCollection, "Job Name:"& JobTitle &", Duration:"&Duration&" ")), Sun: If(Weekday(Now())=1, Concat(JobCollection, "Job Name:"& JobTitle &", Duration:"&Duration&" ")) } )

when you click the "Log Job" button, the corresponding values would be saved into your SP list as below:

More details about the Patch function and Concat function, please check the following article:

Patch function, Concat function

Best regards,
d3ell 276 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, sorry for the late reply and really appreciate your input.

It turns out that with the app that im trying to create I haven’t really thought a great deal about the data source so I think I’m going to have to go back to the drawing board.

I have played around with what you suggest and I like it, but I’m not sure how it would work with different employees and different weeks? Would I need a new data source for each employee or each week?

I had originally hoped that I would have each employee as a column and that each row would represent a week day, i’m not sure if that idea will still work using something similar to as you mentioned due to each row being a different day rather than each column.

What I’m trying to achieve is that on an app an employee can choose and edit the records that are from the current week and the previous week and that all weeks prior to that would be view only, hence the reason why I want to concat all their input so that they can use the app to select a previous week and be able to see what they booked in.

I have an idea of how I can achieve most of this, but now the data source part (SharePoint list) is troubling me as our lads who fill them in on a daily basis will have saved the data to SharePoint and it will of populated the record however if they need to edit the data then they can’t as the data is just one string value and can’t be entered back into the form. I’m assuming that they would just have to clear the data and then start again?

I’ve also being looking at the possibility of using SaveData and loaddata functions so that they could maybe save the collections they have until the end of the week and then submit it and patch it all at once, not sure if this is a good idea either though.

I appreciate your help so far and if you have any pointers then I’d love to hear them but I think I’m back to the start on this one.
d3ell 276 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-xida-msft

I've managed to look back into this and i'm thinking an idea that might work would be to have all my employees, listed as columns along the top and each row would then become a date. the records would all be multi line records and i would concatenate all the info that my app creates into one long string of data. I'm new to the patch function and i'm struggling to work out how this can work? wondered if you can help? - i have read up on this and i'm hitting a wall.

here is the sample of my data souce - only currently has 3 employees and 2 dates in for now while i'm trying to get this working.

The list is called 'Timesheet data' What i want to do is patch the row which the employee has selected the date for. For example in the app the user would choose "Tuesday 7/5/2019" he would input all his job data and when he clicks submit it will patch the record where the column matches his name and the row matches the date selected. If the date selected doesnt currently exist as a row it would create a new row with that date. For testing purposes and trying to see the data transfer i'm not Concatenating all the data at the minute i'm simply trying to add some text for a text input field called 'TextInput1'

Here's what i'm trying to use to do the patch. ( i know this doesnt look for a date record, but i'm just trying to get the patch to work for now and when i do, i assume i can write an if statement for if the row exists then patch that row and if not patch a new row!?) I'm currently just trying to create a new row.

Patch('Timesheet data',Defaults('Timesheet data'),{Title:DatePicker1.SelectedDate, User().FullName:Textinput1.text})

I get the impression that i cannot referance a column by the current logged in user().FullName function?

is this possible?
d3ell 276 on at

Like
a
(0)

Report
Copy link

Link copied!
if i change the User().FullName to the actual Column name ie. 'Daniel Bell' then the data is sucessfully patched.

However i would like it to be able to identify which column to patch by the logged in user so not sure if there is anothe way i can achieve this? I have tried creating a label and writing the User().FullName in here and referencing the label for the patch but that doesnt appear to work either.

Also, with regard to creating the If statement for the patch, so that if the date row already exists i'm not sure if this is possible as i cannot work out how to write the If statement for if a specific row already exists - I tried the below with no joy!

Patch('Timesheet data',If(Title=DatePicker1.SelectedDate,{ 'Daniel Bell':TextInput1.Text}, Defaults('Timesheet data'),{Title:DatePicker1.SelectedDate, 'Daniel Bell':TextInput1.Text}))
d3ell 276 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Any further thoughts on this?