11 Replies Latest reply on Dec 18, 2013 12:20 PM by philmodjunk

    Add a Note Content Issue

    JosephJurson

      Summary

      Add a Note Content Issue

      Product

      FileMaker Pro

      Version

      10

      Operating system version

      Mac

      Description of the issue

      I am thinking of upgrading my version Filemaker 10, but if I can not get a solution to this serious problem, I will seriously consider finding another database product after using Filemaker for over 15 years.

      I have created records in a small separate file and included content in Add a Note. When I import this first smaller file into the larger main file, much of the content in the Add a Note section does not import correctly. Add a Note from some records are missing, other times content from the Add a Note content from existing records end up repeatedly in the imported records. And when I open and close the larger file, there are times when the Add a Note content in different records is either missing, replaced, or added to what was already in place.

      This problem is only in the Add a Note section of each record.

      Thanks for your quick attention to this matter.

        • 1. Re: Add a Note Content Issue
          philmodjunk

               Do you mean the Comment option found for each field in a table?

               Or is this "add a note" something else?

               If it is something else, you'll need to describe what it is in much more detail.

               If these are field comments, you may want to try recovering the file and importing from the recovered copy of the file.

          • 2. Re: Add a Note Content Issue
            JosephJurson

                 I am referring to the Add a Note part of the Notes section of the record template.  I have included a screen grab for review.

            • 3. Re: Add a Note Content Issue
              philmodjunk

                   What I see there is a portal to a related table for adding notes. "add a note:" is the label text on a button that presumably creates a new related record in the portal for recording a note about the contact.

                   When you import your data, you'll will need to import both the data from the contacts table and the notes table. If the match fields used to define the relationship do not get the correct values as a result of the import, the data in the portal won't show or you might see the wrong notes with the wrong contacts.

                   One detail to look out for is that the contacts table is probably set up to auto-enter a serial number as the match field to other tables such as the notes table. If you import data from a different file, you will also need to update the "next serial value" setting for this serial number field to be larger than any serial number in your table after you have imported the data or new contacts will have ID numbers that duplicate existing contact records and then you'll see notes for both contacts in the same portal as the contacts have the same ID numbers.

              • 4. Re: Add a Note Content Issue
                JosephJurson

                     I appreciate your help, but you are using a vocabulary that is unfamiliar to me.  I have attached a screen grab of the import screen with the hope you can advise me about which setting needs fixing.

                      

                      

                • 5. Re: Add a Note Content Issue
                  philmodjunk

                       K_ID_Contact is almost certainly defined as an auto-entered serial number. It sounds like you imported data into the new file without opening up Manage | Database | Fields, opening field options for this field and updating its "next serial value" setting. Thus, when you create a new record, it auto-enters a serial number value that duplicates that of an existing record and you see notes for that other contact record appearing with your new record. If you then add a new note, you'll see that note when you are on either contact with the same value.

                       Here's a quick test to see if this might be the case:

                       Enter layout mode on the layout you posted earlier. Use the field tool to add K_ID_Contact to your layout. Change over to find mode. Put a lone ! in this field. That's an operator to find duplicate values. Perform the find and then use Sort Records to sort your records by K_ID_Contact. Then flip through your records and I predict that you'll see records with duplicate ID numbers. I could easily be wrong, but from what you have posted, I think you'll find duplicate values.

                  • 6. Re: Add a Note Content Issue
                    JosephJurson

                         Well, I went through the exercise you suggested and of the 3848 records in the database, the sort with the lone ! found 1048 records.  Which must mean I have 1048 records that have a duplicate K_ID_Contact number and to fix my problem from here on out I have to assign new K_ID_Contact numbers to the 1048 records, starting with the highest K_ID_Contact number that presently exists. 

                         Then when I import the new records from the small files to the big main file, I should set the info as it is in the attachment - where 5047 was the last K_ID_Contact number.  Right?  Anything else to check?

                         Any explanation why only the Notes section of the record had mixed up content?

                    • 7. Re: Add a Note Content Issue
                      philmodjunk

                           As I mentioned earlier, with non unique ID's the notes may be showing notes from more than one contact record with the same ID number.

                           Replace Field Contents can be used to both assign serial numbers and to update the auto-enter setting at the same time, but if you assign a new ID number to an existing record, you will lose the connection to all existing notes records for that contact.

                           This sounds like you may have imported the same contact record more than once or at least imported a contact record for a contact already present in the file.

                           Do you have back up copies of your files, perhaps made just before you imported any records? It may be simpler to revert back to those back up copies and re-import your records But if you have entered additional data since the back ups, that data will be lost by doing that.

                      • 8. Re: Add a Note Content Issue
                        JosephJurson

                             Here is how we got to this moment:

                             Each week, on a separate computer, in an empty clone copy of the database, an assistant is searching out new contacts.  When 50 contacts are reached, I import the new 50 contacts small file into the main large database file.  (Never knowing about what K_ID_Contact means to this process.)

                             Simultaneously, I am working on the large main database - adding and deleting records and adding notes about any interaction with a contact.

                             Sounds like the only solution at this point is to reassign a new ID number to each record, starting at the beginning with a number larger than any possible number in the existing database.

                              

                              

                              

                        • 9. Re: Add a Note Content Issue
                          philmodjunk

                               That may in deed be the case. But you will lose a great many notes records when you do so and this only fixes things until the next import.

                               If you were using FileMaker 12 or newer, you could redefine K_ID_Contact to auto-enter Get ( UUID ) and then the values imported from the other copy will already be unique. but this is not an option you can use in FileMaker 12.

                               I suggest that you define K_ID_Contact to auto-enter a serial number, but with a Next Value that includes a letter as the first character in one of the two copies of the file. Then make this a text field instead of a number field unless it is already defined as text. That added letter will insure that your imported records have ID's that won't match to records created in the target file since the last import.

                          • 10. Re: Add a Note Content Issue
                            JosephJurson

                                 So, if I upgrade from Filemaker 10 to Filemaker 13, I will avoid this problem in the future?  And will Filemaker 13 give me a way to get to a clean solution faster? 

                                  

                            • 11. Re: Add a Note Content Issue
                              philmodjunk

                                   FileMaker 12 and newer offers an additional way to uniquely identify records in tables that will help for this situation. But your data is well and truly scrambled by what has taken place with these data imports. Upgrading will not magically unscramble what you have, it will only help you avoid additional scrambling of the data and that only if you make the needed design changes to your file to prevent it.

                                   And this can be done just about as simply with the alternate method that you can use with your current version.