cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leanns
Level: Powered On

Splitting multiple lines of text

Hello,

 

I am creating my first app based on a SharePoint List that will store the information. There are multiple fields within this list but I have some multiple lines of text fields that I can't figure out how to split within PowerApps. 

 

I have a my app set up so there's a custom form the user fills out and updates the actual SharePoint List form to create a new item within the list. The custom form is to allow an easy format to input the data since the SharePoint List doesn't not portray this ease. This is shown in the screenshots below. New Entry page.PNGCustom formnew form.PNGActual Form

To combine the data, I'm using this formula (all computed on a different screen that the end user won't see): 

Concatenate(If(IsBlank(Criteria_1),Blank(),Concatenate(Criteria_1.Text,"_",Text(IdealRating_1.Value),"; ")),If(IsBlank(Criteria_2),Blank(),Concatenate(Criteria_2.Text,"_",Text(IdealRating_2.Value), "; ")),If(IsBlank(Criteria_3),Blank(),Concatenate(Criteria_3.Text,"_",Text(IdealRating_3.Value),"; ")),If(IsBlank(Criteria_4),Blank(),Concatenate(Criteria_4.Text,"_",Text(IdealRating_4.Value),"; ")),If(IsBlank(Criteria_5),Blank(),Concatenate(Criteria_5.Text,"_",Text(IdealRating_5.Value),"; ")),If(IsBlank(Criteria_6),Blank(),Concatenate(Criteria_6.Text,"_",Text(IdealRating_6.Value))))

I have basically duplicated these two screens to create an edit an existing item. This is where I am struggling. I want to be able to split the data that's being concatenated in the previous screenshots to be split to portray in an easy way to update the data if needed. I have not been able to get the split function to work. I keep getting the error of this isn't a text value. 

 

For the moment, I'm using the functions, Left(), Mid(), and Right() but I would prefer to use the split function if possible because there are 6 values within each multiple lines of text field. Any help would be appreciative. 

 

Right(BrowseGallery1.Selected.'Ideal Customer Profile',Find(";",BrowseGallery1.Selected.'Ideal Customer Profile'))
Mid(BrowseGallery1.Selected.'Ideal Customer Profile',Find(";",BrowseGallery1.Selected.'Ideal Customer Profile'))
Left(BrowseGallery1.Selected.'Ideal Customer Profile',Find(";",BrowseGallery1.Selected.'Ideal Customer Profile'))

split error.PNGSplit Error

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Splitting multiple lines of text

Hi @leanns ,

[editing to include collection info]

I've spent a little more time looking at your post, and from what I can see it appears as if you're going through quite a bit of effort to work around a different problem - that being the Edit Form format connected to your list.  While working through the long way round often provides more opportunities to learn, we may want to dig a little deeper into the form and how you can customise it to suit your needs.That said, LookUp() takes between two to three parameters depending on what you want to return.

If you want the whole record with all the fields/columns then you only use two parameters.  If you want a specific field/column, then it takes three - the difference is that returning a record behaves like a record, returning a field just returns the value of the field as the type saved in the source (text, int, date etc.). (This is important for when you want to see the result displayed in a certain control)

For a record:

LookUp(source, condition)

For a specific field of that record:

LookUp(source, condition, field to return)

Source: Any tabulated data - whether it's a sharepoint list, a collection, a table output from a function or even a variable that contains a table - as long as it has a table consisting of columns and rows, you should be able to LookUp() it.

 

Condition: A condition that allows the LookUp() function to find a specific record - think of it as a 'Where' clause - usually this is a reference to a unique or primary key column, but really it can be anything that results in a single row of data.  Examples of conditions might look like this;

  • ID=1
  • ID=ThisItem.ID
  • StartsWith(Title, TextInput1.Text)

If the column you're using to 'find' your record has multiple matches to your condition (i.e. the column does not contain strictly unique data), then LookUp() will just return the first one it finds.  It will always do this anyway, so try and make sure your condition will never result in multiple matches, or be sure the one you're looking for is the first one Smiley Happy

