13 Replies Latest reply on Feb 7, 2011 1:03 PM by philmodjunk

    Locked up on Find

    njem

      Title

      Locked up on Find

      Post

      FM locked up with a progress bar on a Find. I had to use task manager to get out. This has been working in development (one user at a time) this is the first time several regular operators were running it. It locked up on the find on one station. Another station could get around in the ap but when I tried the same find it locked up. Same thing on the "server". FM11 Pro. Not running server just several seats with one running as the "server" and sharing the db.

      This is the find to filter out dupes. So I have the table Captains and a dupe of that Captains2 with a self relationship. This is for a non-profit recurring bowling fundraiser and each captain may have several teams. I want a list of all captains but each captain just listed once.

      So I have a field that is a text field with auto enter that combines captains Last & first name, validated during data entry only. It has "all" indexing.

      I have a similar field that also adds the team name. Same field set up otherwise. This is the unique key of the db.

      Captains table and Captains 2 have a relationship on the LastFirst name field. The relationship says that Captains2 should be sorted by LastFirstTeam. So the first LastFirstTeam it should encounter should be the alphabetically first team (team "A"). Actually it doesn't matter much. I don't care which team of a given captain is listed, I just want each captain listed once.

      Finally there is a calculated CheckDupes field If Captains:LastFirstTeam = Captains2:LastFirstTeam then = "Unique" else = "Duplicate".

      The Find is simply looking for CheckDupes to equal "Unique". The Find happens by script when they go to that layout. It was then that it locked up.

      FYI this captain list is not a report. It is how they look for close spellings or misspelled, and how they choose which captain record to go to to see the full details in another layout.

      Is there anything wrong with my setup?

       

        • 1. Re: Locked up on Find
          philmodjunk

          Performing finds on an unstored field such as the calculation field or a field rom a related table will always be much slower than performing the find on a local, indexed field. That seems to a factor here.

          Maybe you can get this to work if the field of is of type text and is an auto-entered calculation. This may or may not work depending on how you want this field to update. Note that a looked up value setting that enters "unique" if there is "no exact match" may accomplish the same result more simply. The catch here is that modifications in the related table will then not modify the value in this field--so you may have manage changes to the name field with a script trigger or something.

          There may be other factors involved here, but would need to see the script to be sure.

          • 2. Re: Locked up on Find
            njem

            Phil, this may take a little back and forth to figure out. I thought this was exactly how you suggested filtering dupes? And it has worked up to this. Though I could easily have some detail wrong. If I have the general idea right, do I have the details right? The join is on the not-unique LastFirst name. In the relationship details Captains2 sorts by the unique LastFirstTeam. The calculated field compares for a match in LastFirstName.

            Note that this is not a related table in the usual sense, this is a self join. I assume what FM does in a self join is NOT create another table but just another index.

            Note that FM will not let me store or index the calculated field CheckDupes, I guess because its calculation involves another table (sort of), the self join.

            I'm not sure how this CheckDupes could be a text field with auto-enter since the calculated result is a function of its self-relation.

            I'm not sure how the "no exact match" thing works or if it applies.

            In trying to debug I replaced the script with a two line script, go to the captains list layout, do the find for one criteria, CheckDupes="Unique". I have worked around by not doing the find, but a show-all, so now the list they look at is much longer and cluttered.

            This wasn't just slow, it was locked up. Esc wouldn't. On one workstation it sat like that for an hour before I could get there to look at it. It had a progress bar that showed no progress. One system had the windows hourglass. The "server" showed the FM coffee cup. All systems could get to other apps and work generally but I could not get out of FM or escape the Find.

             

            • 3. Re: Locked up on Find
              philmodjunk

              Note that this is not a related table in the usual sense, this is a self join. I assume what FM does in a self join is NOT create another table but just another index.

              No, but calculation fields by definition are unstored if they refer to a field via table occurrence as that other record could be updated at any time and if this field were stored, it would not update correctly.

              Can you post the complete script involved here? It's not impossible that having two or more users running the same script may be interfering with one another.

              • 4. Re: Locked up on Find
                njem

                Not sure how to literally cut and paste script steps to notepad but here it is:

                Go to layout["Captain List" (Captains 2)]
                Perform Find [Restore]

                If I open the Perform Find to look at what it is set to find it's this:
                Find Records Captains 2::Check Duplicates: [unique]

                 

                • 5. Re: Locked up on Find
                  philmodjunk

                  I don't see how this script can completely lock up as you describe. Since you are searching a related field in Captains 2, this will be much slower, but it should resolve eventually. You may want to check it for damage to be on the safe side.

                  Let's see if we can avoid searching on a related record.

                  I forget the details of how we set this up originally. So let me know if any of my assumptions are wrong here.

                  I'm assuming you are trying to eliminate Captains records that are 2nd and subsequent instances of the same first/last name.

                  A Self Join relationship, can match to any other records in the same table with the same name:

                  Captains::FirstName = Captains 2::FirstName AND
                  Captains::LastName = Captains 2::LastName AND
                  Captains::IDnumber  ≠ Capatians 2::IDnumber

                  If so, you can define a text field, Unique to look up a field such as Captains 2::FirstName. Select the "If no exact match, then use: ZZZUniqueZZZ" option on this look up. (The last pair of fields requires an auto-entered serial number field and is used to keep a record from matching to itself.)

                  When a new record is created in Captains, if it duplicates an existing record, the first name is looked up from that record. If there is no matching record, ZZZUniqueZZZ is entered. You can then search for records with this text to find a unique instance of every captain record.

                  The only catch here is that you'll need to run a script to delete a Captains record that checks to see if ZZZUniqueZZZ is in Unique, if so, the script has to find all records with this first, last name and set the Unique field of one of them to this same text.

                  Please also consider if you have any use for a new table where you have one record for each "captain" or each participant. Such a table has many potential uses for a relational database like yours and if you have a use for it, you may be able to use that table and completely avoid the need to omit any duplicates as they will not exist in this table.

                  • 6. Re: Locked up on Find
                    njem

                    I've been playing around with this. For one, in off hours I've put things back as they were and tried going through the same steps from various stations that are in it at the same time to try to recreate the lockup. I can't. I don't trust that it won't happen again but I can't debug or narrow down the conditions that cause it.

                    This all sounds very fragile. As a variation on your suggestion I considered a one-time process to sort in the right way then script a run through them all that saves the previous record values in variables and figures if the current name equals the previous name then this is a dupe. Set a permanent text field to say that. On adding any new record mark it as unique. On duplicating a record mark it as a duplicate. But it gets touchy like you say if there are deletions or just if someone corrects a misspelled name.

                    It certainly seems like the original structure should work but now  I don't dare use it.

                    I think my users are just going to have to put up with a list that is long and full of duplicates.

                    There's something to the idea of a table of captains, one entry each, and then a related table of teams. That's actually what the structure should be. This was a holdover from the structure as it was done in Access which I'm recreating in FM. Redoing something so fundamental now is a bit much. Maybe next year.

                    I'm assuming users don't affect one another. As I said this is not FM server, just one FM station sharing for the others to access. Users migth resort a table that another user might need in a different order for something like this "unique" trick to work. Or users might change the value of a global storage field that another user needs a different value in. I don't even want to know what all FM does under the hood for all that, but I do want to know that I'm safe in assuming that what users do has no affect on one another?

                     

                    • 7. Re: Locked up on Find
                      philmodjunk

                      I'm assuming users don't affect one another.

                      Actually they can interfere with each other as can happen with any shared database system, including Access. Can't expand on that without going "under the hood" here a bit.

                      When one user clicks into a field and starts editing, that record is locked to edits from any other users--both from other users making edits and also from scripts. That's one of the reasons I wanted to see the script. I was concerned that your users might be trapping one another with locked records--but that doesn't seem to be the case here as the script only does a find, it doesn't modify data.

                      Global fields, on the other hand, have bee specifically designed so that users do not interfere with one another via changes to global fields. When a guest opens the database (any user that isn't using the copy of Filemaker that's hosting the database is a guest in this context.), they get the current value of any global field as stored from the last time the field was edited on the host machine. Any changes they make to these global field's values will not be seen by other guests nor will changes by others by seen by this guest. When the guest closes the file, the values in the fields will revert back to those from the host--so any changes made by a guest will not be retained.

                      Another way users can interfere with one another is that many actions require that the host computer and FileMaker process the data in ways that can place demands on the host computer's hard drive and CPU. If several users initiate such actions at the same time, the resulting "load" might bring all your users to a halt as they wait for the host computer to process all the stacked up processing tasks. I think that's what's happening for you and why you couldn't replicate the issue by yourself. That's why I suggested finding a way to eliminate the search on a related field, BTW, as that reduces the needed processing by quite a bit. Upgrading to Server, though expensive, might also alleviate the delays due to it's increased capabilities.

                      • 8. Re: Locked up on Find
                        njem

                        I don't think this was a case of slow processing. There were at most three users running it. As mentioned the first one to have the problem was stuck for an hour with no progress showing on the progress bar. At the server I could move around to other programs, even move around in FM and the system did not seem slow. I didn't think to look in task manager how much resources FM was taking. It was only when I tried to go to that same captains list that I got the same thing, stuck at Find with no progress, and esc didnt' help. Less than 2000 records by the way.

                        Do guests sorts affect one another or is FM keeping indexes for each guest? With this self-join eliminated the dupes thing for instance it is dependent on Captains2 being sorted so it finds the first instance of LastFirst name so it knows that is the unique one and the others are dupes. If guest A goes to that list and the script runs and then they are looking at this layout based on Captains2 and they decide to resort, then when guest B tries to go to that layout and that script runs, if they don't have separate indexes they're going to mess each other up. Seems they would have to have their own indexes.

                        So global fields are safe from guests affecting one another, what about calculated fields? If I have a calculated field FoundIt that is set by, if text field LastFirst = glaobal field OneImLookingFor then Z = 1, else 2. Guest A might set the global field to equal this, and guest B might set it to equal that, so the calculated results of Z would be different for each.

                        Getting back to locks: I see that if guest A has a record in mid-edit and guest B trys to change the same record (or actually when they go to commit it) they get a notice it's locked by whomever and a chance to send them a message. I can't try that by script right now. Does it do the same thing if a script tries to change a locked record?

                        That only happens if the script wants to modify a "real" field in a locked record, not a global field, or not a calculated field that might change because some global field did, as with my FoundIt field above?

                        For the most part I can't imagine my scripts changing data in a "real" field in a record other than the one being worked on. I suppose some management script to, for instance, reset some field in all records. So what kind of scripts do people most typically get into this kind of problem with that I should watch out for as I make mine?

                        We probably will do the anual subscription for 10 seats and server for $1000 next year.

                        • 9. Re: Locked up on Find
                          philmodjunk

                          Do guests sorts affect one another or is FM keeping indexes for each guest?

                          Stored indexes will come from the host and thus will be the same for each, I believe.

                          what about calculated fields?

                          Since calculation fields that refer to a global or related field are by definition unstored, these fields evaluate only as they are displayed on a layout and thus this field will display values specific to the values that user has assigned to the global field.

                          Scripts that try to change a locked record will be prevented from modifying that record. I don't think the script will be interrupted by an error dialog inn every case though. (A check of Get ( LastError ) can check for this.)

                          I don't think record locking is a factor here since all this script does is perform a find.

                          On the other hand, it wouldn't hurt to use Recover to first check for file damage and then if no damage is found, use it again to re-index all your fields.

                          If you have FileMaker 11, you can use Advanced Recovery options to rebuild your file's indexes:

                          1. With the file closed, select Recover from the File Menu.
                          2. Select "Use advanced Options"
                          3. Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".

                          • 10. Re: Locked up on Find
                            njem

                            I did some experimenting while people were off line. It seems FM must do pretty well at keeping indexes separate. For instance the Captains2 table that is used to find dupes, and what it declares as the unique one is dependent on order, I couldn't affect it at all. Either by reordering that table by another guest or even by reordering it as I was viewing it on the host. It seems the index used for the relationship is kept as is regardless, and one guest's sorts didn't affect the others.

                            The locked record error may not show up on all scripts that run into it but the one I had handy, a management function to clear a field in all records, did give a locked record error.

                            Ran recover as described and it didn't find any problems.

                            • 11. Re: Locked up on Find
                              philmodjunk

                              I don't think sorting records modify the indexes in any way. (Editng the indexed field, deleting, creating records would do that.)

                              Did you test the recovered copy to see if it locked up on the find the same way? (Recovering the file also rebuilds all the indexes in the file, so the index rebuild might fix things here. (And in FileMaker 11, there's a way to use recover to rebuild all the indexes without having to do a full up recover of the file.)

                              • 12. Re: Locked up on Find
                                njem

                                I don't get the distinction you're drawing between sorting and indexes. Sorting is indexes unless FM is doing something unusual.

                                I have never been able to recreate the lock up. The only way to likely recreate it is to have multiple operators using it with the original set up to filter out dupes, and I can't inflict that on them just for diagnosis.

                                • 13. Re: Locked up on Find
                                  philmodjunk

                                  Sorting is based on the current found set and that is specific to each user, the current layout's table occurrence and the window that's open. While the index is used to help sort the records, sorting does not modify the index in any way. A single user can, in fact, sort the same set of data in several completely different orders all at the same time by using either different table occurrences that refer to the same data source table or by opening several windows to the same layout and performing a different sort on each.

                                  The index is a data structure (a binary tree, I believe) maintained by FileMaker on every indexed field that stores one unique instance of every value that exists in at least one record of the table and in an ascending order specific to the data type of that field. Since this index is based on the data in the actual table all users access the same index but only user actions that modify or add a value in the indexed field will modify the index.

                                  I'm not suggesting that you subject your users to such a test, but you may be able to recreate the issue if you can get to several networked computers not in current use and trigger the same script on them simultaneously. There are ways to use Install OnTimer to get all the client machines to perform the same script within less than a second of each other. If such a test locks up from the original file, but not from the recovered file, then it is very likely that you have a damaged index as the recovery process rebuilds the indexes. (If this is the case, don't use the recovered file with your actual users, but do get a copy of the file and rebuild all its indexes either by using advanced recovery options (fileMaker 11) or by importing all your data into a clone of your file.)