3 Replies Latest reply on Nov 17, 2008 9:32 AM by TSGal

    Setting up summary table of dated events (text) for individuals in chronological order

    malcolml

      Title

      Setting up summary table of dated events (text) for individuals in chronological order

      Post

      Hi I'm new to FileMaker and am trying to develop a solution for keeping track of  events for different people (text about what happened on a specific date) and then to be able to produce a table summary in date order of the text about the events for each individual.

       

      I have managed to set up the database to record single events but am having problems saving the sequential event information and creating a table to display the information for each individual.

       

      I cannot seem to implement lookups and portals and these seem to be one possible solution.

       

      Is there any one who can possibly help with my problem?

       

      Thanking you in anticipation

       

      Joni1

        • 1. Re: Setting up summary table of dated events (text) for individuals in chronological order
          TSGal

          Joni1:

           

          Thank you for your post.

           

          You may want to consider having two tables.  One table to track the people, and one table to track the events.  That way, you may have one event for an individual, and possibly 40 events for another individual.

           

          Some way, you would need to link the two tables together through a common field.  Perhaps a Customer ID field.

           

          This would allow you to enter a Customer ID, have the Customer information display along with all events that have occurred for that Customer.

           

          Pull down the File menu and select "Manage -> Database".  Click on the Relationships tab, and you will see a graphical representation of the tables in your database file.  Find the Customer ID field in the Customer table, highlight it and drag it to the Customer ID field in the Event table and let go.  A line is drawn between the two tables.  In the middle between the two tables, there is an icon.  Click that icon, and for both tables check the option to "Allow creation of records in this table via this relationship".  This will allow you to add records to the Events table when in the Customer Table, or if you are in the Events table you can create a new record in the Customer Table.

           

          Exit from Manage -> Database.  Go to the layout that contains your Customer data.  Pull down the View menu and select "Layout Mode".  On the far left margin, there are different tool icons.  Click on the icon just below the oval tool.  This is the portal tool.  Draw a square on the screen (by clicking one corner and dragging to the bottom right corner) where you want your event information to appear.  When you let go, a new dialog box appears asking you for which relationship you are using and then what fields you want to display from the Events table.

           

          Once finished, pull down the View menu and select "Browse Mode".  FileMaker will probably ask you to save the changes.

           

          Select a customer, or create a new customer, and if any events exist for this customer, they will appear in the portal.  If you want to create a new event, you can just click in the portal and start entering information (since we checked the option to add records in the relationship).

           

          This should probably get you pointed in the right direction as far as portals and linking records together.

           

          Regarding reports, you can either show a report for each customer showing each of the events, or you can do this from the Events table showing the events summarized by each customer.

           

          If you have difficulty setting this up, or if you have any questions for what I covered, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Setting up summary table of dated events (text) for individuals in chronological order
            malcolml
              

            Many thanks TSGal

             

            I was able to get it to work perfectly. However, I was able to get the dummy data to perform reports but not when I tried it with my real data sets.

             

            Could I take you up on your offer of helping with setting up the reports?

             

             I'd like to have the option of showing a report for each customer showing each of the events, as well as showing the events summarized by each customer.

             

            I could not find any thing in the help files about Event tables.

             

            Once again thanking you inadvance for your help

             

            Joni1

            • 3. Re: Setting up summary table of dated events (text) for individuals in chronological order
              TSGal

              Joni:

               

              No problem helping you.  That's why we're here!  :-)

               

              Let's start with the Customer report.

               

              In your layout, you may want to have your customer information showing at the top of the body, and then have a portal into your event information below the customer information.

               

              From my previous post, draw a portal into the Events table and show the fields you want to display in your report.

               

              Once finished, go to Browse mode, and hopefully, you will see a customer name with one or more events.  This is how it should print out.  If you don't like what you see, then go back into Layout Mode and make a few adjustments until you are satisfied.  When finished, go to Preview Mode to get a better idea how it will print out.

               

              In the Event report, we'll do something similar, except we'll have the Event information at the top of the form, and then a portal into the Customer table to show all customers that are linked to each event.  Again, make all the adjustments in Layout Mode and then see how it will print out in Preview Mode.

               

              If you are still having difficulty, let me know what is and what isn't working properly.  A specific example would also be helpful.

               

              TSGal

              FileMaker, Inc.