9 Replies Latest reply on Dec 4, 2015 1:43 PM by szeigen

    Records missing that have no delete relationship

    szeigen

      Product and version (e.g. FileMaker Pro 13 and 14)

      OS and version Windows 2008 and 2012 R2 server with FileMaker Server 13 or 14

      Browser and version (for WebDirect only)

      Description:

      Our medical software, Practicemaker, does not allow "patient" records to be deleted. Instead they are flagged as "closed". Users do not have the ability to delete patient records...there are no delete record scripts. Each patient record has associated tables for phone numbers and insurance information connected via the key field "Chart". These tables ARE set up to cascade delete if the mother record is deleted BUT the user does NOT have access to delete records.

       

      We have been seeing across many installations that just or only just the "patient table" record is missing but none of the other linked table records. For example, if chart 121121 goes missing, the phone table and insurance table still has the records for chart "121121".

       

      If server is closed and services stopped, and the file recovered to rebuild the indexes, these records do NOT reappear. The record(s) is(are) still seen in backups, which are saved for 21 days (there are also progressive backups).

       

      Similar "dropped" records have been noted over the years but the frequency has increased in my clients.

       

      Any suggestions as to why a "mother record" would drop but linked daughter records would not besides hardware failure?

       

      How to replicate: Unable to have any relationship or any table occurrence that would cause just the mother record to be deleted and prevent the daughter records from also being deleted

      Workaround (if any)

        • 1. Re: Records missing that have no delete relationship
          TSGal

          szeigen:

           

          Thank you for your post.

           

          Is it possible the master chart record value to be modified?  That is, if someone renames chart 121121 to 123456, this would account for the phone table and insurance table to still have records for 121121.

           

          If this is only happening in WebDirect, are you seeing this occur with certain clients?  If so, what browser, browser version, and operating system are being used to connect via WebDirect?

           

          If the client closes the solution, clears out their browser cache and reconnects, do the records still appear?

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Records missing that have no delete relationship
            szeigen

            Thanks for the response!

             

            No one has access to change any key field (modification prohibited always

            checked) which is a serial number. Even if they did, there are other ways

            to find a record, such as using the patient name or birth date, etc. These

            records are just gone, and they are gone across all workstations, not just

            one. If records were deleted by script then other related records would

            also be gone, such as phone numbers (store in a separate table) or

            insurance records (separate table). Even invoices and their associated

            lineitems would also be deleted if you were able to delete a patient record.

             

            With the absence of other data being deleted, these records are just being

            dropped. Luckily I setup server to back up 21 days worth of day both

            locally and online, so we could pull one missed record if need be. BUT this

            should not be happening!

             

            With regards to closing the solution, once the record is missing, it has

            theoretically been used by multiple workstation for scheduling, billing,

            etc. The last time this happened was one week ago (Friday the 13th ...

            yikes) and there were no power outages, etc.

             

            The other area of issue is that interactive container fields with PDFs do

            not interact. For those container that do not interact, I am able to export

            the field contents and then view it in a webviewer pointed to the files

            location and that works, so apparently the webkit is working, just not with

            the field itself.

             

            Thanks in advance for all your assistance!

             

            Doc Z

            • 3. Re: Records missing that have no delete relationship
              TSGal

              szeigen:

               

              Thank you for the additional information.

               

              Since there is no way to delete a record, when a record is "dropped", does the record count decrease?  Do you notice any newly archived records?

               

              If the record count decreases, then there is obviously a bigger issue.  Run a Recover on the file.  Although not a cure-all, it may help.  If no errors are found, go back to an earlier working version of the file and import the current data to the tables.

               

              A Web Viewer and an Interactive container field use the same code.  That is, an Interactive container field is a Web Viewer.  If it works in one, it will work in the other.  Somehow, the Container field is not seeing the contents properly.  Do you have the Container field set up for external storage?  If not, create a new layout and place only the Container field on the layout and formatted for interactive content.  Does this now display?

               

              Continue to keep me updated.

               

              TSGal

              FileMaker, Inc.

              • 4. Re: Records missing that have no delete relationship
                szeigen

                Hi TSGal!

                I put the container on the layout in too formats. One is NOT interactive

                with the red arrow and the one below is set to interactive, green arrow.

                You can see the error message after 1 minute. The IP address of the server,

                to which is file is located, is the IP address that cannot be connected to.

                 

                Any suggestions?

                 

                Doc

                • 5. Re: Records missing that have no delete relationship
                  szeigen

                  Dealing with the dropped records issue, I have no idea what the record count is before the drop. I did stop server and recovered the database. The record did not reappear.

                   

                  I will be adding a routine that will capture the current record count and store it as a global on our single record preferences file. Then I will check the current count against the latest "presumed active count" and if suddenly less send up a dialog to call us ASAP. Since users CANNOT delete patient records, this warning may be of help to see that else was happening at the time!

                   

                  Aside from this, why would a record drop to begin with? Are there "database holes"? What can be done to prevent this in the future? In all the cases I have had over my 20+ years with Filemaker, it has always been one single record, never a string of records within the same local number sequence.

                  • 6. Re: Records missing that have no delete relationship
                    TSGal

                    szeigen:

                     

                    Thank you for the screen shot.

                     

                    The IP Address is a local IP Address (192.168.xxx.xxx).  Is this a single-machine configuration?  Regardless, go into Admin Console and verify the IP Address.  The public IP Address should be displayed (something other than 192.168.xxx.xxx).  Or, launch a browser on the server and go to whatismyip.com.

                     

                    Other than manually deleting a record, the only other way a record would be deleted is by a damaged/corrupt file.  Consider running a Recover on the file.  Implementing the routine to capture the record count will also help notify you when a record is dropped/removed.  Definitely keep me advised if this issue continues.

                     

                    TSGal

                    FileMaker, Inc.

                    • 7. Re: Records missing that have no delete relationship
                      szeigen

                      Thanks!

                       

                      Is there a white paper for the best practice to totally rebuild a file? The

                      patient database in question has 7 tables and dozens of layouts and as many

                      relationships.

                       

                      Doc

                      • 8. Re: Records missing that have no delete relationship
                        TSGal

                        szeigen:

                         

                        I don't know of a white paper or best practice for rebuilding a file.  I generally keep a few clones of solutions at different stages of development.  That way, if I make a mistake or need a fundamental design change, I can return to an earlier iteration.  Even with that said, if I had to rebuild a file from scratch, I would create all the tables and fields by scratch, create the relationships, and save a backup of the file.  Then, I would create all the layouts, possibly copying and pasting objects from the old file into the new file, and then save a backup of the file.  I would then continue with creating scripts and save a backup when finished.  Finally, I would import the data from each table of the old file into the new file.

                         

                        If anyone else is reading this thread, please share your thoughts and ideas on rebuilding a database file.

                         

                        TSGal

                        FileMaker, Inc.

                        • 9. Re: Records missing that have no delete relationship
                          szeigen

                          This client has another instance of a record dropping in the Patient table

                          of the Patient file, which has several other related tables. The data in

                          these other tables, using the same dropped foreign key, were still present,

                          so there is no cascading delete.

                           

                          When I run a recovery it says do not use this file and use a previous one,

                          so this is why the rebuild may be needed.

                           

                          Any help form the community would be of benefit!

                           

                          Scott Z.