concatenation of more than one database field into one laserfiche field

replied on June 16, 2017 • Show version history

I've never been able to find a way to do that with just formulas. Forms trims a lot of stuff by default, and even when I try putting in the character code for a white space (using the approach you tried as well as CONCATENATE) it always trims (leading and trailing) white spaces for each item being concatenated.

The only way I've been able to accomplish this is with JavaScript. I even tried concatenating the values with a placeholder then using SUBSTITUTE on the combined text and it still removed the white space. Not sure if this is a bug, or if it meant to be that way.

First, change the " " in your concatenation to a "_" (or any character you choose) so you have a placeholder to identify and manipulate.

=CONCATENATE(FirstName, IF(TRIM(LastName)="","","_"), LastName)

NOTE: the "IF" condition is in there so it will only add the placeholder (and therefore the space) if there is actually something to put after the FirstName.

Then, in the JavaScript for the form use the following:

$(document).ready(function(){
      // Assign a change event to detect changes to the concatenated field
      $('#YourField input').change(function(){
            // Replace your placeholder with a space
            $(this).val($(this).val().replace('_',' '));
      });
});

And every time the field changes, it will look for the "_" (or whatever placeholder you choose) and replace it with a white space.

3 0

View 4 previous replies

replied on June 16, 2017

You might give this a try:
=FirstName & char(127) & LastName

127 is actually ascii decimal for the delete key but it works for me

using 32, which is the space key does not work

Probably best to go with the JS solution since the char(127) might break in a future release once the issue is fixed.

1 0

replied on October 5, 2017

Wicked CHAR(127) does keep spaces in concatenate ... thank you so much!

0 0

replied on December 5, 2017 • Show version history

Jason, this is a great workaround. Thank you. Question: any suggestions on keeping the "_" out of the filed until data is present? In other words, I am getting the "_" as a default character:

I tried setting the default character to " ", but that didn't work.

Thanks!!

Yes, I am a Forms Noob so forgive my ignoramousness!

EDIT:

The solution was to place an IF statement in the formula:

=IF(LEN(apvFName) > 0, apvFName & "_" & apvrLName, "")

Hope this might help another Noob down the road.

0 0

replied on December 6, 2017

Sorry for the late reply, but it looks like you figured it out! That would have been my suggestion

1 0

replied on February 13, 2018

Jason, when I use that code it only replaces the first _ and ignores the rest. Is there some trick I am missing?

0 0

replied on February 13, 2018 • Show version history

Alon,

If you're referring to the JavaScript, then .replace will only replace the first occurrence of the character unless you use a regular expression.

For me, the easiest way to replace all is to do a split-join, which accomplishes the same thing without having to figure out the regex.

For example,

$(this).val($(this).val().split('_').join(' ');

But I suppose the "correct" way is with a regular expression like so,

$(this).val($(this).val().replace(/_/g,' '));

On a side note, I believe this issue has been resolved in 10.3 so such workarounds wouldn't be necessary any longer.

1 0

replied on February 13, 2018

Thanks Jason! That helped me get to what I actually needed, which as to replace a string with a new line in a multi-line field... check it out!


//Replace #No Notes Taken# with new line
$(document).ready(function(){
      // Assign a change event to detect changes to the concatenated field
      $('#q279 textarea').change(function(){
            // Replace your placeholder with a space
            //$(this).val($(this).val().replace('##',"/n"));
        	$(this).val($(this).val().replace(/#No Notes Taken#/g,'\n'));

      });
});

0 0

Question

Question

concatenation of more than one database field into one laserfiche field

Replies

Sign in to reply to this post.