Unanswered

split a string into separate text values and collect them in a table or collection

(0) Share

Report

Posted on by Unbihexium

180

Hi guys
I need help to split a string into separate text values and collect them in a table or collection
The string comes from a sharepoint list

Art.Nr.: 23412 | Bez: asdfasdfasdfasdfasdfgasdfgdfhgsdf | Mng: 23 | Wunsch Datum: 25.09.2020 ||
Art.Nr.: 231 | Bez: dfSDFASDFASDFASDFASDFA | Mng: 3 | Wunsch Datum: 24.09.2020 ||
Art.Nr.: 2341 | Bez: sdfasdfsdafsadfasdfsadfsfas | Mng: 34 | Wunsch Datum: 17.09.2020 ||

I need all the values between:
"Art.Nr.: " and "|"
"Bez:" and "|"
"Mng:" and "|"
"Wunsch Datum:" and "|"
this thing "||" marks the end of a row

I've thought something like
Collect(varList;
{BST:
{Artnr: "23412";
Bez: "asdfasdfasdfasdfasdfgasdfgdfhgsdf";
Mng: "23";
Date: "25.09.2020"}
})
but for each row

no clue how to bring the split and collect function together or just to split them as needed

Collect(varList;
First(Split(ThisItem.'Bestell-Liste';"Art.Nr.: ").Result;
{BST:
{Artnr: ????????;
Bez: "asdfasdfasdfasdfasdfgasdfgdfhgsdf";
Mng: "23";
Date: "25.09.2020"}
}))

for any help thanks in advance

Categories:

Building Power Apps

I have the same question (0)

All responses (19)

Answers (2)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@Unbihexium

A couple ways to go about it, but you might consider this formula to extract those values.

ClearCollect(varList, ForAll(yourCollectionOrDataSource, With({lclSp: Filter(Split(Value, "|"), !IsBlank(Result))}, { Artnr: Last(Split(First(lclSp).Result, ":")).Result, Bez: Last(Split(Last(FirstN(lclSp,2)).Result,":")).Result, Mng: Last(Split(Last(FirstN(lclSp, 3)).Result, ":")).Result, Date: Last(Split(Last(lclSp).Result, ":")).Result } ) ) )

In the above, substitute the yourCollectionOrDataSource with the source of your records. Then substitute "Value" in the first split to be your column name. I think it might have been 'Bestell-Liste', but I wasn't sure from the formula you had.

This will first split the text by the "|" (and incidentally will then filter out the empty on you will have at the end), then assigns each value based on it's position in the split - and then that is the last of a split based on ":" - which should be the final value.

I hope this is helpful for you.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@Unbihexium

Oh sorry, just realized you are using ; for separation in your formulas, please substitute my ,'s for ;'s

Was this reply helpful? Yes No
Unbihexium 180 on at

Like (0)

Report

Hi RandyHayes

Wow, Thanks for this quick reply! Yup, know already the "," and ";" trap 🐵 thanks anyway!

did as recommended, I get one error (the name is invalid. This identifier is not recognized) , not sure why...

best regards

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@Unbihexium

As mentioned - you need to replace the "Value" with whatever your field name is that you want to parse.

In the above, substitute the yourCollectionOrDataSource with the source of your records. Then substitute "Value" in the first split to be your column name. I think it might have been 'Bestell-Liste', but I wasn't sure from the formula you had.

Was this reply helpful? Yes No
Unbihexium 180 on at

Like (0)

Report

Hi RandyHayes

Thanks for your reply
sorry my bad, that's right it should be 'Bestell-Liste'.
'Bestell-Liste' is a column in my SP List named 'Interne Bestellungen'. still got the error

then i realized, I have to say wich specific row or item in my SP List: ... so I've tried with Gallery2.Selected.'Bestell-Liste' but with that I get Errors: "The function "ForAll" has invalid arguments" and "The function "ClearCollect" has invalid arguments"
and with "ThisItem.'Bestell-Liste' " it looks even worse

regards

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@Unbihexium

Your formula has some errors, please correct to the following:

ClearCollect(varList, ForAll('Interne Bestellungen', With({lclSp: Filter(Split('Bestell-Liste', "|"), !IsBlank(Result))}, { Artnr: Last(Split(First(lclSp).Result, ":")).Result, Bez: Last(Split(Last(FirstN(lclSp,2)).Result,":")).Result, Mng: Last(Split(Last(FirstN(lclSp, 3)).Result, ":")).Result, Date: Last(Split(Last(lclSp).Result, ":")).Result } ) ) )

Your ForAll should be in the context of your list ('Interne Bestellungen') and your column you want the Value from is 'Bestell-Liste'

That correction should give you what you want.

Was this reply helpful? Yes No
Unbihexium 180 on at

Like (0)

Report
Hi RandyHayes

Thousand Thanks for your Help!! It works just perfect!
I had to correct the last row a little bit because the date was not showing up
ClearCollect(varList; ForAll('Interne Bestellungen'; With({lclSp: Filter(Split('Bestell-Liste'; "|"); !IsBlank(Result))}; { Artnr: Last(Split(First(lclSp).Result; ":")).Result; Bez: Last(Split(Last(FirstN(lclSp;2)).Result;":")).Result; Mng: Last(Split(Last(FirstN(lclSp; 3)).Result; ":")).Result; Date: Last(Split(Last(FirstN(lclSp; 4)).Result; ":")).Result } ) ) )

have a good day Sir!
regards

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@Unbihexium

Strange that the formula worked perfect when I vetted it on my side. But...what's important is that you have it working now!

Was this reply helpful? Yes No
Unbihexium 180 on at

Like (0)

Report

Hi RandyHayes

Sorry its just me confused almost no plan what I'm doing 😅
I found out, I have to change it to "ThisItem.'Bestell-Liste'
then its working as you provided. If I just use 'Bestell-Liste' (as I did befor) then I need to change the last row.

btw: just wondering... The split function actually works just perfect... unfortunatelly I realized later that its only split the first row.
and it collects 3 times the same row ... o0

sorry to bother you with that... I know this "discussion" is closed already, should I open a new one?

regards

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@Unbihexium

So I believe we are venturing into another area here...context! If you are able to use a ThisItem, then you are either in a DataCard or in a Gallery.

Where is it that you are trying to do this? It also appears that perhaps your intended goal is to have this in a Gallery. Let's explore your Items property of the Gallery as well.

As this post started, there was no context of where you were doing this function, only that you needed it done. So, if you are getting different results and having to make the changes that you mention, then we need to explore the context.

Was this reply helpful? Yes No