Jump to content

Trim White Extra Space From Both Ends.

Recommended Posts

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.





Link to comment
Share on other sites

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.




Link to comment
Share on other sites

  • 2 weeks later...

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.




Link to comment
Share on other sites

  • 2 weeks later...

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Hi Elena,


You can try using the following code:

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");
      entered_text = entered_text.replace("\n\n","\n");
    else break;
document.getElementById(fieldname).value = entered_text;
document.getElementById("caspioform").onsubmit = trim_func;

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...