List to Links on Reports

I have an interesting challenge I’m hoping someone can help me with.

I have a list of [Certificate number]s generated from an EnumList. The output appears like this:
2974611 , 1416837 , M19-0461 on a google sheet (pdf) report sent to the client. Each [Certificate number] matches a pdf file stored on our server.and I’d like to provide a link to the matching pdf for each number. for Example:
Clicking on 2974611 links to http://mywebsite/certificates/2974611.pdf
Clicking on 1416837 links to http://mywebsite/certificates/1416837.pdf
etc.

I am able to generate a HYPERLINK() in a virtual column using a bunch of SUBSTITUTES(), but then the entire link also displays on the report rather than just the certificate numbers.
if I embed HYPERLINK() on the form I can only have one link instead of three.

Looking forward to hearing the solution.

On your template, user expression <<hyperlink([URLFIELD],“View Certificate”)>>.
Could you test it out?

In terms of your second question, I believe your data type is ENUM, which store single value rather than multiple, or list. It will not render the values of the dropdown list.

1 Like

Thank You for your reply.
I am able to use the <<hyperlink([URLFIELD],“View Certificate”)>> expression on the google sheet, but can only generate one link this way. There is no real advantage to doing this over just adding a link to the template.
Ideally I would like to see each Certificate listed produce its own link, but it’s starting to look like it is not possible.

In a workflow template, you use a <<Start>> expression to perform iteration. However, <<Start>> expressions do not currently support iteration over items in an EnumList.

One alternative would be to put each of the PDF links in a separate child record. You could then use a <<Start>> expression to iterate over the child records. You could then use the Hyperlink expression Koichi-san recommended to format the hyperlinks appearing in the child records.

Alternatively, someone in the community may have a better suggestion .

1 Like

Thanks Phil, I had considered using a separate child record, but think it would be too disruptive to the workflow, plus each new record would require more sync time. I sort of found a work around which allows for a limited certificates.

I’ve put the following directly in the template. The [BeginURL] field just saves me from having to repeat the web address:
<<IFS(COUNT([Certificates])>=1,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],1),".pdf")),INDEX([Certificates],1)))>>
<<IFS(COUNT([Certificates])>=2,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],2),".pdf")),INDEX([Certificates],2)))>>
<<IFS(COUNT([Certificates])>=3,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],3),".pdf")),INDEX([Certificates],3)))>>
<<IFS(COUNT([Certificates])>=4,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],4),".pdf")),INDEX([Certificates],4)))>>
<<IFS(COUNT([Certificates])>=5,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],5),".pdf")),INDEX([Certificates],5)))>>
<<IFS(COUNT([Certificates])>=6,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],6),".pdf")),INDEX([Certificates],6)))>>
<<IFS(COUNT([Certificates])>=7,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],7),".pdf")),INDEX([Certificates],7)))>>
<<IFS(COUNT([Certificates])>=8,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],8),".pdf")),INDEX([Certificates],8)))>>
<<IFS(COUNT([Certificates])>=9,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],4),".pdf")),INDEX([Certificates],9)))>>
<<IFS(COUNT([Certificates])>=10,HYPERLINK((CONCATENATE([BeginURL],INDEX([Certificates],5),".pdf")),INDEX([Certificates],10)))>>

1 Like

Clever!

It worked at first, but google has now altered my hyperlinks with their redirect service. And the redirect is altered enough that it does not open the pdf directly. Is there a way of including a hyperlink on a googlesheet template without it going though google redirect?