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

[Smarty_]

Hello!

My case:

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

I have a SharePoint list:

ID	Articul	Labeling	Name
id12345	111 ; 222 ; 333	Lblng1 ; Lblng2 ; Lblng3	bus ; disk ; camera

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

Articul	Labeling	Name
111	Lblng1	bus
222	Lblng2	disk
333	Lblng3	camera

Thank you in advance!

[RandyHayes]

@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:

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.

[GarethPrisk]

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.

[Smarty_]

@RandyHayes Thanks for your answer!!

What is it "_rows"?

[RandyHayes]

@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.

[GarethPrisk]

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

[Smarty_]

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

[RandyHayes]

@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.