Discussions

 View Only
Expand all | Collapse all

Address fields allow invalid State names

  • 1.  Address fields allow invalid State names

    Posted 06-27-2019 16:54
    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?


  • 2.  RE: Address fields allow invalid State names

    Posted 06-27-2019 17:00
    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)


  • 3.  RE: Address fields allow invalid State names

    Posted 06-27-2019 17:00
    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.


  • 4.  RE: Address fields allow invalid State names

    Posted 06-27-2019 17:14
    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.


  • 5.  RE: Address fields allow invalid State names

    Posted 06-27-2019 17:15
    It is the address field types that Quickbase has, so they are split up. 


  • 6.  RE: Address fields allow invalid State names

    Posted 06-27-2019 18:26
    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.


  • 7.  RE: Address fields allow invalid State names

    Posted 06-27-2019 17:58
    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


  • 8.  RE: Address fields allow invalid State names

    Posted 06-27-2019 18:21
    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?


  • 9.  RE: Address fields allow invalid State names

    Posted 06-27-2019 18:23
    It has to be a hard coded formula, and not dependent on a lookup field


  • 10.  RE: Address fields allow invalid State names

    Posted 06-27-2019 18:30
    ok, so I would have to list out every State name in the formula?


  • 11.  RE: Address fields allow invalid State names

    Posted 06-27-2019 18:36
    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"


  • 12.  RE: Address fields allow invalid State names

    Posted 06-27-2019 18:50
    Thanks I have the state list.  Thanks for your help.