Validating a new child object against existing ones.
Hello. First post so please be gentle.
I have a custom object (Codes) with 2 fields in it, a picklist and a free text field. The picklist has 9 possible values in it. There is a master detail relationship between the Account (Master) and the CustomObject (Detail) and they are shown on the Account view as a related list. In order to add a new one the user simply clicks 'new', selects the type from the list and adds a code in the free text and clicks save. All of this is fine but now I need to add a business rule.
Rule: One Account cannot have more than one code for a selected code type.
I am trying to approach this using a validation rule on the detail object on save. I have been told that is could be possible to use a VLOOKUP or something. I need to check the selected code type against existing detail objects for that master to make sure it hasn't already been allocated a code.
Any help would be fantastic and very much appreciated.
Thanks in advance.