Split collection string to multiple columns

(0) Share

Report

Posted on by Coopedup

355

Hi,

So I'm looking at ways to prevent Delegation (2000 rows!) in a SP List.

At the moment I have a Notes List with one row per note. To Simply this is to have all the notes for one item (we'll never have 2000 items for sure) in a single SP List field.

I'm just testing how this would work and get a error with my split command "Invalid argument type (table). Expecting a text value instead".

I build my collection (each field is separated by "*|" and each record by "|*". Example 3 rows of 3 columns.

ClearCollect(colText, "Jon Doe*|12/12/2019*| lots of text aa alakajaaoakjajoajoao[a|*
Jane Doe*|01/10/2020*| more text year stuff
line 2 for text2|*Jon Smith*| 01/02/2020*| Text goes here. Automated ! |*")

but my split returns the error.

Any ideas? The separators can be changed but need to be something that is unlikely to be added in the free text field.

ForAll(
 Split(colData,"|*"),
 Collect(colFinal,
 {Name:First(Split(Result,"*|")).Result,
 Date:Last(FirstN(Split(Result,"*|"),2)).Result,
 Notes:Last(FirstN(Split(Result,"*|"),3)).Result}))

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Pstork1 69,657 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
A collection isn't the same thing as a string. In your case what you want is probably the default value of a collection record which is a string. But I don't think you will be able to use that in a ForAll(). But your Split should be something like

Set(test,Split(First(colText).Value,"|*"))

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Coopedup

Consider the structure of your underlying database. It looks like you have a list of Items and many Notes. If I am correct, then each Item can have many notes. In that case have two lists that are connected by a single field. SharePoint assigns an ID column to every list. In the Notes list, have a number field that captures the ID from the Items table, typically it takes the name of the Parent table, like ItemsID. Take a look at my blog post https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Implementing-a-One-to/ba-p/200521 to see how to implement the relationship in SharePoint.

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(4)

Report
Copy link

Link copied!
@Coopedup

I would first review how to have a delegable column in your notes list so that you can in fact delegate a filter and get the rows that you want. You can have more than 2000 records as long as you are returning less than that from a delegable filter.

Your text would actually be easier broken apart if you just had a single bar in it rather than the alternating *| and |*

For example:

With({val: Split("Jon Doe|12/12/2019| lots of text aa alakajaaoakjajoajoao[a| Jane Doe|01/10/2020| more text year stuff line 2 for text2|Jon Smith| 01/02/2020| Text goes here. Automated ! |","|")}, ForAll(Sequence(CountRows(val)/3, 1, 3), {Name:Last(FirstN(val, Value)).Result, Date:Last(FirstN(val, Value+1)).Result, Notes:Last(FirstN(val, Value+2)).Result} ) )

Would give you all the results you are looking for from your ForAll table result.

I hope this is helpful for you.

Was this reply helpful? Yes No
Coopedup 355 on at

Like
a
(1)

Report
Copy link

Link copied!

So as suggested, I've carried a simple text field across to my Notes list which I filter on and remove the delegation restrictions.

Thanks for the guidance (again) and I've learnt something new (again) from this group!

Was this reply helpful? Yes No