Skip to main content
Question

prevent duplicate records - combined key

  • Oct 23, 2013 12:05 AM UTC
  • 0 replies
  • 192 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
    • Qrew Corporal
    • Oct 23, 2013 12:07 AM UTC
    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
    • Qrew Member
    • Oct 23, 2013 12:09 AM UTC
    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
    • Observing
    • Oct 23, 2013 12:34 AM UTC
    makes sense. Thanks
    • DavidDavid4
    • Author
    • Observing
    • Oct 23, 2013 12:35 AM UTC
    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."
    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.
    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