Creating a URL from specific numeric values from a SharePoint List

[WHSBPjM]

I am creating a flow that is pulling data from our team's SharePoint to create a weekly email, in an HTML table, of incoming projects. 

The details:

I have a column called ID that pulls project ID that consists of a 3 letter prefix followed by 5 distinct numbers.  (EX: ABC12345) I have finally figured out 95% of this ask aside from creating the URL that pulls the last  5 distinct numbers from the project ID and concatenates them to the URL itself.

For example: if I have ABC12345, I want to pull the 12345 and add it to the end of my static URL (https://www.link.com/) to provide a result of:  https://www.link.com/12345

When the Flow runs, it'll send an email containing a HTML table with a linked URL.

Project ID	Title	Project Manager
ABC12345	This Flow is getting insane	John Doe

[Expiscornovus · Accepted solution]

Hi @WHSBPjM,

If the format of the project ID is always the same you could use a substring function to retrieve the last 5 characters.

Below is an example of that approach.

In my example I am using the expression in the Select action. Also keep in mind this is only generating the link, not the a html tag. You have to amend that to fit your requirement.

concat('https://wwww.link.com/', substring(item()?['ProjectID'], 3, 5))

 

[WHSBPjM]

@Expiscornovus Thank you for your response and proposed solution. It took a while but I was eventually able to incorporate the ahref to your solution which now works perfectly. Not sure how I did not come across using substring earlier but I definitely appreciate the help. 

[WHSBPjM]

@Expiscornovus Is it possible to create a type of check for a number of characters first then decide which position to begin from? I ran my flow this morning in preparation for triggering my Monday report and it failed. I just realized we also have a specific operational prefix, that is rarely used, that is only 2 letters long and still has the 5 numeric characters (ie. AB12345, the original is ABC12345). 

So essentially I would need to combine the two:

ABC12345: concat('https://wwww.link.com/', substring(item()?['ProjectID'], 3, 5))

AND 

AB12345: concat('https://wwww.link.com/', substring(item()?['ProjectID'], 2, 5))

Is this possible at all?

[Expiscornovus]

Hi @WHSBPjM,

You could check the length of the ProjectID. If it equals 8 characters you can apply the first concat function. If it is something else you can apply the second concat function.

if(equals(length(item()?['ProjectID']), 8), concat('https://wwww.link.com/', substring(item()?['ProjectID'], 3, 5)), concat('https://wwww.link.com/', substring(item()?['ProjectID'], 2, 5)))

[WHSBPjM]

Hi @Expiscornovus ,

Thank you for the response, I keep receiving a "The expression is invalid" based on your proposed solution. 

I may have attempted to use other forms of expressions based on my research, but I cannot seem to come up with a valid expression. 

if(equals(length(item()?[‘Title’]), 8),

concat('https://jiraalign.com/CapabilityID=',substring(item()?['Title'],3,5)),

concat('https://jiraalign.com/CapabilityID=',substring(item()?['Title'],2,5))

)