14 Replies Latest reply on Aug 1, 2017 2:00 PM by beverly

    Large value numeric field

    VerneArase

      I have a database which I use to track IP addresses, and one of the fields it maintains is an aggregate IP address which is represented by a scalar integer value from 0-(2^32-1).

       

      When I export this data, FileMaker considerately exports this value in scientific notation and maintains about five digits of precision.

       

      How do I get FileMaker to export the original value?

       

      If I treat the field as a string, it won't sort properly - which is the whole reason for the field in the first place (since IP addresses as strings don't sort properly).

       

      -- Thanks, Verne

        • 1. Re: Large value numeric field
          philmodjunk

          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:

           

          000

          001

          002

          003

          • 2. Re: Large value numeric field
            beverly

            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?

             

            Beverly

            • 3. Re: Large value numeric field
              VerneArase

              Just Export Records...

              Perhaps I'm missing something ... is there a way to designate how numeric values are to be represented?

               

              - Thanks, Verne

              • 4. Re: Large value numeric field
                VerneArase

                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

                • 5. Re: Large value numeric field
                  philmodjunk

                  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.

                  • 6. Re: Large value numeric field
                    fmpdude

                    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.

                    • 7. Re: Large value numeric field
                      fmpdude

                      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

                      /123333232858348383843429348.98923333030304040000322938743845793875937485

                       

                      Result (yes, as a String, but the same literal value you had as a numeric):

                      123333232858348383843429348.98923333030304040000322938743845793875937485

                       

                      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!

                       

                      So Easy.

                       

                      ---

                       

                      You haven't replied to my postings here so I'll assume this approach isn't feasible for whatever reason.

                      • 8. Re: Large value numeric field
                        VerneArase

                        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

                        • 9. Re: Large value numeric field
                          beverly

                          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.

                          Beverly

                          • 10. Re: Large value numeric field
                            VerneArase

                            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:

                            hosts.png

                            Here is one where it's displaying the same type of value in scientific notation:

                            subnets.png

                            Note that when I click in a field, it changes to the way I want:

                            subnets 2.png

                            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

                            • 11. Re: Large value numeric field
                              philmodjunk

                              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.

                              • 12. Re: Large value numeric field
                                beverly

                                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.

                                Beverly

                                2 of 2 people found this helpful
                                • 13. Re: Large value numeric field
                                  VerneArase

                                  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.

                                  Verne

                                  • 14. Re: Large value numeric field
                                    beverly

                                    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".

                                    Beverly