Pad 0 to the begining of decimal

Vineeth.R · 2010-06-03

Hi,

I want to show my decimal value in 17 character length. if my amount is 234.56 then i want the result as 00000000000234.56

how can it be possible without using srtring functions. Can it just be achieved using FORMAT

Thanks
Vineeth R

vijay_g · 2010-06-03

Use text data type field instead of decimal, cause of the scenario looking not possible with decimal data type.
Either if you want to show it on report or in excel then you can..

Vineeth.R · 2010-06-03

Hi Vijay,

What would be the FORMAT statement in text data type?

Thanks

rdebath · 2010-06-03

Easy

D := 12.45123;
F := '<Precision,2:2><Sign,1><Filler,S><Integer,12><Filler,Y><Decimals><Filler,X>';
MESSAGE('|%1|%2|', FORMAT(D, 0, F), FORMAT(ROUND(D,0.1), 0, F));

Of course You'll want to replace the S X and Y in the format string Filler parts with zeros.

Vineeth.R · 2010-06-03

Thanks rdebath

FDickschat · 2010-06-04

Hi Robert,

pretty cool formatting. Never managed such a format string myself.

Is there somewhere a better description then in the help file what is possible with the format function and what is not?

It seems to be of the essence where exactly the format strings are placed (which the help completely ignores).

DenSter · 2010-06-04

Help has tons of good information on this.

Click the Help menu, select C/SIDE Reference Guide, go to the search tab and enter 'format'

rdebath · 2010-06-09

No Daniel,
The help isn't particularly good for this it only kind of hints that this sort of format string is possible. From the help it just looks like a somewhat lame and very verbose way of picking from a few standard formats. In reality it's a powerful but very ugly formatter. I think it can do anything that other number or date formatters can do but it's laborious getting it to cooperate.

That's why I saved those three lines of code.

DenSter · 2010-06-09

No Robert, the C/SIDE Reference Guide has all of those formats explained, if you would just be a tiny bit more persistent. I agree that it could be structured better, but it's all in there.

DenSter · 2010-06-09

From the C/SIDE Reference Guide. Tell me again that this does not contain tons of good information about building format strings.

Format Property
See Also
Collapse All Expand All

Sets the formats of the source expression for various data types.

Applies To
Dataport fields

Text boxes

Remarks
You can set the Format property to a predefined format, or you can build your own format. For more information, see FORMAT Function (STRING).

Basic Settings
To choose a predefined format, use the syntax: <Standard Format,X>, where X is one of the entries in the Value column of the following table.

Value Description
0
Standard Display Format (the default for all data types)

1
Standard Display Format 2 (edit)

2
C/AL Code Constant Format

Note
You must enter the < and > angle brackets, such as <Standard Format,2>.

Building Formats
You can use the Format property to create your own formats. To create a format, use the following rules:

A format property consists of zero or more Chars, Fields, or Attributes.

Fields and Attributes are expressed with an identifier inside brackets (<>).

Attributes must contain a comma (,) and a parameter after the identifier.

Fields can optionally take a comma (,) and a FieldLen.

The following table shows the syntax.

FormatProperty :=
[<Char> | <Field> | <Attribute>]

<Char> :=
character with ASCII value [32..255]

<Field> :=
'<' <FieldName> '>'

[, <Attribute>]

<FieldName> :=
literal name of field (format component)

<FieldLen> :=
length of field (0 or no entry means that the length is dynamic)

<Attribute> :=

<AttributeName> :=
[Standard Format | Filler | 1000Character | Comma | Overflow | Filler Character | Precision]

Note
In the following table, a full stop is used as the decimal symbol. Filler and Filler Character indicate the character that is used to fill empty spaces.

The FieldName is a component that you can use to build a format expression. Depending on the data type of the text box or dataport field, you can choose the appropriate FieldName from this list.

Data type FieldName values
Decimal
Sign, Integer, Decimals, Integer Thousand

Date
Day, Month, Month Text, Quarter, Year, Year4, Week, Week Year, Week Year4, Weekday, Weekday Text, Closing

