How to prevent duplicates when using date field?

Question

I have a very simple custom object with just two custom fields, a dropdown yes/no and a date. This custom object serves as a related list for account and should get multiple entries with the combination of date and yes/no.

But how to prevent duplicates. I only want one entry per date. For other custom fields, I could set Do not allow duplicate values, but for date fields, this option is not available.

Are there any easy workarounds, not forcing me to code triggers?

Answer 1

Create a hidden text field and mark it as unique when you're creating the field.

Create a workflow to copy the Text of your date field concatenated with the AccountId (and maybe yes/no) to this new field. Set the workflow to fire every time the record is created or edited and if your date field is not null / changes.

I have a feeling Text (DateField) works

AccountId + TEXT(DateField__c)

but failing that you can construct a string using

 AccountId + TEXT(Year(DateField__c)) + TEXT(MONTH(DateField__c)) + TEXT(DAY(DateField__c))

What this mechanism does is it causes an error to surface when the workflow attempts to insert the same date string in the hidden field which is unique.

Answer 2

Assuming it's the combination of Account and CreatedDate that should be considered unique I'd do this:

• Create a new Text field, Dupe Checker
• Set the Unique flag on the field
• Create a new workflow rule with the following simple condition: TRUE
• Add a new workflow field update to the workflow, setting the Dupe Checker field to something like Account__c & YEAR(Date_Field__c) & MONTH(Date_Field__c) & DAY(Date_Field__c)

The syntax is untested, but the theory should hold.