If Statement with multiple conditions?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
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. 
Photo of Manny De La Cruz

Manny De La Cruz

  • 940 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of dwhawe

dwhawe, Champion

  • 662 Points 500 badge 2x thumb
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
)
Photo of Manny De La Cruz

Manny De La Cruz

  • 940 Points 500 badge 2x thumb
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?
Photo of dwhawe

dwhawe, Champion

  • 662 Points 500 badge 2x thumb
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)
Photo of John Thomas

John Thomas

  • 1,062 Points 1k badge 2x thumb
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")
}
});
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
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.