If you format the string version with leading zeroes, it should sort correctly as text.
Example with much smaller value:
Right ( "000" & NumberHere ; 3 )
will produce values such as:
What type of export are you using?
Did you check "Apply current layout's data formatting to exported data"?
Which leads to how is the field formatted on the layout?
Just Export Records...
Perhaps I'm missing something ... is there a way to designate how numeric values are to be represented?
- Thanks, Verne
I'd rather not add the lettuce - it should not be difficult to ask a database program to spit out data with at least as much precision as existed when the data was ingested.
- Thanks, Verne
It's never "just export records". You also specify a file format for the exported records. That file format can impose limits on the precision of your export. Beverly has pointed out one option, but if the file format can't support the precision needed for your long integer, you might need to export it as a string. The leading zero's might or might not be needed depending on the capabilities of your target application.
A numeric field can hold up to ±10^400 values -- so that part is pretty good.
However, if you need more control over the export, you could use JDBC (a little Java) and handle getting the data that way. Also, if you needed an arbitrarily large number (completely unbounded, ANY SIZE), that's also doable in Java. I did an example here recently where I computed 1200 factorial -> 1200! (I could have done 20000000!, but I didn't want to make the forum thread go on for too many pages.)
Back to your issue ... Java's control over numeric formatting is incredibly powerful, rich, and deep. Nothing you can't do...with relative ease.
Let me know if you'd like to know more or if this sounds like an alternative you'd like to explore.
From my perspective, you shouldn't have to rely on strings to export numeric data, but that might be the best you can do, given your large values, in an FMP-only solution.
I whipped up a simple method in a microservice that could be called from FMP easily (or Terminal, or the Browser, or .....).
Parameter 1: Method: largeDecimalOutput()
Parameter 2: The large decimal
Here's an example (TERMINAL):
$ curl localhost:4567/largeDecimalOutput
Result (yes, as a String, but the same literal value you had as a numeric):
From FileMaker you'd just call the same microservice using INSERT FROM URL to the same "localhost" path as above (minus the curl).
The Java code, regardless whether in a microservice (via Terminal, FMP, or whatever) or in JDBC logic directly getting your field data from the FMP database, can write this information to a file in a couple lines of code!
You haven't replied to my postings here so I'll assume this approach isn't feasible for whatever reason.
FileMaker's display of the variable is extremely fungible - now when I pull it up it's displaying the values as a 1-10 digit integer (which is what I want). Earlier, it was displaying it as a number in scientific notation.
On export (using Export Records...) into a tab delimited text file, it exports the values the way it displays them on-screen.
What controls the character display format for numbers?
-- Thanks, Verne
How the number is formatted (Inspector) for that field on that layout (where you are when you export). Is the field wide enough to show the full number? Have you checked the "Apply current..." in the export dialog?
You said tab delimited is fine. What export type is "munging" your number? As Phil says, the receiving app may or may not be responsible for the "munging" once it gets your values.
Well, sometimes the field (I call it IPv4n) is displayed in scientific notation, sometimes as a large integer. When I click in a field displaying it in scientific notation, it instantly shows the value as a large decimal integer (which is why I call it extremely fungible).
Unfortunately, it exports values (text file, tab delimited) in the same way it displays values on-screen.
I don't know java (but do know C) and have thousands of records, and passing them through a web portal doesn't sound like a good idea.
Is there any way to control how FileMaker displays numeric values? I see 67 things you can do to control the field the value is placed in, but nothing to control how the value is displayed (except maybe decimal places and maybe commas).
Here is an window where it's displaying things the way I want:
Here is one where it's displaying the same type of value in scientific notation:
Note that when I click in a field, it changes to the way I want:
I just want to tell FileMaker to always display this as an integer decimal, and that it should export the value the same way.
Note that each of those IPv4N values represent the base IP address as a 4 byte unsigned integer so they can be properly sorted, and they are all unique values. Exporting the values with five digits of precision ruins the values.
-- Thanks, Verne
Beverly has already answered your question. You can use the inspector to format the disaplay of the number on your layout and then specify that the layout's formatting be used when exporting the data. For a table view, you can enter layout mode, add the field to the layout if it is not already on the layout, then use data formatting in the inspector to keep scientific notation from appearing.
2 of 2 people found this helpful
HINT: make the column wider to show all of the number. "As entered" may (or may not) be a better setting in Inspector for the number field. But make sure the column/field is wide enough to display the full number.
I assume that what I should do is go into the Inspector, select the data field, and make it "Decimal" instead of "General"?
It's just a little deceptive as they're both decimal formats - one's just expressed in scientific notation.
It does seem to be holding though.
I don't know why my other 3 databases worked properly (and were set to decimal) - perhaps it defaults to decimal and I somehow munged the subnet database?
Anyway, thanks for the help. This was driving me crazy.
Actually, the "As Entered" rather than General. I think General may be a problem with the display of larger numbers. Not that it is incorrect, but rather, you just want to show what you typed and not format otherwise or let FM "guess".