Time
Hours24, Hours12, Minutes, Seconds, Thousands, AM/PM, Second dec

DateTime
Day, Month, Month Text, Quarter, Year, Year4, Week, Week Year, Week Year4, Weekday, Weekday Text, Hours24, Hours12, Minutes, Seconds, Thousands, AM/PM, Second dec

Integer, BigInteger
Sign, Integer, Integer Thousand

Boolean
Text, Number

Option
Text, Number

Code
Text

Char
Char/Number, Char, Number

Text
Text

Example
The following examples demonstrate how to use the Format property:

Choosing a standard format.

Use the Standard Format attribute to select one of the standard formats (these are listed at the end of this Help topic).

For example, <Standard Format,5> selects Standard Format 5.

Using a standard format with an attribute.

<Precision,2:3><Standard Format,0> will use Standard Format 0 and will format the data with a minimum of 2 and a maximum of 3 decimal places. See also the DecimalPlaces Property. If you do not specify a precision, then the form uses the precision that is specified in the DecimalPlaces Property of the corresponding field in the table.

Building a format.

You can create your own formats using Chars (which are displayed literally), Fields (to choose specific components of a value, for example the year-part of a date) and Attributes (for example to select which character to use as a filler).

For example, a text box that is based on a source expression of the Date data type can use the following format string:

<Weekday Text>, <Month Text> <Day>

This expression displays the date as Monday, April 15.

Note
The settings that are specified under the Regional and Language Options in Windows determine how some separators are displayed.

Standard Formats
The following tables list the predefined formats for each data type.

Decimal Format Example
<Sign><Integer Thousand><Decimals>
0
-76,543.21

<Sign><Integer><Decimals>
1
-76543.21

<Sign><Integer><Decimals>
2
-76543.21

<Integer Thousand><Decimals><Sign,1>
3
76,543.21-

<Integer><Decimals><Sign,1>
4
76543.21-

XML format
9
-76543.21

For Dates, the Regional and Language Options in Windows is used to select the actual format that is used. The following table shows the fields that are used to build the various formats. This table corresponds to the Regional setting for a European country/region.

Date Format Example
<Closing><Day,2>-<Month,2>-<Year>
0
05-04-03

<Closing><Day,2>-<Month,2>-<Year>
1
05-04-03

<Day,2><Month,2><Year><Closing>D
2
050403D

<Closing><Year>-<Month,2>-<Day,2>
3
03-04-05

<Closing><Day>. <Month Text> <Year4>
4
5. April 2003

<Closing><Day,2><Month,2><Year>
5
050403

<Closing><Year><Month,2><Day,2>
6
030405

<Day,2><Filler Character, >. <Month Text,3> <Year4>
7
5. Apr 2003

XML format
9
2003-04-05

The following table shows how these formats will change if the Regional and Language Options is set to "United States."

US date Format Example
<Closing><Month,2>/<Day,2>/<Year>
0
04/05/03

<Closing><Month,2>/<Day,2>/<Year>
1
04/05/03

<Month,2><Day,2><Year><Closing>D
2
040503D

<Closing><Year>/<Month,2>/<Day,2>
3
03/04/05

<Month Text> <Closing><Day>, <Year4>
4
April 5, 2003

<Closing><Month,2><Day,2><Year>
5
040503

<Closing><Year><Month,2><Day,2>
6
030405

<Day,2><Filler Character, >. <Month Text,3> <Year4>
7
5. Apr 2003

XML format
9
2003-04-05

For Times, the Regional and Language Options in Windows are used to select the actual format used. The following table shows the fields that will be used to build the various formats. This table corresponds to the Regional setting for a European country/region.

Time Format Example
<Hours24>.<Minutes,2>.<Seconds,2>
0
4.35.55

<Hours24>.<Minutes,2>.<Seconds,2><Second dec.>
1
4.35.55.553

<Hours24,2><Filler Character,0><Minutes,2><Seconds,2> <Second dec.><Comma,.>T
2
043555.553T

