1 2 Previous Next 25 Replies Latest reply on Jul 11, 2012 3:25 PM by madeline

    Keeping track of contributors

    madeline

      Title

      Keeping track of contributors

      Post

      Hi, I have just upgraded from FMP6 to FMP12. This may be a good opportunity for me to improve a flaw in the way I set up my database (about 14 years ago!).

      This database keeps track of who has contributed articles to a monthly magazine. It consists of three files: "books," "rolodex" and "articles." Books is simply a list of the books that have been reviewed; it contains the book's title, author and publisher, and a record number. Rolodex is a list of all the writers who have written those reviews -- it contains the contributor's name, postal address, email address and "review history," and a record number. Articles joins the two files together and adds information pertaining the particular issue in production, so I plunk in the book record number and the contributor record number and the Articles fields get populated, plus I add stuff (word count, article type, article title, and so on).

      This system works very well except for the review history. This is a plain text field that contains the issue info. One contributor might write for us more than once, so the field contains a string: "12/06, 11/05, 11/10" (i.e., someone who has written for the June 2012, May 2011 and October 2011 issues). This makes it difficult to find all the contributors who wrote in 2010 or all our contributors from the last five years, or whatever.

      I can't figure out what's the best way to improve this. Does anyone out there have any suggestions, or can point me in the direction where I might see something similar that I could adapt?

        • 1. Re: Keeping track of contributors
          GuyStevens

          It sounds to me like you have your Books and your Rolodex tables and that the Articles table is a join table between the two.

          I don't see why you can't just make multiple records in the articles table for every contribution.

          I think that would be the most practical way to go.

          Just set this table up in a portal on either the Books or the Rolodex layout and add a new record for every contribution.

          You could even make is so that when you select a contribution in the portal that you get more details on the layout.

          An example of that technique would be this file:

          https://dl.dropbox.com/u/18099008/Demo_Files/TwoPortalsWithSetField_V3.fp7

          This might not be to clear for you, if you need any additional explaining let me know.

          • 2. Re: Keeping track of contributors
            madeline

            Thanks for your quick response! You're right that Articles joins Books and Rolodex. And yes, the Articles table has a separate record for each contribution, with a field that indicates which month of publication for that unique contribution. (I don't really care about the Books table.) If I were to search for all the articles published in a specified period, some of the contributors would be repeated. I don't want that: what we are looking for is one record for each person. (Am I making myself clear??) Would I be able to set the search so that it eliminates duplicates?

            In the demo you sent, which is the field that corresponds to my "review history" (I guess it would be all the shipments sent to Sherlock or Martha?)

            • 3. Re: Keeping track of contributors
              GuyStevens

              The demo file i provided was merely for the technique i suggested about clicking into a portal to select a record and show more related data based on the selected record.

              So maybe that's getting us a little off track. Let's forget about that.

              For your find, it will not be showing contributors multiple times if you create a list view based on articles with a subsummary part based on the Contributor ID.

              Then you'll get:

              John Doe
                   Contribution 1
                   Contribution 2
                   ...

              Jane Doe
                   Contribution 1
                   Contribution 2

              ...

               

              Here you could do a find within a date range.

               

              what we are looking for is one record for each person. (Am I making myself clear??)

              No, not really. You are saying in your first post that you have multiple dates in one field and that that makes it hard to perform finds.

              I suggest that you should make multiple records in the articles table. One record for every contribution.

              You reply by saying that there is indeed one record for every contribution.

              So I'm confused.

              Maybe we need to start over from the top. Maybe there is a mistake in your structure somewhere.

              What you need in terms of tables and fields is:

              Books (table)

              Very simple, one record per book.

              Id - a number field set to auto enter serial number
              Title - text field
              Author
              ...

              Rolodex

              Very simple, one record per contributor.

              Id - a number field set to auto enter serial number
              Name
              ...

              Aricles

              One record for every article written. Every article is written by one person and is linked to a book.

              Id - a number field set to auto enter serial number
              RolodexIdFk - a Number field  - FK stands for Foreign Key and is the field where the linked contributor's ID is stored.
              BookIdFk - a Number field  - this is the field where the linked book's ID is stored.

               

              Then you need folowing relationship:

              Rolodex::Id-------[=]---------Articles::RolodexIdFk
              Books::Id--------[=]---------Articles::BooksIdFk

               

              Now from either the Books layout or the Rolodex layout you can add articles to the articles table.

              • 4. Re: Keeping track of contributors
                GuyStevens

                Something like this is what I mean:

                https://dl.dropbox.com/u/18099008/Demo_Files_FMP12/Contributions_Articles_Books.fmp12

                 

                This is just really quickly slapped together. And you can take this a lot further.

                Let me know if this is in the direction you are looking for.

                • 5. Re: Keeping track of contributors
                  madeline

                  DaSaint, thank you, thank you -- no need for you to keep slapping things together. I think the solution already exists in my setup! In one of those smack-your-forehead moments, I have realized that I can actually do the find I want in the Articles table and export the info we usually need -- from the Rolodex table. I'm embarrassed to say that I should have figured this out a decade ago. But thanks for pointing me in the direction so at least now I can do it...

                  However, I do have one more question. Is there a way to filter out the duplicate records in a found set? That is, if I find all the contributions for a specified period in the Articles table, I might end up with repeated Rolodex records. Most of the time, I don't need that (because usually we are looking to use the found set to do a mailing, so we wouldn't want to send the message to one person more than once, even if that person has contributed more than once). Do you know if there's a way, or if we just have to go through the found set and omit repeats manually?

                  Thanks again!

                  • 6. Re: Keeping track of contributors
                    GuyStevens

                    If you only want one record per Rolodex you could do your find from a layout based on the Rolodex table.

                    For instance:

                    Try making a list view based on the Rolodex table showing the Name of the contact but also the Date from the Articles table.

                    Now do a find in this date field. Either search for a date or a Date range:

                    01/01/2012...01/04/2012

                    (This searches all articles between the first of januari and the first of april.)

                    Then you should get a list where you have every Contributor only once.

                    You might not be seing the correct dates in the date field because you'l probably only see the first date depending on how the relationship is sorted but you will have only contacts that have articles in this date range.

                    There might be other options to do this, but I think this would be the easiest option.

                    • 7. Re: Keeping track of contributors
                      madeline

                      I feel like I am missing something very obvious here. I have followed your suggestion. In the Rolodex table I created a layout in list view displaying the fields (Issue, Name) from the two tables that I want (Rolodex and Articles). I created a field in the Rolodex called (for lack of a better term) "Lookup Issue" and made a relationship between that and Articles::Issue. In Rolodex, if I search for issues, the name field stays blank. If I delete the relationship, the name field says "Unrelated table". I KNOW this is a very simple thing to fix, but I can't figure out how. What am I doing wrong??

                      • 8. Re: Keeping track of contributors
                        GuyStevens

                        What's with the "Lookup Issue" field?

                        Why are you using that?

                        And why are you creating a new relationship?

                         

                        • 9. Re: Keeping track of contributors
                          GuyStevens

                          Ok, I have another question. Maybe we can set this up in a slightly different, more visual way.

                          How do you send messages to your contributors?

                          By email?

                          Doe you manually email every Contact?

                          Or do you just send one email to the whole bunch?

                          What if we would stay in the Listview based on the articles table and create a script that goes trough the entire found set, sends an email to every contributor, marks that contributor as "message sent" and goes on to the end.

                          Maybe that's a little easier.

                          What do you think?

                          • 10. Re: Keeping track of contributors
                            madeline

                            Answer to your first question (What's with the lookup issue field): "Lookup" is perhaps misleading. What I was trying to do was use the Articles's field in the Rolodex file. I was thinking this was a one-to-many relationship.

                            Answer to your second question: I'm not directly involved in that part, but it's usually individual emails or letters using a mail merge. I'm just the keeper of the database, and what I usually do is get a request, for example, for all the contributors of the last two years so we can send them an invitation or something.

                            • 11. Re: Keeping track of contributors
                              GuyStevens

                              Whoops, I think something went wrong.

                              If you want to attach an image you first have to upload it somewhere online and paste the URL.

                              If you use Dropbox (www.dropbox.com) you can just put the file in your public folder and copy the public url.

                              • 12. Re: Keeping track of contributors
                                madeline

                                P.S. Sorry about those incomplete postings.
                                Not sure if this is helpful, but I've made clones of the three files and put them here: https://www.dropbox.com/sh/0flm6vgjf0kr8g3/JPGyOgYq0n

                                • 13. Re: Keeping track of contributors
                                  GuyStevens

                                  First thing I noticed before I even opened the files: Three files...

                                  That is so 1995 Tongue out

                                  You can have multiple tables in one file.

                                  That's a little easier to manage.

                                  • 14. Re: Keeping track of contributors
                                    GuyStevens

                                    Well, first problem I'm seeing is that you have multiple book fields in your articles table.

                                    7 To be precise.

                                    What if a certain contact reviews 8 books?

                                    In stead what you should have is one contact and one book per article record.

                                    That way you also have 1 Date field per record in the articles table.

                                     

                                    1 2 Previous Next