1 2 Previous Next 17 Replies Latest reply on Aug 21, 2013 11:09 AM by philmodjunk

    Mass Donor Communication/Correspondence Summary: Where to Start?

    CourtneyTran

      Title

      Mass Donor Communication/Correspondence Summary: Where to Start?

      Post

           I have a donor database. For each donor/record, I have a tab for sending emails and also for typing letters to be sent to that donor (a thank you letter/update letter about our organization) and a table where I can indicate any gifts given to a particular donor (ie: teddy bear)

           What I want to be able to do is draw up a massive report of some sort for each individual donor that will summarize all communications/correspondences I have had with that donor. I want the report to update in real time in conjunction with any additions or changes made to the records (ie: sending a new email to the donor). Ideally, the report will look like a dashboard that will show:

           (i) Any gifts the donor received (a count of how many)

           (ii) If they've received a thank you letter and when (along with a link to the archive of those letters in filemaker)

           (iii) A record of email communications (made via Filemaker server) to that donor (along with a link to the archive of those emails in filemaker)

           (iv) A record of any any phone calls made to that donor (with the date of the call and comments I typed in about the phone call).

           Any suggestions on how to go about generating a report that works this way?

           My two main questions then become:

           1. How to archive letters and emails generated in filemaker (and how to access them)

           2. How to create a dynamic input chart of some sort that allows me to enter in communications each time they are made for a particular donor under his/her record (ie: a chart that has fields for date of correspondence, type of correspondence (ie: phone), and notes regarding the correspondence)

           This is beyond what I know how to do in Filemaker at this point. I've been doing research on "dashboards" but I don't quite understand what they are or how to start, and the videos I've watched are more confusing. Is this even an option for my purposes? Are there any suggestions on what Filemaker features I should look up or use to try and achieve the ends I am aiming for?

            

        • 1. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
          philmodjunk

               Dashboard is a buzzword for a layout that provides the user with a lot of different bits of info that summarize many different things about the database. Think of a car's dashboard and all the meters and indicator lights that tell the driver how many aspectsof the car are functioning. Thus, one dashboard can be very different from another. This is often implemented on a layout with a lot of tabs and/or portals to display data from many of the different related tables that make up a relational database.

               A communications report, however, might not look like a dashboard at all. It might be a summary report that either lists all communication with that donor chronologically or possibly grouped by the type of communication.

               From your list, it sounds like you need to link each donor record to a communication table where you create a new record each time events i, ii, iii, or iv take place. General info common to all such communication, such as the ID of the donor and a date would be recorded in this table. If needed, related tables might be included to further document the details of each communication event. Ideally, the info used in your report should be recorded in this communications table as much as possible as it makes your report simpler to construct. And on a layout, buttons next to each such entry can take the user to layouts to drill down to where they can see all the details associated with that event. If they click on a Thank You letter event, they are taken to a layout where they can read the thankyou letter sent to them. If they click next to a gift they are taken to a layout where they can see the purchase price, description, picture, etc of that Teddy Bear.

          • 2. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
            CourtneyTran

                 That definitely answers my question about the functionality of dashboards and gives me a sense of direction on how to construct this. yes

                 Could I have your opinions about this?

                 My plan is to create many layouts that create a new record each time a correspondence of some type occurs with the donor. So I will have a separate layout for gifts, phone correspondences, and letter correspondences for example. Each layout will have three of the same fields (Date of Correspondence/Type of Correspondence/Notes), and they will also have unique fields (ie: the emails layout will have the email body in it).

                 I will then create a table via portal tool that will appear in each donor record which will consist of all correspondences drawn from the fields that each of my layouts have in common (Date of Correspondence/Type of Correspondence/Notes). So the portal table will consist of all communications, indicating their date, their type (phone/letter) and notes about it, taken from the individual layouts.

                 I wanted to create the multiple layouts that I can produce a unique summary format for each of the different types of communications. For example, I want to be able to manipulate the phone communications layout to show summary of number of phone calls for  given person, most recent phone call, etc. Then I can create a button that goes specifically to this layout summary that will display information that expands beyond the basic date/type/notes shown in the portal table I created.

                 Do you think this is a working plan?

                  

                  

                  

                  

                    

            • 3. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
              philmodjunk

                   It's definitely workable, but you'll need to carefully work out both a functional data model as well as a user friendly interface for each type of communication.

                   At the very least, your fields that are common to each form of communication, (Date of Correspondence/Type of Correspondence/Notes), should all come from the same table for all types of communication. That makes the portal you describe possible and also makes it possible to set up a number of different summary reports that pull data from multiple types of communication into the same report layout.

                   At the data model level: Create related tables for each type of communication with the fields that are specific to that type of communication. Link them to an ID field in the Central communications table. On your layouts, base the layout on the table that is specific to that type of communication, but include the fields from the central communications table as well.

              • 4. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                CourtneyTran

                     Thank you. smiley

                      When I created three multiple layouts, I drew fields into these layouts that originated from separate tables; tables of which are duplicates of the same table.I created relationships between the fields of these tables by creating a link between the donor ID's for each of these tables.

                     I created the relationships in the first place because I need it to create a portal table where, if I enter in a new phone call record, it will appear in the portal table. I must have drawn the relationship wrong?

                     The problem: I think that because of this link, when I, for example, create a new record in the "Phone Calls" layout and indicate the date of correspondence and everything else, a new record is also created in the other two layouts (emails and letters) with the date and type of correspondence fields indicating today's date and the correspondence as "phone call" as well.

                     I thought that, because these are three separate table occurrences (despite the fact that they are duplicates of one table), a change in the fields of one layout would not produce a change in the fields of another.

                      

                      

                • 5. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                  philmodjunk

                       This is not what I recommended. I suggested this:

                       Donors-----<Communication-----Emails
                                                         |
                                                    Letters

                       Each entity shown has a different data source table. I've only shown two tables for different types of communication, you'd add as many as you need and would have a layout for each.

                       The match fields would be:

                       Donors::__pkDonorID = Communication::_fkDonorID
                       Communication::__pkCommunicationID = Emails::_fkCommunicationID
                       Communication::__pkCommunicationID = Letters::_fkCommunicationID

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                       But your relationship also has another issue. The link from DonorInfo to Duplicate 2 should show crows feet where the relationship line connects to Duplicate 2. The absence of that symbol indicates that you have defined Duplicate 2::DonationID incorrectly. You have either defined it as an auto-entered serial number or specified a unique values validation field option. Either field option would be incorrect and should be removed.

                  • 6. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                    CourtneyTran

                         Thank you very much! I created the relationship and everything is working the way I want it to so far. yes

                         I am wondering if you happen to know if there is a way to create a date field in filemaker that will automatically generate and populate with the most recent date that a donor has been given a gift?

                         I went ahead and created a field under manage>database where I indicated that it was a date field and then under options, clicked on the checkbox that said "from "value from last visited record". This did not work.

                    • 7. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                      philmodjunk

                           Assuming these relationships:

                           Donors----<Communication----Gifts

                           and assuming that you have a field in Gifts named DateGiven...

                           then

                           Max ( GIfts::DateGiven ) can be defined as a calculation field in Donors (Must evaluate from the context of Donors) can return the most recent date.

                           A summary field defined in Gifts that computes the maxiumum of DateGiven can return the same value.

                           And ExecuteSQL could also be used to return the same date.

                      • 8. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                        CourtneyTran

                             Uh oh. So I went ahead and linked the various table occurrences by donation ID. I just realized that this is not something I really want to do. Instead, I turned around and created my relationships like this:

                             I linked my communication, gift, phone call, and email tables by the field "type of correspondence". I thought that by creating this relationship, anytime I enter in a new record to the correspondence table under a donor's record,  the record would only populate under the layout corresponding to that type of communication (ie: phone calls layout). Instead, when I entered in the data for a new phone call on the communications table, when I went to click on the layout for phone call it was there but it also appears under the layout for emails and the layout for gifts.

                             My understanding of relationships must be completely off. I'm still learning here sad

                        • 9. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                          CourtneyTran

                               Ah! never mind my last post. I drew out a diagram for myself that mapped out the relationships I've created in a way I understand them. Problem solved!

                          • 10. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                            CourtneyTran

                                 I'm back on this again. frown

                                 I created the three different layouts: emails, phone calls, and letters, and then the join table "communications".

                                 Initially, each of the three layouts (emails/phone calls/letters) were built with fields from different tables. All of the tables, however, consisted of fields that would hold information common to each of the three layouts (Correspondence Data/Type/Notes About Correspondence).

                                 I went ahead and created the many-to-many relationship between each of the three tables (photo attached). What I had in mind was, when I create a new email communication using the email layout for example, then the Date/Type/Notes About Correspondence  will appear in a portal table I create in each donor's layout whenever the Donor ID for that communication matches that of the record that the portal table is in.

                                 I soon realized that this did not work because, when I created the relationships and then the portal table (photo attached), I basically instructed Filemaker that, every time the donor ID matches for an email/phone/letter record that I create in one of the three layouts, I wanted my portal table to show me the date/type/notes about correspondence for that communication under that individual person's record (in the portal table). Problem is, it did not do that because the fields that I used to define the portal table were fields drawn from the "Communications" table, not from the email/phone/letter layout tables.

                                 Does this make sense to you? I know my explanation is a bit convoluted.

                                 So instead, I went ahead and made three separate occurrences of the "Communications" table and created fields in that table that would apply to either phone/email/letter communications. I built each of the three layouts (phone/email/letter) out of these fields from these duplicate tables.

                                 I was hoping then that my portal table would behave as expected by #1: (populating all date/type/notes about correspondence for a given communication for one person, drawn from each of these three layouts). #2: It wouldn't show email data and letter data across all three layouts since the fields were all from different table occurrences.

                                 Instead, if I created an email record under the email layout, it would still populate under the phone call or letters layout as well (even though each layout was build from different table occurrences).

                                 Also, my portal table is not displaying the correspondence date or type as expected when a new communication is created in one of these three layouts.

                                 Any idea what thing(s) I am doing wrong?

                                 I may have to reword this question. It's hard for me to paint a detailed picture just typing.

                                  

                                  

                            • 11. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                              philmodjunk

                                   By the way, wouldn't it be simpler just to upload a screen shot of the relevant portion of Manage | Database | Relationships than to draw your own graphics to show the same thing? wink

                                   

                                        I went ahead and created the many-to-many relationship

                                   Well, it's not a many to many relationship. You have three one to many relationships between communications and each of the other tables.

                                   To repeat from an earlier post:

                                   This is not what I recommended. I suggested this:

                                   Donors-----<Communication-----Emails
                                                                     |
                                                                Letters

                                   Each entity shown has a different data source table. I've only shown two tables for different types of communication, you'd add as many as you need and would have a layout for each.

                                   The match fields would be:

                                   Donors::__pkDonorID = Communication::_fkDonorID
                                   Communication::__pkCommunicationID = Emails::_fkCommunicationID
                                   Communication::__pkCommunicationID = Letters::_fkCommunicationID

                                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                                   The match fields I am suggesting are different. DonorID uniquely identifies the recipient of a given communication and is used only to link Donors to Communication. A different serial number field, __pkCommunicationID is used to link each of the "CommunicationType" tables to the central communications table.

                              • 12. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                                philmodjunk

                                     The basic outline for logging a new communication event is:

                                     Select the donor. This determines the __pkDonorID value to use.

                                     Create a new Communications record and put the value of the donor ID in the matching _fk field in Communications.

                                     Select the type of communications--this selection can perform a script that takes the user to a layout for that type of communication, creates a new record and enters the correct value into _fkCommunicationID to link it to the newly created record in Communications.

                                     And with this setup, a portal to communications placed on the donors layout will list all communications records for that Donor. A button can be placed in the portal row such that clicking it performs a script that takes the user to the correct layout and record to show the details of that specific communication.

                                • 13. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                                  CourtneyTran

                                       My "Type of Correspondence" field is a drowdown menu with options such as "email" or "letter". How do I write a script so that when I select email, it will take me to an email layout and create a new record and enter in the ID?

                                       I know how to make it go to the desired layout, I know how to make it create a new record and all. My problem is not understanding how to make tell Filemaker that when I select the value "Email" then it needs to go to the email layout but if I select the value "Letter", then it needs to go to the letter layout.

                                       I have been scanning through the different commands that I can use to write scripts. I want to use the "if" command but then when I have to specif the "if" to use, nothing I am using is functional.

                                  • 14. Re: Mass Donor Communication/Correspondence Summary: Where to Start?
                                    philmodjunk

                                         Let's assume that you Define a field: Communications::Type as a drop down list with three values: Letter ; Email ; Gift. You can put this field, formatted as a drop down list in your portal to communications located on your Donors layout.

                                         Set Variable [$ComID ; Value: Communications::__pkCommunicationID ]
                                         IF [ Communications::Type = "Email" ]
                                            Go to Layout ["Email" (Email)]
                                            New Record/Request
                                            Set FIeld [Email::_fkCommunicationsID ; $ComID ]
                                         Else IF [Communications::Type = "Letter" ]
                                            Go to Layout ["Letter" (Letter)]
                                            New Record/Request
                                            Set FIeld [Letter::_fkCommunicationsID ; $ComID ]
                                         Else
                                            Go to Layout ["Gift" (Gift)]
                                            New Record/Request
                                            Set FIeld [Gift::_fkCommunicationsID ; $ComID ]
                                         End If

                                    1 2 Previous Next