1 2 3 Previous Next 30 Replies Latest reply on Mar 28, 2016 11:37 AM by t_e_x

    Preferred method for address: Multiple fields or multi-lined field

    t_e_x

      I am curious to get the general consensus on the preferred method of storing multiple lined addresses.  I currently have 3 fields for Address Line 1, Address Line 2, and Address 3.

       

      Would it be better to create a single address field, that on the layouts, accepts multiple lines?

       

      Thank you,

       

      Michael

        • 1. Re: Preferred method for address: Multiple fields or multi-lined field
          fitch

          Of course City, State, Zip, Country would be separate fields, but for the street Address in general I think a single field is fine.

          • 2. Re: Preferred method for address: Multiple fields or multi-lined field
            mikebeargie

            The first time you forget to draw a field as multi-line on a layout or report the multi-line single field is flawed. It happens more often than you think.

             

            I prefer multiple fields because of that, but I find it also makes searching easier.

            • 3. Re: Preferred method for address: Multiple fields or multi-lined field
              lkeyes

              I'm a multi-line guy.    fname, lname, address1,  address2, city, st, zip

              If you validate your addresses with SmartyStreets, they will usually put things on two lines, if it is anything other than a single street address and number. 

               

              ---- Larry 

              • 4. Re: Preferred method for address: Multiple fields or multi-lined field
                DavidJondreau

                It depends on what it's being used for. I generally like one field for Address. If you're creating mailing labels, that's fine. You may one or two or three or four lines.

                 

                If you're using it for something else though, say to put into Google Maps as well as mailing, then you may not want to feed it the second line which is an apartment or something.

                • 5. Re: Preferred method for address: Multiple fields or multi-lined field

                  Agreed. It's not necessarily either/or.

                   

                  I would almost always use, for reasons mentioned here already, separate fields for Name, address, and such, but possibly also a  (possibly calculated) field for your label printer or mail merge with the right "Sir/Madam/Ms/Mr"., etc.

                   

                  I'm not a fan of multi-line fields, but for an address, with "Suite" or other parts, it's fine as it's all part of the address piece.

                   

                  Just remember to set each text field to go to the next field when the user types RETURN or ENTER or the user will be typing in the next line of the field - giving you a multi-line entry when you might not have wanted it.

                   

                   

                  HTH

                   

                  - m

                  • 6. Re: Preferred method for address: Multiple fields or multi-lined field
                    keywords

                    I'll add my vote to the separate fields approach as, in my experience, a better way to control formatting. This is largely because I've had dealings with a client database that got terribly messy when freeform data entry was allowed—extra spaces, rogue carriage returns, upper and lower case issues, sloppy typing, etc. all had crept into this file and it was a major task to unravel all the duplication and superfluous content.

                    Simply splitting data into single-line fields is not sufficient in itself, but it certainly helps. I also use an auto-enter calc to remove unwanted spaces and carriage returns, and to produce standard titlecase formatting. (To allow for odd cases you also need a means of allowing users to override the standard formatting if necessary). For the vast majority of addresses one field is enough, and I don't think it is too big a problem to have a second field which is hardly ever used. Once in a blue moon you might come across an address which needs a third field, which I would allow via the override mentioned already, rather than have a third field.

                    I also have two concatenated calc fields for particular uses. One creates an address label for obvious uses. The other creates a comma-separated string which is useful for various purposes, including value lists.

                    • 7. Re: Preferred method for address: Multiple fields or multi-lined field
                      Extensitech

                      Mike Beargie wrote:

                       

                      The first time you forget to draw a field as multi-line on a layout or report the multi-line single field is flawed. It happens more often than you think.

                       

                      I prefer multiple fields because of that, but I find it also makes searching easier.

                       

                      The first time you forget to add "address2" to a layout, you have the same problem.

                       

                      As far as searching, I find the reverse to be true.

                       

                      I would not ever use multiple fields for the street address unless there's a clear definition of what goes in each one. In my experience, if you have Address1, Address2, you'll end up not knowing what info is in which field. Address2 might be an apartment number, a PO box (which should rightfully be a separate address), a "c/o" line, or just about anything else you can think of. Then again, any of those things might end up as "Address1", as well. If you want to search, you have to guess which field the user decided to put which part of the street address in.

                       

                      If you specifically want to break out any of these things, it makes sense to have a separate field and label it "PO Box" or "Apartment Number" or "c/o", or whatever, but if you leave it as Address1 and 2, users will guess, and you'll end up with inconsistent data, and not knowing where to enter your search criteria.

                       

                      Chris Cain

                      Extensitech

                      • 8. Re: Preferred method for address: Multiple fields or multi-lined field
                        CarstenLevin

                        And I will spread confusion by taking the view that I am definitely not for or against any of the approaches.

                        • If it is only for print of envelopes I would consider lines in one field (use the layout/print function to collapse when the lines are not used).

                        But can you really be sure that it is always only for that purpose or that you do not want the individual attribute for some specific purpose.

                         

                        • If it is for addresses and instructions I would usually consider using two or more fields (how often do you need 3)
                          • 1600 Pennsylvania Avenue
                          • First gate to the left
                        • Or as in many Danish addresses
                          • Northern Bridge Street 34
                          • The back yard entry through small gate
                          • Use the box at the right side for small packages

                         

                        The problem is that when you start by putting more lines, slightly different attributes, into one field you may later find out that you need them for different purposes. Like if you want to send the instruction into a different field on a web formula or print it with a different typography on the package.

                        Thus I would consider getting the best of two worlds: Enter them in 2-3 different fields and use a calculation field to combine them for display/print.

                         

                        The first line is typically the address where you enter data in the right form depending on the convention in the individual country. And the next lines are typically something completely different, like a more local and unconventional description of where to enter/deliver.

                         

                        But then to what could be the real issue

                        A company (or a person) can have more than one address, even if they are only operating from one place.

                        • Visit address for foreign state heads of the White House (constructed)
                          • Madison Pl. NW
                          • Enter through the large marble gate
                        • Mail address
                          • 1600 Pennsylvania Avenue NW
                          • -
                        • The cottage
                          • 2100 Park Central Rd
                          • Only by appointment

                         

                        How will you know how many addresses you need for one entity (the company)?

                        Visit, mail, delivery of large packages and by truck, employee entrance .... you name it.

                        Thus it is in some cases a very good idea not to have the address fields in the company table. Create a different table for addresses. The same for contact information like email, telephone, skype ....https://www.youtube.com/watch?v=1cS9kWB5K3I

                         

                        Most of us have learned that one minute more work with pen and paper before touching the table structure is going to pay back many many times over the life span of a database.

                         

                        Therefore your question is very relevant.

                         

                        This is not a FileMaker issue but a fundamental Relational Database issue. Therefore we can win a lot by using common concepts and rules. It is not, definitely not, waste of time to read some of the relatively brief articles introducing the relational model, TMBF (The Model Behind FileMaker:-).

                         

                        Using an hour or two as a beginning may turn out to be your best investment ever.

                         

                        http://www.dummies.com/how-to/content/knowing-just-enough-about-relational-databases.html

                        http://archive.oreilly.com/pub/a/ruby/excerpts/ruby-learning-rails/intro-ruby-relational-db.html

                        Database normalization - Wikipedia, the free encyclopedia

                        http://agiledata.org/essays/dataNormalization.html

                         

                        Some videos

                        Relational Database Concepts - YouTube

                        00 - Normalization Intro - YouTube

                        Modeling

                         

                        Btw: Some of the keys used in the examples have meaning, like class001 and class002. I personally (strongly) disagree and would suggest a serial number or UUID or a combination of those, absolutely without meaning.

                        • 9. Re: Preferred method for address: Multiple fields or multi-lined field
                          Extensitech

                          Definitely agree that addresses for contacts should have their own table, as should phone numbers and other contact info, since you can't predict ahead of time how many a contact will have.

                           

                          But can you really be sure that it is always only for that purpose or that you do not want the individual attribute for some specific purpose.

                           

                          A field without a specific purpose seems like a problem in and of itself. Could you not name a field for your examples "delivery instructions" (arguably, not part of the address at all) or even just "notes"? This seems to highlight my point that with address1, address2, (etc.) the user will, in the absence of better instructions, enter a wide variety of information into those fields, and choose which field to put data in pretty much at random.

                           

                          Understand, I'm all for adding more fields if you need more attributes. Based on data I've had to wrangle in various systems, though, I'd advocate for naming those attributes clearly so that the entry is at least somewhat predictable.

                           

                          One thing we do is that we have a countries table where you can specify an address mask, such as:

                           

                          <<street>>

                          <<city>>, <<state_province>> <<postal code>>

                           

                          for the US, or

                           

                          <<street>>

                          <<postal code>> <<city>> <<state_province>>

                           

                          for Germany (hope I got that right off the top of my head). You could also include County (UK?), and if Danish addresses commonly include delivery instructions, you could add a field for that and include it in the mask. When entering an address, the country has a default but can be changed. For printing envelopes, labels, etc., we use a calculated field that replaces the merge text with values from the address.

                           

                          Bottom line, if I have a field called "street address", that's what should be in it. Nothing else should be in that field, and that attribute shouldn't be randomly spread over two or more fields. If we need another piece of information (like the apt # in a separate field, or a c/o, or delivery instructions, or even just "notes") then we should add a field for that.

                           

                          To be clear, I'm not trying to be argumentative, especially with such carefully thought-out responses from some posters with obvious experience to draw on. Just sharing what I've come to through my own experiences, and happy to hear what others think or why I might just be totally wrong-headed.

                           

                          HTH

                          Chris Cain

                          Extensitech

                          • 10. Re: Preferred method for address: Multiple fields or multi-lined field
                            beverly

                            Carsten, I typically have an ADDRESS table where all this kind of data is an 'address'. Then you can also provide a means to 'type' the data: street, mailing, main office, back 40, etc. If you need to send something through a postal service a 'mailing address' (street or box#) may be needed. If you ship a large package that UPS, FedEX, etc. needs to deliver, the address may be more complex with instructions.

                             

                            The person setting up electrical work may need to know the location of the nearest pole, and it may not be a building location. So, all kinds of addresses. Just include common types and further descriptions as needed to the table.

                             

                            beverly

                            • 11. Re: Preferred method for address: Multiple fields or multi-lined field
                              DavidZakary

                              I prefer the multi-field approach personally. Especially for city, state/prov, postal code/zip fields. I'll tend to do two or three fields for the street address depending on client usage.

                               

                              In list views, its much easier to perform sorts and analysis of data when things are split out. Labels and full address display is a bit more of a pain but I find having the separate fields creates more visual muscle-memory (eye memory?). The user will always have a fixed place to look for certain data instead of visually scanning a block of text. It is much easier to combine data rather than to parse it out.

                               

                              I find searches are easier on separated data, especially when you have cities that have the same name as a state. If you're searching a single field are you searching for Kansas City or the Kansas the state? Just to add confusion - Kansas State University is located in Manhattan, Kansas. Try explaining how to do a search with operators to a newly on-boarded user with that as an example.

                              • 12. Re: Preferred method for address: Multiple fields or multi-lined field
                                beverly

                                I find searches are easier on separated data, especially when you have cities that have the same name as a state. If you're searching a single field are you searching for Kansas City or the Kansas the state? Just to add confusion - Kansas State University is located in Manhattan, Kansas. Try explaining how to do a search with operators to a newly on-boarded user with that as an example.

                                 

                                quick find?

                                 

                                beverly

                                • 13. Re: Preferred method for address: Multiple fields or multi-lined field
                                  Extensitech

                                  Definitely agree about city, state, postal code. (Also county, country.)

                                   

                                  "Two or three field for street address depending on client usage" could make since, too, if the solution requires unit number broken out, or street number even (for cases where decisions are made, for instance, based on odd/even street numbers.

                                   

                                  Address 1, 2 and 3, though... Am I going on too much about this? Consider:

                                   

                                  Address1: 123 Elm St

                                  Address2: Apartment 4

                                  Address3: PO Box 333

                                   

                                  and then

                                   

                                  Address1: c/o John Smith

                                  Address2: 456 Elm St

                                  Address3; (blank)

                                   

                                  Now, the user wants to find everyone who lives on Elm St...

                                   

                                  Breaking out the parts for an address makes sense for the same reason that having one "name" field is bad: You can always combine things, but you can't always break them apart.

                                   

                                  OTOH, breaking one, distinct attribute (street address) into multiple fields, without making it clear why they are separate attributes and what those are, just means that inevitably, you won't know where to put your data during data entry, or where to find it during a search.

                                   

                                  Chris Cain

                                  Extensitech

                                  • 14. Re: Preferred method for address: Multiple fields or multi-lined field
                                    keywords

                                    Just to clarify my earlier post, when I mentioned two fields being sufficient for the vast majority of cases I was only talking about the "street" part of an address. I have further separate fields for city, state, country, postcode etc. I also totally agree with, and use myself, separate tables for addresses, phone, email, etc. And I have a field to nominate type (postal, home, office, etc).

                                     

                                    As for the street address issue, there are so many variables that if you tried to break it all out into separate fields for specific purposes (street number, unit number, name of street, whether street/road/avenue/etc, PO box number, floor level, etc) you would end up with a lot of fields many of which would be unused data holes in most records, and a confusing data entry process for users. I think it is the lesser evil to accept that the generic Line One and Line Two fields will have mixed data. Most people can cope with entering 666 Main Street into a single field and can accept (if they even notice) that this entry is actually three separate pieces of data. To carry my thinking a little further, I guess the way I see it is that it makes sense to keep together the parts of the address that you only need to deploy as a unit.

                                    1 2 3 Previous Next