cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RubaHAssan
Level: Power Up

fetch collection items from a table in SharePoint column

Hi all, 

 

I'm trying to create a powerapp and here is my obstecle. 

I need a user to dd dynamic number of variable "Strength, and Key actions" So, I've created a collection to reach that. 

Then I add collection items and saved them in "HTML text column" 

 

Now I'm creating an edit screens to the app and  I need to retrieve these collection data from that table again to the collection. 

 

please help. Or give me some ideas where should I save the collection so enable get the items back 

 

regards 


Accepted Solutions
Community Support Team Rank
Community Support Team

Re: fetch collection items from a table in SharePoint column

Hi @RubaHAssan,

Could you please share a bit more about your scenario?

Could you please show more details about the "HTML text" column that you mentioned? Is it a Multiple lines of Text column which enables "Use enhanced rich text" option?

How do you save your Collection items into a column in your SP list?

If the "HTML text" column that you mentioned is a Multiple lines of Text column which enables "Use enhanced rich text" option, and you want to save a Collection into this column, I have made a test on my side, I afraid that there is easy way to retrieve data from this column.

If you save your Collection as a HTML table into this column (Multiple lines of Text column), when retrieve data from this column, it would decorated as below:11.JPG

There is no easy way to extract Collection items data from this column in PowerApps currently.

As an alternative solution, you could consider take a try to create corresponding columns in your SP list for these columns in your Collection. Then patch all items of your Collection into your SP list using Patch function and ForAll function.

I assume that you have created corresponding columns in your SP list for these columns in your Collection, please take a try with the following formula:

ForAll(
    RecordsCollection,        /* <-- RecordsCollection Represents the Collection you want to save into your SP list */
    Patch(
        'YourSPList',
         Defaults('YourSPList'),
         {
             SPColumn1: RecordsCollection[@Column1],
             SPColumn2: RecordsCollection[@Column2],
             SPColumn3: RecordsCollection[@Column3],
             ...
         }
    )
)

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

Patch function

ForAll function

 

Best regards,

Kris


All Replies
KOM_4410
Level 8

Re: fetch collection items from a table in SharePoint column

HI,

 

This is not your exact case but it is similar and may give you an idea on how to solve it.

I also saved a collection of items in one column, but using ";" and "/". So every row in my collection was separated by "/" and every column in that row was separated by ";".

 

For instance. If my collection saved the following table

YearAmountResult
Year 1Amount 1Result 1
Year 2Amount 2Result 2

 

I saved that collection as:  Name 1; Email 1; Address 1 / Name 2; Email 2; Address 2

No, to retrieve the collection back again, on my screen I addedd addedon and 'OnSelect' property I added:

 

ClearCollect(TemporaryCollection,Trim(Split(FieldWithMyData.Text,"/")));
Clear(NewCollection);
   ForAll(TemporaryCollection,
      Collect(NewCollection,{Year:Left(Result,4),Amount:Trim(Mid(Result,7,20)),Record:Result}));
Remove(NewCollection,LookUp(NewCollection,IsBlank(Record)))

 

So basically what I did was:

1. First a created a temporary collection to separate every row of the original collection. This creates one column collection and that column is called "Result".

ClearCollect(TemporaryCollection,Trim(Split(FieldWithMyData.Text,"/")))

 

2. Then the tricky part, how to separate the columns using the ';'. In my case, I knew how many characters the year column had, so that helped. But the idea is, for every row of your collection, collect the values to the columns you want, in this case Year, Amount and Record.

Clear(NewCollection);
   ForAll(TemporaryCollection,
      Collect(NewCollection,{Year:Left(Result,4),Amount:Trim(Mid(Result,7,20)),Record:Result}));

 

3. For some reason, the new collection was created with a blank item  on the previous step, so I added this last part on the function to delete that blank record.

Remove(NewCollection,LookUp(NewCollection,IsBlank(Record)))

 

I hope that helps

Community Support Team Rank
Community Support Team

Re: fetch collection items from a table in SharePoint column

Hi @RubaHAssan,

Could you please share a bit more about your scenario?

Could you please show more details about the "HTML text" column that you mentioned? Is it a Multiple lines of Text column which enables "Use enhanced rich text" option?

How do you save your Collection items into a column in your SP list?

If the "HTML text" column that you mentioned is a Multiple lines of Text column which enables "Use enhanced rich text" option, and you want to save a Collection into this column, I have made a test on my side, I afraid that there is easy way to retrieve data from this column.

If you save your Collection as a HTML table into this column (Multiple lines of Text column), when retrieve data from this column, it would decorated as below:11.JPG

There is no easy way to extract Collection items data from this column in PowerApps currently.

As an alternative solution, you could consider take a try to create corresponding columns in your SP list for these columns in your Collection. Then patch all items of your Collection into your SP list using Patch function and ForAll function.

I assume that you have created corresponding columns in your SP list for these columns in your Collection, please take a try with the following formula:

ForAll(
    RecordsCollection,        /* <-- RecordsCollection Represents the Collection you want to save into your SP list */
    Patch(
        'YourSPList',
         Defaults('YourSPList'),
         {
             SPColumn1: RecordsCollection[@Column1],
             SPColumn2: RecordsCollection[@Column2],
             SPColumn3: RecordsCollection[@Column3],
             ...
         }
    )
)

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

Patch function

ForAll function

 

Best regards,

Kris