Forum Discussion

CarolMcconnell's avatar
CarolMcconnell
Qrew Captain
6 years ago

Address fields allow invalid State names

Is there a way to force the State name to be valid.  I have an office address field but the user can enter anything in the State name, it doesn't have to be valid, so we have a lot of records with invalid state names.  Is there a way to correct this when using address fields?
Tables and fields
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    There is a new feature called data validation that can be enabled at the table level.  I can't seem to get the help system to search in the last couple of days, but you make a formula to calculate an error message.  If the formula is blank result then the record will save, else it will block the save with an error.

    Note that if you enable this you would need to deal with the old records, or maybe have the formula only abort the save where [date created] is after June 27, 2019.

    Or else make users fix the state even though they may not have been the ones to enter it wrong.

    Post back if you need a list of states.
    • CarolMcconnell's avatar
      CarolMcconnell
      Qrew Captain
      6 years ago
      Thanks, My company is still on our own version and not the cloud but we will be moving in September, so I can try it then.
  • JordanMcAlister's avatar
    JordanMcAlister
    Qrew Captain
    6 years ago
    Do they input the address all in one field, or are they split up to multiple sections (for example: street address 1, 2, City, State, Postal Code)
    • JordanMcAlister's avatar
      JordanMcAlister
      Qrew Captain
      6 years ago
      So, are they just entering everything, including State, in only one of the boxes then and not splitting it up? Sorry - trying to understand how they are inputting incorrect State names. The default "state" field within the split up address field should be a drop down.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    If you look at the field properties for that address field it will give you the subfield names that you should be able to use in formulas

    It will tell you what the field name is for the state field
    • CarolMcconnell's avatar
      CarolMcconnell
      Qrew Captain
      6 years ago
      i was thinking of that and I was going to make a formula checkbox field.  My thought is how would I reference a field in another table that is not connected.  So if [Office Address State] is one of [State Table: State], yes, no.  

      Is that possible?
    • CarolMcconnell's avatar
      CarolMcconnell
      Qrew Captain
      6 years ago
      ok, so I would have to list out every State name in the formula?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      Yes, exactly
      This is not the formula you ned, but it has all the states.

      Case([<reference field name>],
      "Alabama", "AL",
      "Alaska", "AK",
      "Arizona", "AZ",
      "Arkansas", "AR",
      "California", "CA",
      "Colorado", "CO",
      "Connecticut", "CT",
      "Delaware", "DE",
      "Florida", "FL",
      "Georgia", "GA",
      "Hawaii", "HI",
      "Idaho", "ID",
      "Illinois", "IL",
      "Indiana", "IN",
      "Iowa", "IA",
      "Kansas", "KS",
      "Kentucky", "KY",
      "Louisiana", "LA",
      "Maine", "ME",
      "Maryland", "MD",
      "Massachusetts", "MA",
      "Michigan", "MI",
      "Minnesota", "MN",
      "Mississippi", "MS",
      "Missouri", "MO",
      "Montana", "MT",
      "Nebraska", "NE",
      "Nevada", "NV",
      "New Hampshire", "NH",
      "New Jersey", "NJ",
      "New Mexico", "NM",
      "New York", "NY",
      "North Carolina", "NC",
      "North Dakota", "ND",
      "Ohio", "OH",
      "Oklahoma", "OK",
      "Oregon", "OR",
      "Pennsylvania", "PA",
      "Rhode Island", "RI",
      "South Carolina", "SC",
      "South Dakota", "SD",
      "Tennessee", "TN",
      "Texas", "TX",
      "Utah", "UT",
      "Vermont", "VT",
      "Virginia", "VA",
      "Washington", "WA",
      "West Virginia", "WV",
      "Wisconsin", "WI",
      "Wyoming", "WY"