Search code examples
salesforceformulasalesforce-lightningsalesforce-communities

Validation Formula throws an Invalid Syntax Error - Salesforce

I am trying to create a validation formula in order to perform the below logic.

  • I_Priority__c is a Picklist
  • C_Start_Date__c is a Date Field
  • C_End_Date__c is a Date Field

These field values cannot be duplicated in the database. Therefore every new entry to be inserted needs to be validated accordingly.

For this, I am using the below formula

NOT(EXISTS((SELECT Id  FROM I_Coverage__c  
 WHERE I_Priority__c = {{I_Priority__c}}
   AND C_Start_Date__c = {{C_Start_Date__c}}
   AND C_End_Date__c = {{C_End_Date__c}})))

When I press the 'Check Syntax' button, I am getting

Error: Syntax error. Missing ')'

Error

Solution

  • This is... very misguided. You can't run queries in formulas like that and where's that {{syntax}} coming from. Are you a Marketing Cloud person or something?

    I'll list some options but you have lots of reading to do.

    • If it was Account/Contact/Lead/Person account - you could use duplicate rules. You have a custom object so go upvote an idea: https://ideas.salesforce.com/s/idea/a0B8W00000GdZjcUAF/potential-duplicates-for-custom-objects, read the comments, maybe there's an app you can use.

    • You could look into VLOOKUP function - but it'd require the 3 values all appear in the Name field of the object which may be a no-go for you.

    • you could write an apex trigger or flow but properly protecting the scenario may be harder than you think.

      • How do you not detect "yourself" (I'm updating an unrelated field on a record, how do you ensure it won't find itself)
      • How do you protect an insert of 2 identical records at same time (say with Import Wizard or Data Loader) - your query could return 0 pre-existing records in the database and let it through
      • What if I insert #1, delete it, insert #2 (exact duplicate), go to recycle bin and restore #1? Validation rules don't fire on undelete

    So... is there an easy config solution? Yep.

    1. Create a helper text field, Unique__c Text, 255. Mark it Unique case insensitive.
    2. Create an "early flow" or before insert, before update trigger if you feel like it, populating the field with combination of your 3 fields. No conditions, can run always (low performance impact and before means it's a save for free).
    3. Run a data fix that mass updates your records (if you did #2 you can just "touch" them, update without changing anything, should populate.
    4. Try to break it.