function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Shawn Low 59Shawn Low 59 

Need help witha complex formula field.

The formula field needs to function like this.
It looks at one PL field (ChempaxDB), and then look at a 2nd PL field, and based on the vales of the 2 PL fields, populate with a specific value.
An example would be, (1st PL Field) ChempaxDB = CASE, and (2nd PL Field) Division = 01, the Divisoin Name Field (this is the formula field) populates with "01 - Chemical Raw Materials".
This is what I have come up with but can;t get the formula to work:

OR (IF ( ISPICKVAL( Chempax_DB__c , "CASE"),
CASE( Division__c  ,
"01", "01 - CHEMICAL RAW MATERIALS",
"10", "10 - Azelis CASE, LLC - Garland",
"15", "15 - FOOD PRODUCTS",
"19", "19 - Azelis CASE Garland Direct",
"20", "20 - Azelis CASE - Houston",
"21", "21 - Azelis CASE Bulk",
"29", "29 - Azelis CASE Houston - Direct",
"30", "30 - Azelis CASE - Phoenix",
"31", "31 - Azelis CASE Denver",
"39", "39 - Azelis CASE Direct - Phoenix",
"40", "40 - Azelis Americas CASE, LLC",
"45", "45 - INTERCO ORDERS/TRANSFERS CASE",
"50", "50 - Azelis Americas CASE, LLC",
"51", "51 - Azelis Construction Solutions",
"52", "52 - Azelis CASE Fulton",
"54", "54 - CASE HOUSE ACCOUNTS",
"55", "55 - Azelis CASE, LLC - Atlanta",
"56", "56 - Azelis CASE, LLC - Greenville",
"57", "57 - Azelis CASE, LLC - Meredith",
"58", "58 - FINE INGREDIENTS GROUP",
"59", "59 - Ribelin Atlanta - Direct",
"60", "60 - Azelis CASE, LLC- Orlando",
"61", "61 - Azelis CASE, LLC - Whitaker",
"69", "69 - Azelis CASE Orlando - Direct",
"70", "70 - GMZ - SW",
"88", "88 - DL Trading Duty Drawback",
"89", "89 - Azelis CASE, LLC - IC",
"90", "90 - Azelis CASE, LLC - IC",
"91", "91 - ACS - Intercompany",
"92", "92 - Azelis CASE, LLC - IC",
"94", "94 - GMZ SW COMMISSIONS",
"95", "95 - OPERATIONS",
"96", "96 - COMMISSIONS - CHEMICAL",
"97", "97 - COMMISSIONS - CONTAINERS",
"98", "98 - COMMISSIONS",
"99", "99 - Azelis CASE, LLC - Supplier",
                    null),  

IF ( ISPICKVAL( Chempax_DB__c ,"DeWolf" ),
CASE( Division__c ,
"10", "10 - DeWolf - Industrial Line HI&I",
"20", "20 - DeWolf - Personal Care Line",
"23", "23 - Dewolf - Color Cosmetics",
"89", "89 - DeWolf Intercompany Sales",
null),            

IF ( ISPICKVAL( Chempax_DB__c ,"Glenn" ),
CASE( Division__c ,
"03", "03 - Cosmetic",
"04", "04 - Reseller",
"06", "06 - CAS Number"
"10", "10 - Industrial and Institutional",
"20", "20 - Personal Care",
"23", "23 - Color Cosmectics",
"89", "89 - Glenn Intercompany Sales",
"90", "90 - Glenn Sales To DeWolf",
null),
                
IF ( ISPICKVAL( Chempax_DB__c ,"Marcor" ),
CASE( Division__c ,
"30", "30 - KDG CORPORATE",
"80", "80 - MARCOR DEVELOPMENT",
"89", "89 - INTERCOMPANY SALES",
null),
                
IF ( ISPICKVAL( Chempax_DB__c ,"Monson" ),
CASE( Division__c ,
"01", "01 - INDUSTRIAL SPECIALTIES",
"05", "05 - PIGMENTS AND PLASTICIZERS
"10", "10 - GENERAL INDUSTRIAL",
"15", "15 - FINE INGREDIENTS",
"20", "20 - NON-TOXIC",
"24", "24 - FOCUSED INDUSTRIAL",
"51", "51 - MONSON CO CHEMICAL DIV",
"52", "52 - MONSON CO LMW DIVISION",
"61", "61 - COMMISSION PRODUCTS ISG",
"62", "62 - GIG COMMISSION PRODUCTS",
"63", "63 - PWT COMMISSIONS",
"64", "64 - COMMISSIONS - P&P",
"70", "70 - AUTOMOTIVE/FREEZE PROTECT",
"75", "75 - ICE MELTERS",
"88", "88 - FRT CLAIMS FOR CARRIERS",
"89", "89 - INTERCOMPANY",
"95", "95 - OPERATIONS, SERV WHS, MISC",
"98", "98 - MONSON COMPANIES FREIGHT",
"99", "99 - COMMISSIONS RECEIVABLE",
null),
                    
IF ( ISPICKVAL( Chempax_DB__c ,"Ross" ),
CASE( Division__c ,
"10", "10 - Ross Organic",
"20", "20 - Ross - Personal Care Line",
"23", "23 - Color Cosmetics",
"89", "89 - Ross Intercompany Sales",
null),
null))

Thank you,
Shawn
Best Answer chosen by Shawn Low 59
Maharajan CMaharajan C
Hi Shawn,

You formula needs to be like below:

Note : Surely the below formula will cross the 5000 character. So you can't save the below formula... To avoid this you have to remove some conditions from below formula's... if you want all below combination is required then go for code...
 
IF( ISPICKVAL( Chempax_DB__c , "CASE") ,
CASE( Division__c  ,
"01", "01 - CHEMICAL RAW MATERIALS",
"10", "10 - Azelis CASE, LLC - Garland",
"15", "15 - FOOD PRODUCTS",
"19", "19 - Azelis CASE Garland Direct",
"20", "20 - Azelis CASE - Houston",
"21", "21 - Azelis CASE Bulk",
"29", "29 - Azelis CASE Houston - Direct",
"30", "30 - Azelis CASE - Phoenix",
"31", "31 - Azelis CASE Denver",
"39", "39 - Azelis CASE Direct - Phoenix",
"40", "40 - Azelis Americas CASE, LLC",
"45", "45 - INTERCO ORDERS/TRANSFERS CASE",
"50", "50 - Azelis Americas CASE, LLC",
"51", "51 - Azelis Construction Solutions",
"52", "52 - Azelis CASE Fulton",
"54", "54 - CASE HOUSE ACCOUNTS",
"55", "55 - Azelis CASE, LLC - Atlanta",
"56", "56 - Azelis CASE, LLC - Greenville",
"57", "57 - Azelis CASE, LLC - Meredith",
"58", "58 - FINE INGREDIENTS GROUP",
"59", "59 - Ribelin Atlanta - Direct",
"60", "60 - Azelis CASE, LLC- Orlando",
"61", "61 - Azelis CASE, LLC - Whitaker",
"69", "69 - Azelis CASE Orlando - Direct",
"70", "70 - GMZ - SW",
"88", "88 - DL Trading Duty Drawback",
"89", "89 - Azelis CASE, LLC - IC",
"90", "90 - Azelis CASE, LLC - IC",
"91", "91 - ACS - Intercompany",
"92", "92 - Azelis CASE, LLC - IC",
"94", "94 - GMZ SW COMMISSIONS",
"95", "95 - OPERATIONS",
"96", "96 - COMMISSIONS - CHEMICAL",
"97", "97 - COMMISSIONS - CONTAINERS",
"98", "98 - COMMISSIONS",
"99", "99 - Azelis CASE, LLC - Supplier",
null), 
IF(ISPICKVAL( Chempax_DB__c , "DeWolf"), 
CASE( Division__c ,
"10", "10 - DeWolf - Industrial Line HI&I",
"20", "20 - DeWolf - Personal Care Line",
"23", "23 - Dewolf - Color Cosmetics",
"89", "89 - DeWolf Intercompany Sales",
null), 
IF(ISPICKVAL( Chempax_DB__c , "Glenn") ,
CASE( Division__c ,
"03", "03 - Cosmetic",
"04", "04 - Reseller",
"06", "06 - CAS Number",
"10", "10 - Industrial and Institutional",
"20", "20 - Personal Care",
"23", "23 - Color Cosmectics",
"89", "89 - Glenn Intercompany Sales",
"90", "90 - Glenn Sales To DeWolf",
null),
IF(ISPICKVAL( Chempax_DB__c , "Marcor") ,
CASE( Division__c ,
"30", "30 - KDG CORPORATE",
"80", "80 - MARCOR DEVELOPMENT",
"89", "89 - INTERCOMPANY SALES",
null),
IF(ISPICKVAL( Chempax_DB__c , "Monson") ,
CASE( Division__c ,
"01", "01 - INDUSTRIAL SPECIALTIES",
"05", "05 - PIGMENTS AND PLASTICIZERS",
"10", "10 - GENERAL INDUSTRIAL",
"15", "15 - FINE INGREDIENTS",
"20", "20 - NON-TOXIC",
"24", "24 - FOCUSED INDUSTRIAL",
"51", "51 - MONSON CO CHEMICAL DIV",
"52", "52 - MONSON CO LMW DIVISION",
"61", "61 - COMMISSION PRODUCTS ISG",
"62", "62 - GIG COMMISSION PRODUCTS",
"63", "63 - PWT COMMISSIONS",
"64", "64 - COMMISSIONS - P&P",
"70", "70 - AUTOMOTIVE/FREEZE PROTECT",
"75", "75 - ICE MELTERS",
"88", "88 - FRT CLAIMS FOR CARRIERS",
"89", "89 - INTERCOMPANY",
"95", "95 - OPERATIONS, SERV WHS, MISC",
"98", "98 - MONSON COMPANIES FREIGHT",
"99", "99 - COMMISSIONS RECEIVABLE",
null),
IF(ISPICKVAL( Chempax_DB__c , "Ross"),
CASE( Division__c ,
"10", "10 - Ross Organic",
"20", "20 - Ross - Personal Care Line",
"23", "23 - Color Cosmetics",
"89", "89 - Ross Intercompany Sales",
null),
NULL ) 
) 
)
)
)
)

Thanks,
Maharajan.C

All Answers

Maharajan CMaharajan C
Hi Shawn,

You formula needs to be like below:

Note : Surely the below formula will cross the 5000 character. So you can't save the below formula... To avoid this you have to remove some conditions from below formula's... if you want all below combination is required then go for code...
 
IF( ISPICKVAL( Chempax_DB__c , "CASE") ,
CASE( Division__c  ,
"01", "01 - CHEMICAL RAW MATERIALS",
"10", "10 - Azelis CASE, LLC - Garland",
"15", "15 - FOOD PRODUCTS",
"19", "19 - Azelis CASE Garland Direct",
"20", "20 - Azelis CASE - Houston",
"21", "21 - Azelis CASE Bulk",
"29", "29 - Azelis CASE Houston - Direct",
"30", "30 - Azelis CASE - Phoenix",
"31", "31 - Azelis CASE Denver",
"39", "39 - Azelis CASE Direct - Phoenix",
"40", "40 - Azelis Americas CASE, LLC",
"45", "45 - INTERCO ORDERS/TRANSFERS CASE",
"50", "50 - Azelis Americas CASE, LLC",
"51", "51 - Azelis Construction Solutions",
"52", "52 - Azelis CASE Fulton",
"54", "54 - CASE HOUSE ACCOUNTS",
"55", "55 - Azelis CASE, LLC - Atlanta",
"56", "56 - Azelis CASE, LLC - Greenville",
"57", "57 - Azelis CASE, LLC - Meredith",
"58", "58 - FINE INGREDIENTS GROUP",
"59", "59 - Ribelin Atlanta - Direct",
"60", "60 - Azelis CASE, LLC- Orlando",
"61", "61 - Azelis CASE, LLC - Whitaker",
"69", "69 - Azelis CASE Orlando - Direct",
"70", "70 - GMZ - SW",
"88", "88 - DL Trading Duty Drawback",
"89", "89 - Azelis CASE, LLC - IC",
"90", "90 - Azelis CASE, LLC - IC",
"91", "91 - ACS - Intercompany",
"92", "92 - Azelis CASE, LLC - IC",
"94", "94 - GMZ SW COMMISSIONS",
"95", "95 - OPERATIONS",
"96", "96 - COMMISSIONS - CHEMICAL",
"97", "97 - COMMISSIONS - CONTAINERS",
"98", "98 - COMMISSIONS",
"99", "99 - Azelis CASE, LLC - Supplier",
null), 
IF(ISPICKVAL( Chempax_DB__c , "DeWolf"), 
CASE( Division__c ,
"10", "10 - DeWolf - Industrial Line HI&I",
"20", "20 - DeWolf - Personal Care Line",
"23", "23 - Dewolf - Color Cosmetics",
"89", "89 - DeWolf Intercompany Sales",
null), 
IF(ISPICKVAL( Chempax_DB__c , "Glenn") ,
CASE( Division__c ,
"03", "03 - Cosmetic",
"04", "04 - Reseller",
"06", "06 - CAS Number",
"10", "10 - Industrial and Institutional",
"20", "20 - Personal Care",
"23", "23 - Color Cosmectics",
"89", "89 - Glenn Intercompany Sales",
"90", "90 - Glenn Sales To DeWolf",
null),
IF(ISPICKVAL( Chempax_DB__c , "Marcor") ,
CASE( Division__c ,
"30", "30 - KDG CORPORATE",
"80", "80 - MARCOR DEVELOPMENT",
"89", "89 - INTERCOMPANY SALES",
null),
IF(ISPICKVAL( Chempax_DB__c , "Monson") ,
CASE( Division__c ,
"01", "01 - INDUSTRIAL SPECIALTIES",
"05", "05 - PIGMENTS AND PLASTICIZERS",
"10", "10 - GENERAL INDUSTRIAL",
"15", "15 - FINE INGREDIENTS",
"20", "20 - NON-TOXIC",
"24", "24 - FOCUSED INDUSTRIAL",
"51", "51 - MONSON CO CHEMICAL DIV",
"52", "52 - MONSON CO LMW DIVISION",
"61", "61 - COMMISSION PRODUCTS ISG",
"62", "62 - GIG COMMISSION PRODUCTS",
"63", "63 - PWT COMMISSIONS",
"64", "64 - COMMISSIONS - P&P",
"70", "70 - AUTOMOTIVE/FREEZE PROTECT",
"75", "75 - ICE MELTERS",
"88", "88 - FRT CLAIMS FOR CARRIERS",
"89", "89 - INTERCOMPANY",
"95", "95 - OPERATIONS, SERV WHS, MISC",
"98", "98 - MONSON COMPANIES FREIGHT",
"99", "99 - COMMISSIONS RECEIVABLE",
null),
IF(ISPICKVAL( Chempax_DB__c , "Ross"),
CASE( Division__c ,
"10", "10 - Ross Organic",
"20", "20 - Ross - Personal Care Line",
"23", "23 - Color Cosmetics",
"89", "89 - Ross Intercompany Sales",
null),
NULL ) 
) 
)
)
)
)

Thanks,
Maharajan.C
This was selected as the best answer
Shawn Low 59Shawn Low 59
Thank you Maharajan,
Can I use a Flow for this, or would I hit that same limit?
Character count is 7500.
Do I need to just use a Trigger?
Thanks
Shawn
Vladmir ZrajevskyVladmir Zrajevsky
Can I use a Flow for this, or would I hit that same limit?
Character count is 7500.
Do I need to just use a Trigger?
https://fateka.com (https://fateka.com/)
GreyGorilla VaporGreyGorilla Vapor
Very nice blog and articles. I am really very happy to visit your blog. Now I am found what I actually want. I check your blog every day and try to learn something from your blog. Thank you and waiting for your new post.

Caliburn G2