XML format
9
04.35.55.553Z

The following table shows how these time formats will appear if the Regional setting is changed to "United States."

US time Format Example
<Hours12>:<Minutes,2>:<Seconds,2><Second dec.> <AM/PM>
0
4:35:55 AM

<Hours12>:<Minutes,2>:<Seconds,2><Second dec.> <AM/PM>
1
4:35:55.553 AM

<Hours24,2><Filler Character,0><Minutes,2><Seconds,2> <Second dec.><Comma,.>T
2
043555 .553T

XML format
9
04:35:55.553Z

The following table shows the fields that have been used to define the standard formats for DateTime. Note that the date, time, and decimal separators that are displayed are determined by the Regional and Language Options that have been selected on your computer.

DateTime Format Example
<Day,2>-<Month,2>-<Year> <Hours24>.<Minutes,2>
0
05-04-03 4.35

<Day,2>-<Month,2>-<Year> <Hours24>.<Minutes,2>.<Seconds,2><Second dec>
1
05-04-03 4.35.55.553

<Day,2>-<Month,2>-<Year> <Hours24>.<Minutes,2>
2
05-04-03 4.35

<Day,2>-<Month,2>-<Year> <Hours24>.<Minutes,2>.<Seconds,2>
3
05-04-03 4.35.55

XML format
9
2003-04-05T03:35:55.553Z

US DateTime Format Example
<Month,2>/<Day,2>/<Year> <Hours24,2>:<Minutes,2> <AM/PM>
0
04/05/03 04:35 AM

<Month,2>/<Day,2>/<Year> <Hours24,2>:<Minutes,2>:<Seconds,2><Second dec> <AM/PM>
1
04/05/03 04:35:55.553 AM

<Month,2>/<Day,2>/<Year> <Hours24,2>:<Minutes,2> <AM/PM>
2
04/05/03 04:35 AM

<Month,2>/<Day,2>/<Year> <Hours24,2>:<Minutes,2>:<Seconds,2> <AM/PM>
3
04/05/03 04:35:55 AM

XML format
9
2003-04-05T03:35:55.553Z

Integer and BigInteger Format Example
<Sign><Integer>
0
-567

<Sign><Integer>
1
-567

<Sign><Integer>
2
-567

XML format
9
-567

Boolean Format Example
<Text>
0
Yes/No

<Text>
1
Yes/No

<Number>
2
1/0

XML format
9
1/0

Option Format Example
<Text>
0
Blue

<Text>
1
Blue

<Number>
2
1

XML format
9
1

Standard format 9 is used to display the data in the standard XML formats.

For Chars, all formats are: <Char/Number>.

For Text and Code, all formats are: <Text>

rdebath · 2010-06-09

Yup, I see them and when I tried them they didn't work, at first.

The format strings are complex, not very well documented and fail silently. From my experience FDickschat's reaction is typical, most people seem to believe Navision format strings are like I described even after reading the documentation.

For example, consider these functions from the Report 88 .

VAT Entry - OnPostDataItem()
VATFile.WRITE(
  FORMAT(
    '10' + DecimalNumeralZeroFormat(NoOfGrTotal,9) +
    DecimalNumeralZeroFormat("VAT Entry".Base + EU3PartyTradeTotalAmt,15) +
    DecimalNumeralSign(-("VAT Entry".Base + EU3PartyTradeTotalAmt)),
    80));
VATFile.CLOSE;

DecimalNumeralSign(DecimalNumeral : Decimal) : Text[1]
IF DecimalNumeral >= 0 THEN
  EXIT('+');
EXIT('-');

DecimalNumeralZeroFormat(DecimalNumeral : Decimal;Length : Integer) : Text[250]
EXIT(TextZeroFormat(DELCHR(FORMAT(ROUND(ABS(DecimalNumeral),1,'<'),0,1)),Length));

TextZeroFormat(Text : Text[250];Length : Integer) : Text[250]
IF STRLEN(Text) > Length THEN
  ERROR(
    Text004,
    Text,Length);
