Forum Discussion

RebeccaGarcia-P's avatar
RebeccaGarcia-P
Qrew Member
4 years ago

Nested Case formula issue / snapshot field

I'm relatively new to formulas and having an issue with a nested case statement. The main thing I'm trying to accomplish is setting up a snapshot field for target profit margin. I created a new table, relationship, and snapshot field itself just fine, but the target profit margin is based on a Margin Category, which is based on two other criteria, Customer Type and Project Type. I've tried writing the formula for Margin Category in any number of ways but keep getting different errors. My latest try is below, and the current error is 'Expecting textlist but found text.' Any help is greatly appreciated!

Case([Customer Type], "Corporate Coalition",
Case([Project Type Rollup],
"Audits", "Corporate Coalition-Audits",
"Consulting", "Corporate Coalition-Consulting",
"Desk Research", "Corporate Coalition-Desk Research",
"Guidebook", "Corporate Coalition-Guidebook",
"Training", "Corporate Coalition-Training",
"CUMULUS", "Corporate Coalition-CUMULUS",
"Other", "Corporate Coalition-Other"),

Case([Customer Type], "Foundation",
Case([Project Type Rollup],
"Audits", "Foundation-Audits",
"Consulting", "Foundation-Consulting",
"Desk Research", "Foundation-Desk Research",
"Guidebook", "Foundation-Guidebook",
"Training", "Foundation-Training",
"CUMULUS", "Foundation-CUMULUS",
"Other", "Foundation-Other"),

Case([Customer Type], "Government",
Case([Project Type Rollup],
"Audits", "Government-Audits",
"Consulting", "Government-Consulting",
"Desk Research", "Government-Desk Research",
"Guidebook", "Government-Guidebook",
"Training", "Government-Training",
"CUMULUS", "Government-CUMULUS",
"Other", "Government-Other"),

Case([Customer Type], "Large Business",
Case([Project Type Rollup],
"Audits", "Large Business-Audits",
"Consulting", "Large Business-Consulting",
"Desk Research", "Large Business-Desk Research",
"Guidebook", "Large Business-Guidebook",
"Training", "Large Business-Training",
"CUMULUS", "Large Business-CUMULUS",
"Other", "Large Business-Other"),

Case([Customer Type], "Small Business",
Case([Project Type Rollup],
"Audits", "Small Business-Audits",
"Consulting", "Small Business-Consulting",
"Desk Research", "Small Business-Desk Research",
"Guidebook", "Small Business-Guidebook",
"Training", "Small Business-Training",
"CUMULUS", "Small Business-CUMULUS",
"Other", "Small Business-Other"),

Case([Customer Type], "NGO/Non-Profit",
Case([Project Type Rollup],
"Audits", "NGO/Non-Profit-Audits",
"Consulting", "NGO/Non-Profit-Consulting",
"Desk Research", "NGO/Non-Profit-Desk Research",
"Guidebook", "NGO/Non-Profit-Guidebook",
"Training", "NGO/Non-Profit-Training",
"CUMULUS", "NGO/Non-Profit-CUMULUS",
"Other", "NGO/Non-Profit-Other")))))))

------------------------------
Rebecca Garcia-Pons
Program Director
------------------------------
  • Only have a second to look now, but on firs glance try this, i only copy/paste the first 3 but you will get the idea

    If([Customer Type] = "Corporate Coalition",
    Case([Project Type Rollup],
    "Audits", "Corporate Coalition-Audits",
    "Consulting", "Corporate Coalition-Consulting",
    "Desk Research", "Corporate Coalition-Desk Research",
    "Guidebook", "Corporate Coalition-Guidebook",
    "Training", "Corporate Coalition-Training",
    "CUMULUS", "Corporate Coalition-CUMULUS",
    "Other", "Corporate Coalition-Other"),

    ([Customer Type] = "Foundation",
    Case([Project Type Rollup],
    "Audits", "Foundation-Audits",
    "Consulting", "Foundation-Consulting",
    "Desk Research", "Foundation-Desk Research",
    "Guidebook", "Foundation-Guidebook",
    "Training", "Foundation-Training",
    "CUMULUS", "Foundation-CUMULUS",
    "Other", "Foundation-Other"),

    ([Customer Type] = "Government",
    Case([Project Type Rollup],
    "Audits", "Government-Audits",
    "Consulting", "Government-Consulting",
    "Desk Research", "Government-Desk Research",
    "Guidebook", "Government-Guidebook",
    "Training", "Government-Training",
    "CUMULUS", "Government-CUMULUS",
    "Other", "Government-Other"))

    ------------------------------
    Michael Tamoush
    ------------------------------
    • RebeccaGarcia-P's avatar
      RebeccaGarcia-P
      Qrew Member
      Thanks for the quick reply! I tried it, but now the error says 'expecting bool but found textlist'

      ------------------------------
      Rebecca Garcia-Pons
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        What is the field type of your formula field?

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------