Storing a Table in Sharepoint List

[The_Morlock]

Hey everyone!

I have the need to create a form on top of a Sharepoint List that will be used to track Site-based Updates. These range from tracking who owns the implementation to if the update is done, what needs done, etc.

My hope is to have a form on top of a Sharepoint List and each implementation effort will be a single record on the List (storing multiple sites in multiple rows in the List doesn't make sense as one effort could consist of 30 sites, etc. 30 records will grow to thousands quite quickly). Thus one record would equal one effort (one update effort to many sites).

This is the information I need to be able to store:

That example would be a single effort but has 4 sites to keep track of.

I do not have DataVerse to create a database to store this information (company doesn't want to spend the money, etc.).

Can I use something in Power Apps to create the table above, allow it to be editable, and save the sites list with status and other info in a single List record? Other things along with the site status will be recorded in other columns, like the effort name, requestor info, etc.

For instance, I see things like the following (using the data in the above table as an example):

{Site1,Pending,2023.10.11,OnSite,2023.10.11,,,},
{Site2,Complete,2023.10.01,Complete,2023.10.01,2023.10.01,Ask before arriving,},
{Site3,n/a,n/a,n/a,n/a,n/a,n/a,<b><i><color=blue>FYI:</color></i></b> Not Needed.},
{Site4,Requested,n/a,Implementation,n/a,n/a,n/a,<b><i><color=red>Note:</color></i></b> Implementation will install.}

The above is neat and clean and is easily readable without the form but is also stored rather easily as it is just text.

If I am able to store this in a single column this would go a long way to keeping things together logically especially when reports are being run where you need to know how many sites, who did what where, etc, etc.

Thank you!

[WarrenBelz]

Hi @The_Morlock ,

It might be clean to store, but very messy to extract and then write back to. You could use Split() to turn it all into a Table and then Index() of the Table depending on the number of the field to display it. You would then use Concat() to write it all back. Honestly, why not simply have a separate field for each item ?

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[The_Morlock]

Hey @WarrenBelz ,

Thank you for the reply. I'll look into that to see if I can get it working.

As for your question, if you have a task to update 30 sites (not uncommon), wouldn't that create 30 records in the list for a single update effort? If you have quarterly updates, that would be 120 records for one update. We have 16 people that need to perform updates quarterly, so as you can imagine, 30 records for one update, another 16 for another, another 5 or so with another 10 or less sites, just one update rollout can easily be hundreds of records and all that for a single update effort timeframe.

If I can do this one record per person, then that would be 16 record for an effort, not hundreds.

Hope this makes sense.

Back to your suggestion...

1. To read from the List record, sounds like I would have the "Site List" column use:

Split()

The above will take the {x.y.z},{a,b,c}.. text and separate each into their own column in the table.

2. Use Index() to display the table.

3. Finally use Concat() to recombine the entries back into the {x.y.z},{a,b,c} format to be stored into the "Site List" column.

Considering we were going to store loads of emails in rich text format using a similar column, sounds like this should work properly. the amount of data really isn't a ton as compared to the emails we would store.

Any recommendations on the type of column to use, or just a single line of text column?

Anything else that you would see as a caveat or recommendation?

Thanks again.

[WarrenBelz]

@The_Morlock ,

OK- this gets messier now I have had a good look at your structure - you seem to have several records within the delimited structure, so I am not sure how you would actually split them (you need to determine when each record starts and ends, so you can probably forget Split/Concat). I honestly would simply have one record per update per person with separate fields and SharePoint should have no issues handling the potential record numbers.

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[The_Morlock]

I honestly would simply have one record per update per person with separate fields and SharePoint should have no issues handling the potential record numbers.

This is what I am wanting. 🙂

The problem I am having is how to display this and save it.

My thought was to have a table. The table would have each location, the status, notes, etc. These are all shown in the table image and the example of the text in the field. The only thing is to try to figure out how to record all that in a text blurb that needs read, deconstructing, and reconstructing. It SEEMED simple enough to use something like I've got, but I need to get the List sorted out and see if it can be done.

Or, are you thinking something different than my initial post and the above paragraph?

Something like this...

This is similar, except I do not have access to DataVerse.

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/add-editable-tables#step-2-add-a-data...

[WarrenBelz]

@The_Morlock ,

I have never considered storing data with the model you are contemplating (so really cannot offer insight). Three fields (Site, Date, Status) per record should be all that is needed unless I am misreading this.

[The_Morlock]

I think that I'm complicating this a bit. I was thinking that "Why can't I store all the data in a simple format, like in CSV format, in a single column and then build the table from that?". After checking the ShorePoint Lists limit, honestly, that seems like a lot of work, for not a lot of return.

From the looks of it, it can be 50,000 items per list:

https://learn.microsoft.com/en-us/answers/questions/1281138/handle-the-limit-of-50-000-unique-items-...

That equates to about 10 years worth of items (We do 250 update tasks at roughly 20 sites per task).

Is the above correct?

[WarrenBelz]

@The_Morlock ,

I have numerous SharePoint lists and libraries over 100k with no issues. Storing multiple records in an array like that is certainly possible, and you would have a couple of nested ForAll(Split) functions to get it out and ForAll(Concat) to put it back but my brain hurts thinking about it.

[The_Morlock]

I think I'll go with the "one record per side per update" (One update can have 10+ sites, thus there would be 11 records made (one record for the update and another record for each site)) bit considering that we should have years worth of data without issue. If we need to move the data off to make it more efficient over time, then so be it. By then, it would be several years before we would need to do that.

So, knowing that I'll use a list to get this done, a shift on what I am doing is needed, am I missing anything below:

1. Need a form to be able to take records from a list, AppList (which contains a Lookup column with sites),

2. From there, select an app from AppList,

3. The form will then look at a Lookup Column, read the number of Sites on the column, and build a sheet using those items,

4. Once the user has updated the list with the AppList Sites (say it has 10 sites and it makes a record, one for each site with a unique identifier matching it to the form record),

5. Then the user saves the whole form as a record, but references the items from the table that is listed in another List, AppListDatastore.

6. If an update is needed, the form then looks at the Update record which also looks at the matching other site records in the AppListDatastore.

Did I miss anything and does all that make sense?

Thanks again for the assistance!

[WarrenBelz]

Hi @The_Morlock ,

The only one I will comment on is 4 - Lookup columns are really not necessary and generally only overcomplicate what at times should be simple things - you can easily do the count in Power Apps by referring to the other list directly.