EXIT(PADSTR('',Length - STRLEN(Text),'0') + Text);

einsTeIn.NET · 2010-06-09

I think a help file can only contain general information, not specific problems. You have to think your own. So, NAV help is a complete documentation (in this case), but I think you need some kind of experience to understand how to use that information.

DenSter · 2010-06-09

rdebath wrote:

Yup, I see them and when I tried them they didn't work, at first.

Maybe that's the key, your expectation that help should give you every tiny little detail about everything. I had the exact opposite experience, and I think that the C/SIDE Reference Guide is one of the best references out there, right up there with SQL Server's BOL, although the online feature is something we could use in NAV. It's sometimes hard to find information, but there are absolute gems in Help. Your characterization that NAV help is crap is just not accurate.

I don't expect Help to tell me exactly everything I need for my job, I expect some level of trial and error to master syntax. If I am unfamiliar with some syntax I create a test form and I start playing around with it.

rdebath · 2010-06-09

DenSter wrote:

rdebath wrote:

Yup, I see them and when I tried them they didn't work, at first.

Maybe that's the key, your expectation that help should give you every tiny little detail about everything. .... Your characterization that NAV help is crap is just not accurate.

Please don't get me wrong, for the vast majority of the Navision help and documentation I agree with you wholeheartedly, but for this the help doesn't appear to be enough for most people. Including some of Microsoft's (Navision's?) developers.

DenSter wrote:

If I am unfamiliar with some syntax I create a test form and I start playing around with it.

Naa, a report is better.

DenSter · 2010-06-10

rdebath wrote:

but for this the help doesn't appear to be enough for most people. Including some of Microsoft's (Navision's?) developers.

It does appear that many people are struggling with format strings. I don't think that's because of the quality of this particular piece of Help file, because it is actually one of the more elaborate topics. What I think might contribute is that it works completely different than VB formatting code, and that's where most people get their first programming experience. I never had to do any VB text formatting/concatenation, and for me the help file was very clear, to the point, and I was productive with formatting within minutes of discovering the help topic.

Not saying that I am particularly smart

but perhaps without the VB 'gold standard' (or other language) clouding my frame of reference, it wasn't difficult for me to figure out. So for me it was a matter of "aaaaahhh THAT's how you do it", and for someone with that reference point it's a matter of "that's SO NOT how you are supposed to do it". Dunno, just guessing. Very interesting to see how much different people perceive things isn't it.

rdebath · 2010-06-30

You may be right, the "picture style" formatting method that VB (and everyone else) copied from COBOL is very common and very easy. I was somewhat surprised that Navision didn't copy it too; it was something of a "WTH moment".

But IME it's very easy for people to get stuck in the "it won't work" mindset for the NAV formatting because, even now, I will get things the wrong way round or misspell one of the keywords and it doesn't work with no indication that there's actually something wrong with my code. Perhaps all the documentation needs a few unrealistic examples like my one above so people can see that it does work for more than the defaults.

PS: I'm not a VB programmer and only used COBOL for a few hours back in collage. For example right now I can't remember if VB uses "END WHILE" or "WEND".

Mike_HWG · 2011-11-29

Sorry to dig up an old thread, but I had trouble today with this exact issue, and I'd like to add a few notes to prevent others from stumbling.

DenSter wrote:

No Robert, the C/SIDE Reference Guide has all of those formats explained, if you would just be a tiny bit more persistent. I agree that it could be structured better, but it's all in there.

The reference is missing a key bit of information: the attributes have a order of precedence.

Try the following code:

Num := 123;
MyText := format(Num,0,'<standard format,2><precision,2:2>');
MESSAGE(MyText);

Then try this code:

Num := 123;
MyText := format(Num,0,'<precision,2:2><standard format,2>');
MESSAGE(MyText);

The first example would print '123'
The second message would print '123.00'

Both examples compile and run without error, which I think is what rdebath was grumpy about - I know I certainly did a #-o

Howdy, Stranger!

Quick Links

Categories

Pad 0 to the begining of decimal

Comments