4 Replies Latest reply on Nov 9, 2014 3:27 PM by philmodjunk

    finding records with more than 2 character in the first name field.

    gregStone

      Title

      finding records with more than 2 character in the first name field.

      Post

      Hi i am new to the forum and FM Pro and have a simple question regarding finding records with more than 1 character in the first name field.

      I have a record set with 1.2 million records and i need to find using the find feature how many full first names there are in the database.

      should be a char length more than 2 to find all the useful names  for a call list

      Any pointer most appersiated.

      Thanks

      Greg

       

       

        • 1. Re: finding records with more than 2 character in the first name field.
          BarrySpence

          The Len(text) function will return the length in characters of the text in a specified field.

           

          Create a new Calculation field. Enter the formula using these steps:

                 
          • double click the len(text) function in the function list.
          •      
          • double click 'text' in the formula box.
          •      
          • double click the field name of your 'first name' field in the field list.

          Done. Close the dialogue windows until you see only the edit layout window.

                 
          • Click the Field Picker button in the tools above the workspace.
          •      
          • Drag the new field to where you want it on the layout.
          •      
          • Close the Field Picker dialogue, and click Exit Layout to return to Browse mode.

          You can now use Find <3 on this field to find (and count) the records with only an initial or an initial plus a period in the 'firt name' field.

          Regards,

          Barry

           

           

           

          • 2. Re: finding records with more than 2 character in the first name field.
            gregStone

            Hi Barry,

            Unfortunatly i am so new to this interface i cant work out where the function list is in the menu bar.

            i have some idea how to us the manage script and can create basic queriers.

            What i was hoping to do is just use the simple find feature and type in the comand there, which is what i am sure your explained but i can find where to start.

            any other pointers?

            Thanks

             

            • 3. Re: finding records with more than 2 character in the first name field.
              BarrySpence

               

              Hi Greg,

              Apologies for misnaming the function. The shortened "LEN(text)" version is what I''m used to from spreadsheets; FMP uses the full "length(text). I suspect we're both in the baby steps stage with FMP, so what I'll describe may not be the most efficient way to do this.

              Creating a new Calculation field:

                     
              • With the database file open in FMP, click the Edit Layout button just above the top right corner of the work area.
              •      
              • Click the Manage icon that appears above the button, then choose Database from the pop-up menu that opens.
              •      
              • In the "Manage Database for…" window that opens, click Fields to get a list of all fields in the DB.
              •      
              • In the Field Name box below the list, type a name for your new field, then use the pop-up to the right of the box to set the type to Calculation.
              •      
              • Click Create to open the Formula box (and the operator buttons and list of functions).
              •      
              • Scroll the function list to and double-click on Length(text) to move that function to the formula box.
              •      
              • In the formula box, double click on the word 'text' to select it, then in the Field list, double-click on your 'first name' field to  make it replace the word 'text'. 
              •      
              • The formula box should now contain "Length ( first name )" and have the insertion point located immediately after the word 'name'. 
              •      
              • Check that "Calculation result is" is set to Number, reset if necessary, then click OK to create the field and return to the previous window, with new field added to the bottom of your Field list (but not to your Layout).
              •      
              • Click OK to close this window, then click OK to close the "Manage…" window.
              •      
              • To add the field to your Layout, click the Field Picker button in the group to the right, above the work space.
              •      
              • Select your new field (at the bottom of the list) and drag it to the layout. Place it in a convenient location, and resize to hold a number less than 100 (unless you have and want to see the length of names with more than 100 characters).
              •      
              • Click the Exit Layout button to return to browse mode.

               

              One thing I would strongly suggest, is to create a 'sandbox' version of your database to use for testing changes before inserting them into the 'real' version. 

              Regards,
              Barry

              • 4. Re: finding records with more than 2 character in the first name field.
                philmodjunk

                Enter Find mode and use this criteria in the field:

                @@@*

                @ is a find operator meaning any single character. * represents from 0 to any number of characters.