cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danbert1973
Helper I
Helper I

Limit returned characters in Text field

I have a flow which is pulling data from a SQL database into a SharePoint list.

 

One of the fields that is pulling through from SQL may on occasion contain more than 255 characters, however the SharePoint field must remain as a 'Single Line of Text' type.  When the character limit is exceeded, the flow fails for the specific item with the error: "Invalid text value.\n\nA text field contains invalid data. Please check the value and try again."

 

What I would like to happen is for the field to pull through just the first 255 characters from the field in SQL.  Is there an expression I can use to specify that this happens rather than it just trying to pull through the whole field?

 

Many thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User
Super User

Just bring the SQL data in as-is, but when you insert it into your SharePoint list you can truncate using an expression if it is over 256 characters.

See the example below I made from your message:

2021-04-08_14-19-06.png

Here is the expression I used:

if
(
greater(length(outputs('danbert1973')), 256),
substring(outputs('danbert1973'), 0,256),
outputs('danbert1973')
)

The expression is saying...

 

If the length of the compose action called danbert1973 is greater than 256 characters, then grab the first 256 characters, but if it isn't, output the whole string.

 

You can use an expression like this in your SharePoint create record action.

View solution in original post

3 REPLIES 3
Paulie78
Super User
Super User

Just bring the SQL data in as-is, but when you insert it into your SharePoint list you can truncate using an expression if it is over 256 characters.

See the example below I made from your message:

2021-04-08_14-19-06.png

Here is the expression I used:

if
(
greater(length(outputs('danbert1973')), 256),
substring(outputs('danbert1973'), 0,256),
outputs('danbert1973')
)

The expression is saying...

 

If the length of the compose action called danbert1973 is greater than 256 characters, then grab the first 256 characters, but if it isn't, output the whole string.

 

You can use an expression like this in your SharePoint create record action.

danbert1973
Helper I
Helper I

Hi @Paulie78 

 

Thanks for pointing me in the right direction.  The Create List item action is within an apply to each, so I've amended the expression as below (the SQL field is called 'LineDescription').

 

if ( greater(length(items('Apply_to_each_9')?['LineDescription'], 256), substring(items('Apply_to_each_9')?['LineDescription'], 0,256), items('Apply_to_each_9')?['LineDescription'] ))

 

In terms of success, the flow saved without errors and is currently running and adding new records to SharePoint, although as it's pulling in a huge amount of rows from SQL, I will need to wait until it completes before I can see if it's dealt with any of the records with 255+ length fields.

 

I'll report back when complete - thanks again.

danbert1973
Helper I
Helper I

Thanks again @Paulie78 .  I misunderstood you had 2 compose actions at first but got my head around it in the end.

 

This is a useful thing to know for the future.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (3,012)