When i import a file it adds extra digits ie the field says "1" in csv but the field after import says "1.0000" it's driving me mad! I've tried changing the field to number and max characters 1. What am i doing wrong?
In Layout Mode, can you select the field and open Inspector? What is (the Data tab) formatting for this number?
and/or it may really be that in the CSV (unless you look at it in a text editor). A column from Excel, for example may have formatting that hides the precision. However an export from there would show all of them upon Import into FileMaker.
When you click in the field do the zero numbers disappear?
No they stay so our reports don't work properly
It's set to general. I'm guessing it must be the latter as i export from two places and the other imports fine. Is there anything I can do?
A couple of observations:
1. Those settings affect how a number is displayed, not the actual field contents. I suggest you try (1) setting the display setting to As Entered, and (2) clicking inside the field. Either of these should reveal what the field actually contains, as distinct from what is displayed (which should be the same thing if As Entered is selected).
2. Is it possible that the actual number imported from the csv is a multi-place decimal, such as 1.000000012 or some such, and that what you are seeing is just a truncation of this?
3. If you've done all this checking, conclude that the field content is actually what you see, and still wish to clean it up, you could do so with Replace Field Contents by calculation, using the following calc: Abs ( name of field ). The Abs ( ) (absolute) function returns only the whole number portion of a number—conversely the Mod ( ) (modulus) function returns only the decimal portion.
brilliant thank you!
keywords you said in whole or in part:
Abs ( ) (absolute) function returns only the whole number portion of a number—conversely the Mod ( ) (modulus) function returns only the decimal portion.
FileMaker Pro 16 Help Abs(number)
The absolute value of a number is positive. For example, if a negative number appears in a field, the Abs function removes the minus sign and changes it to a positive value.
Abs ( -1.25 ) = 1.25 not 1
FileMaker Pro 16 Help Int(number)
Drops digits to the right of the decimal point and returns the integer part of a number without rounding.
FileMaker Pro 16 Help Mod(number;divisor)
Returns the remainder after a number is divided by a divisor.
I believe the function needed is Int() not Abs(). And there may be custom functions to get the decimal, but Mod() requires a divisor. I think I use this for "decimal":
number - Int(number)
If you have other ways to get the whole number with Abs() or the decimal with Mod(), I'd love hear! I know there are tricks introduced here all the time that I may have missed.
To use Mod I think you would need to do Mod (number;Int(number)) . I'd prefer number-Int(number) since you are only dealing with 1 function.
beverly wrote:I believe the function needed is Int() not Abs(). And there may be custom functions to get the decimal, but Mod() requires a divisor. I think I use this for "decimal":
Oops! My mistake, sorry. Beverly is right, Int ( ) is the function I was thinking about when I wrote my post.
Thanks for picking up my error, Beverly, and apologies to any reader for whom this caused confusion.
I really was hoping there were some new ways of doing things. It's always good to learn new things.
Thanks for confirming, keywords & rgordon!
Retrieving data ...