cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Smarty_
Helper I
Helper I

Several elements separated by a symbol in one row in SPList TO Collection

Hello!

 

My case:

I've tried all the methods - doesn't work... I'm totally stuck 😣

I have a SharePoint list:

IDArticulLabelingName
id12345111 ; 222 ; 333Lblng1 ; Lblng2 ; Lblng3bus ; disk ; camera

 

I need to make a collection for the ID (e.g. id12345):

ArticulLabelingName
111Lblng1bus
222Lblng2disk
333Lblng3camera

 

Thank you in advance!

 

7 REPLIES 7
RandyHayes
Super User
Super User

@Smarty_ 

I am not sure of your intention for a collection, so I will provide a formula that will produce a table of the results you are looking for.  You can then use as you see fit.

 

Given your scenario, the following formula will produce the results:

With({_item: {Articul:"111;222;333", Labeling:"LbIng1;LbIng2;LbIng3", Name:"bus;disk;camera"}},
    With({_rows: Split(_item.Articul, ";")},
       ForAll(Sequence(CountRows(_rows)),
        {Articul: Last(FirstN(_rows, Value)).Result,
         Labeling: Last(FirstN(Split(_item.Labeling, ";"), Value)).Result,
         Name: Last(FirstN(Split(_item.Name, ";"), Value)).Result
        }
       )
    )
)

When used as an Items property of a Gallery, for example, this produces:

RandyHayes_0-1627495518509.png

The formula can be used for and Items property of any control you want.

You will obviously want to replace the hard codes _item record with the record of your choice, either via lookup or other means.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
GarethPrisk
Resident Rockstar
Resident Rockstar

This is possible. It assumes your data will always be structured that way (3 splits (1 per semicolon), per row).

Here is a sample code, that achieves what you're looking for.

// Example collection, mirroring what you have in SharePoint
ClearCollect(
    colSharePoint,
    {
        ID: "id12345",
        Articul: "111 ; 222 ; 333",
        Labeling: "	Lblng1 ; Lblng2 ; Lblng3",
        Name: "bus ; disk ; camera"
    },
    {
        ID: "id54321",
        Articul: "111 ; 222 ; 333",
        Labeling: "	Lblng1 ; Lblng2 ; Lblng3",
        Name: "bus ; disk ; camera"
    }
);
// Reset Collection
Clear(colSplitSharePoint);
// For Each record, collect 3 records (assumes all rows have 3 splits)
ForAll(
    colSharePoint,
    With(
        {
            varID: ThisRecord.ID,
            varSplitArticul: Split(
                ThisRecord.Articul,
                ";"
            ),
            varSplitLabeling: Split(
                ThisRecord.Labeling,
                ";"
            ),
            varSplitName: Split(
                ThisRecord.Name,
                ";"
            )
        },
        // Split 1
        Collect(
            colSplitSharePoint,
            {
                ID: ThisRecord.varID,
                Articul: Last(
                    FirstN(
                        varSplitArticul,
                        1
                    )
                ).Result,
                Labeling: Last(
                    FirstN(
                        varSplitLabeling,
                        1
                    )
                ).Result,
                Name: Last(
                    FirstN(
                        varSplitName,
                        1
                    )
                ).Result
            }
        );
        // Split 2
		Collect(
            colSplitSharePoint,
            {
                ID: ThisRecord.varID,
                Articul: Last(
                    FirstN(
                        varSplitArticul,
                        2
                    )
                ).Result,
                Labeling: Last(
                    FirstN(
                        varSplitLabeling,
                        2
                    )
                ).Result,
                Name: Last(
                    FirstN(
                        varSplitName,
                        2
                    )
                ).Result
            }
        );
        // Split 3
		Collect(
            colSplitSharePoint,
            {
                ID: ThisRecord.varID,
                Articul: Last(
                    FirstN(
                        varSplitArticul,
                        3
                    )
                ).Result,
                Labeling: Last(
                    FirstN(
                        varSplitLabeling,
                        3
                    )
                ).Result,
                Name: Last(
                    FirstN(
                        varSplitName,
                        3
                    )
                ).Result
            }
        )
    )
);

Example tables, with the two collections.

GarethPrisk_0-1627497123671.png

 

@RandyHayes Thanks for your answer!!

What is it "_rows"?

RandyHayes
Super User
Super User

@Smarty_ 

It is a With scoped variable.  Please review the documentation on the With function to understand it further.

 

Essentially what it does is split the articul column by the semicolons.  Split is then a table of results and is stored in the _rows variable that is only in the scope of the With function.

It then uses that to count the number of records it will return and then puts the records together with the corresponding splits of the other columns.

It then uses the ForAll function to return a table of the records you want (this is the intended purpose and use of the ForAll...as a function that returns a table, not as a For loop function like in programming - which you're not).

 

The advantage with this is it will work fine even if you only have 1, 2, 3 or any number of semicolon separated items in the articul column.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
GarethPrisk
Resident Rockstar
Resident Rockstar

Nicely done, @RandyHayes. I forgot I could sequence for the loop N instead of hard-coding the collects like I did.

@RandyHayes Totally works! However, for some reason, when I run this code the empty row is created.

RandyHayes
Super User
Super User

@Smarty_ 

Yeah, sometimes Split will generate a separate empty return.

Change the formula to the following:

With({_item: {Articul:"111;222;333", Labeling:"LbIng1;LbIng2;LbIng3", Name:"bus;disk;camera"}},
    With({_rows: Filter(Split(_item.Articul, ";"), !IsBlank(Result))},
       ForAll(Sequence(CountRows(_rows)),
        {Articul: Last(FirstN(_rows, Value)).Result,
         Labeling: Last(FirstN(Split(_item.Labeling, ";"), Value)).Result,
         Name: Last(FirstN(Split(_item.Name, ";"), Value)).Result
        }
       )
    )
)

Obviously, replace the _item part to fit your needs.

 

You'll notice in there that there is an Filter function on the Split function.  This Filter will filter out any results that are blank.  That should solve the extra empty row.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,155)