Re: FORMAT NUMBER
Hi Ferdinando, Just tested v16.5 (on Mac, but that should not differ from Windows). IMHO there are no changes in number formatting between v16 and v17. Created a 1 column array based list box on a form. Populated the array with some random real numbers. The column is set to variable type 'Numeric' and number format being: ###,##0.###;-###,##0.###; This means, positive non zero numbers format up to 6 digits to the left of the decimal point and 3 to the right. Negative non zero numbers have minus sign followed by the same format as positive numbers and zero is displayed as blank. In belgium we use period as thousands separator and comma as decimal point. Beware, 4D internally uses (US notation) period for decimal and comma for thousands. But at runtime uses the OS number format setting. There is a compatibility setting for upgraded databases (created before v11)* Eg: 1234567/1000 will display as 1.234,567 on my OS, but I have to set ###,##0.### as number format in the property pane. If you want to see three decimal digits for each number, you must use ###,##0.000 as number format. This will display 1 as 1,000 or 12/10 as 1,200 If you have larger numbers, add extra # on the left side, eg ###,###,##0.000 HTH Koen *4D Design Reference>Database Settings>Compatibility page: "Use period and comma as placeholders in numeric formats": starting with v11, 4D uses regional system parameters for numeric display formats (see “Number formats” in Display formats). 4D automatically replaces the “,” and “.” characters in numeric display formats by, respectively, the thousand separator and the decimal separator defined in the operating system. The period and comma are thus considered as placeholder characters, following the example of 0 or #. In previous versions of 4D, numeric display formats do not take the regional parameters of the system into account. For example, the “###,##0.00” format is a valid format for an American system. However, when it is applied to a numeric value displayed on a French or Swiss system, the result is incorrect. In converted database, for the sake of compatibility, this new mechanism is not activated. To take advantage of it, you must check this option. > Op 5 aug. 2019, om 23:24 heeft stardata.info via 4D_Tech > <4d_tech@lists.4d.com> het volgende geschreven: > > I need to display a number in an array real displayed in a list box. > In column settings of the list box, if I do not insert anything in Display > number format, the decimal numbers are correctly displayed, but I have 0 > instead of blank. Compass bvba Koen Van Hooreweghe Kloosterstraat 65 9910 Aalter Belgium tel +32 495 511.653 ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: FORMAT NUMBER
Dňa ut, 6. aug 2019, 0:31 Chuck Miller via 4D_Tech <4d_tech@lists.4d.com> napísal(a): > Read my previous note. You will need to convert the array to text array > and fill as a formatted number using code I already supplied > Format "### ###. 00;### ###. 00;" (format for positive, negative, zero) should display empty string for 0. -- Peter Bozek > ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: FORMAT NUMBER
Read my previous note. You will need to convert the array to text array and fill as a formatted number using code I already supplied Regards Chuck Sent from my iPhone > On Aug 5, 2019, at 5:59 PM, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > if ###,## does not display a blank for 0 you may have to convert back > and forth to/from text to get the formatting you want > > Chip > >> On Mon, 5 Aug 2019 23:24:26 +0200, stardata.info via 4D_Tech wrote: >> Sorry i use 4D V16 on Windows. >> >> I need to display a number in an array real displayed in a list box. >> In column settings of the list box, if I do not insert anything in >> Display number format, the decimal numbers are correctly displayed, >> but I have 0 instead of blank. > --- > Gas is for washing parts > Alcohol is for drinkin' > Nitromethane is for racing > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: FORMAT NUMBER
if ###,## does not display a blank for 0 you may have to convert back and forth to/from text to get the formatting you want Chip On Mon, 5 Aug 2019 23:24:26 +0200, stardata.info via 4D_Tech wrote: > Sorry i use 4D V16 on Windows. > > I need to display a number in an array real displayed in a list box. > In column settings of the list box, if I do not insert anything in > Display number format, the decimal numbers are correctly displayed, > but I have 0 instead of blank. --- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: FORMAT NUMBER
Sorry i use 4D V16 on Windows. I need to display a number in an array real displayed in a list box. In column settings of the list box, if I do not insert anything in Display number format, the decimal numbers are correctly displayed, but I have 0 instead of blank. Thanks Ferdinando Il 05/08/19 21:00, 4d_tech-requ...@lists.4d.com ha scritto: Message: 3 Date: Mon, 5 Aug 2019 11:48:46 -0400 From: Chuck Miller To: 4DTechList Tech<4d_tech@lists.4d.com> Subject: Re: FORMAT NUMBER Message-ID: <95863d81-6b67-400e-98a0-96534e372...@informed-solutions.com> Content-Type: text/plain; charset=utf-8 First of all. what version of 4D I would think you can not do all you want with a number. You could do it with a text variable for example in the in display detail form event If $YourNumber_R (is a real) $YourNumber_R:= 100.5 $YourText:=string($YourNumber_R;”###.0”) $YourNumber_R:= 100.52 $YourText:=string($YourNumber_R;”###.00”) $YourNumber_R:= 0 $YourText:=“” hope this helps If do not think you cab change the format of a field during the display of it on a list box Regards Chuck --- Chuck Miller Voice: (617) 739-0306 Informed Solutions, Inc. Fax: (617) 232-1064 mailto:cjmillerinformed-solutions.com Brookline, MA 02446 USA Registered 4D Developer Providers of 4D and Sybase connectivity http://www.informed-solutions.com ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: FORMAT NUMBER
That is why I asked about version and what you show is truncated not rounded Also I would suppose ###.## would take care of all Didn’t think of that one Regards Chuck Chuck Miller Voice: (617) 739-0306 Informed Solutions, Inc. Fax: (617) 232-1064 mailto:cjmillerinformed-solutions.com Brookline, MA 02446 USA Registered 4D Developer Providers of 4D and Sybase connectivity http://www.informed-solutions.com This message and any attached documents contain information which may be confidential, subject to privilege or exempt from disclosure under applicable law. These materials are intended only for the use of the intended recipient. If you are not the intended recipient of this transmission, you are hereby notified that any distribution, disclosure, printing, copying, storage, modification or the taking of any action in reliance upon this transmission is strictly prohibited. Delivery of this message to any person other than the intended recipient shall not compromise or waive such confidentiality, privilege or exemption from disclosure as to this communication. > On Aug 5, 2019, at 12:14 PM, Keith Culotta via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > 10.1237 as 10.123 <- rounding ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: FORMAT NUMBER
This ##0.###; ; produces good results for the example of 0.5 There are some things to watch for in the display detail event 4D v17 0 as blank 1 as 1 .5 as 0.5 .007 as 0.007 .0007 as 0 <- not blank 10.12345 as 10.123 10.1237 as 10.123 <- rounding Keith - CDI > On Aug 5, 2019, at 9:58 AM, stardata.info via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > Hi All, > > I need to format one column in a list box. > The column contains numeric values. > The values can have tre decimal digits. > I want a blank if number is zero. > I need to display 0,5 value for examples and i use ##0,00;; as a format but > not work. > > Does someone have a suggestion? > > Thanks > > Ferdinando ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: FORMAT NUMBER
First of all. what version of 4D I would think you can not do all you want with a number. You could do it with a text variable for example in the in display detail form event If $YourNumber_R (is a real) $YourNumber_R:= 100.5 $YourText:=string($YourNumber_R;”###.0”) $YourNumber_R:= 100.52 $YourText:=string($YourNumber_R;”###.00”) $YourNumber_R:= 0 $YourText:=“” hope this helps If do not think you cab change the format of a field during the display of it on a list box Regards Chuck --- Chuck Miller Voice: (617) 739-0306 Informed Solutions, Inc. Fax: (617) 232-1064 mailto:cjmillerinformed-solutions.com Brookline, MA 02446 USA Registered 4D Developer Providers of 4D and Sybase connectivity http://www.informed-solutions.com This message and any attached documents contain information which may be confidential, subject to privilege or exempt from disclosure under applicable law. These materials are intended only for the use of the intended recipient. If you are not the intended recipient of this transmission, you are hereby notified that any distribution, disclosure, printing, copying, storage, modification or the taking of any actionin reliance upon this transmission is strictly prohibited. Delivery of this message to any person other than the intended recipient shall not compromise or waive such confidentiality, privilege or exemption from disclosure as to this communication. > On Aug 5, 2019, at 10:58 AM, stardata.info via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > Hi All, > > I need to format one column in a list box. > The column contains numeric values. > The values can have tre decimal digits. > I want a blank if number is zero. > I need to display 0,5 value for examples and i use ##0,00;; as a format but > not work. > > Does someone have a suggestion? > > Thanks > > Ferdinando ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
FORMAT NUMBER
Hi All, I need to format one column in a list box. The column contains numeric values. The values can have tre decimal digits. I want a blank if number is zero. I need to display 0,5 value for examples and i use ##0,00;; as a format but not work. Does someone have a suggestion? Thanks Ferdinando ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: XLS plugin - format number with leading zero
Hello,
I found some information in the documentation (header files, I can not stress
this enough).
short answer, the library supports it, the plugin doesn't.
---
from format.h
// good resource for format strings:
http://www.mvps.org/dmcritchie/excel/formula.htm
// Good explanation of custom formats:
http://www.ozgrid.com/Excel/CustomFormats.htm
// MS examples (need Windows):
http://download.microsoft.com/download/excel97win/sample/1.0/WIN98Me/EN-US/Nmbrfrmt.exe
// Google this for MS help: "Create or delete a custom number format"
the plugin uses the format_number_t enumeration in XLS_FORMAT_SET_NUMERIC_FORMAT
i.e.
typedef enum
{
FMT_GENERAL = 0,
FMT_NUMBER1,// 0
FMT_NUMBER2,// 0.00
FMT_NUMBER3,// #,##0
FMT_NUMBER4,// #,##0.00
FMT_CURRENCY1, // "$"#,##0_);("$"#,##0)
FMT_CURRENCY2, // "$"#,##0_);[Red]("$"#,##0)
FMT_CURRENCY3, // "$"#,##0.00_);("$"#,##0.00)
FMT_CURRENCY4, // "$"#,##0.00_);[Red]("$"#,##0.00)
FMT_PERCENT1, // 0%
FMT_PERCENT2, // 0.00%
FMT_SCIENTIFIC1,// 0.00E+00
FMT_FRACTION1, // # ?/?
FMT_FRACTION2, // # ??/??
FMT_DATE1, // M/D/YY
FMT_DATE2, // D-MMM-YY
FMT_DATE3, // D-MMM
FMT_DATE4, // MMM-YY
FMT_TIME1, // h:mm AM/PM
FMT_TIME2, // h:mm:ss AM/PM
FMT_TIME3, // h:mm
FMT_TIME4, // h:mm:ss
FMT_DATETIME, // M/D/YY h:mm
FMT_ACCOUNTING1,// _(#,##0_);(#,##0)
FMT_ACCOUNTING2,// _(#,##0_);[Red](#,##0)
FMT_ACCOUNTING3,// _(#,##0.00_);(#,##0.00)
FMT_ACCOUNTING4,// _(#,##0.00_);[Red](#,##0.00)
FMT_CURRENCY5, // _("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)
FMT_CURRENCY6, // _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
FMT_CURRENCY7, // _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"*
"-"??_);_(@_)
FMT_CURRENCY8, // _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
FMT_TIME5, // mm:ss
FMT_TIME6, // [h]:mm:ss
FMT_TIME7, // mm:ss.0
FMT_SCIENTIFIC2,// ##0.0E+0
FMT_TEXT// @
} format_number_t;
but custom format based on string requires format_t
> 2019/01/30 16:42、4dialog via 4D_Tech <4d_tech@lists.4d.com>のメール:
>
> The problem is not i 4D, i get the leading zero but only in a text cell.
> In excel you can format manualy a column as number with fixed numbers
> preformated (selfdefined format). In my case, 11 numbers, "000". If
> you put 10 numbers in a cell it gets leading zero.
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: XLS plugin - format number with leading zero
I don't know anything about the plugin you were attempting to use, but one way to create formatted documents for Excel is to use XML. You specify the type for each cell. I'm sure you could figure out the format option you want by saving a document with Excel and looking at the XML. John DeSoi, Ph.D. > On Jan 30, 2019, at 1:42 AM, 4dialog via 4D_Tech <4d_tech@lists.4d.com> wrote: > > The problem is not i 4D, i get the leading zero but only in a text cell. > In excel you can format manualy a column as number with fixed numbers > preformated (selfdefined format). In my case, 11 numbers, "000". If > you put 10 numbers in a cell it gets leading zero. > > But is there a way to do this formating from 4d? ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
AW: XLS plugin - format number with leading zero
Hi, Some time ago I needed to export data as fixed length strings, including numbers (reals). So I wrote this method: // Uty_FixLengthNum(rToConvert;lLength;lLengthDec;tFillChar;tDecSign;bAlwaysReturnDec) -> tFixLengthNum // // User name (OS): Olivier // Date and time: 16.03.15, 16:00:49 // // Method: Uty_FixLengthNum // Description // Converts a number (real) to a fixed length string // // Parameters: // $1, real, number to convert to a fixed lenght string // $2, longint, total lenght to fix // $3, longint, lenght of decimals to fix // $4, text, fill character, e.g. "0" (zero) // $5, text, decimal separator sign, e.g. "." // $6, boolean, if true, always return string with decimals, even if decimals are zero // if false, return numbers with zero decimal without decimals; // e.g. 100 is returned as 100 when $2 is 7, even if $3 is greater than zero // $0, text, number converted to a fixed length string // ASSERT(Count parameters=6;"Not allowed number of parameter: "+String(Count parameters)) C_REAL($1;$rFixThis;$rInteger;$rDecimal) C_LONGINT($2;$3;$lFixMaxLenght;$lFixDecLenght;$lLenghtInt;$lLenghtDec;$lDifference;$lWhere) C_TEXT($4;$5;$0;$tFillChar;$tDecimalSeparator;$tResult;$tSystemDecimalSeparator;$tInt;$tDec) C_BOOLEAN($6;$bReturnZeroDecimals) $rFixThis:=$1 $lFixMaxLenght:=$2 $lFixDecLenght:=$3 $tFillChar:=$4 $tDecimalSeparator:=$5 $bReturnZeroDecimals:=$6 ASSERT((Length($tFillChar)=1);"Lenght of fill char is not exactly 1: "+$tFillChar) $tResult:=String($rFixThis) GET SYSTEM FORMAT(Decimal separator;$tSystemDecimalSeparator) $lWhere:=Position($tSystemDecimalSeparator;$tResult;*) If ($lWhere>0) $lDifference:=Length($tResult)-$lWhere $tInt:=Substring($tResult;1;$lWhere-1) $tDec:=Substring($tResult;$lWhere+1;$lDifference) $rInteger:=Num($tInt) $rDecimal:=Num($tDec) Else $tInt:=$tResult $tDec:="" $rInteger:=Num($tInt) $rDecimal:=Num($tDec) End if $lLenghtInt:=Length($tInt) $lLenghtDec:=Length($tDec) Case of : ($lFixMaxLenght<=0) $tResult:="" : (($rDecimal=0) & ($bReturnZeroDecimals=False) & ($lLenghtInt=$lFixMaxLenght)) $tResult:=$tInt : (($rDecimal=0) & ($bReturnZeroDecimals=False) & ($lLenghtInt<$lFixMaxLenght)) $lDifference:=($lFixMaxLenght-$lLenghtInt) If ($rInteger>=0) $tResult:=($tFillChar*$lDifference)+$tInt Else $tResult:="-"+($lDifference*$tFillChar)+String(Abs($rInteger)) End if : (($rDecimal=0) & ($bReturnZeroDecimals=False) & ($lLenghtInt>$lFixMaxLenght)) $tResult:="ERROR" Else Case of : ($lFixDecLenght<=0) & ($lLenghtInt=$lFixMaxLenght) $tResult:=$tInt : ($lFixDecLenght<=0) & ($lLenghtInt<$lFixMaxLenght) $lDifference:=$lFixMaxLenght-$lLenghtInt If ($rInteger>=0) $tResult:=($lDifference*$tFillChar)+$tInt Else $tResult:="-"+($lDifference*$tFillChar)+String(Abs($rInteger)) End if : ($lFixDecLenght<=0) & ($lLenghtInt>$lFixMaxLenght) $tResult:="ERROR" Else Case of : ($lLenghtDec>$lFixDecLenght) $tDec:=Substring($tDec;1;$lFixDecLenght) : ($lLenghtDec<$lFixDecLenght) $lDifference:=($lFixDecLenght-$lLenghtDec) $tDec:=$tDec+($lDifference*$tFillChar) End case $tResult:=$tInt+$tDecimalSeparator+$tDec Case of : (Length($tResult)<$lFixMaxLenght) $lDifference:=$lFixMaxLenght-(Length($tResult)) If ($rInteger>=0) $tResult:=($lDifference*$tFillChar)+$tResult Else $tResult:="-"+($lDifference*$tFillChar)+String(Abs($rInteger))+$tDecimalSeparator+$tDec End if : (Length($tResult)>$lFixMaxLenght) $tResult:="ERROR" End case End case End case $0:=$tResult // end of method This method is part of my utility methods component: https://flury-software.ch/a-swiss-army-knife-for-4d/ Best, Olivier -Ursprüngliche Nachricht- Von: 4D_Tech <4d_tech-boun...@lists.4d.com> Im Auftrag von 4dialog via 4D_Tech Gesendet: Mittwoch, 30. Januar 2019 08:42 An: 4d_tech@lists.4d.com Cc: 4dialog Betreff: Re: XLS plugin - format number with leading zero Thanks for all answers. The problem is not i 4D, i get the leading zero but only in a text cell. In excel you can format manualy a column as number with fixed numbers preformated (selfdefined format). In my case, 11 numbers, "000". If you put 10 numbers in a cell it gets leading zero. But is there a way to do this formating from 4d? - Hilsen -- Helge Antonsen www.4dialog.no -- Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr
Re: XLS plugin - format number with leading zero
Thanks for all answers. The problem is not i 4D, i get the leading zero but only in a text cell. In excel you can format manualy a column as number with fixed numbers preformated (selfdefined format). In my case, 11 numbers, "000". If you put 10 numbers in a cell it gets leading zero. But is there a way to do this formating from 4d? - Hilsen -- Helge Antonsen www.4dialog.no -- Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: XLS plugin - format number with leading zero
Hilsen: How about putting the logic in 4D code and then branch to the appropriate format. Intelligent formats are handy and elegant but don't have the simplicity, flexibility, or transparency of 4D code. This sort of code is very quick to code and very easy to understand: If(length of date=5) use format with leading 0 Else use standard date format End if -- Douglas von Roeder 949-336-2902 On Tue, Jan 29, 2019 at 3:45 AM 4dialog via 4D_Tech <4d_tech@lists.4d.com> wrote: > I need to build a excel document where born date (6 char) needs to be > number > format with leading zero if only 5 chars. > How can i do this in XLS plugin, what command, what params. > There is many format commands but cant find any documentation on them with > explanation? > > > > - > Hilsen > -- > Helge Antonsen > www.4dialog.no > -- > Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: XLS plugin - format number with leading zero
the plugin has no documentation because it is just a wrapper of an existing free library which has no documentation other than its header files (which is quite normal for a C library) sorry about that. if that's not good enough for you then you might to explore commercial options. https://www.pluggers.nl/product/xl-plugin/ > 2019/01/29 20:43、4dialog via 4D_Tech <4d_tech@lists.4d.com>のメール: > There is many format commands but cant find any documentation on them with > explanation? ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
XLS plugin - format number with leading zero
I need to build a excel document where born date (6 char) needs to be number format with leading zero if only 5 chars. How can i do this in XLS plugin, what command, what params. There is many format commands but cant find any documentation on them with explanation? - Hilsen -- Helge Antonsen www.4dialog.no -- Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
