Forum Discussion

SumedhaWeerasur's avatar
SumedhaWeerasur
Qrew Cadet
7 years ago

Forcing a text field to be unique in two tables

I have a serial number field in two tables. I have set these two fields to be unique.  These two tables are not currently related to each other but they can be if needed. 

Both tables use the default record ID as its key field. 


When a record is being added in either table, i would like for my application to check other table to see if the serial number exists. IF it dose exists. id like it to be prevent me form adding a record or at least let me know that the serial number exists. Basically i want to keep serial numbers unique in both tables and not just one. Is there a way to do this?  
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    In order to do that, the serial number would need to be the Key field in at least one of the tables.  Then you would make a relationship between the tables based on a new field called [Serial number mirror].  The formula for that field would be [Serial number]

    Then on the parent table, you would be able to summarize the # of children and if > 0, then put up a big red error message saying that the serial is not unique enough.

    Then on the parent table create a formula checkbox field with a value of TRUE.  call id [Serial exists in Table A]

    Look up that checkbox field to the child table.

    Then put up a big red error message if that lookup field is checked.