What you're returning is quite important based on the control displaying the result - for example, a text label is looking for text - so you'd need to give it a text field, not a record.

For now, your lookup for the results of your split will depend on what it is you're looking up. 

 

A collection is a temporary table you create that persists for the duration of your powerapps session.  It's defined by the data you put in it and you can call it whatever you want, whenever you want.  While it can behave like a connected source, it's not 'connected', it's 'created'.  You can manually create columns with data to put into a collection as part of the Collect() statement, or your can collect the results of any function that returns tabular or record data into a collection.  You can add columns and data to it at any time, and you can even just collect data from a source into a collection "as is" to create an 'in memory' copy of your source data inside PowerApps to work with.  

In this instance, we can collect the results of the Split() function into a collection we'll call splitCollection.  We'll assume for now myVeryLongConcatenatedString looks something like this;

"criteria1_rating3;criteria2_rating2;criteria3_rating1....." and so on.

Put the following onto a button OnSelect property;

Split(myVeryLongConcatenatedString, ";")

Running Split() by itself is splitting the string, but the output is disappearing into nowhere.  We could continue to wrap the Split() function with the rest of our formula, but then your formula will be a few paragraphs long and even harder to read than it already is Man Wink

So the idea here is to Collect() the results of the Split() function into a table we can reference in other formulas.

Change the OnSelect property of the button to;

ClearCollect(splitCollection, Split(myVeryLongConcatenatedString, ";"))

We use ClearCollect() just to clear it before we add anything to it - it's easier than first using Clear() and then Collect() straight after each other, although you need to do this in some cases.  If we wanted to append data to the collection, we'd just use Collect().

You can display the collection in a gallery or data grid, but it's not necessary - you can view the first 5 rows by clicking on View, Collections.

Your splitCollection collection should look something like this;

[Result]

criteria1_rating3

criteria2_rating2

criteria3_rating1

criteria4_rating5

To get the rating for criteria3, you would need to do a few things.

First, you need to get the specific data you're looking for out of the collection.  The values are quite predictable so we can use a StartsWith() function as our condition for a LookUp().  Add a text label to your screen and set its Text: property to the following formula so we can watch the formula as it works;

LookUp(splitCollection, 
StartsWith(Result, "criteria3"),
Result)

Here, we're returning the actual field instead of the whole record.  Remember, even if there's only one column to the table, there is a distinct difference between a record and a field, and in this case the field will make our formulas a little easier, and it's what the text property of the text label is expecting.  (It would complain if you fed it a record)

Now we have the field, we need to split that again using the "_" separator.  Change your text property formula to look like this;

Split(
        LookUp(splitCollection, StartsWith(Result, "criteria3"), Result), 
"_")

Remember, this places the results into another split table, so you can expect your text label to complain at this point - but we don't need to collect it as we can continue processing the results in-line in our formula to get to the field.  The last step is to pull out the second part of that split as a field - we know there are only two results from the split, a first half and a second half - so we can safely use the Last() function to get the last (or in this case, second) one.

Last(
      Split(LookUp(splitCollection, StartsWith(Result, "criteria3"), Result),"_")
       ).Result

Last() returns the last record of a table, so the .Result on the end tells it that we want to access the actual field "Result" of the last record - which should make our text label Text: property happy again.

[Text Label]
Text Property: 

Last(Split(LookUp(splitCollection, StartsWith(Result, "criteria3"), Result),"_")).Result

 

That should do it.

String manipulation in PowerApps is very useful, if somewhat convoluted - I usually reserve string construction and deconstruction only when there really is no other choice, simply because the formulas can get very complicated very quickly - which makes debugging later quite painful.  It's useful to know, but even better would be knowing something that means you don't have to use it Smiley Wink

 

Kind regards,

 

RT

 

 

8 REPLIES 8
Super User
Super User

Re: Splitting multiple lines of text

Hi @leanns ,

 

