I am importing a spreadsheet where one column has numbers. Most are five or six digit numbers but there are some four digit numbers. The numbers are being imported into a number field. Is there a way to add a leading zero to the four digit numbers only using an agent after the import?
Subject: Adding a Leading Zero?
Even cleaner:
@If(@Length(Field) = 4; @SetField(“Field”; “0”+ Field);@Success);
Subject: Adding a Leading Zero?
Not if it’s a number. You can force text formatting using the Format LotusScript statement, but the only control you have over stuff to the left-hand side of the decimal point in a number’s display is the thousands separator and a currency symbol – you can’t force length.
Subject: RE: Adding a Leading Zero?
If some values have leading zeroes now (to follow up to Stan’s answer) they are not numbers, but text strings that contain digits. The field being defined as numeric on a form does not force the data to be numeric if it is assigned in some way other than by editing with the form (and sometimes, not even then, depending on input translations). You should regard the field datatype as a suggestion rather than an unbreakable rule.
Subject: Adding a Leading Zero?
More to the point, why would you do that?Is it for layout purposes? then align the field to the right.
Is it for display in views? Then either align right or change the view column to convert the numbers to text and then add leading zero.
Subject: RE: Adding a Leading Zero?
Figured it out myself:
Create a new text field, create agent with following code:
@If(@Length(OldField) < 5; @SetField(“NewField”; “0”+ OldField);
This adds a 0 in front of any numbers already in the old field. This can be used to insert anything in front or behind the original text/number in a field.
Subject: Adding a Leading Zero?
in LS, can do something like this:
Function padZero(strValue As String, iFixedStrLen As Integer, sPadChar As String)
' ================================
' strValue = input string
' iFixedStrLen = the number of chars expected. for example, SSN = 9
' what to pre-pad char with.
' example usage ==>
' ssn = "123"
' padSSN = padZero(ssn, 9, "0")
' padSSN <-- "000000123"
' ================================
Dim iStrLen As Integer
Dim i As Integer
Dim sPadStr As String
iStrLen = Len(strValue)
If (iStrLen = 0 Or iStrLen = iFixedStrLen) Then
padZero = strValue
Exit Function
End If
If (iFixedStrLen < iStrLen) Then
padZero = strValue
Exit Function
End If
sPadStr = ""
For i = 1 To (iFixedStrLen - iStrLen)
sPadStr = sPadStr & sPadChar
Next
padZero = sPadStr & strValue
End Function
in @Formula,
strValue := “123”;
sPadChar := “0”;
iFixedStrLen := 9;
lenStr := @Length(strValue);
@If(lenStr <= iFixedStrLen; @Repeat(sPadChar ; iFixedStrLen - lenStr) + strValue; strValue);
Subject: RE: Adding a Leading Zero?
< rather inappropriate comment deleted >
Subject: RE: Adding a Leading Zero?
I read the first two which basically informed me I couldn’t do it. I didn’t see Timothy’s LS response until today.
Thanks,
Subject: RE: Adding a Leading Zero?
What you should’ve gotten from the first two responses is that you shouldn’t do it. If the value is a number, store it as a number, not as text. You can’t store leading zeroes as part of a number, yes, but why should you want to?
Subject: RE: Adding a Leading Zero?
Because I can send e-mail based on employee numbers and using the employee number is far better then trying to use the employees name. The names often do not match the e-mail address standard and return as undelivered. However, I have numerous employees who still have employee numbers which begin with zero so I need to add one to employee numbers which are displayed as four digits within the excel spreadsheet. I would perfer to do this as an agent within the database rather then making changes to the spreadsheet prior to importing.
Subject: RE: Adding a Leading Zero?
If the value is really a string that just happens to contain digits, then why did you make the field a number field? It’s text.
Subject: RE: Adding a Leading Zero?
In that you are correct I did change it to a text field vice number field. Thanks,
Subject: RE: Adding a Leading Zero?
btw… I was not condoning the mis-use of number fields, sometimes you get data from other sources, such as someone else’s excel import, etc. So having generic padding functions around in your toolkit can be a good thing.
Personally I never use number fields, unless I am doing an actual calculation with them.