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

[Unbihexium]

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

[RandyHayes]

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

[Unbihexium]

Alright I try to explain it as good as I can.

all starts here (screenshot)  - input from user in the colored frame --> when clicking the button it collecting thous four text values.
the collection "Bestellliste.Liste" is displayed just below in a Gallery.

As soon as all entries (orders) are made, the user clicks on the "Bestellung absenden" button in the upper right corner, which submits the form to my SP List.
The informations from "Bestellliste.Liste" are converted to text with concat function (screenshot2) on the specific DataCard (@update).

later, the Item can be selected in the main screen from a Gallery which shows all item from my SP List. After clicking on the item it navigates you to the "edit screen". The goal is to be able to make corrections to the needed text values and (more important) add an other text value.
for example:  for each row at the end I want to add    "|ImdNr:  ******"

Art.Nr.: 123-456 | Bez: qwertz | Mng: 12 | Wunsch Datum: 30.09.2020 | ImdNr: 244599
Art.Nr.: 111-222 | Bez: rrrrrrrrrrrrrrrrrr | Mng: 55 | Wunsch Datum: 24.09.2020 | ImdNr: 244886
Art.Nr.: 222-333 | Bez: zzzzzzzzzzz | Mng: 77 | Wunsch Datum: 18.09.2020 | ImdNr: 244555

I thought something like that... but I'm probably wrong

[RandyHayes]

@Unbihexium 

It's a little convoluted but let me see if I have it clear:

1) User Clicks on Button5 (add to the order list/zur bestellliste hinzufügen) where you collect a Bestellliste collection that has one column called Liste with individual records of Artnr, Bex, Mng, Date, ImNr. 

First question arises - why are you making a single column collection with sub records?

2) On that screen, you have a Gallery.  It appears to be in a DataCard (Bestell-Liste) but it is not.  It is just over the card. 

I assume it is the Gallery BestellGallery?  What is the Items property?

3) User clicks on (button name unknown) (Bestellung absenden/Submit your request) - 

I am assuming it is a basic SubmitForm(Form1) ??

4) Later - a user can select the Order from a Gallery of all the items.  This is done by clicking on Button6 (Bearbeiten/Edit)

Observation here...yes, you are in the Gallery context at this point with the collection formula.  So, that is known now.

Okay, before going too much further - There is something that I am missing that is important.  You mention that each order line is in a string.  My question is, does the string include all of the order lines?

My original assumption was that each line was in its own record.

By the way, thank you for being so very descriptive in your responses - they help narrow in on the solution easily - it is much appreciated!

[Unbihexium]

Hi RandyHayes

Thank you so much for you help!
My Answers in green text.

1) User Clicks on Button5 (add to the order list/zur bestellliste hinzufügen) where you collect a Bestellliste collection that has one column called Liste with individual records of Artnr, Bex, Mng, Date, ImNr.
First question arises - why are you making a single column collection with sub records?

Hmmm actually for no good reason. I was experimenting with Table function tried to get these text values in to a single line.

2) On that screen, you have a Gallery. It appears to be in a DataCard (Bestell-Liste) but it is not. It is just over the card.
I assume it is the Gallery BestellGallery? What is the Items property?

Correct, this Gallery (BestellGallery) is not in the DataCard respectly not in the “form1” formula.
Item property = Bestellliste.Liste

3) User clicks on (button name unknown) (Bestellung absenden/Submit your request) -
I am assuming it is a basic SubmitForm(Form1) ??

Absolutely, see screenshot

4) Later - a user can select the Order from a Gallery of all the items. This is done by clicking on Button6 (Bearbeiten/Edit)
Observation here...yes, you are in the Gallery context at this point with the collection formula. So, that is known now.

I am so bad at explaining 😊 …what I’ve tried so say: On the main page, I have a Gallery overview of all items of my SP List.
which were previously transmitted with the button2 (Bestellung absenden)

In the "Einkauf_edit" screen I have the button "Button6" which makes the split 

Okay, before going too much further - There is something that I am missing that is important.
You mention that each order line is in a string. My question is, does the string include all of the order lines?