This might not solve the underlying challenge, (meaning, there may be better ways to do what you're trying to do), but to answer you specifically regarding Split()....

 

If you're trying to show the result of your split in a text label, you should know that the Split() function actually creates a table where each 'split' is a row, the column name of which is "Result".  The best way to see this is to collect the results of your Split() function into a collection - try placing a ClearCollect(mySplitResults, Split(...yoursplitformula...)) and you'll see what I mean.

 

To return a specific split result as text then would require you to pick a field from a record in this table - there is only one field/column, but multiple records so you will need to stipulate which record and which field you want to show - an easy way to see this in action is to use First(Split(.....)).Result in a Text Label.

 

Slightly harder would be to pull out key-value pairs or something halfway down the list - unless you know what you're looking for.  Plus, you appear to be using multiple separators - _ for criteria_rating and ; for each record.


So let's assume you first collect the results of your Split(....) using ; as your separator.  This should return a table with a Results column and values like "criteria1_rating", "criteria2_rating" and so on.  To further extract the ratings from the criteria you could then do a lookup on this collection and then split a specific record but this time using the _ character - just remember that each Split() function returns a table of records that need to be filtered or looked up to get a specific field.

 

As I said, there may be better ways to achieve your ultimate goal, but I'm not sure what that is, so hopefully this helps you with the Split() function Smiley Happy

 

Kind regards,

RT

 

 

leanns
Level: Powered On

Re: Splitting multiple lines of text

Hello @RusselThomas ,

 

Thank you for the insight and the help explaining the split funtion. 

 

I was able to figure out how to pull the first and last pair of criteria1_rating. You mentioned doing a lookup to split it up even more. Is there a chance you could provide some code for that? I don't fully understand the powerapps language yet to know how to properly work it. 

 

Also, I don't understand how to make a new collection. I am struggling to understand the datasource part of the function. I tried reading the help document on this function but it's not making sense in my head. I understand that if the datasource isn't there, I can create a new one for the collection to add data to which is what I want. I try and it doesn't work.

 

I think once I understand this and how to do a lookup through a collection, I will be able to achieve my goal. 

 

Thanks!

Super User
Super User

Re: Splitting multiple lines of text

Hi @leanns ,

[editing to include collection info]

I've spent a little more time looking at your post, and from what I can see it appears as if you're going through quite a bit of effort to work around a different problem - that being the Edit Form format connected to your list.  While working through the long way round often provides more opportunities to learn, we may want to dig a little deeper into the form and how you can customise it to suit your needs.That said, LookUp() takes between two to three parameters depending on what you want to return.

If you want the whole record with all the fields/columns then you only use two parameters.  If you want a specific field/column, then it takes three - the difference is that returning a record behaves like a record, returning a field just returns the value of the field as the type saved in the source (text, int, date etc.). (This is important for when you want to see the result displayed in a certain control)

For a record:

LookUp(source, condition)

For a specific field of that record:

LookUp(source, condition, field to return)

Source: Any tabulated data - whether it's a sharepoint list, a collection, a table output from a function or even a variable that contains a table - as long as it has a table consisting of columns and rows, you should be able to LookUp() it.

 

Condition: A condition that allows the LookUp() function to find a specific record - think of it as a 'Where' clause - usually this is a reference to a unique or primary key column, but really it can be anything that results in a single row of data.  Examples of conditions might look like this;

  • ID=1
  • ID=ThisItem.ID
  • StartsWith(Title, TextInput1.Text)

If the column you're using to 'find' your record has multiple matches to your condition (i.e. the column does not contain strictly unique data), then LookUp() will just return the first one it finds.  It will always do this anyway, so try and make sure your condition will never result in multiple matches, or be sure the one you're looking for is the first one Smiley Happy

What you're returning is quite important based on the control displaying the result - for example, a text label is looking for text - so you'd need to give it a text field, not a record.

For now, your lookup for the results of your split will depend on what it is you're looking up. 

 

A collection is a temporary table you create that persists for the duration of your powerapps session.  It's defined by the data you put in it and you can call it whatever you want, whenever you want.  While it can behave like a connected source, it's not 'connected', it's 'created'.  You can manually create columns with data to put into a collection as part of the Collect() statement, or your can collect the results of any function that returns tabular or record data into a collection.  You can add columns and data to it at any time, and you can even just collect data from a source into a collection "as is" to create an 'in memory' copy of your source data inside PowerApps to work with.  

In this instance, we can collect the results of the Split() function into a collection we'll call splitCollection.  We'll assume for now myVeryLongConcatenatedString looks something like this;

"criteria1_rating3;criteria2_rating2;criteria3_rating1....." and so on.

Put the following onto a button OnSelect property;

Split(myVeryLongConcatenatedString, ";")

Running Split() by itself is splitting the string, but the output is disappearing into nowhere.  We could continue to wrap the Split() function with the rest of our formula, but then your formula will be a few paragraphs long and even harder to read than it already is Man Wink

So the idea here is to Collect() the results of the Split() function into a table we can reference in other formulas.

Change the OnSelect property of the button to;

ClearCollect(splitCollection, Split(myVeryLongConcatenatedString, ";"))

We use ClearCollect() just to clear it before we add anything to it - it's easier than first using Clear() and then Collect() straight after each other, although you need to do this in some cases.  If we wanted to append data to the collection, we'd just use Collect().

You can display the collection in a gallery or data grid, but it's not necessary - you can view the first 5 rows by clicking on View, Collections.

Your splitCollection collection should look something like this;

[Result]

criteria1_rating3

criteria2_rating2

criteria3_rating1

criteria4_rating5

To get the rating for criteria3, you would need to do a few things.

First, you need to get the specific data you're looking for out of the collection.  The values are quite predictable so we can use a StartsWith() function as our condition for a LookUp().  Add a text label to your screen and set its Text: property to the following formula so we can watch the formula as it works;

LookUp(splitCollection, 
StartsWith(Result, "criteria3"),
Result)

Here, we're returning the actual field instead of the whole record.  Remember, even if there's only one column to the table, there is a distinct difference between a record and a field, and in this case the field will make our formulas a little easier, and it's what the text property of the text label is expecting.  (It would complain if you fed it a record)

Now we have the field, we need to split that again using the "_" separator.  Change your text property formula to look like this;

Split(
        LookUp(splitCollection, StartsWith(Result, "criteria3"), Result), 
"_")

Remember, this places the results into another split table, so you can expect your text label to complain at this point - but we don't need to collect it as we can continue processing the results in-line in our formula to get to the field.  The last step is to pull out the second part of that split as a field - we know there are only two results from the split, a first half and a second half - so we can safely use the Last() function to get the last (or in this case, second) one.

Last(
      Split(LookUp(splitCollection, StartsWith(Result, "criteria3"), Result),"_")
       ).Result

Last() returns the last record of a table, so the .Result on the end tells it that we want to access the actual field "Result" of the last record - which should make our text label Text: property happy again.

[Text Label]
Text Property: 

Last(Split(LookUp(splitCollection, StartsWith(Result, "criteria3"), Result),"_")).Result

 

That should do it.

String manipulation in PowerApps is very useful, if somewhat convoluted - I usually reserve string construction and deconstruction only when there really is no other choice, simply because the formulas can get very complicated very quickly - which makes debugging later quite painful.  It's useful to know, but even better would be knowing something that means you don't have to use it Smiley Wink

 

Kind regards,

 

RT

 

 

leanns
Level: Powered On

Re: Splitting multiple lines of text

Hello @RusselThomas ,

 

Thank you for the very detailed explanation, I really appreciate it. This helped a lot. You mention using a button to create the collection, does it have to be a button? 

 

Another question, within the final split you used the function StartsWith(), what if I don't know what it starts with since the user can put any text they want within the text input that is named Criteria_1? But I know that the second part is always going to be a number? 

 

To answer your question about my custom form. Since the information is being stored in SharePoint List, I have a column in the list for every field, which means that a lot of them are multiple lines of text as shown in the picture below.

 sp list.PNGSP List 

 

Basically SharePoint is just storing the information and I'll be creating a PowerBI report to showcase the data. Where I can split the columns and manipulate the data there. Due to the fact that the columns in SharePoint don't allow an easy way to input the data, I made my own form which updates the EditForm that's tied to the list which is what I showed in the initial posting. 

 

Basically I took the multiple lines of text columns and made it so it was formated into a table of sorts and the user can input up to 6 values (which is text for the most part) that pertains to that column. Then I was concatenate() each of the 6 values together to update the SP List. I got this to work. What I'm struggling with is what if the user wants to edit their item after they submit it. I don't really want them to directly edit the form because I feel like they would mess up the formating which would mess up everything most likely. 

 

So if you have a better idea on how to do this, please share. I will take any help in figuring this out as simply as possible. 

 

Thank you again for all the help. 

 

 

Super User
Super User

Re: Splitting multiple lines of text

Hi @leanns ,

 

Nope, it doesn't have to be a button Smiley Happy

Pretty much anything that has an action behaviour property that executes a formula - it could be a button, an icon or anything with an "OnSelect" property, or you could do it at the beginning of the application load, screen load or any other event that provides for executing a formula.  It's an action, so the basic rule is, it needs to form part of an action or event.

 

The overhead on concatenating and then splitting stuff out can be cumbersome, but in some cases it's easier than building out your data model - depending on how complicated the data model is.

That said, my personal preference for records containing fields with a many-to-one relationship to the record is to place the field in another list and relate it back to the record.

That is - if I have a record that has a field in it that requires multiple entries for that record, it's usually a good idea to take that field and place it in another list.  As part of the other list you can have a column that provides the ID of the current record - this allows you to create a relationship between the two and you can easily 'fetch' the relevant field entries related to that record.

 

I posted something about this here which you're welcome to read and see if it helps.

Adding other list fields to a form is a little trickier as a form is by it's nature connected to a single source, but you can place those fields into the form using custom cards for the user to capture within the form, and then save them after the primary form has been submitted.  You can do this by using Patch() and LastSubmit() in the OnSuccess property of your form to insert the newly created record's ID as a reference for each field.

 

Kind regards,

 

RT

 

leanns
Level: Powered On

Re: Splitting multiple lines of text

@RusselThomas 

 

Wow, that was extremely helpful! Thank you very much!

 

With the use of SharePoint Lists, if I were to implement this option, I would have like 5 little lists (one for each of the multiple lines of text columns from the main list) and 1 main list. Is there a way to combine and make it so there aren't so many lists? This idea could probably work really nicely for me if I didn't have to have so many lists. 

 

Thanks again,

leanns

Super User
Super User

Re: Splitting multiple lines of text

Hi @leanns 

The only way I'm aware of consolidating information into a single table would be to repeat each record for each iteration of data in the multiple fields (which would massively bloat and duplicate data in your table if you have lots of multiple-field entries for a single record), or to do what you're doing with the concatenate() and split() functions to put multiple fields into a single field.

At the end of the day you need to pick an approach that works for you and your app - which is often a trade-off.  For example, you would have difficulty filtering on your multiple field columns, but using them would save you having to have multiple lists.  When you remove a record, you only have to remove one record, not go looking for all that records related data in other lists to remove it there to.  In PowerApps, Flow and PowerBI you will have to add steps or formulas to deconstruct and reconstruct your strings to make them usable as fields which might be cumbersome.

All in all, everything you do is a step closer to being more experienced and more informed, so try everything, learn along the way and share your learnings back into the community Smiley Happy

Kind regards,

RT

Highlighted
leanns
Level: Powered On

Re: Splitting multiple lines of text

Hi @RusselThomas ,

 

Thank you for all your help, I truly appreciate it! 

 

leanns

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 92 members 4,287 guests
Please welcome our newest community members: