cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WHSBPjM
Frequent Visitor

Creating a URL from specific numeric values from a SharePoint List

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 IDTitleProject Manager
ABC12345This Flow is getting insaneJohn Doe

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Expiscornovus
Super User
Super User

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

 

getsubstring_5.png 

View solution in original post

5 REPLIES 5
Expiscornovus
Super User
Super User

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

 

getsubstring_5.png 

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

@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
Super User
Super User

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
Frequent Visitor

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

)

 

 

WHSBPjM_1-1645596182171.png

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (4,924)