10 Replies Latest reply on May 21, 2011 8:14 PM by MarkSurber

    Business Contacts



      Business Contacts


      Hey, everybody. I'm new to Filemaker, and just getting started with a business contact manager where my primary interest is the business, with multiple people, phone calls, visits, appointments and notes associated with each company. I think I have the tables arranged the way I need them to be, but getting reports to do what I want them to do without being able to just use SQL is a bit frustrating right now.

      Right now I have a separate table for companies, people, calls, visits, appointments and notes, with each entry automatically being assigned a unique key (integer).

      Mostly what I need right now is a way to make a report that shows one company at a time, with all entries in other tables associated with it. The report needs to dynamically add each person associated with the company, along with each visit, call and appointment, the notes associated with each approach to that company, and the names of the people I spoke to at each approach.

      Any tips on how to get this done?

      And, just so I know, is there any way I can populate feilds in a report based on SQL or some other scripting language?

      Thanks a bunch for the help.

        • 1. Re: Business Contacts

          The only scripting languages available to you are FileMaker scripts created via Manage | Scripts or AppleScripts (Mac only).

          How did you relate your tables in Manage | Database | Relationships?

          I would guess something like this:

          Companies::CompanyID = People::CompanyID
          People::PeopleID = Calls::PeopleID
          People::PeopleID = Visits::PeopleID
          People::PeopleID = appointments::PeopleID

          People::PeopleID = Notes::PeopleID

          This assumes "one to many" relationships in all cases. If you have "many to many" for any of these, then you use a join table much like you would with other databases. I'm also assuming that this report is something you want to print or save as a PDF:

          If you want all of the above in a single report, (assuming I've replicated your relationships correctly), You could build a report on a layout based on People. Fields from Companies can be added to the report header. Entries for each of the other related tables can be placed in portals and there are ways to filter the portals so that only specific related records are listed in each portal (such as only appointments for today onward rather than all past and present appointments.)

          You'd make each portal many rows taller than you expect to need and then set them to "slide up" , "resize enclosing part" in the inspector. (And sliding is only visible when you print/preview/PDF the report.)

          This isn't perfect, if you end up with more portal records than will show in the portal, you have to update the layout design to make the portal taller. If, on the other hand, you limited your report to just one of the "child" tables related to People, you could build the layout based on that table and no portals would be needed.

          If all else fails, you might need to set up a "report table" where you import the records you want in your report, combining records from all of the "child" tables linked to People in a common table so that you can generate a summary report that lists them all without using any portals.

          • 2. Re: Business Contacts

            Actually, since everything connects to Notes, I started my relationships there (to simplify the relationships graph), but you basically got the idea. It's been a while since I've worked with databases, so I forget exactly what counts as "many to many," but I think that's how you would classify the relationships in my database. Here's a few of the connections just so you can get the idea. It gets repetitive, so I left out some.

            Notes::RegardingCompany = Companies::CompanyID

            Notes::RegardingPerson (1-5) = People::PersonID

            People::Company = Companies::CompanyID

            Calls::ToPerson (1-5) = People::PersonID

            The database is set up so I can link up to five people with each note/call/visit/appointment (which brings up another issue, but I can ask about that later). Right now I'm interested in creating a report based on a company, since my contact is the company, not the person. I'll try using portals for now, but just in case, could you tell me a bit about joining tables?

            Again, thanks for the help.

            • 3. Re: Business Contacts

              Those are all appear to be one to many relationships and they are a subset of the list I posted (you don't show vists or appointments). You've just swapped them around so that the "many" table is listed on the left where I put them on the right. That's assuming that CompanyID and PersonID are auto-entered serial number fields.

              The relationship between Pople and Notes, and People and calls is not limited to a maximum of 5 records. You might have a portal to Notes that has just 5 portal rows specified, but that's a limitation set on the layout, not the relationship. If you select the scroll bar option for this portal, you will find you can have any number of related records in the portal, not a maximum of 5.

              • 4. Re: Business Contacts

                OK, it's Notes::RegardingPerson1, RegardingPerson2, RegardingPerson3, RegardingPerson4 and RegardingPerson5, and the others similarly. The idea is that each note, call, appointment and visit can be related to a maximum of five different people, but I could theoretically have an infinite number of notes, calls, etc. with each connecting to a different five people and one company each. I wasn't sure how else to do that, short of creating a table of just relationships, so I just created static space for five people in each of the other catagories. Would a table of just relationships be be a better solution for that? I didn't think of doing that before, but it seems like linking things together that way would just be an additional hassle.

                And yes, all ID feilds are auto-entered serial numbers.

                • 5. Re: Business Contacts

                  one of you guys can't answer the question I posted?

                  • 6. Re: Business Contacts

                    Are you Mark Surber?

                    • 7. Re: Business Contacts

                      "it's Notes::RegardingPerson1, RegardingPerson2, RegardingPerson3, RegardingPerson4 and RegardingPerson5, and the others similarly. The idea is that each note, call, appointment and visit can be related to a maximum of five different people,"

                      You want a star join.  In FileMaker, you need to use a join table.  I haven't had time to review this entire thread and honestly, we cannot track in our brains your entire structure to then offer the best solution.  It is best to present a simple demo with the structure and a few dummy records in each so we can see, feel and taste it.  Only then we can properly adivse.  This is like asking a doctor where to cut on your stomach when you belly hurts and you are pointing 'here' but we can't see where 'here' is. Laughing

                      Anyway, a join table allows many-to-many (one record for every combination of person/note).  If you want a report which combines left outer then FM doesn't do it.  Example - you want all the notes for the appointments for the people but you also want to include Companies who haven't had an appointment, or include companies who have no people then you will need to combine the multi-dimensional arrays into variables and write it to a virtual table.

                      When I first read this, I put together a file and thought that there should be a join between people and appointments since more than one person from a Company can attend a meeting.  But again, we can't 3D visualize what you have.  You need to help us out here or we can send you to the deep end of the pool with lead shoes.

                      Added: join between people & appointments would simply hold three IDs (its primary ID, PersonID and AppointmentID).  See where we're going here?  However, structuring properly will NOT provide total solution to reporting since FileMaker itself cannot combine tables.  So your request needs more fleshing out first.

                      • 8. Re: Business Contacts

                        Ok, here's a copy of my database exactly as I have it right now. I removed all the records and added a few dummy entries. See what you think, and feel free to comment on anything I did wrong.

                        You'll probably notice that in the relationships graph I have a bunch of iterations of People. I was, and still am, having trouble getting multiple people show up on pages like company reports and such. My most recent attempt at bringing it all together was Layout #9, which I had not properly named yet. I've already kind of given up on Companies Plus.

                        • 9. Re: Business Contacts

                          I added into every table, creation timestamp and modification timestamp.  These are very important because if you have a crash or need to restore or simply need to sort your notes by when they were created, you need the time as well.  It should be standard in all tables, just like adding the primary key is standard.  Large text fields should be split into their own table even if there is only one note and not multiple notes.  The reason is that, when FM loads a table, it loads all fields and all data regardless whether it is displayed on the layout or not.  Large text fields waste resources when they are never used for relationships. 

                          And now to the structure changes:  I have combined Calls, Visits and Appointments into one table called Activities.  They hold virtually the same information and it is easier for UI and also easier for reporting if they reside in the same table.  I have added an ActivityType.  It doesn’t hurt if one of the types uses a field the others don’t.  I have also attached Notes in several locations; you may want to add a note to the Company, add a note to an Appointment (remember to take proposals) or add a note to a Person.  See how I added CompanyID, PersonID and ActivityID to the notes table and I use ‘allow creation’ for adding notes in any section.  I have added a join table between People and Activities.  One activity (a meeting at their company) might involve several of their people.  And one person can attend several activities.

                          Before we turn to your reporting needs, we must be sure the structure is logical for you.  I only had a few hours today so I stopped designing when I reached Activities but I think you can see how it works.  Once we are sure the structure will allow the flexibility you need, then we will design the reports to pull the information together.  I didn't want to continue the structural aspects (after all, you must have SOMETHING to do, LOL) and I COULDN'T design the report until I know the 1:n and n:n relationships and what needs to be displayed on the report.


                          Anyway, check it over.  I believe that you will find it much simpler in structure and cleaner to work with.  You may need an additional join table; I don't know your business so I could not advise to that detail level.  I have confidence that you can take this concept and carry it further as well.

                          • 10. Re: Business Contacts

                            Ok, It took me a few minutes to wrap my head around what you did, but that makes a lot more sense.

                            Just so you know, I mostly deal in relationships, so I don't need to track products or invoices. That's handled by the company headquarters.

                            I'm going to try to import some records and see how it works. If it works really well, maybe I'll move the whole thing over. First I have to write invoices and LineItems out of the deal, but that's no biggie.

                            You know, the really funny thing with all this is that just four years ago I was doing all of this in Oracle using mostly just SQL for a class in college. It's really surprising how much of that I've forgotten.