3 Replies Latest reply on Jun 18, 2015 3:46 PM by philmodjunk

    New Record comes loaded with portal data from unrelated record

    ac311

      Summary

      New Record comes loaded with portal data from unrelated record

      Product

      FileMaker Pro

      Version

      13

      Operating system version

      Mac OS

      Description of the issue

      I'm having a problem creating new records. Happens randomly. My layout has a portal. When I create a new record, the new record portal comes with data from an unrelated record. Unfortunately when I delete the new record, it wipes away all portal data from the original record. Any Ideas?

      Exact text of any error message(s) that appear

      No Error messages

      Configuration information

      The table relationship is set to allow creation of records/delete related records via relationship.

      Workaround

      No workaround

        • 1. Re: New Record comes loaded with portal data from unrelated record
          philmodjunk

          Better check the values used in your relationship match fields. It would appear that the new layout record already matches to a portal record. This might happen if your primary key is an auto-entered serial number and you imported data from one copy of the file into a backup or a new version release of the file without correctly updating the "next serial value" setting on the field.

          That's not the only option, but one that strikes me as most likely.

          You might want to enter find mode, put a lone ! into the ID field for your layout record and perform a find to see if any records with duplicate values are found.

          • 2. Re: New Record comes loaded with portal data from unrelated record
            ac311

            Hey Phil, I think you nailed it. I just reviewed the list of items and I do have lots of duplicate ID numbers.

            That table/layout is a heavily used part of my database and you are right I created a new database and imported data from an older version of it. 

            So here's my follow-up question to your statement:

            "you imported data from one copy of the file into a backup or a new version release of the file without correctly updating the "next serial value" setting on the field."

            I assume that there is a setting under File Manage Database in the Tables Tab, in the ID field options to correctly update the serial value. Can you give me specifics on how to do that so there are no further duplicates? 

            Also.... Any suggestions as to how I can modify existing duplicate ID numbers? I think there are hundreds of duplicates.

             

             

             

             

             

            • 3. Re: New Record comes loaded with portal data from unrelated record
              philmodjunk

              You've got a real mess on your hands.

              First, if you open the auto-enter tab in file options for your ID field, you'll find a box where the next serial value is specified. If you sort your records by this ID fields (Hope it's a number field and not text...), you can easily look at the last or first record to see the largest ID in your table so as to be able to specify a larger value as the "next serial value". In scripted imports there is a script step that sets the next serial value as well so the same basic method can be used.

              But fixing the duplicate values will be a major pain. You can perform a find using the ! operator to find duplicates and then you can sort on the ID field to group them. But fixing the problem then requires comparing parent and related child records in order to figure out (if it is even possible to do so) which records to change and which not to change.

              I once used a technique of finding all child records on one layout first, then going back to the layout for the parent records an modifying the ID of one number by adding a decimal value. A value of 1003 became 1003.1 so that both parent records now had different IDs. Then I went to the layout with the child records and added the same .1 to those child records that I could tell belonged to the parent record that I had already renumbered.

              This takes time and requires some method that enables you to tell which child record go with which. If you have old back up copies made just before this happened, you might be able to use them to identify which records should match to which here. I was lucky in that I had records that for which users were not permitted to edit them and a number field stored a total computed from the related line items so I could use that value to confirm that I had correctly parsed the records back into their original relationships.And I was able to do at least part of the fix via a looping script.