10 Replies Latest reply on Oct 15, 2009 1:01 PM by squiggle

    Creating links and tables

    squiggle

      Title

      Creating links and tables

      Post

      I'm using FMP 10 on XP pro. I have multiple Sales Rep Groups (10 of them) that sell my products. There are an average of 7 sales reps in each group (est. total of 70 sales reps). I want to associate their Sales Rep Group name, phone # and email address with them. So heres what I'm trying to do: Let's say a store buys 3 widgets from us and John Doe sold them these widgets. I will look up the store in my database and when I select a sales rep name (Doe, John) I want all of the sales rep info to appear like this:

      Doe, John

      XZY Sales Rep Group

      203-555-1212

      jdoe@yabados.org

       

      Hope this explains it well enough. Thank you

       

       

        • 1. Re: Creating links and tables
          philmodjunk
            

          Assumptions I am making:

           

          Each sales person belongs to only one sales group.

          Each store transaction is credited to only one salesperson.

           

          What you need:

           

          Two tables for your sales person data:

           

          Table:  SalesPersonnel

          SalesPersID (auto-entered serial number)

          Name

          (any other data such as contact info specific to this one sales person)

          GroupID (number)

           

          Table: SalesGroups

          GroupID (auto-entered serial number)

          GroupName

          GroupPhone

          GroupEmail

           

          Relationship:

          SalesGroups::GroupID = SalesPersonnel::GroupID

           

          In your table(s) where you record sales to stores, record the SalesPersID number in a field and link that field to the SalesPersonnel table. Now you'll be able to access the group info for a given salesperson using those two relationships simply by placing the desired fields from the related tables directly on your layout.

          • 2. Re: Creating links and tables
            squiggle
              

            Thank you. I'm very new at this so I don't quite get it yet. Let me try this. I uploaded a step by step screen shot of what I'm doing and may have over explained it. Here is the picture story: http://www.flickr.com/photos/39221961@N08/4007475916/

            I already created a value list of my Rep Groups . . that was easy. But now I have 7 people in each rep group with a phone number and email associated with them. So for example, I select "Be Smart Company" as the company who sold my product to a customer. After I do that, the next field below lists (in a drop down format) the 7 people that work for Be Smart. When I select one of the people, the next 2 fields below fill in with their email and phone number. 

             

            If you can explain less technically . . . great! Thank you for your help!!!

            • 3. Re: Creating links and tables
              philmodjunk
                

              You need tables, not just value lists.

               

              Select Manage | Database | Tables

              Enter SalesPersonnel as the new table name and click "Create"

              Enter SalesGroups as a new table name and click "Create"

               

              Click the fields tab and select SalesPersonnel from the table drop down at the top left corner of your screen.

              Enter SalesPersID in the field name box at the bottom. Select Number from the type drop down in the bottom right corner and click "Create".

              Click Options..., select the Auto-enter tab from the pop up dialog. Select the Serial Number auto-enter option. Click OK.

              Enter Name in the field name box, select text as its type and click Create.

              Create a field named GroupID as a field of type Number.

              If you have other information you need to track on an individual sales person--such as their personal cell phone number--you can create addition fields in this same manner.

               

              Select SalesGroups from the from the table drop down.

              Create GroupID as a number field, set it up as an auto-entered serial number just like you did for SalesPersID.

              Create the other three fields, GroupName, GroupPhone, GroupEmail, for your group records as text fields.

               

              Select the table you've already created from the table drop down and create a new number field, SalesPersID for it.

               

              Click the relationships tab.

               

              You will see at least one table occurrence box for each of the three tables I've mentioned here. Use your mouse to click and drag the SalesPersID field in your original table to the SalesPersID field in SalesPersonnel (SalesPersonnel::GroupID). 

               

              In the same manner, create a relationship linking SalesGroups::GroupID to SalesPersonnel::GroupID

               

              Click OK to dismiss the Manage | Database dialog box.

               

              Select your new Sales Personnel layout and set it up as a table view layout. Format the GroupID field as a pop-up menu listing a 2 column value list with SalesGroups::GroupID as the hidden first column and SalesGroups::GroupName as the visible 2nd column. Enter each of your SalesPersonnel records here, using the pop up menu to assign them to sales personnel groups.

               

              On your invoice layout that you've already created. Add the SalesPersID field you've created for this table. Format it as a Popup menu with a 2 column value list: SalesPersonnel::SalesPersID (hidden); SalesPersonnel::Name

               

              Now you can add the fields from the SalesGroups table to your invoice layout and they will display the relevant Sales Group information whenever you select a sales person from the pop-up menu.

              • 4. Re: Creating links and tables
                squiggle
                  

                Very well detailed and I breezed through it until . . .  I got lost here: >> Format the GroupID field as a pop-up menu listing a 2 column value list with SalesGroups::GroupID as the hidden first column and SalesGroups::GroupName as the visible 2nd column. (I can do this>)Enter each of your SalesPersonnel records here, using the pop up menu to assign them to sales personnel groups.

                 

                On your invoice layout that you've already created. Add the SalesPersID field you've created for this table. Format it as a Popup menu with a 2 column value list: SalesPersonnel::SalesPersID (hidden); SalesPersonnel::Name

                 

                Now you can add the fields from the SalesGroups table to your invoice layout and they will display the relevant Sales Group information whenever you select a sales person from the pop-up menu.

                 

                • 5. Re: Creating links and tables
                  philmodjunk
                    

                  Select Manage | Value Lists...

                   

                  Enter a name for your value list and click New.

                  Select the use values from Field option and specify the GroupID field in the SalesGroups tables. (Select the table from the left drop down, click the field to highlight it.)

                  Select SalesGroup from the right hand drop down and click GroupName to specify GroupName as the second column value.

                  Click the "Show values only from second field" check box to hide the GroupID values.

                  Click OK three times to dismiss the dialogs.

                   

                  Locate your layout.

                  Enter layout mode.

                  Use Field/Control | setup... to format the field as a popup menu and select the above value list for its values.

                  • 6. Re: Creating links and tables
                    squiggle
                      

                    Thank you again. I'm sorry to say think I got everything in place, but the relationships are not working. Would it be possible for me to email you what I have??

                     

                    Thank you

                    John

                    • 7. Re: Creating links and tables
                      philmodjunk
                        

                      Or you can upload it to a fileshare site and post the link here.

                       

                      I've sent my email address via private message. (Check the envelope icon in upper right corner of this screen.)

                      • 8. Re: Creating links and tables
                        squiggle
                          

                        Thank you. I sent the file by email.

                         

                        I look forward to your help and comments.

                        • 9. Re: Creating links and tables
                          philmodjunk
                            

                          There are a number of things not quite right. To start, you left out this part of the process:

                           

                          "On your invoice layout that you've already created. Add the SalesPersID field you've created for this table. Format it as a Popup menu with a 2 column value list: SalesPersonnel::SalesPersID (hidden); SalesPersonnel::Name"

                           

                          As a result, there's no link to your Sales Group and Sales Personnel data.

                           

                          Repeating the steps but naming tables and layouts from the file you sent me:

                           

                          Create a new value list, Sales Personnel that specifies the name field in SalesPersonnel. Specify the SalesPerID field as column 1, the name field as column 2 and hide the first column.

                          Go to Manage | Database | Fields

                          Select the Contact Management Table

                          Create a new number field, SalesPersID.

                          Click the relationships tab

                          Link Contact Management::SalesPersID to SalesPersonnel::SalesPerID

                          Place Contact Management::SalesPersID on your Record Detail layout.

                          Use Field/Control | Setup... to format it as a pop up menu displaying values from the Sales Personnel value list.

                           

                          More details to fix:

                          In Manage | Database | Relationships, double click the = in the relationship line linking SalesPersonnel and SalesGroup.

                          Click the Row, SalesPerID = SalesPersID and click the delete button to remove it. These tables should be linked just by groupID, nothing else.

                           

                          Return to your layout, enter layout mode an delete the fields next to the layout text, Rep Group, Rep Name, Rep phone and Rep Email.

                          Place the new SalesPersID field from Contact Mangement in place of the Rep Name field you just deleted.

                          Add new fields to the layout for:

                            Rep Group, Select GroupName from the SalesGroup table

                            Rep Phone, Select GroupPhone from the SalesGroup table

                            Rep Email, Select GroupEmail from the SalesGroup table.

                           

                          Return to browse mode, select your SalesPersonnel layout and enter unique ID numbers for each sales person. (I found these fields blank in the copy you sent to me.) Future records will be automatically numbered when you create a new record. (You can update the next value setting for the serial number to be at least 4 to avoid duplicate sales personnel ID numbers.)

                           

                          Now go back to Record Detail and test results by selecting different Sales Personnel from the pop-up menu.

                           

                          I'm emailing you an updated copy of your file so that you can compare it to the above steps.

                          • 10. Re: Creating links and tables
                            squiggle
                              

                            Hi Phil

                             

                            Thanks again. Did you receive my email regarding the issue?

                             

                            Thank you

                            John