ExecuteSQL Question

[aguest]

HI, I've got the following piece of code which pulls some information from a table to allow me to manipulate. The last two fields Summary and Actions can be large pieces of text.

What I've found is, if a staff member puts carriage returns in the middle of the text to format it into paragraphs, I lose the rest of the data within that field and subsequent fields that follow.

Is there anyway to avoid this from happening as Senior Leadership team will want to be able to see the data formatted nicely into paragraphs.

TIA

ExecuteSQL(
"SELECT LLSDate, DeptID, YearGroup, LLType, Rationale, Summary, Actions
 FROM LearningLenseSummary
 WHERE LLSDate >=? AND LLSDate <=?
 AND LLType =?
 AND DeptID =?
 AND YearGroup = ?";
"|"; "¶";

$$LLEndDate ;
$$LLInfoDate ;
$$LLType ;
$$LLDept ;
$$LLYrGrp

)

[Kris M]

put two calculations fields in the table that do Substitute ( Summary ; pilchrow ; "" ) and  Substitute ( Actions ; pilchrow ; "" ) and base your sql on those calculated fields.

Probably other ways to slice this but its the first thing that comes to mind

[LaRetta]

I would sure hate to see creating calculations of these fields just to remove characters from them for this single implementation.  Why not use Let() and change those fields first and reference those Let() variables instead?  Then no additional calculation fields would be required.

As an aside, using ExecuteSQL() within calculations can be hoggish.  I would explore other approaches.

[Kris M]

The op did not say they wanted to eliminate carriage return entry by a user they just wanted to get the format correct for an SQL calculation

[aguest]

Thanks both for your responses. perhaps I should say, the above code is being used to pull the information from a table to be used in a report. I've attached a copy of what the report looks like.

The script pulls the data for the chart into a temp table, and then the data for the text part of the report in a separate temp table (hence the sql above).

When the teachers type the information in, the place carriage returns in the text  (on iPad) to format it. I want to keep this formatting to show in the report attached.

Alternatively, is there an easier way to produce said report?

 

TIA

[Wim Decorte]

I don't follow when you say that you "lose the data after the return"... what do you do with the results of the SQL query?

Note that you are using the default record delimiter, which is a return.  Change that to something else if you want to be able to keep the text with returns intact and later be able to identify whole records in the SQL query result.

Edited September 10, 2015 by Wim Decorte

[aguest]

I don't follow when you say that you "lose the data after the return"... what do you do with the results of the SQL query?

Not that you are using the default record delimiter, which is a return.  Change that to something else if you want to be able to keep the text with returns intact and later be able to identify whole records in the SQL query result.

To try explain a bit better I've attached the results of the sql query in the SQL LIst below. As you can see it's returning all the data but with a carriage return half way through the 6th field, bringing the rest of the result onto the next line before then adding the 7th (final field into the list, which also has a carriage return in the middle of it.

I've also attached the script. The next line after populating the variable with a result is to count the row. This is returning 3, although in fact it's only 1 row (it just has 2 carriage returns in the middle of it.

When I then populate the current row variable it is taking what it sees as the first row, which in fact is only part of the row. The rest is seen as row 2 and 3.

Is there a way of getting it to see all of the attached as one single row of data instead of 3?

Hope that makes more sense.

TIA

[Wim Decorte]

Use a different record delimiter in your ExecuteSQL() call.  Then parse it out later in your script.