Carriage Returns Stripped from TextArea Submissions

jlkane · (This post was last modified: Sep 30th, 2009, 2:49 PM by jlkane.)

The <TEXTAREA> tag allows for long text input, stored in the FT database as field size = "Large (TEXT)" or "Very Large (MEDIUMTEXT). But carriage returns -- breaks between paragraphs -- are stripped from such submissions, making the result less readable.

Is there a way to retain such breaks in the saved text?

Thanks in advance,

Jim

I take it back ...sort of.

The good news: Those CRs are still there if you look at the submission through the "Edit Submission" page.

The bad news: They *appear* to be gone if you look at the submission through any of the HTML/Printer Friendly views or export the data to Excel.

So let me reframe the question:
Is there any way to help those CRs survive the export process to any of those handy ways to view the data?

***Ben*** · Oct 4th, 2009, 11:32 AM

Ah, sorry Jim - this is a known bug. See:
http://bugs.formtools.org/index.php?cmd=view&id=137

It's actually not a terribly tricky fix... you can just update the export manager template to display them normally. I'll take a look at this now, keep you posted.

***Ben*** · Oct 4th, 2009, 11:46 AM

Hey Jim,

Here we go. I'll release an update to the module today - unfortunately, that will only fix it for NEW installations. Current installations will need to manually edit the export type Smarty templates via the Form Tools UI to fix it. This is a nuisance, I know... but the whole point of the Export Manager was to allow users to create and customize the templates to display/download the data in whatever format they want. So I deliberately made upgrading unobtrusive: it would NEVER override the smarty templates.

I'll obviously have to rethink this!

Anyway, here are the new templates. To update them, select the Export Manager module in your Modules page, and update the Smarty Template for each one.

HTML / Printer-friendly: table format

Code:
<h1>{$form_name}</h1>

<table cellpadding="2" cellspacing="0" width="100%" class="print_table">

{* display the column headings *}

<tr>

  {foreach from=$display_fields item=column name=row}

    <th>{$column.field_title}</th>

  {/foreach}

</tr>

{* loop through all submissions in this current result set, and display each item in a manner

   appropriate to the field type *}

{foreach from=$submissions item=submission name=row}

  <tr>

    {foreach from=$display_fields item=field name=col_row}

      {assign var=field_id value=$field.field_id}

      {assign var=field_type value=$field.field_info.field_type}

      {assign var=col_name value=$field.col_name}

      {assign var=value value=$submission.$col_name}

      <td>

        {if $field_type == "select" || $field_type == "radio-buttons"}

          {smart_display_field_values field_id=$field_id selected=$value}

        {elseif $field_type == "checkboxes" || $field_type == "multi-select"}

          {smart_display_field_values field_id=$field_id selected=$value multiple=true}

        {elseif $field_type == "file"}        

          <img src="{$field.field_info.settings.file_upload_url}/{$value}" />

        {elseif $field_type == "system"}

          {if $col_name == "submission_id"}

            {$submission.submission_id}

          {elseif $col_name == "submission_date"}

            {$submission.submission_date|custom_format_date:$timezone_offset:$date_format}

          {elseif $col_name == "last_modified_date"}

            {$submission.last_modified_date|custom_format_date:$timezone_offset:$date_format}

          {elseif $col_name == "ip_address"}

            {$submission.ip_address}

          {/if}

        {elseif $field_type == "textarea"}

          {$value|nl2br}

        {else}

          {$value}

        {/if}

      </td>

    {/foreach}

  </tr>

{/foreach}

</table>

HTML / Printer-friendly: one by one

Code:
<h1>{$form_name}</h1>

{* loop through all submissions in the current result set *}

{foreach from=$submissions item=submission name=row}

