4 Replies Latest reply on Nov 3, 2011 12:05 PM by philmodjunk

    More Newbie Comments

      Title

      More Newbie Comments

      Post

      Having used FMP for a few days, I am surprised (or haven't yet found...) that it's not easy to:

       

      1. Remove deleted records - that is, EXCEPT for the unique one (through the interface without creating some type of self-referential join, etc.). For example, when I import non-relational Excel data, it has hundreds of duplicates. This should be simple to do. Just search using "!" and then select remove (all but one) deleted records. Perhaps the interface would have to prompt you for which unique record to leave (latest, etc.).

      2. No raw data view that I can find. When I had to re-import all the Excel data, my portal stopped working. Browse mode doesn't show the "ID" fields in the database. I'm so used to be able to just do a "SELECT * FROM ...." and see everything, I'm baffled how to just do a raw data view.

      The Manage database still shows the matching "ID" fields matched between the two tables, but the portal is broken. Debugging is therefore difficult.

      Since I'm an Oracle/MySQL developer, I'm not sure I'm thinking about FMP the right way yet. I would love to see a way to just drop down to a command line and do things like I'm used to doing rather than the endless menus, modes, etc..

      Thanks in advance for any suggestions.

       

      - m

        • 1. Re: More Newbie Comments
          philmodjunk

          1) If you want to filter out duplicates during an import, this is very easy to do. So easy, that I sometimes import data just to eliminate duplicates.

          Set unique values, validate always field options on the field that contains possible duplicate values. Then import your data. The duplicates will be automatically filtered out during the import. The only catch is that FileMaker is keeping the first record with the duplicate value and blocking the import of subsequent records with the same value so you have to order your records so that the record you want to keep is listed first in the file from which it is imported.

          2) No Raw data view...

          It's easy to set up a table view of your data on a layout based on the table which will receive your imported data. Immediately after import, you'll see all the records you have just imported and can easily examine all or a subset of the fields of these records. Show All records will then pull up all the records if you want to see all that are currently in the table.

          The Manage database still shows the matching "ID" fields matched between the two tables, but the portal is broken.

          Likely, the portal is broken because your imported data does not contain the correct matching values needed in order for this to work. If you enable auto-enter options, newly imported data can recieve a new serial number during import. If the imported data must match via ID to existing data in another table, this ID must be imported with the import process or you must find a way to match your records without using this ID and then copy the ID from the matched record(s).

          • 2. Re: More Newbie Comments

            Wow, thanks! I didn't see how to eliminate dups so easily.

            Yes, I had to reset the numbering on the import "ID" field. I thought of that after I did the posting.

            Definitely a learning curve here, but a powerful program.

            Thanks very much for your reply!

             

            - m

            • 3. Re: More Newbie Comments
              LaRetta_1

              Also keep in mind that, if using the ADD records with the ID field set to validate unique always, it will increment your auto-enter serial even if the additional records are NOT imported.  What this means?  If you import 2,000 records to hopefully catch the 100 that aren't existing in your table, it will properly import those 100 but it will increment the serial counter by 2,000.  So your auto-enter serials will have major holes in them on any record skipped by system:

              204 .... then import happens
              205 - 1000 are already in system so next number will be
              1001 ... and so on.

              It wastes a lot of serial numbers and, although serials do not matter, if you are working on a system tightly controlled and you have no deletions, seeing a sudden hole of 2,000 records in your serials would stop ones heart.  It can also mean you will run out of serial numbers before the year 2314 like you had planned, LOL.

              Reason this happens:  To validate if a record is unique, it must be imported first.  If it is imported, FileMaker increments the number because it is auto-enter and is not wise enough to recognize, when validation fails and it kicks the record back out, to reverse the increment.

              • 4. Re: More Newbie Comments
                philmodjunk

                A very good point!

                If necessary, and if you take proper precautions when setting this up not to introduce duplicated values or other errors, you can also use Replace Field contents to assign or change serial number values for you newly imported records in order to eliminate the "hole". And you can also use the same script to reset your next serial value setting for the field as well.