Trim White Extra Space From Both Ends.

[Elena]

Hi there, 

 

I would love to get rid of extra white space from both sides (leading/trailing).

 

I noticed that two of the text fields on my table, invariably gets an automatic extra white spaces, each time they are updated.

 

Users typically exceed the shorter 255 limit, but they don't generally reach the max 6,400 either. But, since Caspio has nothing in between 255 and 6400, I have no choice but to choose 6,400.

 

I have the following code, which, when verified comes out as "valid". However, when I go and check to see my details page, it returns "error in the formula".  

 

Please help and show me the right way. 

 

SELECT * FROM Employee_Record WHERE LTRIM(RTRIM([@field:Last_Name]>[@LName])) = LTRIM(RTRIM("[@field:Education_New]"))

 

All help is greatly appreciated as usual. Thank you in advance.

 

Elena

 

 

[Jan]

Hi Elena,

 

Could you please elaborate on your formula?

What do you want to display and what condition do you want to use?

[Elena]

Good afternoon Jan,

 

Hello it's been a while.

 

To elaborate on my syntax:

 

1. I have a  table "Employee_Record"

 

2. In this table,  have a filter set up and I'm using the field "Last_Name" for this purpose. The parameter it is giving upon exit is [@LName].

 

3. In this table I have a field named "Education_New".

 

 

"Education_New" is a text field that has 6,400 maximum lenght. As mentioned earlier, when users enter their updated academic achievements (e.g. they obtained Master's degree after employment), they tend to elaborate on what the academic achievement is all about. For example, they tend to also write the discipline.

 

Invariably, each time this particular field is updated some big giant white space becomes visible right after users press "submit". Some of them admitted to pressing the enter button several times when they are thinking. Some of them said, they didn't deliberately enter white spaces.

 

So, I am thinking that perhaps I should utlize CASPIO's trim SQL function. Below is what I came up with.

When I click the validation button in the calculated field, it says everything is honky dori. But then when I check the embedded scripts on the web, it is giving me Error in Formula.

 

Could it be that I have that extra "LTRIM(RTRIM(" that is actually trying to trim the filter field when it shouldn't?

 

 

Here is the syntax:

 

SELECT * FROM Employee_Record WHERE LTRIM(RTRIM([@field:Last_Name]>[@LName])) = LTRIM(RTRIM("[@field:Education_New]"))

 

 

 

SELECT * FROM Employee_Record  =  is my table

 

WHERE LTRIM(RTRIM([@field:Last_Name]>[@LName]))  = The field Last_Name if my filter field. The [@LName] is my parameter.

 

LTRIM(RTRIM("[@field:Education_New]"))  = Education_New is the very field that I want trimmed.

 

 

 

Thank you Jan. Please help me.

 

 

Elena

[Elena]

By the way, in all of my spiel above, I meant to say 64,000 character lenght not 6,400. 

Just thought I'd clarify.

[Jan]

Hi Elena,

 

Thank you for providing details!

 

As far as I know, there are standard functions Ltrim and Rtrim in Calculated fields.

Could you try the following formula?

 

Ltrim(Rtrim([@field:Education_New]))

 

Does it work for you?

[Elena]

Hi Jan,

 

The syntax you recommended did not work either, Caspio's "trim" function in my observation only allows for strings to be trimmed, and not the whole text field.

 

This is all good if one knows the most common letters or words typically abused with spaces either before or after them.

 

Unless I am wrong, it appears that Caspio is not allowing for the whole text field to be trimmed at all.

 

There has got to be another way.

 

Best,

Elena

[Elena]

or perhaps I am using the wrong function all along.

[Jan]

Hi Elena,

 

I am sorry for delaying with the answer.

If I understand correctly, several empty lines are saved in the records. Probably, function can clear spaces but not empty lines that has special symbols for "Enters".

Maybe, you can use a JavaScript code that deletes the empty lines when a user clicks the button?

I can try writing the code, if you provide a bit more information:

- Does the data is displayed as Text or as HTML?

- Is this text entered on a Submission form or on a Single Update/Details page, or on a Result set?

[Elena]

Hi Jan,

 

Many thanks for the assitance.

 

- The data is displayed as HTML

- Data is submitted as a Single Update Form

 

A JavaScript code would probably help tremendously.

 

Elena

[Jan]

Hi Elena,

 

I apologize for the long delay.

I have tried several solution, and I have one more question - what Form Element is used for this field?

 

If I understand correctly, it is a TextArea, but I am not sure if the "Enable rich text editing toolbar" checkbox on the Advanced tab is checked. And solutions are quite different, if the checkbox is checked and if it is not checked.

[Elena]

Hi Jan,

 

Good to hear from you, the form element used is Text Area. The "Enable rich text editing toolbar" is not checked.

 

Thank you again.

 

Elena

[Jan]

Hi Elena,

 

You can try using the following code:

<script>
function trim_func()
{
  var fieldname = "FEILDNAME";
  fieldname = "EditRecord" + fieldname;
  var entered_text = document.getElementById(fieldname).value;
  var n = 1;
  for (i=0;1<100;i++)
  {
    n = entered_text.indexOf("\n\n");
    if(n>-1)
      entered_text = entered_text.replace("\n\n","\n");
    else break;
  }
document.getElementById(fieldname).value = entered_text;
}
document.getElementById("caspioform").onsubmit = trim_func;
</script>

You can add a Header&Footer element, select the Footer element, click the Source button and enter the code.

Please enter the name of your field instead of FEILDNAME.

 

I hope, it helps.

[Elena]

oh my word! it works!  Thank you so much. I sent you the link to my app and the login credentials to test, plus links to click to see the results. I tested it and even exported an updated database to see if the record has been updated with spaces in them. No spaces. Yeayy!

 

I am so impressed because this has always been an Achilles' heel to me.

 

Thank you so much. Other folks will benefit from this code as well.

 

Hey everyone, if you ever had a need to trim those nasty extra spaces in your datapages, this is the code y'all.