List to Links on Reports

Peter_Eby
Participant II

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.

0 6 410
6 REPLIES 6

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.

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 .

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

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?

Top Labels in this Space