1 2 Previous Next 28 Replies Latest reply on Jan 20, 2016 11:10 AM by electon

    Question about Get(RecordID)

      Hi,

       

      I have a script that adds a new record and I need the ID for further script steps. Is "Get ( RecordID )" delivering 100% sure the ID also in a multi-user-environment (of another user is adding a record a second after mine before further executing...)

       

      Another possibility is the read the ID of the record just after commiting the new one. Does this deliver the same?

        • 1. Re: Question about Get(RecordID)
          coherentkris

          i would capture the primary key value of the new record in a variable...

          Pseudo code would look like

          create new record

          set script local variable to primary key of the new record

          Use the variable as needed

          • 2. Re: Question about Get(RecordID)

            Yes, thanks - I did not think of capturing the ID after creating, but

            before commiting the record...

             

            Am 30.01.15 um 13:25 schrieb coherentkris:

            <https://community.filemaker.com/?et=watches.email.thread>

             

            >

                Question about Get(RecordID)

             

            Antwort von coherentkris

            <https://community.filemaker.com/people/coherentkris?et=watches.email.thread>

            in /Discussions/ - Komplette Diskussion anzeigen

            <https://community.filemaker.com/message/171672?et=watches.email.thread#171672>

            >

            • 3. Re: Question about Get(RecordID)
              coherentkris

              when using variables that refer to newly created records you always need to plan for commit failures and you have a value in the variable that does not exist in the data. This must be factored in even when using get recordid

              • 4. Re: Question about Get(RecordID)
                jbrown

                What if you created the record, then committed it, then got the ID in the variable then. It seems like that would solve the problem you mention.

                 

                And by the way, you mention a good problem. One that I need to be diligent about.

                • 5. Re: Question about Get(RecordID)
                  wimdecorte

                  Luna.media wrote:

                   

                  Is "Get ( RecordID )" delivering 100% sure the ID also in a multi-user-environment

                   

                  Absolutely not.  When you clone your file, the record IDs are reset.  So each new record starts with 1 again.  And that will break all your relationships if you clone a file and import the data.

                   

                  User your own ID, either an auto-enter serial or an auto-enter UUID

                  • 6. Re: Question about Get(RecordID)
                    pjreagan

                    Auto-entered serial numbers can be flaky, but I haven't heard of problems with Get(RecordID).  Maybe others on this list could relate any issues they've found.

                     

                    I believe, and a test just now seems to confirm this, that the record ID is offered with the setup of the record.  This seems to be unrelated to whether the record is committed.  Maybe there are better terms than offered and setup, but this is situation can be demonstrated in a transaction design pattern test:  Create a layout with a portal to a related table.  Make sure the relationship allows for creation in the related table.  Include a portal row field for text entry, and another that's a calc to display the RecordID.  Also create a script that only has one step: Revert Record/Request.

                     

                    Create a new record then tab into the portal.  Don't commit the record.  Now enter something into the text field.  The RecordID number increments.  This can be repeated for additional rows to see other offered RecordIDs.  Then, without committing the record, run the Revert script.  The parent record and all its portal rows should disappear. 

                     

                    Then repeat the experiment.  The previously offered RecordIDs have been lost.  They were never committed.

                     

                    Two additional details from FileMaker documentation:

                    - table record count is limited to 64 quadrillion total records over life time of file  (Apparently a table can run out of RecordIDs)

                    - Get(RecordID) may not return a consistent value for records in ODBC data sources ('Haven't tested this, so can't comment.)

                     

                    'Hope this helps

                    • 7. Re: Question about Get(RecordID)
                      jbrown

                      I haven't heard of Serial numbers being flaky. Is there a reason why they would be? I use them in all my tables, although we are slowly, I think, switching to UUID as the auto enter.

                       

                      The Record ID is the one FIleMaker uses internally itself. I've never heard of people using that as a primary key. And you're right, on creation it happens. I guess, so far, I've never seen a reason to use it. I'm sure there is a reason out there. . .

                      • 8. Re: Question about Get(RecordID)
                        wimdecorte

                        pjreagan wrote:

                         

                        but I haven't heard of problems with Get(RecordID).  Maybe others on this list could relate any issues they've found.

                         

                         

                        I've just posted about a HUGE one: record IDs are reset when a file is cloned.  Cloning a file is a fairly common approach when moving a dev file into production.

                         

                        If you currently have a parent record with ID 8, and use that ID as the foreign key in its child records.  After you clone the file and import the data, the child records will still be set to the foreign key of 8, but there is no guarantee that the parent record will actually have record id 8.

                        • 9. Re: Question about Get(RecordID)
                          pjreagan

                          Sorry, I should have explained instead of just throwing out that auto-enter serial number issue.  'Was trying to stay on the RecordID topic.

                           

                          The issue comes up with when a file is hosted, and accessed by multiple users.  Sometimes serial numbers can be skipped. 

                           

                          I set up a test of this last June.  Three users ran the same script on the same commercial-service hosted file.  The script just looped to create records.  We ran it long enough to create about 2.5 million records.  During that time six serial numbers were skipped.  (Rumor has it that duplicate serial numbers can happen also but this test did not produce any duplicates.)

                           

                          So I'm using this information as is appropriate.  Sometimes losing serial numbers at this low rate doesn't matter, sometimes it does.  It depends upon the situation of course.

                          • 10. Re: Question about Get(RecordID)
                            coherentkris

                            I don't think I've ever seen a primary key constraint for unique, not empty, sequential with no gaps

                             

                            Sent from my iPhone

                            • 11. Re: Question about Get(RecordID)
                              BruceHerbach

                              Hi

                               

                              I usually have a primary key for all records. I use that when I need to identify the record or set a relationship between table occurrences. Primary key = foreign key.

                               

                              Record Id function will not be reliable for you.

                               

                              Sent from my mobile device... Please excuse typos.

                              • 12. Re: Question about Get(RecordID)
                                pjreagan

                                Right, for keys number gaps probably don't matter.  But some people use the auto-enter number in ways that shouldn't have gaps - invoice numbers for instance.

                                • 13. Re: Question about Get(RecordID)
                                  wimdecorte

                                  pjreagan wrote:

                                   

                                  But some people use the auto-enter number in ways that shouldn't have gaps - invoice numbers for instance.

                                   

                                  That's a different problem, one of design nature.

                                  Skipped numbers in pks is not a problem. Duplicates would be.

                                  • 14. Re: Question about Get(RecordID)

                                    I for myself decided long ago NOT to use auto-enter numbers as primary

                                    keys any more mostly because a total unique ID never will give me the

                                    feeling I could control or oversee theses IDs.

                                     

                                    And if someone needs ascending numbers he can easily calculate the next

                                    one and add per script...

                                     

                                     

                                    Am 30.01.15 um 18:04 schrieb wimdecorte:

                                    <https://community.filemaker.com/?et=watches.email.thread>

                                     

                                    >

                                        Question about Get(RecordID)

                                     

                                    Antwort von wimdecorte

                                    <https://community.filemaker.com/people/wimdecorte?et=watches.email.thread>

                                    in /Discussions/ - Komplette Diskussion anzeigen

                                    <https://community.filemaker.com/message/171705?et=watches.email.thread#171705>

                                    >

                                    1 2 Previous Next