Skip to main content
Question

prevent duplicate records - combined key

  • October 23, 2013
  • 0 replies
  • 99 views
I have a bridge table between two entity tables; a many-to-many relationship. For example, Beers, Distributers, Beer_Distributers.

I want to prevent duplicate entries in the Beer_Distributers table. A duplicate entry would be one where two records that have the same Beer_id and Distributer_id
    This topic has been closed for replies.

    Forum|alt.badge.img+14
    • LauraThacker
    • Registered
    • October 23, 2013
    Build a formula-text field concatenating the [Related Beer] & "-' & [Related Distributor] and then make that field Unique.
    Laura Thacker | laura@intelligentdbs.com | (626) 771 0454
    Forum|alt.badge.img+6
    • MarkShnier
    • Registered
    • October 23, 2013
    No problem
    Make a text formula field with the formula

    List("-",totext(Beer_id]),totext([Distributer_id])

    .. and turn on the Unique checkbox for that field.

    call the field something like [Same BeerID - same Distributor ID] so that you error message is meaningful to the end user of your app. Try entering a duplicate, observe the red error message and edit your field name to make the error message instructive to the user.
    • DavidDavid4
    • Author
    • Registered
    • October 23, 2013
    makes sense. Thanks
    • DavidDavid4
    • Author
    • Registered
    • October 23, 2013
    makes sense. Thanks
    I have the same issue, but the solution doesn't work. i get this error "This formula is incompatible with this field being marked unique. Please change the formula or uncheck the unique checkbox."
    • FreddyFreddy
    • Registered
    • October 6, 2017
    Same issue here
    I'm guessing that you are using a lookup field in your formula.

    You may not make a lookup field part of the formula.  for example instead of using [Customer Name] in the formula (which is a lookup field), use [Related Customer] which is not a lookup field.

    If that is not the problem I suggest that you post a new question and explain what field types you are using in your formula.
    • FreddyFreddy
    • Registered
    • October 6, 2017
    I did use a lookup field from a parent, the [related field] only showed the record ID instead of the name. I made the name the Record ID in the parent table and now it works. The parent table can't have duplicate names.

    Thanks for the quick reply!
    Terms & ConditionsAccessibility statement