Build Flow that creates ID using first characters of first 4 words of title

[MD7]

Hi all,

I need to create a flow that creates a ID for projects. Our ID for our projects uses the first character of the first 4 words followed by a number. If there is a duplicate ID, then the number adds 1. 

How can i do this? I attempted to do this using a formula column but it doesn't have all of the formula's i need. Hoping a flow can do this using some clever formula's but very unsure on how to do it.

Any help is appreciated.

Thanks!

[RobElliott · Accepted solution]

@MD7 yes this can be done with a flow. There may be improvements that could be made but this is what I have quickly put together to demonstrate it. The flow will work fine if there are are 4 or more words in the project title but will fail if there are less than 4.

This is my list and I'm triggering the flow from a JSON-formatted button in a single line of text column but you could equally use the "when an item is created" trigger. The code code for formatting the button is in the spoiler below:

Spoiler

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "button",
  "txtContent": "Create Project ID",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "='{\"id\":\"ff9dfed0-86f1-4fd4-9359-c342d0272f72\", \"headerText\":\" ' + [$Title] + '\",\"runFlowButtonText\":\"Go\"}'"
  },
  "style": {
    "background-color": "#3db8cd",
    "color": "white",
    "border-radius": "10px"
  }
}

Flow

1.  As the flow is triggered from the button the trigger in the flow is "for a selected item". Then add a "get item" action and select ID from the dynamic content box.

2. Next, initialize 2 variables, the first is a string variable I've named varCharacters which will hold the 4 letters of each word int he title column. The second variable is a float variable which will hold the count of the number of items with the same 4 characters.

3. Next, add 2 compose actions. For the first one click in the inputs field and select the Title column from the get item section of the dynamic content box.

For the second one, click in the inputs field and in the dynamic content box click the Expression tab and enter the following expression:

split(outputs('ComposeTitle'),' ')

This will create an array of the 4 words in the project title and uses a space as the delimiter.

4. Next, add a compose action with the expression

substring(outputs('ComposeSplit')?[0],0,1)

This will get look at the outputs of the first ComposeSplit and from position 0 will get 1 character. Note that the index number of the first item in an array is [0].

Copy that compose to your clipboard and then create new parallel branches and add 3 more compose actions from the clipboard, changing the expression to:

substring(outputs('ComposeSplit')?[1],0,1)

substring(outputs('ComposeSplit')?[2],0,1)

substring(outputs('ComposeSplit')?[3],0,1)

5. Click the new step button and add an append to string variable action selecting the outputs of ComposeFirst, ComposeSecond, ComposeThird and ComposeFourth.

So if we are adding a new item for HS2 Birmingham Ecology Study that will result in the following in the varCharacters string variable:

6. Next, we want to check if any of the items in the list have a project ID that starts with the characters in the varCharacters variable. So add a Get items action and in the filter query field type ID ne '' and inside the single quotes select ID from the get item section of the dynamic content box. We are doing this because we don't want the get items to bring back the item we've just created.

7. Next, add an apply to each and add a condition of ProjectID starts with varCharacters

Leave the red if no channel empty. For the green if yes channel add an increment variable action, select the carCount variable and set the value to 1.

8. Outside the apply to each add another condition of varCount is equal to 0.

• In the green if yes channel add an update item action and select the site, list and the ID from the get item section of the dynamic content box. For the Project ID filed select varCharacters. Copy this action to your clipboard.
• In the red if no channel paste in the update item action from your clipboard but after varCharacters in the ProjectID field type a dash - and select varCount.

So when we add another item with the project title HS2 Birmingham Ecology Study and click the Create Project ID button the flow runs. Because there are already items HBES and HBES-1 it creates the ProjectID HBES-2.

Come back if you have questions about any of this.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[MD7]

Wow. Thank you for such an amazing, detailed answer. Absolutely smashed it! Thanks!