none
Limitation on "tests" in a Switch Formula RRS feed

  • Question

  • I am using a Switch formula in a custom text field to return provide a description based on another custom text field.  The problem is, I have more values than what the formula allows.  If I add one more "test" to the Switch function below, I get an error. 

    Is ther any way to fix that?  Or, some other creative way to fix it?

    Switch([Text1]="A013","A013 Description",[Text1]="A020","A020 Descriptiont",[Text1]="A014","A014 Description",[Text1]="A008","A008 Description",[Text1]="A010","A010 Description",[Text1]="A016","A016 Description",[Text1]="A017","A017 Description",[Text1]="A018","A018 Description",[Text1]="C001","C001 Description",[Text1]="C007","C007 Description",[Text1]="D001","D001 Description",[Text1]="D005","D005 Description",[Text1]="A009","A009 Description",[Text1]="A015","A015 Description")

    Thursday, October 25, 2012 2:59 PM

Answers

  • VBA code will also do it. The following in a new Module will work with a little editing. You will also need to make sure then users enable macros if asked when opening the project.

    Sub UpdateDescription()
    'Edit Const rows to use correct custom field names
    Const TextLookup = "Text1"
    Const TextDescription = "Text2"
    
    Dim Tsk As Task
        For Each Tsk In ActiveProject.Tasks
            If Not Tsk Is Nothing Then
                Select Case Tsk.GetField(FieldID:=FieldNameToFieldConstant(TextLookup, pjTask))
                    Case "A013"
                        Tsk.SetField FieldID:=FieldNameToFieldConstant(TextDescription, pjTask), Value:="A013 Description"
                    Case "A020"
                        Tsk.SetField FieldID:=FieldNameToFieldConstant(TextDescription, pjTask), Value:="A020 Description"
                    'Add as many Case rows as you like
                    Case Else
                        Tsk.SetField FieldID:=FieldNameToFieldConstant(TextDescription, pjTask), Value:="No Description"
                End Select
            End If
        Next Tsk
    End Sub


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by gharisiades Monday, October 29, 2012 8:30 PM
    Thursday, October 25, 2012 9:21 PM
    Moderator
  • Greg,

    Actually I would probably do this using VBA as Rod suggests in his response. It's easier to program, easier to troubleshoot and more flexible. The downside is that you have to manually run the macro whereas a formula "fires" automatically. Well, technically a macro could be set up to fire automatically but that gets more complex.

    Okay, so let's continue with your formula idea. Unlike VBA, a formula cannot "trap" an error. That's why your attempts to use an error function do not work. However you don't need to worry about an error. Unlike an IIf statement, the Switch statement does not provide a "controlled" output if everything is false. That's one reason I never used Switch statements. However, if you provide a method of giving a result when none of the other expressions is true then you can use that in the formula in field 2. For example, based on the data you have in Text1, you can use the following Switch formula (mine is abbreviated to show the intent)

    Switch([Text1]="a013","a13 desc",[Text1]="a020","a20 desc",[Text1]="d001","d01 desc",[Text1]="c001","c01 desc",InStr(1,[Text1],"0")>0,"next")

    So instead of looking for an error in field 3, you look for a valid description or the "next" string which directs the formula in field 2 to look in field 4 for the desired value.

    One other point I'd like to make. When testing for string functions, a simple typing error can throw things off. For example, in your case if the user forgets to capitalize the first character or types an "o" instead of a "0", the whole thing fails.

    Hope this helps.

    John

    • Marked as answer by gharisiades Monday, October 29, 2012 8:30 PM
    Friday, October 26, 2012 2:32 AM
  • Greg,

    You're welcome and thanks for the feedback.

    To answer your question, yes VBA can be used to translate task fields to resource and assignment fields and vice versa. For sample code go to the MVP website at, http://project.mvps.org/faqs.htm, and take a look at FAQ 37 - Custom fields in views.

    John

    • Marked as answer by gharisiades Monday, November 12, 2012 4:43 PM
    Monday, October 29, 2012 7:37 PM

