+ Start a Discussion
SeannoSeanno 

Formula Limit on "Formula Editor" in "New Field Update"

Is there a work around for Workflows that Update Fields where the limit for the Formula is 3900.  I want to create an "IF" formula that would include 1800 cities in California with Sales tax Rates that would automatically update custom field we have in Opportunities called "Sales Tax Rate" (the formula I created is around 100,000 characters).   Does anyone know of any other way to apply sales tax to opportunities automatically depending on city/state???

Best Answer chosen by Admin (Salesforce Developers) 
SeannoSeanno

Yeah, I've tried using CASE and splitting it up into more than one workflow, however the last workflow that's created cancels out the first one, because the end of the formula has to read NULL, and the NULL cancels out any preceeding workflow if they are used to modify the same field.

 

I found another work around by exporting Leads/Accounts and using VLOOKUP in Excel with a tax array to enter tax rates into a new field column....updating the lead/accounts with APEX, then using a workflow to insert the tax rate from the account page for the opportunity page. 

 

Thanks.

All Answers

yudhvirmoryudhvirmor

Use case rather than IF, It'll reduce the size. Other way around may be creating two workflows and distributing cities in Tier 1 & Tier 2 cities. I am not sure if there is any way to apply sales tax on the basis of city/State in address field.

 

 

SeannoSeanno

Yeah, I've tried using CASE and splitting it up into more than one workflow, however the last workflow that's created cancels out the first one, because the end of the formula has to read NULL, and the NULL cancels out any preceeding workflow if they are used to modify the same field.

 

I found another work around by exporting Leads/Accounts and using VLOOKUP in Excel with a tax array to enter tax rates into a new field column....updating the lead/accounts with APEX, then using a workflow to insert the tax rate from the account page for the opportunity page. 

 

Thanks.

This was selected as the best answer
yudhvirmoryudhvirmor

Lets try this:

 

1800 Cities with length 10 character : 18000 Characters

 

Lets assume we have 10 tax structures, Lets name them 1 to 10.

 

Create a picklist field with these values.

 

Create 10 workflows, Workflow can have condition as OR(City A:City B: City C:CityD), update the picklist value field as field update action to relevant tax structure.

 

Let me know if it  works for you.

SeannoSeanno

There are 1800 cities having 10 different tax rates...your solution will only work assuming one tax rate.

SeannoSeanno

You probably meant to do a picklist of all the different tax rates, then a workflow of the different cities with the same tax rate to field update the specific tax rate in the picklist....right?  That sound like it would work....thanks.

yudhvirmoryudhvirmor

yes :)

SeannoSeanno

The OR function returns value TRUE or FALSE.   Is there a function that would return the tax value in the picklist I want, and at the same time not canceling out the other workflows?  The IF and CASE function both have to end in NULL, which cancels out all preceding workflows.

yudhvirmoryudhvirmor

You need True or False to trigger the workflow, and then u'll do the field update of Picklist value, u can select one relevant tax rate from the picklist for that workflow.

 

so, if you have 10 workflows, only 1 will trigger and update the record.