4 Replies Latest reply on Dec 13, 2013 12:42 PM by julesmim

    Database of historical events and way to report chronologically special anniversaries of all events

    julesmim

      Title

      Database of historical events and way to report chronologically special anniversaries of all events

      Post

           Hello,

           I just bought Filemaker Pro 13 and I'm still a newbie to Filemaker and databases.

           In my database, I have a list of historical events (from about 500 years ago until today). I put those events in an "Events" table with fields EventID, Event name, Event date, Event category, and maybe a few more.

           The thing I want to get is a list (report or view) of various anniversaries (5th, 10th, 20th, 25th, 50th, 100th and maybe more) of each of those events, ordered by date. So for a particular date, I can have the fifth anniversary of an event and the 10th of another event each on its own line. On this line should be fields for the day (for example today), Event name, the date of the passed event, which anniversary number it is (5th, 10th, etc.) and maybe other fields from the "Events" table.

           I thought adding some calculated fields in the "Events" table for every anniversary (5th, 10th, etc.) would be a good start but then I don't know how to use these fields. I played with intermediate tables and relationship from a date field in it. But it seems that I can't have a relationship that matches one field in a table with field 1 OR field 2 OR field 3 in another table. I tried making the connection of the field in the first table with different fields in different occurrences of the second table. But if I want to represent the results in a portal, I can't fill the portal form records coming from different table occurrences.

           Or maybe I am on a completely wrong way?

           Any hint to help me figure how to implement this would be very welcome.

           Thanks.

        • 1. Re: Database of historical events and way to report chronologically special anniversaries of all events
          philmodjunk

               I would use a related table of "anniversary records" for this. Each record in this new table would link to a specific event in your Events table and that link would allow you to set up a list view of Anniversary records that includes fields from events so you don't have to double enter any data or copy it over to the new table. All you'd need in the new table is a date field for the anniversary, a number field to indicate what anniversary it represents and an Event ID field to link back to a record in the Events table.

               A script could run through your Events and create new anniversary records that uses a number field to generate a different interval for each such event so that you can have anniversary records every 10 years for one event and every 50 for another. You might run such a script once every January to update your anniversary records.

          • 2. Re: Database of historical events and way to report chronologically special anniversaries of all events
            julesmim

                 Hello,

                 Thanks a lof for your answer, I think it showed me that putting fields in the Event table for each nth anniversary was wrong. I read a bit about relational databases and, if I understand well, what you proposed me (using a related table) is a kind of normalizing.

                 I tried to go further making another table called "Anniv_number" with one field also called "Anniv_number" containing a number. Every record in this table contains the kind of anniversary (5th, 10th, 25th, etc.) I want to have for every event. This gives me flexibility to add new numbers. In the graph of relationships I linked the "Anniv_number" field in my "Anniv_number " table to a field "fk_Anniv_number" I created in the table "Anniversary_records". In my table "Anniversary_records", you told me I must put a date field for the anniversary. For now I put a field that contains only the year of the anniversary (I must still learn ho to manage the calculation to get the same date many years later (taking in to account leap years).

                 What I would need now is a way to automatically populate my 2 foreign keys in the the "Anniversary_records", in order to connect every event with every nth anniversary I have in my table "Anniv_number". I thought that applying the cartesian product link between my tables would have automatically linked my 2 tables through the join table. But that did not happened.

                 You talked about a script for creating anniversary records. But I’m completely new to scripting in Filemaker Pro. Could you help me find how to make a script to get the desired result. An example would be welcome.

                 Thanks for your help.

            • 3. Re: Database of historical events and way to report chronologically special anniversaries of all events
              philmodjunk

                   Well it isn't a "kind of normalizing" it IS normalizing your data such that you don't have to enter the same data over and over again in events. wink

                   I'm not sure I see the purpose for the additional table. The only reason I see for it is if you want to use that relationship to access all anniversary records with the sane anniversary number. (all the first anniversaries, all the 2nd anniversaries, etc.) Is that what you had in mind?

                   I also think that I misunderstood what was meant by an anniversary number. Every event will have a new event record every year and the anniversary number records whether this is the 1st, 2d, 3rd, 50th or whatever anniversary. I was thinking more in a different direction that really isn't making sense to me now.

                   

                        I must still learn how to manage the calculation to get the same date many years later now.

                   Date ( Month (Events::event_date ; Day ( Events::Event_Date ) ; Year ( Events::Event_Date ) + 1 )

                   will return the date for an Event's 1st anniversary.

                   Date ( Month (Events::event_date ; Day ( Events::Event_Date ) ; Year ( Events::Event_Date ) + Anniversary_Number )

                   will compute the anniversary date for whatever anniversary is specified in Anniversary_Number.

                   The following script will compute the next anniversary record for every record in Events.

                   #This script assumes that the relationship between events and anniversaries is unsorted.
                   Freeze Window
                   Go to Layout ["Events" (Events) ]
                   Show All Records
                   Go to Record/Request/Page [First]
                   Loop
                      Set Variable [$EventID ; value: Events::Event_ID]
                      Set Variable [$AnnivNumb ; value: Last ( Anniversary_Records::AnniversaryNumber ) + 1]
                      Go to Layout ["Anniversary_Records" (Anniversary_Records) ]
                      New Record/Request
                      Set Field [Anniversary_Records::fk_Event_ID]
                      Commit Records
                      Set Field [Anniversary_Records::AnniversaryDate ;
                                Date ( Month ( Events::Event_Date ) ; Day ( Events::Event_Date ) ; Year ( Events::Event_Date ) + $AnnivNumb ]
                      Set Field [Anniversary_Records::AnniversaryNumber ; $AnnivNumb ]
                      Go to Layout [ "Events" (Events) ]
                      Go to Record/Request/Page [ Next ; Exit after last ]
                   End Loop

              • 4. Re: Database of historical events and way to report chronologically special anniversaries of all events
                julesmim

                     Hello PhilModJunk,

                     Thanks for the time you took to answer me. The script you wrote in you answer was not exactly corresponding to what I was trying to do. But there were enough hints to help me find my way. Thanks for having me introduced to Filemaker Pro Scripting.