Requirement: Trim, truncate, or otherwise modify the text from one SharePoint list field to make it appear in another.
Obstacle: Text functions in SharePoint list columns do not work properly on multiple line text columns.
The work-around here is to trick the new SharePoint list field into thinking it is operating on a single-line text field. While it is true that the text functions don’t work perfectly for multiple line text fields, they are adequate for simple tasks such as truncation. The issue is that SharePoint validates the calculated field when saving and throws an error if the function refers to a multiple line text field, preventing the save operation.
So how do you trick it?
1. Create Field1, single line text
2. Create Field2, calculated field, formula=RIGHT([Field1],LEN([Field1])-8)
(this gets rid of the html tag that will appear at the beginning as an artifact of operating on a multiple line text field)
3. Create Field3, calculated field, formula=LEFT([Field2],50) & “…”
(this will display the first 50 characters of the original field follows by the ellipse)
4. To prevent validation, do this from the site settings window
a. Delete Field1
b. Create Field1, multi-line text
5. Create a custom view and hide Field1 and Field2 and set it as the default view
(they will still appear in the item view, but not in the edit window since they are calculated fields)![Lines Lines](https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/126806i85DF5EB102AE485A?v=1.0)
There you have it. Steps for tricking SharePoint into doing something it was designed to do, but prevented from by poor validation logic. Now how to we trick those MS Devs into removing that erroneous validation logic?
Enjoy.
Did this help you? If so, please leave a comment!
The following two tabs change content below.
- This issue should be caused by the Rich Text for the Multi-line field in SharePoint list. You may take a try to switch the Label control into an HTML text control, which could handle the Rich Text format. Adding the control reference: HTML text control in PowerApps.
- You can use the following formulas to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, removing characters or spaces, and repeating characters. Change the case of text. To change the case of text, use the UPPER, LOWER, or PROPER function.
- Solution: Make the new field a calculated field and enter the formula, referencing the original field in brackets. Obstacle: Text functions in SharePoint list columns do not work properly on multiple line text columns. The work-around here is to trick the new SharePoint list field into thinking it is operating on a single-line text field.
Re: SharePoint list with 'Multiple lines of text' column not displaying entries It seems there are some issues with modern lists so best you can do here is to open a support ticket to report the problem. I’m using SharePoint Online with DFFS and have a requirement to relate one list to another. Unfortunately the user experience requires the use of a multi-line description field from List A to relate to List B but using a Lookup column in List B it isn’t allowed to use a multi-line column.
Solution: Make the new field a calculated field and enter the formula, referencing the original field in [brackets].Obstacle: Text functions in SharePoint list columns do not work properly on multiple line text columns.
The work-around here is to trick the new SharePoint list field into thinking it is operating on a single-line text field. While it is true that the text functions don’t work perfectly for multiple line text fields, they are adequate for simple tasks such as truncation. The issue is that SharePoint validates the calculated field when saving and throws an error if the function refers to a multiple line text field, preventing the save operation.
So how do you trick it?
1. Create Field1, single line text
2. Create Field2, calculated field, formula=RIGHT([Field1],LEN([Field1])-8)
(this gets rid of the html tag that will appear at the beginning as an artifact of operating on a multiple line text field)
3. Create Field3, calculated field, formula=LEFT([Field2],50) & “…”
(this will display the first 50 characters of the original field follows by the ellipse)
4. To prevent validation, do this from the site settings window
a. Delete Field1
b. Create Field1, multi-line text
5. Create a custom view and hide Field1 and Field2 and set it as the default view
(they will still appear in the item view, but not in the edit window since they are calculated fields)
Sharepoint Calculated Column Date
There you have it. Steps for tricking SharePoint into doing something it was designed to do, but prevented from by poor validation logic. Now how to we trick those MS Devs into removing that erroneous validation logic?
Enjoy.
Did this help you? If so, please leave a comment!
This is one of the challenges to SharePoint search content on a list from multiline field. Multi line text field cannot be indexed as it’s an Unsupported Column Type.
Indexing the field is separate from search crawling which is needed for search result. Make sure the column has a managed property which allows a column to be searchable, retrievable and quarriable.
I noticed an issue on a list with over 10000 lists items where content for multiline text was not working on list search page and not showing the required outcome whereas it shows the required result on the site search. So I believe that in a SharePoint list, multiple line content search do not working properly if list view threshold is over 5000 items.
Steps to Create Managed Property
First you need to create a site column and then create a Managed Property
1 – First create a site column for multi-line text field by going to site settings –> Web Designer Gallery –> Site Columns –> Create
2 – Create the site column and add it to a content type or SharePoint list.
3 – Add atleast one list item to the list, its important so it can create the crawled property for the body column. Now run full crawl on the list.
4 – Now go back to Site Settings –> Site Collection Administration –> Search Schema and click on New Managed Property.
5 – Now select the add mapping properties
6 – Leave “Text” selected, also check Searchable, Queryable and Retrievable.
7 – You can map multiple properties against this managed property to make sure you are fetching all the relevant columns.
8 – The crawled property is different depending upon what you named it. My column was called Body, so I found it under “ows_body”, “Body”, “ows_Body1” and “ows_Body_1”. I added multiple properties because I used body field in multiple lists.
Sharepoint Calculated Column With Multiple Lines Of Text In Java
9 – Now you can start the list index or wait for some time so search crawl can run automatically.
Adnan is two time SharePoint MVP (Most Valuable Professional) with over 12 years of extensive experience with major expertise on SharePoint Server, Office 365, .Net Platform and Microsoft BI. He is currently working SharePoint Architect at FMT Consultants. He is MCT Regional Lead for Pakistan Chapter since 2012. He is working on SharePoint for past nine years and worked on different intranet/intranet solutions for private & govt. sector majorly in Gulf region, which include OOB and customized solutions. He is a trainer, technology evangelist and also speaks in community forums.
- Five ways to get OneDrive URL for a User in Microsoft 365 - March 1, 2020
- OneDrive Sync issue: SharePoint Documents Library not syncing - February 12, 2020
- Teamwork in Microsoft 365 - September 18, 2019