<table cellpadding="2" cellspacing="0" width="100%" class="print_table">

  {* loop through all fields in the current View *}

  {foreach from=$display_fields item=field name=col_row}

    {assign var=field_id value=$field.field_id}

    {assign var=field_type value=$field.field_info.field_type}

    {assign var=col_name value=$field.col_name}

    {assign var=value value=$submission.$col_name}

    <tr>

      <th width="140">{$field.field_title}</th>

      <td>

        {if $field_type == "select" || $field_type == "radio-buttons"}

          {smart_display_field_values field_id=$field_id selected=$value}

        {elseif $field_type == "checkboxes" || $field_type == "multi-select"}

          {smart_display_field_values field_id=$field_id selected=$value multiple=true}

        {elseif $field_type == "system"}

          {if $col_name == "submission_id"}

            {$submission.submission_id}

          {elseif $col_name == "submission_date"}

            {$submission.submission_date|custom_format_date:$timezone_offset:$date_format}

          {elseif $col_name == "last_modified_date"}

            {$submission.last_modified_date|custom_format_date:$timezone_offset:$date_format}

          {elseif $col_name == "ip_address"}

            {$submission.ip_address}

          {/if}

        {elseif $field_type == "textarea"}

          {$value|nl2br}

        {else}

          {$value}

        {/if}

      </td>

    </tr>

  {/foreach}

</table>

<br />

{/foreach}

HTML / Printer-friendly: one by one

Code:
<h1>{$form_name}</h1>

{* loop through all submissions in the current result set *}

{foreach from=$submissions item=submission name=row}      

<table cellpadding="2" cellspacing="0" width="100%" class="print_table">

  {* loop through all submissions in the current View *}

  {foreach from=$display_fields item=field name=col_row}

    {assign var=field_id value=$field.field_id}

    {assign var=field_type value=$field.field_info.field_type}

    {assign var=col_name value=$field.col_name}

    {assign var=value value=$submission.$col_name}

    <tr>

      <th width="140">{$field.field_title}</th>

      <td>

        {if $field_type == "select" || $field_type == "radio-buttons"}

          {smart_display_field_values field_id=$field_id selected=$value}

        {elseif $field_type == "checkboxes" || $field_type == "multi-select"}

          {smart_display_field_values field_id=$field_id selected=$value multiple=true}

        {elseif $field_type == "system"}

          {if $col_name == "submission_id"}

            {$submission.submission_id}

          {elseif $col_name == "submission_date"}

            {$submission.submission_date|custom_format_date:$timezone_offset:$date_format}

          {elseif $col_name == "last_modified_date"}

            {$submission.last_modified_date|custom_format_date:$timezone_offset:$date_format}

          {elseif $col_name == "ip_address"}

            {$submission.ip_address}

          {/if}

        {elseif $field_type == "textarea"}

          {$value|nl2br}

        {else}

          {$value}

        {/if}

      </td>

    </tr>

  {/foreach}

</table>

{if !$smarty.foreach.row.last}

  <br />

  <div class="no_print"><i>- {$LANG.phrase_new_page} -</i></div>

  <br class="page_break" />

{/if}

{/foreach}

Excel: table format

Code:
<h1>{$form_name}</h1>

<table cellpadding="2" cellspacing="0" width="100%" class="print_table">

{* display the column headings *}

<tr>

  {foreach from=$display_fields item=column name=row}

    <th>{$column.field_title}</th>

  {/foreach}

</tr>

{* loop through all submissions in this current result set, and display each item in a manner

   appropriate to the field type *}

{foreach from=$submissions item=submission name=row}

  <tr>

    {foreach from=$display_fields item=field name=col_row}

      {assign var=field_id value=$field.field_id}

      {assign var=field_type value=$field.field_info.field_type}

      {assign var=col_name value=$field.col_name}

      {assign var=value value=$submission.$col_name}

    <td>

      {if $field_type == "select" || $field_type == "radio-buttons"}

        {smart_display_field_values field_id=$field_id selected=$value}

      {elseif $field_type == "checkboxes" || $field_type == "multi-select"}

        {smart_display_field_values field_id=$field_id selected=$value multiple=true}

      {elseif $field_type == "system"}

        {if $col_name == "submission_id"}

          {$submission.submission_id}

        {elseif $col_name == "submission_date"}

          {$submission.submission_date|custom_format_date:$timezone_offset:$date_format}

        {elseif $col_name == "last_modified_date"}

          {$submission.last_modified_date|custom_format_date:$timezone_offset:$date_format}

        {elseif $col_name == "ip_address"}

          {$submission.ip_address}

        {/if}

      {elseif $field_type == "textarea"}

        {$value|nl2br}

      {else}

        {$value}

      {/if}

    </td>

    {/foreach}

  </tr>

