You are viewing limited content. For full access, please sign in.

Question

Question

Concatenating multiple field into one field. Leading zero drop?

Forms
Updated November 30, 2022
asked on December 5, 2018 • Show version history

Hello Everyone

 

I am concatenating 3 field into one field in Laserfiche Forms.

 

For example: 

Field1 = 1  Field2=03  Field3=9

 

I am expecting (concatenating) field4= 1039

But it drops 0 from 03  and field4 show 139

 

I tried adding "0", before Field2 (on concatenation formula)  Than it works for leading zero but not for 2 digit numbers. 

 

For Example:  if Field1=1 , Field2=11 , Field3=9

I am expecting Field4 = 1119

 

But it gives me 10119

How can I resove this issue.

Actual conct. sytex is:  "=CONCATENATE(AddNewRecord.NextProcess,"0",AddNewRecord.ReasonTCode,AddNewRecord.Doc_Type)"

 

Any direction on this would be appreciated.

 

0 0

Replies

replied on December 6, 2018

Which version of Forms are you currently using?

There was a bug in previous versions that caused it to drop white spaces when using formulas, and this may be related to that issue.

I just tested a similar formula in 10.3.1 and it works as expected.


 

=CONCATENATE(Table.Column_1,Table.Column_2,Table.Column_3)

 

However, it looks like you're using a table or collection since you have the Variable.Field format, and that means you should be using the Index function.

If the fourth field is part of the same table, you can use ROW() and it will automatically use the row value of the field itself, or you can just put in a 1 if you only need one row.

INDEX(Table.Column,ROW()) 
or 
INDEX(Table.Column,1)

Another thing to try is an IF condition that evaluates length, so IF the length of Field 2 = 1, you add the leading 0 back. The problem you're having now is that it is always adding the extra 0.

Try something like the following:

=CONCATENATE(INDEX(Table.Column_1,1),IF(LEN(INDEX(Table.Column_2,1))=1,"0",""),INDEX(Table.Column_2,1),INDEX(Table.Column_3,1))
2 0
replied on November 21, 2022

Hello :)

I am having the same issue, however I have 9 financial codes that need to be concatenated and the leading 0's are dropped.  We are using 10.4. 

I thought I may run out of real estate using a table but that would be much cleaner and will try that.

Still wondering why with separate drop downs aren't working.  The values of all the fields do hold the metadata correctly.  

Thanks :)

0 0
replied on November 21, 2022

Hi Sue,

The table portion of this post is only there because the syntax for referencing variables in a table requires the index function, so you shouldn't have any reason to convert your setup to use tables.

Forms had an off-and-on "bug" with the Concatenation function dropping leading zeros for quite a while and that is the bigger problem.

One possible workaround is to wrap your variable in the TEXT function since it seems like the issue is that it was treating these values as numeric and dropping the zero since numeric values other than 0 cannot start with 0.

You can try using something like this to force it to treat the values as text.

CONCATENATE(TEXT(Variable1),TEXT(Variable2))

1 0
replied on November 30, 2022

Thanks Jason!  Got it :)  Thanks again!

0 0
replied on December 6, 2018

Hello Jason,

 

Thank you so much for the help.   Your syntax fix my issue,  I am grateful and thank you.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...