Yes that’s correct, the string include all the lines (screenshot SP list) and these lines can vary for each “order” (one row in SP list represents one order) 

My original assumption was that each line was in its own record.
That was my fault, I have a talent to explaining things in a complicated way. in other words... just "bad"

thanks in advance
regards

[RandyHayes]

@Unbihexium 

Actually, my friend, you are doing an excellent job at explaining.  It is always difficult to articulate intentions and concepts to someone that is not connected to the data, the app or the goals - so...I'm trying 😉

BUT, I am getting the concept I believe - or at least closer to it.  I have a couple of questions that would weigh into my suggestions.

1) On the edit screen - again you have a Gallery outside of the EditForm and it shows the lines of the order.  There is a TextBox in each line where the user can enter what will become ImNr.   My question is this - is this the ONLY edit they can make?  There is no concept of adding a line or changing any of the information in the other elements (ArtNr, Bex, Mng, etc.), just adding that one element.  Is that a correct assumption?

2) What is varList used for?  I see that you are setting it in Button6, but not sure where it is used from there.  Is that what is used as the Items property for Gallery5?  I assume this based on the action of the arrow icon, but want to be clear.

3) On Button6, I see your image of the formula, but it is not completely shown - does that action then have a Navigate to the Einkauf_edit screen?  I ask this because I am reviewing all the formulas and screenshots and it seems that there is an extra screen somewhere in between.  You have a screenshot of your Hauptseite screen and I see the Gallery there and the Navigate to the Einkauf_edit screen, but then there is this Button6 (Bearbeiten)...where is that button?  That does not appear to be the Edit screen.  So, I'm just a little confused on the Edit aspect and what initiates that - is it perhaps both places?

So, I believe if I can get those questions down, I should be able to give you some pretty good suggestions on how to handle all of this.

[Unbihexium]

@RandyHayes

True, but not everyone is as understanding as you are, and even less does it actually care to help. hats off!

1) On the edit screen
Yes, totally correct. Only one more text value is need which should be that "ImNr".
I've already tried to add that text value, which works as far as I can tell.

2) What is varList used for?
Thats correct, the idea was just to use it for displaying it nicely in a gallery

3) On Button6, I see your image of the formula, but it is not completely shown - does that action then have a Navigate to the Einkauf_edit screen?
not quite, the navigation happens in the main screen, and it takes you to the "Einkauf_edit" screen. The Button6 is in the "Einkauf_edit" screen.
I made this button originally just to split the string from 'Bestell Liste'. In the meantime it makes some things visible or invisible. (see gif)

I ask this because I am reviewing all the formulas and screenshots and it seems that there is an extra screen somewhere in between.
I had one more screen "Screen1" which was just a copy of the "Einkauf Edit" for experimenting/testing.

You have a screenshot of your Hauptseite screen and I see the Gallery there and the Navigate to the Einkauf_edit screen,
but then there is this Button6 (Bearbeiten)...where is that button? That does not appear to be the Edit screen.
It is actually in the "Einkauf_edit" screen, I added the button6 manually to the "Bestell-Liste" DataCard. (see screenshot)

I'm appreciate your help very much! I owe you a big beer! 😁

regards 
 

[RandyHayes · Accepted solution]

@Unbihexium 

Perfect!!  I believe I have it all down now and I can offer a relevant solution.

First, I've attached a working sample app that you can look over to see what I am mentioning working.  The only real difference between the sample and what you have is that I was not able to use EditForms (as I'm not connected to data - it's all internal) but the concept is the same and I believe I've conveyed the "what is needed" in order to get it over to the edit forms.

So, starting from the beginning.  

Main Screen:

Gallery2 - all fine here, except you might set your OnSelect to the following:

Set(VisList; true);; Navigate(Einkauf_edit, Fade);; 

On the Edit screen:

    Bestell-Liste_DataCard1:

        Default property

ThisItem.'Bestell-Liste'

        Update property (note: I was not able to determine the name of the TextInput control in your Gallery5 in the app, so in this formula, it is called TextInput1)