All replies

  • gharisiades,

    As I recall a similar issue was raised previously concerning the number of nested "IIF" statements. The limit is 15. It sounds like the same limit is true for the Switch statement. My suggestion is to break the formula into two or more parts, (i.e. use as many custom fields as necessary), and then combine the result in yet a third custom field. This also has the advantage of much easier troubleshooting.

    John

    Thursday, October 25, 2012 3:28 PM
  • Thanks John,

    I was already trying that, so I think that's brilliant! ;o)   What I did was to create two separate fields each checking for different values.  Here's where I'm having difficulties:

    I want a third field to hold the values.  So, Field2 is where I want the values to be displayed.  Field3 holds the Switch function for the first 14 values and Field4 holds the remaining values.  The formula in Field2 first checks for the value in Field3, if there's an error, then get the value from Field4.  So, I need some way to check for an error in Field3.  Something like:

    IIf([Field3]="#ERROR",[Field4],[Field3]) -or-

    IIf([Field3]=#ERROR,[Field4],[Field3]) -or-

    IIf(IsError([Field3]),[Field4],[Field3])  <<< This formula passes the syntax check, but it doesn't appear the IsError is supported.....


    Greg Harisiades

    Thursday, October 25, 2012 4:20 PM
  • VBA code will also do it. The following in a new Module will work with a little editing. You will also need to make sure then users enable macros if asked when opening the project.

    Sub UpdateDescription()
    'Edit Const rows to use correct custom field names
    Const TextLookup = "Text1"
    Const TextDescription = "Text2"
    
    Dim Tsk As Task
        For Each Tsk In ActiveProject.Tasks
            If Not Tsk Is Nothing Then
                Select Case Tsk.GetField(FieldID:=FieldNameToFieldConstant(TextLookup, pjTask))
                    Case "A013"
                        Tsk.SetField FieldID:=FieldNameToFieldConstant(TextDescription, pjTask), Value:="A013 Description"
                    Case "A020"
                        Tsk.SetField FieldID:=FieldNameToFieldConstant(TextDescription, pjTask), Value:="A020 Description"
                    'Add as many Case rows as you like
                    Case Else
                        Tsk.SetField FieldID:=FieldNameToFieldConstant(TextDescription, pjTask), Value:="No Description"
                End Select
            End If
        Next Tsk
    End Sub


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by gharisiades Monday, October 29, 2012 8:30 PM
    Thursday, October 25, 2012 9:21 PM
    Moderator
  • Greg,

    Actually I would probably do this using VBA as Rod suggests in his response. It's easier to program, easier to troubleshoot and more flexible. The downside is that you have to manually run the macro whereas a formula "fires" automatically. Well, technically a macro could be set up to fire automatically but that gets more complex.

    Okay, so let's continue with your formula idea. Unlike VBA, a formula cannot "trap" an error. That's why your attempts to use an error function do not work. However you don't need to worry about an error. Unlike an IIf statement, the Switch statement does not provide a "controlled" output if everything is false. That's one reason I never used Switch statements. However, if you provide a method of giving a result when none of the other expressions is true then you can use that in the formula in field 2. For example, based on the data you have in Text1, you can use the following Switch formula (mine is abbreviated to show the intent)

    Switch([Text1]="a013","a13 desc",[Text1]="a020","a20 desc",[Text1]="d001","d01 desc",[Text1]="c001","c01 desc",InStr(1,[Text1],"0")>0,"next")

    So instead of looking for an error in field 3, you look for a valid description or the "next" string which directs the formula in field 2 to look in field 4 for the desired value.

    One other point I'd like to make. When testing for string functions, a simple typing error can throw things off. For example, in your case if the user forgets to capitalize the first character or types an "o" instead of a "0", the whole thing fails.

    Hope this helps.

    John

    • Marked as answer by gharisiades Monday, October 29, 2012 8:30 PM
    Friday, October 26, 2012 2:32 AM
  • Thanks to both John and Rod (Rod, I'm going to buy your book)!

    I did it both ways and I do like the VBA method. 

    Rod,

    Here's one final question on this.....I also have a custom task field that I want to fill with information in a custom resource field.  Do you have a sample to do this?  Is it even possible?


    Greg Harisiades

    Monday, October 29, 2012 4:13 PM
  • Greg,

    You're welcome and thanks for the feedback.

    To answer your question, yes VBA can be used to translate task fields to resource and assignment fields and vice versa. For sample code go to the MVP website at, http://project.mvps.org/faqs.htm, and take a look at FAQ 37 - Custom fields in views.

    John

    • Marked as answer by gharisiades Monday, November 12, 2012 4:43 PM
    Monday, October 29, 2012 7:37 PM
  • Hi,

    Was a long time ago when this thread was last active but thought to share a workaround if there are also others sticking with formulas (for any reason) instead of the otherwise great VBA macros.

    So if you "split" what you have to do into smaller chunks, you can do this all by formula(s) to keep its benefits.

    The trick I apply is using IIF in combination with SWITH, sample below (indents only for better readability, remove tabs and line breaks before using) I assume even a SWITCH in SWITCH would also work.
    (Here this is a country code auto-column by country name selected, we use both of them in reporting)

    This way it works like a charm and you can extend it further, however indeed not on my "TOP 10 stuffs that make my parents proud of me" list ;) :).

    Have fun.

    Imi

    IIf (
    [Text3]<>"Latvia" And [Text3]<>"" And [Text3]<>"All",
    Switch (
    [Text3]="Bosnia and Herzegowina","BiH",
    [Text3]="Ukraine","UA",
    [Text3]="Poland","PL",
    [Text3]="Netherland","NL",
    [Text3]="United Kingdom","UK",
    [Text3]="Switzerland","CH",
    [Text3]="Czech Republic","UA",
    [Text3]="Lithuania","LT",
    [Text3]="Bulgaria","BG",
    [Text3]="Romania","RO",
    [Text3]="Serbia","RS",
    [Text3]="Macedonia","MK",
    [Text3]="Hungary","HU"
    ),
    Switch (
    [Text3]="","?",
    [Text3]="All","ALL",
    [Text3]="Latvia","LV"
    )
    )

    Wednesday, August 13, 2014 4:29 PM