9 Replies Latest reply on Oct 25, 2011 10:25 AM by datajim

    Unique records

    datajim

      Title

      Unique records

      Post

      I negelected to set up a unique field when I created the datbase so it won't stop me if i enter a duplicate record. Can I set up a unique field (that numbers the records, for instance) now that i have over 800 records or do I make a self join script using several records?  If so, how do I do that? Thanks.

      DJ

        • 1. Re: Unique records
          bumper

          Not really a problem, however you might want to verify that your existing records are indeed unique before you begin, but not a requirement.

          1. in layout mode create an ID field, number, in the auto enter tab make it a serial number, but don't check the prohibit modification of value during data entry (you will want to come back and check this afterwards.

          2. go to the validation tab and select validate always, strict data type numeric only, not empty and unique value. turn off allow user to modify.

          3. in browse mode, put the cursor in the ID field and go to Records > Replace field contents. select Replace with serial numbers, fill in as appropriate, check update entry options and click Replace.

          4. after it has run, go back to layout mode select the ID field and turn on prohibit modification of value during data entry. Check the value in next serial number update and now you are good to go.

           

          • 2. Re: Unique records
            datajim

            Thanks Bumper, That all worked, each record now has it's own ID but it will still not stop me from entering a dupe record since it's giving each new record it's own ID...I probably wasn't clear- maybe I need to use a unique combo of fields?

            DJ

            • 3. Re: Unique records
              bumper

              Well now you are talking about operator error. It is a constant problem, one space or other character will break the comparison of field data. There are a few things you can do, you can use the unique validation, you can first enter the data into a duplicate set of global fields and then do a scripted comparison before moving to the real table, or you can use a self join to alert you to a duplicate, or run a script after the fact to mark duplicate records for comparison. You just have to decide what will work best for your situation and users. But sometimes there is real needs to be duplicate data and that's where the unique ID field helps you differentiate between them.

              • 4. Re: Unique records
                datajim

                I have another file that does alert me when I create a dupe record but for the life of me I can't remember how I set it up. Maybe I used a template that already had it. I'm doing baseball cards and will end up with over 5000 records. I'm thinking if i used the Brand+Year+Card# fields that would do it. How would i set it up?  Thanks, DJ

                • 5. Re: Unique records
                  bumper

                  Create a text field (FMP doesn't validate a calc field), auto enter a calculation, Trim(Brand&Year&CardNo) I wrap it in the Trim function because an extra space seems to be the number one cause of this method failing.

                  Set it to require a Unique value, and create a message to alert you, for what it is worth FMP doesn't do a very good job with the native error message, but there is no getting around it, you will be given the options to revert or die.

                  For all the records you currently have you will need to force the calc into the validate with your new friend Replace, for new records it will automatically fill it in.

                  There are some nifty Knowledge Base articles, 6186 Using Self Joins and Using a script, 3441. There are probably others but this will get you started.

                  I forget to mention in my first response NEVER EVER do a replace on the only copy of your file, always make a back up and set it aside in a safe place. Sometimes bad things happen with good functions, and Replace is usually the one.

                  • 6. Re: Unique records
                    datajim

                    That seemd to work fine, many thanks!

                    DJ

                    • 7. Re: Unique records
                      datajim

                      Sorry Bumper- didn't work- now it alerts me that any record i imput is a dupe! 

                      For the field I have: text field; in auto enter: calculated value: Trim ( Brand + Year + Card Number ). Nothing else in that window. In Validation: Validate always; allow user to override; unique value; validated by calculation (same calc)...At this point i'd have to delete that field in order to enter data. I've tried to use it without the validation calc but no go. Help!

                      • 8. Re: Unique records
                        bumper

                        Don't use plus signs + use ampersands & between the fields and no spaces look at my calc.

                        The plus sign is for math, the ampersand is for concatenations of text.

                        Take out the validate by calculation. The "must be unique" will do the validating for you.

                        • 9. Re: Unique records
                          datajim

                          I see-no wonder it didn't work. I'll try that. Thanks Bumper for all your help and patience. This is a terrific resource and learning tool- glad you're here.

                          DJ