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?

  • I tried to solve the problem with an invisible text field that gets updated with data when entering a new combination, following this advice. It failed however, as I cannot update a text field (which prevents duplicates) with a date entry. Is there any way to convert date to text? – mcbetz Oct 29 '12 at 18:24
  • Can you not use TEXT( your_date_field__c ) ? – Phil Hawthorn Oct 29 '12 at 18:29
  • I am working on a solution with VLOOKUP, almost done. BTW do you mind your Name field value being hijacked with a the date value? What is your Name field currently Text? – Andrew Fawcett Oct 29 '12 at 18:36

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.

  • Glad it helped :) – techtrekker Oct 30 '12 at 15:16

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.

  • 2
    ISNEW() || ISCHANGED(Account__c) || ISCHANGED(Date_field__c) ;) Besides it seems it's not for NOW() but for some "Date_Field__c" – eyescream Oct 29 '12 at 22:13
  • @eyescream, good catch. I assumed it would be the date the record was created, but after re-reading the post it's obvious you're correct. I've updated my answer to include this. – JLiljegren__c Oct 29 '12 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.