12 Replies Latest reply on Nov 21, 2012 8:40 PM by ArchitectureConceptsLighting

    How to Separate Name and Address into Separate Fields (not answered)

    ArchitectureConceptsLighting

      Hi:

      This is my second post ever so hope that its sent correctly. Hope my spelling and gramer is OK - am explaining my question so that its understandable. and not too long which is seems to be. Very much welcome any advice on best / better way to do a post including how detailed it needs to be, etc. Also have 3 screen shots attached to field definition, calc with eror messages to help desiribe. Couldn't get past the first calc.

       

      My intent is to hpefully copy to my clip board contact info from a company's web page to one field then FM separates the complete contact info to separate fields but based on the following instructions from an FM support Q an A I can not get it to work. We have very slow typest at work including myself with lack of proficiency and this may come in handy if it works. Its not for importing. For now if the data must be limited to the first name, last name street adress, city state and zip per based on instructions that would be great but would be nicer if phone email email, etc. can be part of formula too. Instructions are as folows:

       

      FM Q and A Web Page:

      http://help.filemaker.com/app/answers/detail/a_id/5063/~/how-to-separate-name-and-address-into-separate-fields

       

      Instructions:

      If your database has name and address entered into a single field, you can break out this information into separate fields. The name and address may be in a single field because you originally entered the data that way, or because you imported names and addresses from another program or database.

      In FileMaker, you separate the name and address information into separate fields by defining calculation fields. Here’s one example. These calculation fields make the following assumptions (you may have to modify these calculation formulas to fit your situation):

      1. 1) The name includes only a first and last name (no middle initial or title)
      2. 2)There is a comma between the city and state
      3. 3) The state is two characters
      4. 4) The zip code is 5 characters
      5. 5) The address is one line long
      6. The field with the name and address is called Full Text, and is three lines long: a return is typed between the name and address, another return is typed between address and city

      TRY THAT AGAIN. AS I SAID IN THE EDIT: Sorry accidently clicked assumed answerd which it is not.

       

      FirstName - Calculation (Text)
      Formula: Left (FullText,Position (FullText," ",1) -1)
      LastName - Calculation (Text)
      Formula: Middle (FullText,Position (FullText," ",1) + 1,Position (FullText,"¶",1) - Position (FullText," ",1) - 1)
      Address - Calculation (Text)
      Formula: Middle (FullText,Position (FullText,"¶",1) +1,Position (FullText,"¶",Position (FullText,"¶",1) + 1) - Position (FullText,"¶",1) - 1)
      CityStateZip - Calculation (Text)
      Formula: Right (FullText,Length (FullText) - Position (FullText,"¶",Position (FullText,"¶",1) + 1))
      City - Calculation (Text)
      Formula: Left (CityStateZip,Position (CityStateZip,",",1) -1)
      State - Calculation (Text)
      Formula: Middle (CityStateZip,Position (CityStateZip,", ",1) + 2, 2)
      Zip - Calculation (Text)
      Formula: Right (CityStateZip,5)

      After the calculation fields have calculated, verify that they are working properly in Browse. You can then go back to Define Fields and change them from calculation fields to text fields. This will freeze the name and address values in those fields. You can then delete the original Full Text field and enter new names and addresses into the separate fields.


      Note: The CityStateZip field does not need to appear on any layouts. It is created as an intermediate field to creating the separate City, State and Zip fields. If you change these calculation fields to text fields, you can delete the CityStateZip field along with the FullText field.

       


       

       

      Message was edited by: architect. Sorry accidently clicked assumed answerd which it is not.