Question

Unable to concatenate leading zeroes in new Forms Designer. Ideas?

Forms Layout Designer Forms

Updated June 27, 2023

asked on June 26, 2023 • Show version history

Bug or feature? No formula combination that I have found in the new Forms Designer will allow me to concatenate more than one leading zero to a string.

I have an lookup to an external table that requires an 8-digit number. If the number entered is less than 8 digits, I have to zero-pad it. I have tried various combinations of RIGHT and REPT with CONCATENATE, I have tried using "TEXT()", I have tried formulas that do all the combining in one field and formulas that combine two different fields. So far none of my attempts have succeeded.

I am able to get the correct number of leading zeroes in a stand-alone field. For example, either "REPT(0, 8-LEN(Number))" or "RIGHT("00000000", 8-LEN(Number))" will both return the string of zeroes that I need. However, as soon as I try to concatenate that string with the number (even using "TEXT()"), it will only add a single zero.

With the old Designer, I could overcome this issue with javascript, but cannot do so in the new one. Is this a known bug that has perhaps been addressed in a newer version? Any other ideas for my dilemma?

Using Laserfiche Forms Professional Version 11.0.2201.20436

0 0

Answer

SELECTED ANSWER

replied on June 26, 2023 • Show version history

I think I actually found a better workaround lol.

=RIGHT(CONCATENATE("_00000000",Number),8)

I realized that since it already has the RIGHT function, I could just add an underscore (or any non-zero character for that matter) and it would fix the issue.

The bug is basically that it is treating "000.." as numeric so it is always "0"

However, as soon as you put something other than 0 at the beginning it is either a string/text value or at least a valid number.

Since we take the right 8 characters and there are still 8 zeros, the first character will always be ignored, but it bypasses the conversion bug.

3 0

Replies

replied on June 26, 2023 • Show version history

Seems to be specific to the new designer. The modern designers appears to be to converting "00000000" to a numeric value when you put it in any kind of formula function, including TEXT().

The following works in the classic designer, but not in the modern designer.

=RIGHT(CONCATENATE("00000000",Single_Line),8)

I feel like this used to be a more frequent issue in the classic designer too, so maybe it's just a matter of applying that same fix to the new designer.

1 0

replied on June 26, 2023

In case anyone else has the same issue and needs SOME solution, the only workaround I've found so far is to put in a really long, ugly "IF" that concatenates each zero individually. Here is a version that goes up to four digits as an example (where 'Number' is the field that needs to be zero-padded):

=IF(LEN(Number) = 8, Number, IF(LEN(Number) = 7, CONCATENATE("0", Number), IF(LEN(Number) = 6, CONCATENATE("0", "0", Number), IF(LEN(Number) = 5, CONCATENATE("0", "0", "0", Number), IF(LEN(Number) = 4, CONCATENATE("0", "0", "0", "0", Number), "")))))

0 0

View 1 previous reply

replied on June 26, 2023 • Show version history

Try this, it seemed to work in the modern designer.

=RIGHT(CONCATENATE("0","0","0","0","0","0","0","0",Number),8)

Once I saw you got something working, I kind of expanded on the multiple "0" approach by utilizing the RIGHT function instead of the IFs.

1 0

replied on June 26, 2023

Ah, yes, that's much cleaner! Still a little rougher than I feel like it ought to be, but for a workaround it's a much nicer solution. Well done!

0 0

SELECTED ANSWER

replied on June 26, 2023 • Show version history

I think I actually found a better workaround lol.

=RIGHT(CONCATENATE("_00000000",Number),8)

I realized that since it already has the RIGHT function, I could just add an underscore (or any non-zero character for that matter) and it would fix the issue.

The bug is basically that it is treating "000.." as numeric so it is always "0"

However, as soon as you put something other than 0 at the beginning it is either a string/text value or at least a valid number.

Since we take the right 8 characters and there are still 8 zeros, the first character will always be ignored, but it bypasses the conversion bug.

3 0

replied on June 27, 2023

You're on fire, Jason!

0 0

You are not allowed to follow up in this post.

Question

Question

Unable to concatenate leading zeroes in new Forms Designer. Ideas?

Answer

Replies

Sign in to reply to this post.