6 Replies Latest reply on Feb 19, 2015 6:27 AM by PradeillesThomas

    Issue : filemaker and long



      Issue : filemaker and long



      I'm working with ip adresses. In my database, I have a set of ip block. Each ip block as an integer representation to be able to find a ip inside a block. Exemple :

          id = 1    (number)

          ip_start =     (text)

          ip_end  =  (text)

          integer_start = 3452776192  (number)

          integer_end  = 3452776703  (number)


      For some reason, if i watch at my base, I see that integer start is set to "3.4528e+09" instead of 3452776192. But if I click on the field, it display the correct 3452776192. So I'm wondering if this may cause issue for finding purpose.

      Also, for ipv6, integer representation will be more something like this : 58569007865278300019850917199241805823

      Can filemaker handle a long value like this as number ?



      For test I have made this relation (see attached pic) :

               Filter_ip has a relation >= on integer_from

               Filter_ip has a relation <= on integer_to

      If I enter a filter_ip and perform a find, I can't find any related record. Not sure If I am doing it bad or if this is related to my 3.4528e+09 issue...


        • 1. Re: Issue : filemaker and long

          surprise I just noticed one error ... sometime, when you have the head inside, you can't see things anymore ... Relation between Ipv4 block and Filter_Countries should be on country_id -> id ...


          Also, I changed my strategy about thoses ip ranges. I now create a record for each ip possible instead of one record per range. I hope this will speed up a bit the process for find, do you know if it's faster to find on a range <=  >= with less record or on a = ? with more records?

          Regarding my other issue, the one that display 3.4528e+09 instead of 3452776192 after some testing, it seems like it does not create any error at the end. May be only a display issue.


          • 2. Re: Issue : filemaker and long

            For IP addresses, I'd use a field of type text rather than a field of type number and what you see is a display issue. You can change the data format for that number field in the inspector. If I recall correctly, you need to change this from "as entered" to "general", but you can experiment with the options here to see what works for yourself.

            • 3. Re: Issue : filemaker and long

              Hello PhilModJunk,


              Thanks for validating that's only a display issue.


              I do convert the ip in integer before storing it. That's why I use a number field instead of a text field. I did this cause, as far as I understood, it's faster to perform find on number than on text, am I right or there is nothing to do with the field type?


              • 4. Re: Issue : filemaker and long

                Don't know if number field types are faster than text or not but that sounds very possible. But all searches and sorts are a function of the number of records to search or sort (and it's a non-linear funciton) so it will depend on the number of records in the table. If the total number of records are a 1,000 or less, I suspect that you won't see much difference. If, on the other hand, you have millions of records...

                • 5. Re: Issue : filemaker and long

                  Ok, I'm working with millions of records. I guess my starting number of records will be 5-15 millions based on this no range system, then database will grow more.


                  In my case, I don't want to get found sets, but only input an ip and find the country associated to this. So in this particular case, I guess that I only need to enter my ip filter and then relationship will do the job.


                  Anyway I'll do some performance testing and let you know here what works the best on this special case.

                  • 6. Re: Issue : filemaker and long

                    Ok, so after some test, the fastest solution seems to be working with range of ip converted to integer. Even if the search was performed pretty fast considering I had something like 20M records using the "one record per ip" solution, it slower than the range one with only 120 000 records.