4 Replies Latest reply on Sep 2, 2016 2:37 PM by philmodjunk

    Unique combination of two fields?


      Hi, i'm a newbie user.

      I have a table of two fields, "P/N" and "Rev.".

      I want that cannot exist two records with same P/N AND Rev.


      I was thinking to solve creating a third field as auto-calculation, P/N&Rev concatenated and set as unique this field.


      Is this the best solution?

      Are there other ways?



        • 1. Re: Unique combination of two fields?

          that would be my method of choice

          • 2. Re: Unique combination of two fields?

            Agreed. By far the most efficient.

            • 3. Re: Unique combination of two fields?

              It's a fine approach. The downside is that you can't control the validation error dialog. You can set the message, but it's not dynamic, and you can't script the dialog's buttons. So it can be a jarring user experience.


              If you're using some kind of controlled UI, e.g., where the user selects from a list, or clicks a Submit button, you could easily script a Find and then offer the user a more graceful fail.

              • 4. Re: Unique combination of two fields?

                There are a number of methods, You can also commit records with error capture on, check for the error code and then your code can provide the user with a graceful way to handle the problem. I'd definitely keep the built in validation that you describe as "insurance" in case the User Interface has some "loophole" that might allow the duplication.


                And many UI schemes can preclude the possibility of getting a duplicate value pair in the first place. For example, a relationship that matches by these two fields can be used to match to an existing record if one already exists and automatically create a new one if it does not, but won't allow data added via the relationship to create a second record with the same value pair.