Can I build a hyperlink to a file based on two file names

[Anonymous]

Hi all,

We have a quotes folder in Sharepoint where we put quotes and add the quote number to the file name.

Example A:  VendorName_E188233.pdf   (E188233) is the quote number

We have a purchase order folder where we put the purchase orders when they are sent

Example B: VendorName_PO0112662_E188233.pdf, again the quote number is in the purchase order file name.

In our Purchase Order folder on Sharepoint we have a column called Quote which is a hyperlink type.

I have been asked to see if there is a way that when a PO is placed in the Purchase Order folder, find the matching quote in the quote folder and create a hyperlink against the Purchase Order file that links back to the quote.

Has anyone ever done anything like this before?

Many thanks.

[fchopo]

Hi @Anonymous 

You can do the following:

1) When a Purchase Order is created

2) Get the Quote Number using the following expression:

replace(split(triggerOutputs()?['headers/x-ms-file-name'],'_')[2],'.pdf','')

Which splits the file name by the "_" character, gets the 3rd element, and then removes the ".pdf".

3) Get the Vendor Name using the expression:

split(triggerOutputs()?['headers/x-ms-file-name'],'_')[0]

which gets the first element of the file name if it's split by "_" character.

4) Compose the Vendor Name using the output of previous expressions.

5) Find a vendor with that name using the expression:

FileLeafRef eq '@{outputs('Compose-Quote')}'

6) If it's found, get the Link to the item:

first(outputs('Get_files_(properties_only)')?['body/value'])?['{Link}']

The get files properties only action could return more than one element, and that's the reason we only take the first one (using first function).

7) Update Purchase Order with the link.

Hope it helps!

Ferran

[robofski]

You can use the lastindexof function to find the last _ in the filename of the PO and then if your quote numbers are always the same length you can use substring to get the quote number starting at the position returned by lastindexof for a length of the number of characters your quote number is.  Then you'll have the quote number and can create the hyperlink using concat to add the quote number to the quote folder url. 

[Anonymous]

Thank you that is a very detailed and complex solution.  I will start building that and try and get an understanding as to what each step does.

Greatly appreciate the time spent with it for me.

[Anonymous]

This has almost worked which is amazing.

First issue is if there is a space in the supplier name the link is built with a % in it which breaks the link working as a a link.

Second one is just I can't match your last step.

When I put the Update File Properties in there I end up with it inside a apply to each but then I can actually select anything other than Documents as the Library Name.

BUT this works only it adds the hyper link the the actual quote in the quotes folder, so it's adding the hyperlink to itself and not to the Purchase Order in the Purchase Order folder.

[Anonymous]

ah it seems our purchase orders folder has a heap of subfolders for the actual suppliers.  I can't see anywhere to tell the trigger to look inside of nested folders like the get files does for the nested items?

So we have Purchase Order/Vender Name.

Weirdly, I can see the full link in the Quotes column but when you click on it teams tries to open it in teams but in the browser and it fails.

https://teams.microsoft.com/teams is what is at the start of the URL when clicked but it has the correct domain / address for us when you see the hyperlink in teams, not sure why when it's left clicked to open it the link changes.