0

So I'm trying to integrate Power Automate into our SharePoint workflow to create a folder with the same value as the value inputted into the Title column of our SharePoint list. The flow failed today and I determined it was because the user submitted a title with trailing spaces.

I'd like to be able to do one of the following:

  1. Trim out the leading or trailing spaces and remove any possible invalid charaters from the Title field before the list item values are forwarded in the flow OR
  2. Use the Column Validation option to tell the user to remove any invalid charaters / leading/trailing spaces

Can someone guide me towards a solution for this?

EDIT: So I've found a semi-solution, though the leading/trailing spaces issue I'd like to still solve at the Sharepoint level rather than using Power Automate.

In the Column Validation for Title I used the following code: =AND(IF(ISERROR(FIND(",",[Title])),TRUE), IF(ISERROR(FIND("&",[Title])),TRUE), IF(ISERROR(FIND(";",[Title])),TRUE), IF(ISERROR(FIND("[",[Title])),TRUE), IF(ISERROR(FIND("+",[Title])),TRUE), IF(ISERROR(FIND(":",[Title])),TRUE), IF(ISERROR(FIND(")",[Title])),TRUE), IF(ISERROR(FIND("-",[Title])),TRUE), IF(ISERROR(FIND("*",[Title])),TRUE), IF(ISERROR(FIND("(",[Title])),TRUE), IF(ISERROR(FIND("$",[Title])),TRUE), IF(ISERROR(FIND("%",[Title])),TRUE), IF(ISERROR(FIND("~",[Title])),TRUE), IF(ISERROR(FIND("#",[Title])),TRUE), IF(ISERROR(FIND("]",[Title])),TRUE), IF(ISERROR(FIND(".",[Title])),TRUE), IF(ISERROR(FIND("!",[Title])),TRUE), IF(ISERROR(FIND("@",[Title])),TRUE), IF(ISERROR(FIND("/",[Title])),TRUE), IF(ISERROR(FIND("",[Title])),TRUE))

It works like a charm.

Will probably append it to add more invalid characters if they creep up but it should be fine for now.

0

Trimming the leading and trailing spaces is as simple as using the Trim(['Title']) expression in an Update Item action. Replacing invalid characters is slightly more complex but still not too difficult.

The way I would approach this would be:

Trigger - When an item is created

Actions:

  1. Create an array of invalid characters

  2. Store the Title in a String variable

  3. For each Invalid character, replace with a more suitable character. (For demonstration purposes, I'm using '$' but realistically I'd probably replace it with '_' or omit it entirely ''. I'm also wrapping the expression in trim() to remove the leading and trailing spaces.)

    trim(replace(variables('varTitle'),items('Apply_to_each'),'$'))

  4. Update varTitle with the new string

  5. Update the SharePoint item with varTitle

enter image description here

Initial List item Title: enter image description here

After flow has run: enter image description here

4
  • Thanks for this. I just trimmed it down to trim(variables('varTitle') since I took care of the invalid characters at the Sharepoint level. Still hoping for a solution at the validation stage but this works for now!
    – Devin
    Sep 23 at 17:20
  • Sorry, I thought your preference was for the Flow option. These formulas are not my strong suit but perhaps you could try incorporating something like this into your existing validation: =AND(NOT(LEFT([Title],1)=" "),NOT(RIGHT([Title],1)=" "))
    – user79408
    Sep 24 at 4:04
  • I haven't tried it yet (haven't had a chance) but if I am reading this code correctly, wouldn't this only screen for a single instance of white space? So if there is more than one space leading or lagging it would still cause the same error.
    – Devin
    Sep 24 at 19:20
  • If you have more than one leading or lagging space, the first character would still be a space, so the formula should still work.
    – user79408
    Sep 26 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.