6 Replies Latest reply on Dec 18, 2014 12:00 PM by MarkGores

    Deleting a record in a script

    MarkGores

      Title

      Deleting a record in a script

      Post

      I have a file that imports csv files from UPS and FedEx shipping computer and combines them into one table containing the tracking number, date and our jobnumber.  I am trying to have the file update our jobs database by entering the tracking numbers with the jobs as they ship.

      The shipping file is just meant to be transitory so I wanted to clear it out every day after the jobs file was updated.  I have been just using a lookup from the jobs file, but noticed I occasionally get an error saying a record couldn't be updated because another user was modifying it.  With no indication of which record that was, I don't want to delete the records and lose the tracking number for that job.  So I tried a looping script that goes to each shipping record, gets the job number, performs a find in the jobs file.  If a record is found it updates the ship date and tracking number fields, if not it sets an error marker in the shipping record.  Then goes back to the shipping file and if the error field is empty delete that record.  The problem is, it won't delete that record.  I get a "record is being modified by another user or in another window" and even if I exit the script I can't edit that record anymore.  Tried inserting a commit record step to make sure there aren't any active fields, tried using a set field to "NO" on the error field so I could go back and delete them later, but still no luck

        • 1. Re: Deleting a record in a script
          philmodjunk

          If you are using FileMaker Pro, that error means exactly what it says. Another user has the record open for editing or you have the record open for editing in another window. You will not be allowed to delete or modify this record until the other user (or you in that other window) commits records to release the record.

          If this is FileMaker GO, you may have encountered one of the more frustrating and hard to pin down bugs in FM GO. Double Click the home button, flick the FM GO icon up to quit the app and then restart to temporarily clear this error condition.

          • 2. Re: Deleting a record in a script
            Sorbsbuster

            Committing the record is of no use, as you are committing your 'opening' of the record, but it is the other user who needs to 'Commit'.  You will not be able to tag the record with anything as it is, by definition, locked.  I would suggest you create a temporary holding spot (in a 'Preferences' table, for example), and as you find a shipping record with no match, collect its ID (the 'Tracking Number'?) and build the IDs up in a list, stored in that field. You can make it neat by building up the checking date, etc.  Assuming there aren't many to check you can then find and delete them manually.

            • 3. Re: Deleting a record in a script
              philmodjunk

              Sorbsbuster, but YOU may be the one that has the record open and locked, but in another window open at the same time. Thus YOU may be the one that needs to commit records--but in the other window, not in the one where this script is running, so that's a good point that you make.

              • 4. Re: Deleting a record in a script
                Sorbsbuster

                How much of an impracticality would it be to let the locked record sit there, and delete it over the next few runs of the script, presuming that it won't be locked all day, every day...?

                • 5. Re: Deleting a record in a script
                  MarkGores

                  I had made sure no one else had the file open, so there was no one else in the record and it was only after I had run the script that the record became unmodifiable.  Since my original post I went back and looked at the scripts.  The script from shipping calls a script in the jobs folder (passing the jobnumber as a parameter) to perform the find.  Then I used some set field steps in the Jobs script to set the tracking number and date, but also set the shipping error field to "" make sure it was empty.  Apparently when I set the shipping field from the Jobs script, the shipping file maintained that the record was being modified from another source and wouldn't let me do anything else.  I disabled that step and it works fine now.

                  Original thinking was that if I got an error due to someone in a record, that shipping record would stay in shipping until it ran the next day and when I was able to modify the Jobs record it would clear the error field.  Will have to find another way of doing it.

                  • 6. Re: Deleting a record in a script
                    MarkGores

                    Come to think of it, running this way I will get an error when I try to set field Jobs::trackingnumber to Shipping::trackingnumber if someone happens to be in that Jobs record.  I will have to set an error capture there and flag the shipping record if that occurs.