function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
WPCMSWPCMS 

Can you make a Formula a unique value?

We enter our vendor invoices into our system and we have issues with duplicate invoices being entered. I wanted to make the vendor invoice number field (text) unique BUT what if there was the same invoice number for more than on vendor? (believe me it does happen with vendors who have four or three digit invoice numbers.)

 

I then thought of a way to create a formula that would concatenate the vendor account code with the vendor invoice number. That would then be a very unique number. BUT I don't think that you can make formulas unique.

 

What can I do?

Best Answer chosen by Admin (Salesforce Developers) 
Steve :-/Steve :-/

You're right, you can't make a formula field unique.  But you can still do this.  You're halfway there with your Formula(Text) field that concatenates the Vendor ID and the Invoice Number.  

 

Now all you need is a straight Text Field that is set to "No Duplicate Values allowed" then create a WorkFlowRule with a Field Update that will populate your custom Unique Text field with the value from your concatenated Formula(Text) field, and you're good to go.  

 

PS.  You owe me another beer!  

 

PPS.  I'm still waiting the first one ;-)

 

 

All Answers

Steve :-/Steve :-/

You're right, you can't make a formula field unique.  But you can still do this.  You're halfway there with your Formula(Text) field that concatenates the Vendor ID and the Invoice Number.  

 

Now all you need is a straight Text Field that is set to "No Duplicate Values allowed" then create a WorkFlowRule with a Field Update that will populate your custom Unique Text field with the value from your concatenated Formula(Text) field, and you're good to go.  

 

PS.  You owe me another beer!  

 

PPS.  I'm still waiting the first one ;-)

 

 

This was selected as the best answer
WPCMSWPCMS

It woks! Thank you for you help.

 

Sorry I don't drink beer, so I don't have it handy. But I do have whiskey :)
Steve :-/Steve :-/

Last time I answer one of your questions... ;-)
nancym1nancym1
Actually you don't even have to create a separate formula field for this. You can specify whatever formula you want in the "Field Update" workflow action :)
Arpit Sethi 9Arpit Sethi 9
@nancym1,
Can you pls elaborate.
Ramdas RamaniRamdas Ramani
@Arpit Sethi 9, You can create a Custom Field and then define a workflow which will update the Custom field with the Formula that you specify. For Eg You have a standard Phone field and lets say, you need to remove the automatic format of number from (999) 999-9999 and make it 9999999999 (Just an eg). You have to create a custom text field in the module that you are working on, define a workflow rule which will update the custom text field ( The formula can be specified when you are setting up the workflow rule and using 'Field update' option upon match of the workflow rule ) whenever the standard phone field is entered. If you had made the custom text field Unique during the time of creation, then Salesforce would prevent us from saving the duplicate record. Hope this helps.  
jaysunjaysun
create unique text field and update on workflow update based on combination of one or more fields entered. 
gyani19901.3956550919266765E12gyani19901.3956550919266765E12
Hi Steve,

I have the same issue but not able to understand your approach.
It will be helpful for me if you can provide one demo example of your apporach.

Thanks,
Gyanender Singh
Andy@AdnubisAndy@Adnubis
Finally a useable answer!!
 
With Winter 18, time functions have been added to formulas. This allows us to get a unique value:
See Below:
((Year  (Datevalue (now()     ))-2000)*10000000000)+
(Month (Datevalue (now()     ))*100000000  )+
(Day   (DateValue (now()     ))*1000000    )+
(Hour  (Timevalue (timenow() ))*10000      )+
(Minute(Timevalue (timenow() ))*100        )+
(Second(Timevalue (timenow() )))
 
Note: I shortened the Year to two digits to reduce the size of the value.