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!
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.
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.
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.
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.
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.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.
User | Count |
---|---|
200 | |
97 | |
56 | |
51 | |
42 |
User | Count |
---|---|
264 | |
157 | |
83 | |
80 | |
57 |