6

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?

3
  • 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, 2012 at 18:24
  • Can you not use TEXT( your_date_field__c ) ? Oct 29, 2012 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? Oct 29, 2012 at 18:36

2 Answers 2

6

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.

1
  • Glad it helped :) Oct 30, 2012 at 15:16
4

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
  • 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, 2012 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. Oct 29, 2012 at 22:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .