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.
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.
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.
Go to Layout ["Events" (Events) ]
Show All Records
Go to Record/Request/Page [First]
Set Variable [$EventID ; value: Events::Event_ID]
Set Variable [$AnnivNumb ; value: Last ( Anniversary_Records::AnniversaryNumber ) + 1]
Go to Layout ["Anniversary_Records" (Anniversary_Records) ]
Set Field [Anniversary_Records::fk_Event_ID]
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 ]
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.