Discussions

Expand all | Collapse all

If Statement with multiple conditions?

  • 1.  If Statement with multiple conditions?

    Posted 05-12-2017 14:59
    I'm having a moment and figure out how to format this formula. 

    I just need the statement to read,

    If [state abbreviation]= ("NJ","NY" etc. etc....), 30,
    If [state abbreviation] = ("MI", etc...), 20....

    and so one. So a select number of states need to have a number associated with them in this field. The format I'm using is just returning syntax errors every time. 


  • 2.  RE: If Statement with multiple conditions?

    Posted 05-12-2017 15:27
    You may want to consider using CASE.

    Case([state abbreviation],"NJ",30,"NY",30"MI",20)

    To use the IF statement, I would do it like this:

    If([state abbreviation]="NJ",30,
    [state abbreviation]="NY",30,
    [state abbreviation]="MI",30
    )


  • 3.  RE: If Statement with multiple conditions?

    Posted 05-12-2017 16:21
    Case(
         [abbreviation], "HI","ME","MI","MO","MS","NE","NJ","NM","OH","OK","SC","UT","WV", 30,
    Case(
         [abbreviation], "AZ","CO","FL","GA","IA","ID","IN","MA","MD","MT","NC","ND","OR","VA","WY", 45,
    Case(
         [abbreviation], "AL","AK","AR","CA","CT","DE","IL","KS","LA","MN","NH","NV","NY","PA","RI","SD","TN","TX","WI", 60,
    Case(
         [abbreviation] = "KY", 75,""))))


    This is what I have but its saying its still not right, any ideas?


  • 4.  RE: If Statement with multiple conditions?

    Posted 05-12-2017 16:28
    You didn't follow the format.  You only use the function Case once.  You call the function, state the field then value testing for and value to return.

    I only used three in the example but Case, then (, then the field then the state and then the value for that state then the next state and then the value for that state and son on until you list all the states and values for each.  You ;ist a vaule for each state, not a bunch of states and one valu for all combined.

    Case([state abbreviation],"NJ",30,"NY",30"MI",20)


  • 5.  RE: If Statement with multiple conditions?

    Posted 05-15-2017 08:05
    Using  Case you can list the values what dwhawe  said is correct .Option has a value. You can't use nested cases 

    Case(
      [state abbreviation], "HI", 30, "ME",30,"MI",30,"MO",30,"MS",30,"NE",30,"NJ",30,"NM",30,"OH",30,"OK",30,"SC",30,"UT",30,"WV", 30,
    "AZ",45,"CO",45,"FL",45,"GA",45,"IA",45,"ID",45,"IN",45,"MA",45,"MD",45, "MT",45,"NC",45,"ND",45,"OR",45,"VA",45,"WY", 45,
    "AL",60,"AK",60,"AR",60,"CA",60,"CT",60,"DE",60,"IL",60,"KS",60,"LA",60,"MN",60,"NH",60,"NV",60,"NY",60,"PA",60,"RI",60,"SD",60,"TN",60,"TX",60,"WI", 60,
    "KY", 75 )

    Default value is null , if you need default value after 75 just type a value without quotes 


    Another Solution

    Instead of that i think you can use script. 

    Create a text field with From list options: State Abbreviations 

    Create another text field : Value for Abbreviation

    Using Image on load technique call the JS page on to the form

    Write the Code like

    Using on Change  functionality , check the value of the State Abbreviations field , get the value from the State Abbreviations Field, using  switch case define the three cases and  values for each  cases  Take that value using id , and  store the value to the Value for Abbreviation Field .

    using J query Properties make  the  Value for Abbreviation Field as read only.

    Code :

    You need to change in code according to field id's
    _fid_38 -> id of State Abbreviations Field
    _fid_37 -> id of Value for Abbreviation Field

    $("#_fid_38").change(function(){
    var caseName = $("#_fid_38").val();
    switch(caseName){
        case "HI":
        case "ME": 
        case "MI": 
        case "MO": 
        case "MS": 
        case "NE": 
        case "NJ": 
        case "NM": 
        case "OH":
        case "OK": 
        case "SC":
        case "UT": 
        case "WV": 
        $("#_fid_37").val(30).prop("readonly", true);
        break;

        case "AZ": 
        case "CO": 
        case "FL":
        case "GA":
        case "IA":
        case "ID":
        case "IN":
        case "MA":
        case "MD": 
        case "MT":
        case "NC":
        case "ND":
        case "OR":
        case "VA":
        case "WY": 
        $("#_fid_37").val(45).prop("readonly", true);
        break;

        case "AL": 
        case "AK": 
        case "AR":
        case "CA":
        case "CT":
        case "DE":
        case "IL":
        case "KS":
        case "LA": 
        case "MN":
        case "NH":
        case "NV":
        case "NY":
        case "PA":
        case "RI":
        case "SD": 
        $("#_fid_37").val(60).prop("readonly", true);
        break;

        default: alert("Name Not Listed in any of the three cases")
    }
    });


  • 6.  RE: If Statement with multiple conditions?

    Posted 05-15-2017 10:26
    Another simple choice is to make a table of State Abbreviations, and makea Relationship back to the detail records. Then pull in the numerical values on a lookup.