Answered

Creating a URL from specific numeric values from a SharePoint List

(0) Share

Report

Posted on by 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

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

Expiscornovus 33,871 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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))

Was this reply helpful? Yes No
WHSBPjM 59 on at

Like (1)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
WHSBPjM 59 on at

Like (0)

Report
Copy link

Link copied!

@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?

Was this reply helpful? Yes No
Expiscornovus 33,871 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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)))

Was this reply helpful? Yes No
WHSBPjM 59 on at

Like (0)

Report
Copy link

Link copied!

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))
)

Was this reply helpful? Yes No