4 Replies Latest reply on Jun 23, 2009 7:51 AM by TSGal

    Need helping figuring out how to make this work..Complete newbie

    Bcannon

      Title

      Need helping figuring out how to make this work..Complete newbie

      Post

      I've used Filemaker for years, first time trying to design a database. So please be patient if this comes easy to you. 

       

      I've tried for the last week trying to figure out how to make this work properly, so let me explain what I am trying to do and hopefully some of you know the best was to do it.

       

      I manage a 4 bands(projects) full time and essentially I am trying to build a contact management database for all the people that I know in the music industry that are related to my bands. Additionally, if the contact has generated press or whatever for particular bands, I want to be able to copy and paste notes and put them in a note section that is limited to a particular artist. So I'm completely lost at the very basic, relationship level.. trying to figure out how one table links to another.

       

      After numerous, back to the drawing board moments, I've given up.. This was the last draft of tables that I had...

       

      -Companies-

       Company ID

      Company Name

      Company Type

      Address

      Phone Number

      Website

       

      -Contacts-

      Contact ID

      First Name

      Last Name

      Company

      Phone Number

      Email

      City

       

      -Bands-

      band ID

      band name

       

      -Involvement-

      Involvement ID

      contact ID

      band ID

       

      So then I linked Company name from -Companies- to company in -Contacts-

      and then linked contact id from -contacts- to contact id in -involvement- and finally linked project id from -involvemen- with project id from -projects-

       

      I know this is probably very basic, but I really don't know where to go from here.

       

      Help!

       

        • 1. Re: Need helping figuring out how to make this work..Complete newbie
          TSGal

          Bcannon:

           

          Thank you for your post.

           

          You are very close to what you want.

           

          Your Companies table looks solid.  The Contacts table is very close.  I would change the Company field to Company ID.  This way, if a Company changes name, you won't have to worry about changing the name in two places.

           

          The Bands table looks fine.

           

          In the Involvement table, you probably don't need an "Involvement ID" field.  As long as you have the Contact ID and Band ID, you are fine.  You probably have some additional fields that describe the involvement.  

           

          Since you want to copy and paste notes for a particular band, you may want to include another table called "Notes".  This would include:

           

          Band ID 

          Date (Date field)

          Notes (Text field)

           

          After creating these fields, click the "Relationships" tab to see a graphical representation of all your tables.  In the Bands table, select the "band ID" field and drag it over to the "Band ID" field in the Notes table.  You will now see a line connecting the two tables together.  On that line, halfway between the two tables, is an icon.  Double-click on that icon, and an "Edit Relationship" dialog box appears.  On one side has information about the Bands table, and the other side has information about the Notes table.  On the side that displays the "Notes" table, be sure to check the option "Allow creation of records in this table via this relationship".  This will allow you to add notes to the Note table when viewing the Band information.

           

          Go to the layout that has the Band information.  Pull down the View menu and select "Layout Mode".  At the top, in the Status area, there are several icons.  Putting your cursor over these icons display the type of tool.  Find the Portal tool and click on it.  On the layout, your cursor turns into a crosshair, and you are able to draw a box.  When you let go, you are prompted for the table you want to display.  Select the "Notes" table.  When prompted for the fields, select the Date and Notes fields.

           

          Go into Browse Mode (View menu) and go to one of your four bands.  You can now click on this portal, enter a date and some notes associated with it.  Perhaps paste in a review of a recent show.  That information is now linked to that band.

           

          This should get you pointed in the right direction.

           

          As you become more familiar with this when setting up the Companies, Contacts, Bands and how they all related, you may want to consider adding a "Shows" table (that may also link to a Venues table) with information about upcoming (and past) shows.

           

          Please let me know if you need clarification for any of the above steps.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Need helping figuring out how to make this work..Complete newbie
            Bcannon
              

            Filemaker screen

             

            TSGal-- thanks so much for taking the time to reply.

             

            Just a short clarification...

             

            If you could explain changing the company field to company ID? I'd appriciate it. 

            As you can see from the picture, I messed around with it enough to get it to show the name of the company rather than a Number, which I would rather see?

             

            My next question is, how can I show which bands a contact is involved with? and how can they be involved in multiple bands? 

             

            I posted a picture of all the relationships to make it visually easier to understand what I'm working with.

             

            Again, I value your time and I appriciate any tips you can give me.

             

            Brett

             

             

             

             

            • 3. Re: Need helping figuring out how to make this work..Complete newbie
              Bcannon
                

              Also- on the contact table, should I make just a name field, instead of having first and last name seperate? Will this make the showing up of the name easier on the bands field?

               

              Brett

               

               

              • 4. Re: Need helping figuring out how to make this work..Complete newbie
                TSGal

                Bcannon:

                 

                Thanks for the screen shot.  This helps with clarification.

                 

                You can definitely use the "Company Name" field.  Whatever works well for you.  My only concern is if the Company Name changes, you will have to change it in the Company table and the Contact table.  For instance (an extreme example), take Irving Azoff who had his own company managing the Eagles.  He was also CEO of MCA and a couple of other companies and managing several groups.  It is easy to change the name in the Company table, but then your relationship between the Contact breaks unless you also change the name of the Company name in the Contact field.  I'm sure this is an exception to the rule, and the Company may never change for you, but I would be negligent if I didn't mention it.  That is why I suggested a Company ID field.  The Company name may change, but the ID field would be constant.

                 

                Since a band can have several contacts, and a contact can have several bands, this creates a many-to-many relationship.  If you try to set a relationship between Contacts and Bands, you'll see you can get one contact to several bands or one band to several contacts, but not both.  Therefore, you need to create an intermediary table (join table) that contains a Contact ID field and a Band ID field, and you have done this with the Involvement table.  For example, Band "A" may have contacts "Contact1", "Contact3" and "Contact5".  You would create three records in the Involvement table with Band ID for Band "A", and the Contact ID having in successive records Contact1, Contact3 and Contact5.  Include the Band name from the Band table, and the Contact name from the Contacts table.  Then, at any time, you can search for a specific Contact to see what Bands they are associated with, or search any Band to see what Contacts are connected.  Does this make sense?

                 

                In the Contacts table, you can have just a name field if you want.  The caveat is if someone is named Jon Smyth or John Smith.  Or, if two Contacts are named the same but work at different Companies.  Again, whatever works best for you.  You can also create a calculation field, FullName, with the formula: First Name & " " & Last Name.  You can then use this field to display the full name instead of putting two fields on the layout.  Something to consider.

                 

                This should get you closer, but it may also bring up more questions.  :-)

                 

                TSGal

                FileMaker, Inc.