{/foreach}

</table>

jlkane · Oct 4th, 2009, 4:09 PM

Ben,

Thank you SO much! I've very grateful to you for taking the time to put the fix together and deploy it for me this way. Perfect!

Jim

jlkane · Nov 5th, 2009, 6:18 PM

I'm back with a related bug discovery with the export to Excel. (Sorry!)

In the Excel file created, those carriage returns create new cells, while adjacent cells are merged to (sort of) make the resultant Franken-row hold together. I'll attach a sample Excel file, derived from a single form submission. One form submission should create a single row of data in the Excel file, but you can see that the line breaks in the form's text area have been translated into (in this case) five rows of data.

Frankly, the use of merged cells is a horrible construct in Excel which invariably causes trouble with sorting and other everyday operations. There's no reason to use it here, when simple line breaks within a cell would do the job nicely.

Is there any chance of this being a quick fix in the Export Manager module?

Thanks in advance,

Jim

***Ben*** · Nov 7th, 2009, 12:41 PM

Hey Jim,

This is an interesting one. I can't reproduce this on my own system. The newlines all appear within the same cell, as they should.

A few questions:
- what's your server type (Unix, Windows)?
- what OS are you running (Mac, Windows)?
- what version of Excel are you running?

Ben

jlkane · Nov 7th, 2009, 5:35 PM

(Nov 7th, 2009, 12:41 PM)Ben Wrote: Hey Jim,

This is an interesting one. I can't reproduce this on my own system. The newlines all appear within the same cell, as they should.

A few questions:
- what's your server type (Unix, Windows)?
- what OS are you running (Mac, Windows)?
- what version of Excel are you running?

Ben

My server is Unix, I'm on Mac OS X 10.4, and I've looked at the spreadsheet you're creating in both Office 2004 (Excel 11.5.5) and Office 2008 (Excel 12.2.0).

I'll email you the login to the FT installation on my server, so you can export a spreadsheet and quickly verify on which side of the divide the problem lies.

Thanks for your help,

Jim

***Ben*** · Nov 14th, 2009, 12:11 PM

Yeah sure! Send me an email, Jim: ben.keen@gmail.com

However, I'm awfully busy for the next few days & probably won't be able to look at it until mid-week. Just a heads up.

- Ben

jlkane · (This post was last modified: Nov 14th, 2009, 1:55 PM by jlkane.)

(Nov 14th, 2009, 12:11 PM)Ben Wrote: Yeah sure! Send me an email, Jim...

- Ben

I sent that email (through the Forum's email system) back on Nov. 7 when I made my previous post, Ben. If you need me to dig it up and send it to your gmail address, please let me know.

Thanks,

Jim

jlkane · Feb 7th, 2010, 9:33 PM

(Nov 5th, 2009, 6:18 PM)jlkane Wrote: In the Excel file created, those carriage returns create new cells, while adjacent cells are merged to (sort of) make the resultant Franken-row hold together. I'll attach a sample Excel file, derived from a single form submission. One form submission should create a single row of data in the Excel file, but you can see that the line breaks in the form's text area have been translated into (in this case) five rows of data.

Ben,

Yes, I know that you told me that you couldn't replicate this problem, but it continues to be a problem for my users.

I installed FT2.0 in hopes that a clean install might make my installation work as well as yours, but it did not. I'll send the login info for my installation to your gmail address, in hopes that you can run a quick test to see whether the problem is on my server, or in the way Excel on a Mac interprets the .xls files you're creating.

Thanks in advance for your help,

Jim

FORUMS