7 Replies Latest reply on May 13, 2010 6:58 AM by needsolution

    Creating database with different needs/outcomes

    needsolution

      Title

      Creating database with different needs/outcomes

      Post

      Hi,

      I am fairly new to using Filemaker Pro and use version 10.  I am trying to create a database that will provide mailing contact addresses for letters and avery labels.   I have two tables that require connecting (table 1 is MPs and table 2 is our organizations) but not always for the same reasons.  What is the best way to develop such a database?  The MP table updates roughly every 4 years and the organizations table has constant changes.

       

      How do I make this work if I want to generate letters and mailing addresses from the following queries?

      1.  to MPs where we have our organizations

      2.  to MPs where we do not have our organizations

      3.  to MPs on the outskirts where we have our organizations (no overlaps)

      4.  to MPs on the outskirts where we do not have our organizations (no overlaps)

      5.  to MPs where we have multiple organizations in same district

       

      How do I connect the tables to retrieve what I want?

      Thanks for any input.

      needsolution

        • 1. Re: Creating database with different needs/outcomes
          fitch

          Can the MP belong to more than one organization? If so, it's generally best to connect it using a "join" table, e.g. "Memberships," which will contain one record for each connection of MP/org, with fields containing the primary key of those tables.

           

          Can the MP belong to more than one district?

           

          I'm not clear on what is meant by "on the outskirts" and "no overlaps."

          • 2. Re: Creating database with different needs/outcomes
            needsolution

            Fitch,

            Thank you for responding.

             

            Yes, an MP can have more than one organization.  How do I "join" ?

            No, an MP has only one district.

            Outskirts is the surrounding district in which the primary organization and MP reside; e.g. Core is org/Mp; Tier 1 is the surrounding districts connected to core.

            No overlap is that I do not want the MP to appear more than once so that the letter/label is sent to the MPs but once.

             

            Initially, I created numerous columns in excel and imported into Filemaker Pro.  But not all the information followed.  I would love to establish a database that not only provides addresses, but the MP's bio, pic, etc. and expertise all in the same database.

             

            In excel I created columns for each area i.e. title, name, address, etc. for main MP; outskirt MPs 1, 2, 3 up to 10 districts.  But I could not eliminate the overlap/duplications without adding a column named TYPE.  This was goo to separate the organizations with/without, but not to separate the outskirt districts without duplicating.  Any suggestions?

             

            Thanks,

            needsolution

             

            • 3. Re: Creating database with different needs/outcomes
              fitch

              Your basic problem is already solved by virtue of the MP name and address existing in its own table, assuming there are no duplicates in this table (and there shouldn't be). Therefore you can't have overlaps, if by that you mean duplicate mailing labels.

               

              Assuming you have set up relationships as I described earlier:

              MP -< Memberships >- Organizations

               

              1. Find one or more Organizations, and then Go to Related (All) to get the MPs.

              2. Find all Organizations, Go to Related (All), then show omitted

              3. ...

              4. ...

              5.  Same as 1

               

              Someone else is going to have to jump in here as I still don't get what you're trying to do with outskirts.

              • 4. Re: Creating database with different needs/outcomes
                needsolution

                Hi Tom,

                Thanks for your reply.

                 

                As I do have separate tables for MPs vs Organizations, how do I connect/relate the ones that have MPs with organizations or without MPs?.  Not sure how to actually do the relating.  Very new to me.  What do you mean by membership (as a connector?).   Sorry if I am not catching on quicker.

                 

                I am also not sure how to filter the adjacent MPs with or without organizations (outskirts).  In excel I was able to separate by different sheets as an extension of the main database that has all the information on one sheet.

                 

                Any help you can provide will be much appreciated.

                Thanks,

                Barb

                 

                 

                • 5. Re: Creating database with different needs/outcomes
                  needsolution

                  Can anyone help better describe how to connect the tables in laymans terms.

                  Thanks,

                  Barb

                  • 6. Re: Creating database with different needs/outcomes
                    aammondd

                    The membership is a separate table of organization members.

                    In it you simply store the keys for the org and the key for the MP

                     

                    You link the org and the mp tables  each  to this by their keys

                     

                    Once the relationship is built then you can work on the appropriate layouts and finds to produce what it is you want

                     

                    What you are apparently missing is this membership table

                     

                    You may have some of this data stored in your Org and MP tables at the moment but it should be its own.

                     

                     

                     

                    • 7. Re: Creating database with different needs/outcomes
                      needsolution

                      Thank you for this solution.

                       

                      I will try to create another table just for the adjacent MPs (outskirt districts) and hopefully this will solve the issue.