10 Replies Latest reply on Oct 22, 2013 9:51 AM by andreb

    Discrepancy between Get(RecordID) and AppleScript record ID

    andreb

      Title

      Discrepancy between Get(RecordID) and AppleScript record ID

      Post

           Using FileMaker Pro Advanced 11 on Snow Leopard.

           Why does the result of Get(RecordID) differ from the result returned when the same request is made using AppleScript?

           More specifically, the results seem to be the same up to the 127th record. From 128th record on, the record IDs differ.

           In spite of that, telling AppleScript to show a particular record using any ID returned beyond the 127th record still works,

           This example code shows record IDs for the first 165 records returned by AppleScript:

            

      getIDofeveryrecordofwindow "RecordID" ofdatabase "RecordID"

      --> {1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0, 20.0, 21.0, 22.0, 23.0, 24.0, 25.0, 26.0, 27.0, 28.0, 29.0, 30.0, 31.0, 32.0, 33.0, 34.0, 35.0, 36.0, 37.0, 38.0, 39.0, 40.0, 41.0, 42.0, 43.0, 44.0, 45.0, 46.0, 47.0, 48.0, 49.0, 50.0, 51.0, 52.0, 53.0, 54.0, 55.0, 56.0, 57.0, 58.0, 59.0, 60.0, 61.0, 62.0, 63.0, 64.0, 65.0, 66.0, 67.0, 68.0, 69.0, 70.0, 71.0, 72.0, 73.0, 74.0, 75.0, 76.0, 77.0, 78.0, 79.0, 80.0, 81.0, 82.0, 83.0, 84.0, 85.0, 86.0, 87.0, 88.0, 89.0, 90.0, 91.0, 92.0, 93.0, 94.0, 95.0, 96.0, 97.0, 98.0, 99.0, 100.0, 101.0, 102.0, 103.0, 104.0, 105.0, 106.0, 107.0, 108.0, 109.0, 110.0, 111.0, 112.0, 113.0, 114.0, 115.0, 116.0, 117.0, 118.0, 119.0, 120.0, 121.0, 122.0, 123.0, 124.0, 125.0, 126.0, 127.0, 3.2768E+4, 3.2769E+4, 3.277E+4, 3.2771E+4, 3.2772E+4, 3.2773E+4, 3.2774E+4, 3.2775E+4, 3.2776E+4, 3.2777E+4, 3.2778E+4, 3.2779E+4, 3.278E+4, 3.2781E+4, 3.2782E+4, 3.2783E+4, 3.2784E+4, 3.2785E+4, 3.2786E+4, 3.2787E+4, 3.2788E+4, 3.2789E+4, 3.279E+4, 3.2791E+4, 3.2792E+4, 3.2793E+4, 3.2794E+4, 3.2795E+4, 3.2796E+4, 3.2797E+4, 3.2798E+4, 3.2799E+4, 3.28E+4, 3.2801E+4, 3.2802E+4, 3.2803E+4, 3.2804E+4, 3.2805E+4}

            

            

           Get(RecordID) calculates the IDs of the same 165 records in consecutive order: record 1=recordID 1, record 2=recordID2 . . . record 165=recordID165
            
           Yet telling AppleScript to show record ID 32768 produces record 128, but telling AppleScript to show record ID 128 results in an object not found error.
            
           What's going on?

        • 1. Re: Discrepancy between Get(RecordID) and AppleScript record ID
          davidanders

               Do you have a known good backup that you can test?
               Save as Clone (empty) and creating 200 records would be a test.
               Doing a Recover and testing would be another test.

               This is really old.
          http://macscripter.net/viewtopic.php?id=25602

          This has links to Applescript and broken ID numbers
               https://www.google.com/search?q=applescript+get+Record+ID+filemaker

          • 2. Re: Discrepancy between Get(RecordID) and AppleScript record ID
            andreb

                 Not sure what using a backup or clone would accomplish because the file I used to generate the record IDs was created specifically for that purpose. It has just four fields (see image) and no data other than the auto entered values. It's probably as clean as you can get.

            • 3. Re: Discrepancy between Get(RecordID) and AppleScript record ID
              philmodjunk

                   RecordID's are assigned when the record is created. If you import the records into a different table, new record ID's will be assigned to the records created by the import.

              • 4. Re: Discrepancy between Get(RecordID) and AppleScript record ID
                andreb

                     Non sequitur. What's your point and how is this relevant?

                • 5. Re: Discrepancy between Get(RecordID) and AppleScript record ID
                  philmodjunk

                       You wanted to know how importing into a clone would make a difference. If you import into a clone, the record ID's may change. After importing, the resulting ID's should then list in ascending order starting with 1 as the record Id of the first record imported.

                  • 6. Re: Discrepancy between Get(RecordID) and AppleScript record ID
                    andreb

                         Pardon the impertinence. I think I see what you're getting at—that importing into a clone may change record IDs. In fact, if records were deleted in the original file thus producing gaps in the record IDs, then am I correct to assume importing into a clone would reset all IDs to consecutive order starting with 1?

                         Well, I just did exactly that. I took the original file, deleted five records at random, then imported into a clone. The record IDs in the clone with 160 records imported are indeed consecutive from 1 to 160 as calculated by Get(RecordID).

                         However, running the same AppleScript to get the record IDs of those 160 imported records yielded this result:

                          

                    getIDofeveryrecordofwindow "RecordID2" ofdatabase "RecordID2"

                    --> {1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0, 20.0, 21.0, 22.0, 23.0, 24.0, 25.0, 26.0, 27.0, 28.0, 29.0, 30.0, 31.0, 32.0, 33.0, 34.0, 35.0, 36.0, 37.0, 38.0, 39.0, 40.0, 41.0, 42.0, 43.0, 44.0, 45.0, 46.0, 47.0, 48.0, 49.0, 50.0, 51.0, 52.0, 53.0, 54.0, 55.0, 56.0, 57.0, 58.0, 59.0, 60.0, 61.0, 62.0, 63.0, 64.0, 65.0, 66.0, 67.0, 68.0, 69.0, 70.0, 71.0, 72.0, 73.0, 74.0, 75.0, 76.0, 77.0, 78.0, 79.0, 80.0, 81.0, 82.0, 83.0, 84.0, 85.0, 86.0, 87.0, 88.0, 89.0, 90.0, 91.0, 92.0, 93.0, 94.0, 95.0, 96.0, 97.0, 98.0, 99.0, 100.0, 101.0, 102.0, 103.0, 104.0, 105.0, 106.0, 107.0, 108.0, 109.0, 110.0, 111.0, 112.0, 113.0, 114.0, 115.0, 116.0, 117.0, 118.0, 119.0, 120.0, 121.0, 122.0, 123.0, 124.0, 125.0, 126.0, 127.0, 3.2768E+4, 3.2769E+4, 3.277E+4, 3.2771E+4, 3.2772E+4, 3.2773E+4, 3.2774E+4, 3.2775E+4, 3.2776E+4, 3.2777E+4, 3.2778E+4, 3.2779E+4, 3.278E+4, 3.2781E+4, 3.2782E+4, 3.2783E+4, 3.2784E+4, 3.2785E+4, 3.2786E+4, 3.2787E+4, 3.2788E+4, 3.2789E+4, 3.279E+4, 3.2791E+4, 3.2792E+4, 3.2793E+4, 3.2794E+4, 3.2795E+4, 3.2796E+4, 3.2797E+4, 3.2798E+4, 3.2799E+4, 3.28E+4}

                          

                         As you can see, the problem I originally described is still there. Record IDs returned by AppleScript are not consecutive and DO NOT match the Get(RecordID) function after the 127th record.

                          

                         So, what's up with that? 

                    • 7. Re: Discrepancy between Get(RecordID) and AppleScript record ID
                      FentonJones

                           I'm not sure what you meant to use this for; but I think this is what will fix what you're seeing:

                      setid_lasttolastitemofids_listasinteger

                           -- 32800

                            

                            

                           Here is something else I wrote to myself, years ago (which I no longer remember well):

                            

                           -- The largest value that can be expressed as an integer in AppleScript is 536870911

                      setito 536870911 asinteger

                           -- 536870911

                           -- class of result

                           -- integer

                            

                           -- I entered: 536870912; it was auto-changed

                           -- set i to 5.36870912E+8 as integer

                           -- class of result

                           -- real

                      • 8. Re: Discrepancy between Get(RecordID) and AppleScript record ID
                        FentonJones

                             I just found another old AppleScript file (by someone else; too complex for me :-), which can handle larger numbers than "integer". It only handles one number at a time. I imagine a another routine could run it thru an entire list; but that's another post:

                        number_to_string(8.72124243234E+11)

                             --> returns: "872124243234"

                              

                        on number_to_string(this_number)

                        set this_number to this_number as string

                        ifthis_numbercontains "E+" then

                        setxtotheoffsetof "." inthis_number

                        setytotheoffsetof "+" inthis_number

                        setztotheoffsetof "E" inthis_number

                        setthedecimal_adjusttocharacters (y - (lengthofthis_number)) thru -1 ofthis_numberasstringasnumber

                        ifxisnot 0 then

                        setthefirst_parttocharacters 1 thru (x - 1) ofthis_numberasstring

                        else

                        setthefirst_partto ""

                        endif

                        setthesecond_parttocharacters (x + 1) thru (z - 1) ofthis_numberasstring

                        set the converted_number to the first_part

                        repeatwithifrom 1 tothedecimal_adjust

                        try

                        set the converted_number to the converted_number & character i of the second_part

                        onerror

                        set the converted_number to the converted_number & "0"

                        endtry

                        endrepeat

                        return the converted_number

                        else

                        return this_number

                        endif

                        end number_to_string

                        • 9. Re: Discrepancy between Get(RecordID) and AppleScript record ID
                          philmodjunk
                               

                                    Pardon the impertinence. I think I see what you're getting at...

                               No impertinence perceived and I think Fenton has put his finger on the source of the issue...

                          • 10. Re: Discrepancy between Get(RecordID) and AppleScript record ID
                            andreb

                                 I'm sorry, but I still don't see how this addresses the discrepancy.

                                 Fenton provided some AppleScript code to convert scientific notation to integer. That's fine, but the crux of the problem is that the numbers still don't match. 

                                 For record number 128, Get(RecordID) returns 128, AppleScript returns 32768 or 3.2768 x 10^4. 

                                 Doesn't matter whether you express the AppleScript result as integer or in scientific notation, 128 ≠ 32768.

                                 Why the discrepancy? Is it a bug?