SQL with an HTML Output

[Clint]

I have a working conditional statement using SQL. However, the output (following the "then" statement outputs plain text.

Does anyone know how to force it to be formatted as a clickable link?

Here's the full statement: 

CASE

WHEN [@field:connect_PIF_ER_eval_complete]='' then '<a href="participant_evaluation.html?ER_ID=[@field:ER_Events_1_ER_ID]&Ev_Type=[@field:ER_Events_1_Event_Type]">Evaluation</a>'

ELSE 'Evaluation Completed'

END

I'm sure it's obvious, I'm just not sure what needs to surround the href tag. 

Thanks!

[Mathilda]

On 4/19/2017 at 1:39 PM, Clint said:

I have a working conditional statement using SQL. However, the output (following the "then" statement outputs plain text.

Does anyone know how to force it to be formatted as a clickable link?

Here's the full statement: 

CASE

WHEN [@field:connect_PIF_ER_eval_complete]='' then '<a href="participant_evaluation.html?ER_ID=[@field:ER_Events_1_ER_ID]&Ev_Type=[@field:ER_Events_1_Event_Type]">Evaluation</a>'

ELSE 'Evaluation Completed'

END

I'm sure it's obvious, I'm just not sure what needs to surround the href tag. 

Thanks!

You can't use html tags in calculated fields.  You need to use Java Script. As far as I understand, you need to show link only if the field is blank.

I would recommend using the following script in html block

<script>
if ('[@field:connect_PIF_ER_eval_complete]'.length<1) {
document.write('<a href="participant_evaluation.html?ER_ID=[@field:ER_Events_1_ER_ID]&Ev_Type=[@field:ER_Events_1_Event_Type]">Evaluation</a>');
}
else {
document.write('Evaluation Completed');
}

</script>

 

[DaeBo]

On 4/21/2017 at 5:35 AM, Mathilda said:

You can't use html tags in calculated fields.  You need to use Java Script. As far as I understand, you need to show link only if the field is blank.

I would recommend using the following script in html block

<script>
if ('[@field:connect_PIF_ER_eval_complete]'.length<1) {
document.write('<a href="participant_evaluation.html?ER_ID=[@field:ER_Events_1_ER_ID]&Ev_Type=[@field:ER_Events_1_Event_Type]">Evaluation</a>');
}
else {
document.write('Evaluation Completed');
}

</script>

 

Hi Mathilda,

Thank you for the script. It worked for me as well. However, the script is only showing on the first page of results and when I click to the next page of results, it doesn't show. Do you know why?

 

[Mathilda]

11 hours ago, DaeBo said:

Hi Mathilda,

Thank you for the script. It worked for me as well. However, the script is only showing on the first page of results and when I click to the next page of results, it doesn't show. Do you know why?

 

Hi, I'm glad that it works for you as well

Try disabling AJAX on your report, it should helps. You can do that on 'result page options' screen

Hope that helps

[DaeBo]

16 hours ago, Mathilda said:

Hi, I'm glad that it works for you as well

Try disabling AJAX on your report, it should helps. You can do that on 'result page options' screen

Hope that helps

Thanks, that worked!

[CFSol]

Hi,

I have just tried to recreate the above code but I cant get it to work. I have put my code below, any help would be appreciated.

<script>
if ('[@field:Invoice_Number]'.length<1) {
document.write('<a href="https://c1hcp423.caspio.com/dp/c88fb000f4126dbfd7824161a0d3?Connote=[@field:Connote]">[@field:Connote]</a>');
}
else {
document.write('<a href="https://c1hcp423.caspio.com/dp/c88fb0000838aba48ed646358911?Connote=[@field:Connote]">[@field:Connote]</a>');
}

</script>

 

[CoopperBackpack]

Hello @CFSol,

As far as I understood, you would like to add a conditional link in the HTML block on the Tabular Report DataPage.

And the condition is: if the Invoice_Number field is blank the user should be redirected to one DataPage, if it is not blank - to another DataPage.

Please note that document.write  method is not recommended https://developer.mozilla.org/en-US/docs/Web/API/Document/write

You may test the following approach instead (this code should be added to the HTML block):

<div id="companyID[@field:Company_ID]"></div>

<script>

if ('[@field:Invoice_Number]'.length < 1) {
document.querySelector('#companyID[@field:Company_ID]').innerHTML = '<a href="https://c1hcp423.caspio.com/dp/c88fb000f4126dbfd7824161a0d3?Connote=[@field:Connote]">[@field:Connote]</a>';
}
else {
document.querySelector('#companyID[@field:Company_ID]').innerHTML = '<a href="https://c1hcp423.caspio.com/dp/c88fb0000838aba48ed646358911?Connote=[@field:Connote]">[@field:Connote]</a>';
}

</script>

1) The first line of code is a 'div' element (container) to add the link to it. It should have a unique ID. 

To generate the unique ID you may use some text (in my example it is companyID) and the unique field of the data source (in my example it is [@field:Company_ID])

<div id="companyID[@field:Company_ID]"></div>

This unique field does not have to be added to the Results, we can reference it anyway.

 

2) In the conditions below we reference this id to display the link.

Please replace companyID[@field:Company_ID] with your text and your unique field in a 'div' and in a 'document.querySelector'

When you add a field as a parameter, please as it as a String:

 

Hope this helps.