3 Replies Latest reply on Jun 7, 2010 2:20 PM by philmodjunk

    Converting text field to something simplier

    mgxdigital

      Title

      Converting text field to something simplier

      Post

      Hi,

      When our company first started, we only had 2 salespeople. Filemaker was not set up correctly and to keep track of what order went to what salesperon, there was a salesperson text field where the sales initals were entered.

       

      Now, we're up to 6 salespeople and i'm really needing to change how things are set up.

       

      If we contine to just type their initals in each order, then calculations become very difficult.

       

      I'm making calculations ranging from how many orders per salesperon shipped per month to the dollar amount per week eacah salesperon is bringing in. Since the field is currenly a text field, i'm having to create 6 different caluclations for each one i need.

       

      So for instance.

      There is one field   salesperson     text field

      There are 6 different values

      AAA

      BBB

      CCC

      DDD

      EEE

      FFF

      GGG

       

      I have a field called: salesperson a    calculation        salesperson = "AAA"

                                          salesperson b     calculation       salesperson = "BBB"

                                          and so on....

       

      So now, if i need to make another calulation for monthly sales for example. i have to make 6 calculations again refering all 6 salesperson fields.

      I'm wanting to reference all 6 salesperons figures on one layout.

       

      Is there any way at all to make this simpler? I know that this was not set up correctly and i really need to make it right. It's getting too out of control.

      Thanks so much!!!!

      -Robin

        • 1. Re: Converting text field to something simplier
          philmodjunk

          I agree that initials are not the way to go, you should have a table of sales personnel with unique ID numers generated in this table for each sales person.

           

          However, I'm not sure how your calculations are being complicated by the different sets of initials for each salesperson as described in your post.

           

          You can create summary reports that total the number of orders shipped per month by salesperson. You'd sort your records first by month and then by salesperson ID or their name (by referencing their name field in the prsonnel table). This would give you the sub totals you describe without a separate calculation for each salesperson.

           

          It would also work using their initials but the ID codes will work better for you for other reasons.

           

          Here's a link to a simple tutorial on setting up summary reports that you may find useful:

          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Converting text field to something simplier
            mgxdigital

            The table sounds like something i can do pretty easily.

            Should i relate it to the current salesperson field that i already have?

             

            In order words....if i make the salesperson table with an autogenerated salesperon id field then how do i relate it to my orders, purcahase orders, invoices that already have my old salesperon text field on it?

             

            Also, if the salesperon field is an auto generated serial number i guess i would need to make a layout to add new salespeople? Or how do i list the salespeople we can choose from for each order?

            • 3. Re: Converting text field to something simplier
              philmodjunk

              In order words....if i make the salesperson table with an autogenerated salesperon id field then how do i relate it to my orders, purcahase orders, invoices that already have my old salesperon text field on it?

               

              You'd restructure your database in steps.

              1. Create your new table and define your new ID field.
              2. Add an initials field and enter the sales person initials for each sales person in your new table.
              3. Temporarily link your existing tables to the new table by initials.
              4. Add a SalesPersonID field to each table you need to link to the SalesPersonnel table
              5. Use a script or Replace field contents to update your existing records with the new SalesPersonID number.
              6. Now that your records have matching ID numbers, update your relationships to use the new ID number fields in place of initials.

              A Layout to add newly hired Sales Persnnel and edit the info on existing personnel would be a very good idea.

               

              To select a sales person for an order or other related record, define a two column value list. Column one lists the new ID number and column 2 would list the sales person's name.