Concat(Gallery5.AllItems; 
    "Art.Nr.:" & Artnr &
    "|Bez: " & Bez &
    "|Mng: " & Mng & 
    "|Wunsch Datum: " & Date &
    If(!IsBlank(TextInput1.Text); "|ImNr: " & TextInput1.Text) & 
    "||" & Char(10)
)

    DataCardValue9:

        Default property

Parent.Default

    Gallery5:

        Items Property

With({lclRec: Filter(Split(Substitute(Gallery2.Selected.liste; Char(10); "); "||"); !IsBlank(Result))};
    ForAll(lclRec;
        With({lclSp: Filter(Split(Result; "|"); !IsBlank(Result))};
            {
                    Artnr: Trim(Last(Split(First(lclSp).Result; ":")).Result);
                    Bez: Trim(Last(Split(Last(FirstN(lclSp; 2)).Result; ":")).Result);
                    Mng: Trim(Last(Split(Last(FirstN(lclSp; 3)).Result; ":")).Result);
                    Date: Trim(Last(Split(Last(FirstN(lclSp; 4)).Result; ":")).Result);
                    ImNr: If(CountRows(lclSp)>4; Trim(Last(Split(Last(FirstN(lclSp; 5)).Result; ":")).Result); Blank())
            }
        )
    )
)

With the above, you do not need the individual save buttons in the Gallery for each line and you don't need any collections.  

The magic of this is that we set the Update property of the datacard to be the Concat of the information in the Gallery.  And the Items in the Gallery is the Splitting of the string into individual elements.

With this, in order to actually update your SharePoint list...all you need to do is a SubmitForm(Form3) - it will have the information as you want it.

Take a look over this - it's a slightly different concept than what you had, but it is pretty streamlined and should be easier to maintain as well.

I've tried to go through the above formulas and changed to comas to semi's - hopefully I got them all.

Also, don't forget to take a look at this in action in the attached app (download it, then in PowerApps studio, create an app or open an existing app, and then click on File->Open->Browse and then select the downloaded file.

Let's see if this gets you closer to where you want to be.

[Unbihexium]

Good Morning RandyHayes

Hope you had a relaxing weekend! 

Thank you so much for your support! 
I will implement this right now and give you feedback... and I will take a interested look at your msapp.

kind regards

[Unbihexium]

@RandyHayes 

 

Hi M8
It took me awhile, but I think I got it.

As expected, everything you made works outstanding. (see gif)

Your demo app was amazing helpfully to understand what is happening, actually the biggest benefit for me 😄 
Its still a bit witchcraft for me, but I'm highly motivated and interested to learn this witchcraft  ^^. 

I think about to change it later or make a copy of my app with your alternative method of updating the record with the "updatelf" function.
Just to see if it perhaps runs smoother and for practice. 

I could make a giant step forward with that, so Thank you very much for your efforts!
Should you ever decide to visit Switzerland, would be a pleasure to offer you a beer! 😁

Thanks again! 
regards

just for the others:  added the missing " in the first row

With({lclRec: Filter(Split(Substitute(Gallery2.Selected.liste; Char(10); ""); "||"); !IsBlank(Result))};
    ForAll(lclRec;
        With({lclSp: Filter(Split(Result; "|"); !IsBlank(Result))};
            {
                    Artnr: Trim(Last(Split(First(lclSp).Result; ":")).Result);
                    Bez: Trim(Last(Split(Last(FirstN(lclSp; 2)).Result; ":")).Result);
                    Mng: Trim(Last(Split(Last(FirstN(lclSp; 3)).Result; ":")).Result);
                    Date: Trim(Last(Split(Last(FirstN(lclSp; 4)).Result; ":")).Result);
                    ImNr: If(CountRows(lclSp)>4; Trim(Last(Split(Last(FirstN(lclSp; 5)).Result; ":")).Result); Blank())
            }
        )
    )
)
 

[RandyHayes]

@Unbihexium 

Excellent!! I am glad we got to a working point.  Always happy to help where I can.  And always enjoy a nice beer - so, I'll put you in my list when I ever visit Switzerland 🙂

Keep learning and asking questions as you go!