Discussions

Expand all | Collapse all

Nested Case formula issue / snapshot field

  • 1.  Nested Case formula issue / snapshot field

    Posted 18 days ago
    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
    ------------------------------


  • 2.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    Edited by Michael Tamoush 18 days ago
    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
    ------------------------------



  • 3.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    Thanks for the quick reply! I tried it, but now the error says 'expecting bool but found textlist'

    ------------------------------
    Rebecca Garcia-Pons
    ------------------------------



  • 4.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    What is the field type of your formula field?

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



  • 5.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    It's text. Should it be something else?

    ------------------------------
    Rebecca Garcia-Pons
    ------------------------------



  • 6.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    Can you copy and paste the whole formula as as well as the whole error message?


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



  • 7.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    Thank you, I very much appreciate the help. I made the adjustments Michael suggested so the full formula is now:

    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"),

    ([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"),

    ([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"),

    ([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")))))))))

    The first instance of Customer Type is highlighted and the message when I hover over it is 'expecting bool but found textlist.' When I try to save, the error message is:

    "Formula error -- Bad or missing arguments in function call

    The types of the arguments or the number of arguments supplied do not meet the requirements of the function Case.

    The function is defined to be Case (.

    You may need to do one of the following to fix the problem:
    • Choose the right number and type of arguments.
    • Use a type conversion function to convert the arguments you are using to the correct type.
    • Choose a different function."


    ------------------------------
    Rebecca Garcia-Pons
    ------------------------------



  • 8.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    try this

    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"),

    [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"),

    [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"),

    [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"))

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



  • 9.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    Now the error is:

    Formula Error -- Type Mismatch

    The expression [Customer Type] on the left hand side of the operator "=" is of type textlist while the expression "Corporate Coalition" on the right hand side is of type text. The operator "=" cannot be used with these types of expressions.

    You may need to do one of the following to fix the problem:
    • Add a type conversion function.
    • Choose a different operator.


    ------------------------------
    Rebecca Garcia-Pons
    ------------------------------



  • 10.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    What type of formula field is [Customer Type]? Is it a combined text field?

    Try If(totext([Customer Type) = "Corporate Coalition",

    If the error solves it self in the first instance, do that to all instances.

    ------------------------------
    Michael Tamoush
    ------------------------------



  • 11.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    ... further to Mike's post above, what if the record has more than one Customer Type selected.  What do you want the formula to do.

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



  • 12.  RE: Nested Case formula issue / snapshot field

    Posted 18 days ago
    That worked! Thank you both so much!

    ------------------------------
    Rebecca Garcia-Pons
    ------------------------------