10 Replies Latest reply on Jul 29, 2014 11:13 AM by nihmbrisby

    Creating a batch editing script that handles locked records

    nihmbrisby

      Title

      Creating a batch editing script that handles locked records

      Post

           I'm creating a pretty simple script that applies a tag to all records in a found set.  Loops through each records and creates a new tag (tags are just records in a child table).  Works fine.  However, I want the script to behave well in the event of any locked records.  I've been googling about for how to do this.

           First of all, I thought I'd be able to test this by simply having another open window with a record from the found set open for editing.  Indeed, this prevents that one record from having a tag generated.  However all the methods I've read to capture this event (Get last error, Get record Open State) fail to register an error or locked record (or perhaps I'm doing it wrong).  Get last error equals 0 and Get record open state = 0 for all records (so I must be misunderstanding it's function).

           My questions- first of all, am I on the right track?  Are get last error and/or Record open state the preferred methods for capturing and alerting the user that a record was not editing by a batch script?  Second of all, is a record locked the same for records being used by different users vs. records being edited by the same user but in different windows?  The latter is all I have been able to test for, but the former is the only one with significant discussion on the web.  My expereience with records open in multiple windows so far seems quite different to what I'm reading about records locked on account of multiple users viewing the same record.

           Thanks.

        • 1. Re: Creating a batch editing script that handles locked records
          nihmbrisby

               A bit more info:

               So I watched Todd Geist's video on using the transactional nature of portals.  Much to my surprise, he does in fact use the 'multiple windows' scenario to simulate record locking.  And he get's his error 301.  

               In my solution, the script was editing a related field.  In particular, it was editing a field for a record that didn't exist and using the 'allow creation of records in this table' property to create a new record.  With a second window open to (and editing) a record in the found set- the script was not able to set the field.  However, it did not generate an error.  When I manually edited the field, filemaker did give me an error.  At the moment I'm using a dedicated field ('check_lock') to edit.  It's a local field and trying to edit does generate an error 301.  Nevertheless, I'd like to have a better understanding of what's going on.

          • 2. Re: Creating a batch editing script that handles locked records
            nihmbrisby

                 A Bit more info- So using a dummy field, the script successfully notifies the user of a locked record.  Problem is, as best I can tell, the edit only fails if the other user is editng the same field.  This leaves me with the rather ridiculous error message: "Tag may not have been applied."  I mean I suppose I could check after committing if the field was equal to the tag.... but is there no simpler way about this?  Seems like this is the sort of thing a database would have a built in solution for?

                  

                 Thanks.

            • 3. Re: Creating a batch editing script that handles locked records
              philmodjunk

                   What is the purpose of "tagging" the records? There are some possible scenarios that can tag a record even when it is locked.

                   You can build a list of Primary keys in a variable or global field to "tag" them.

                   You can create a related record linked to the record to "tag" it.

                   Both have their advantages and disadvantages and can't be used in all situations.

                   You can also set up this type of scenario if you truly must modify data in the records to tag them:

                   Perform find to pull up found set of records to be tagged.

                   Use set error capture to turn on error capture

                   Use replace field contents to tag the records

                   Use get ( LastError ) to check for errors due to locked records

                   If any were locked, perform the same find again, but with criteria that omit the tagged ones.

                   Your resulting found set will be the records that could not be tagged due to being locked in either another window or by another user.

                   But what you do next can be problematic as you still can't tag the records until you kick the other user out of the record...

              • 4. Re: Creating a batch editing script that handles locked records
                nihmbrisby

                     Hi Phil,

                     This is just 'general purpose' tagging.  It serves both descriptive and searching functions.  It allows the user to apply a tag (ie a word) to a group of records and then later search by that.  It isn't much more than a 'saved find,' except of course it doesn't add new records that fit the criteria.  It can also be thought of as a description of sorts.  Like- you could search and manually refine a list of contacts that you then tag as 'serious client.'  This would both allow you to search for all 'serious clients', and would also indicate to anyone viewing the record that this was a 'serious client.'

                     The tags are child records, so the method you mentioned of building a list of pk's would surely suit my situation.  Are there any performance implications to filling a variable with over 1000 uuids?

                     Regarding replace field contents- that was my first thought as I mentioned.  However, as best I can tell, this does not work when the field that you are trying to modify is a related field across a relationship where no record will exist in order to create a new record (ie, the tag).

                     As always- much appreciated.

                • 5. Re: Creating a batch editing script that handles locked records
                  philmodjunk

                       I would use this approach:

                       TableOfTaggedRecords----<Join>------Tags

                       Tagging a record in the first table would be a matter of creating a new record in Join--which can avoid issues with locked records.

                       You could use this method to avoid record loops if you need to tag large numbers of records all in one batch:

                       Pull up your records to be tagged in a found set, then use Import Records to create new records in Join by importing the PK values from TableOfTaggedRecords into the FK field of Join. Use Replace Field Contents (on a layout based on Join) to update the newly created Join records to link them all the the PK value of the specified Tag record.

                  • 6. Re: Creating a batch editing script that handles locked records
                    nihmbrisby

                         That's a very interesting method.  Is the import records script step safe to use if my clients may rename their database files?

                    • 7. Re: Creating a batch editing script that handles locked records
                      philmodjunk

                           It should be as you can use a path variable that is set to Get ( FileName ) as a relative path reference to the database file.

                      • 8. Re: Creating a batch editing script that handles locked records
                        nihmbrisby

                             That's really great actually.  Even if I don't use this it in this scenario- the functionality of import had simply not been on my horizon. Thanks for this.  So let me just be clear on this one point- record locking never prevents filemaker from reading info (ie pk's) from a record?  Thanks.

                        • 9. Re: Creating a batch editing script that handles locked records
                          philmodjunk

                               Correct. "edit locking" (as opposed to Record Level Access control in Manage Security) will not prevent reads. It prevents another user or script from opening the locked record for editing.

                          • 10. Re: Creating a batch editing script that handles locked records
                            nihmbrisby

                                 So much good info- Thanks Phil!