Creating a Custom Report

This tutorial describes a report that will read data in your file and calculate the average age at which males and females were married and the average age of fathers and mothers when their children were born. These calculations can help you calculate the average age per generation among your ancestors or descendants. The output can be to a built-in GEDitCOM II report or can be directed to another application. Examples are given for sending the report to Pages and to Microsoft Word. When you are done this tutorial, you should be able to create your own custom reports by changing the type of data collected and the format of the output report.

This tutorial was written prior to GEDitCOM II, version 2.0 where scripts were converted to extensions. The tutorial still works, but when creating your own reports and is preferrable to package the scripts in an extension. The details for packaging scripts in extensions are provided in the GEDitCOM Editor help.

Listing 1
(* Generational Ages Report Script (10 NOV 2009, by John A. Nairn)
	
   This script generates a report of average ages of all spouses when
   they got married and when their children were born. The report can
   be for all spouses in the file or just for spouses in the currently
   selected family records.
*)

-- Key properties and variables
property scriptName : "Generational Ages"
global numHusbAge, numWifeAge, numFathAge, numMothAge
global sumHusbAge, sumWifeAge, sumFathAge, sumMothAge

-- Verify OK to run this script
if CheckAvailable(scriptName) is false then return

-- Choose all or currently selected family records
tell application "GEDitCOM II"
  set whichOnes to user option title ¬
    "Get report for All or just Selected family records" ¬
    buttons {"All", "Cancel", "Selected"}
  if whichOnes is "Cancel" then return

  -- Get a list of the chosen family records
  if whichOnes is "All" then
    set fams to every family of front document
  else
    set selRecs to selected records of front document
    set fams to {}
    repeat with selRec in selRecs
      if record type of selRec is "FAM" then
        set end of fams to selRec
      end if
    end repeat
  end if
end tell

-- Exit now if no family records were chosen
if (count of fams) is 0 then
  return "No family records were selected"
end if

-- Collect all report data in a subroutine
CollectAges(fams)

-- Write to report and then done
WriteToReport()
return

Main Script

Listing 1 shows the entire main script, although crucial components of the script are done in subroutines that are described later. This section describes the logic of the main script.

The script starts with comments between "(*" and "*)". It is a good idea to start all scripts with comments. If you share your script with other GEDitCOM II users, these comments can document use of the script. Even if the script is only for yourself, the comments will remind you what you wrote in the script.

The Key properties and variables section defines global variables for the script. These are mostly used in subroutines described below. The scriptName holds the name of the script. Any place you need to refer to the script by name, use this variable rather than literal text of the name. This approach will make parts of your script more reusable in other scripts.

The first step is to verify it makes sense to run this script. All the work is done in the CheckAvailable() subroutine (see utility subroutines). The subroutine returns true if it is OK to proceed or false to exit. This script, for example, requires a document to be open. This script also requires version 1.5 or newer of GEDitCOM II (because it uses some commands first defined in version 1.5); you can verify version number when the script is packaged into an extension.

You will often want to run reports on your entire file. But, it can be helpful to focus a report on a subset of your file. To achieve this goal, many scripts will have an option to be on the entire file or on just the selected records. To run a report on a subset of the file, a user selects the records first and then runs the script. The next section lets the user choose the report target. First, the user option command displays a box with three buttons for "All", "Cancel", or "Selected" to report on the entire file, to abort the script, or to report on the currently selected records, repectively. The "All" option, which is first, is the default option (user can hit return to use that option). This section must be in a tell "GEDitCOM II" block so it can access the user option command.

Once the user decides which records to use, the script compiles all needed records into a list variable (fams). This report is reading ages of fathers and mothers and thus only needs to look at family records. If the user selects "All", the list is found by reading every family from the front document. If "Selected" is chosen instead, the script fetches the selected records of the front document. A list of currently selected records is a standard property of GEDitCOM II documents. This list may have any number or records (including none) and may have any type of record. Because this report only cares about family records, the repeat loop goes through the list of selected records and adds only the family records to the master list in the fams list variable.

Finally, once all family records are in the fams list variable, the length of that list is checked. If it has no elements, there is no need to proceed and the script exits with a message that "No family records were selected". Otherwise the script continues.

The final section is the main part of the script, but all work is done in two subroutines. First the CollectAges() subroutine extracts all needed age information from the provided list of family records and stores the results in the global variables defined at the beginning. Next, a WriteToReport() subroutine formats the report for output to the user. Now the script is done and exits with an optional return command. A return command with no string argument means the script has successfully finished. If a string is provided, GEDitCOM II will display that message in the script palette that appears while a script is running.

CollectAges() Subroutine

Listing 2
(* Collect data for the generational ages report *)
on CollectAges(famList)
  -- initialize variables
  set {numHusbAge, sumHusbAge, numFathAge, sumFathAge} to {0, 0, 0, 0}
  set {numWifeAge, sumWifeAge, numMothAge, sumMothAge} to {0, 0, 0, 0}
  set {fractionStepSize, nextFraction} to {0.01, 0.01}
  set numFams to number of items in famList
  
  tell application "GEDitCOM II"
    repeat with i from 1 to numFams
      -- read family record information
      tell item i of famList
        set {husbRef, wifeRef, chilList} to {husband, wife, children}
        set mdate to marriage SDN
      end tell
			
      -- read parent birthdates
      set {hbdate, wbdate} to {0, 0}
      if husbRef is not "" then
        set hbdate to birth SDN of husbRef
      end if
      if wifeRef is not "" then
        set wbdate to birth SDN of wifeRef
      end if
			
      -- spouse ages at marriage
      if mdate > 0 and hbdate > 0 then
        set husbAge to my GetAgeSpan(hbdate, mdate)
        set {numHusbAge, sumHusbAge} to {numHusbAge+1, sumHusbAge+husbAge}
      end if
      if mdate > 0 and wbdate > 0 then
        set wifeAge to my GetAgeSpan(wbdate, mdate)
        set {numWifeAge, sumWifeAge} to {numWifeAge+1, sumWifeAge+wifeAge}
      end if
			
      -- spouse ages when children were born
      if hbdate > 0 or wbdate > 0 then
        repeat with chilRef in chilList
          set cbdate to birth SDN of chilRef
          if cbdate > 0 and hbdate > 0 then
            set fathAge to my GetAgeSpan(hbdate, cbdate)
            set {numFathAge, sumFathAge} to {numFathAge + 1, ¬
                sumFathAge + fathAge}
          end if
          if cbdate > 0 and wbdate > 0 then
            set mothAge to my GetAgeSpan(wbdate, cbdate)
            set {numMothAge, sumMothAge} to {numMothAge + 1, ¬
                sumMothAge + mothAge}
          end if
        end repeat
      end if
			
      -- time for progress
      set fractionDone to i / numFams
      if fractionDone > nextFraction then
        notify progress fraction fractionDone
        set nextFraction to nextFraction + fractionStepSize
      end if
    end repeat
  end tell
	
end CollectAges

This subroutine (see Listing 2) collects all data on ages from the information in your file. It is where most of the work of this script is done and most of the interaction with your data through GEDitCOM II's AppleScripting objects and their properties.

The first section initializes variables. A convenient short cut in AppleScript lets you set several variables at once. For example, the command set {x,y,z} to {1,2,3} looks like it is working with two lists, but actually it is shorthand for the three commands:

   set x to 1
   set y to 2
   set z to 3

Furthmore, the elements in the second list can contain variables, expression, or properties of objects. This shorthand is used often in this script; it makes scripts shorter and easier to read.

The tell application "GEDitCOM II" block is a repeat loop over all family records passed to this subroutine. The loop starts by reading data from the family record - namely references to the husband and wife records (in husbRef and wifeRef), a list of all children records (in chilList), and the marriage date (in mdate). The marriage date, like all dates in this script, is read as a serial day number (using build in SDN attributes, which is a day number starting with 1 back around 4000 B.C.. Serial day numbers are ideal for date calculations such as finding years between dates. These SDN attributes return the serial day nnumber for a date or return 0 if the date is either not known of if the date in the file is an invalid date.

The next section reads the parents' birthdates. From above husbRef and wifeRef are referenced to the parents in this family or either could be an empty string meaning the record does not have that spouse. For each spouse that is in the family record, this section reads their birth serial day numbers using attributes of their individual records.

The next two sections do the date calculations for this script. First are the calculations for ages of each parent at the time of marriage. This calculation can only be done if both a spouse's birth date and the family's marriage date are known. Thus if both serial day numbers are greater then zero, the age is calculated (using a utility method called GetAgeSpan()). The variables numHusbAge and numWifeAge count the number of age calculations done. The sumHusbAge and sumWifeAge variables hold a sum of all ages. When this subroutine is done, the sum variable divided by the num variable will be the average age.

The age at child birth section is similar. It contains a loop over all children in the family. For each child, it looks for their birth date. If a birth date is found, the ages of each parent with a known birth date are added to global variables analogous to the num and sum variables in the previous section. This entire section is enclosed in a conditional that says to do these calculations only if at least one parent birthdate is known.

The last section of the loop informs the user of the script progress using the nofity progress command.

When the repeat loop is done, the global variables (e.g., numHusbAge, sumHusbAge, etc.) will contain all data needed to output the report. The subroutine ends and returns control to the main script. The next section explains formatting of the output report.

Listing 3
(* Write the results not in the global variables to a
     GEDitCOM II report *)
on WriteToReport()	
  -- build report using <html> elements beginning with <div>
  set rpt to {"<div>" & return}
	
  -- begin report with <h1> for title
  set fname to name of front document of application "GEDitCOM II"
  set end of rpt to "<h1>Generational Age Analysis in " & ¬
      fname & "</h1>" & return
	
  -- start <table> and give it a caption
  set end of rpt to "<table>" & return
  set end of rpt to "<caption>" & return
  set end of rpt to "Summary of spouse ages when married " ¬
      & "and when children were born" & return
  set end of rpt to "</caption>" & return
	
  -- column labels in the <thead> section
  set end of rpt to "<thead><tr>" & return
  set end of rpt to "<th>Age Item</th><th>Husband</th><th>Wife</th>" & return
  set end of rpt to "</tr></thead>" & return
	
  -- the rows are in the <tbody> element
  set end of rpt to "<tbody>" & return
	
  -- rows for ages when married and when children were borm
  set end of rpt to InsertRow("Avg. Age at Marriage", numHusbAge, ¬
      sumHusbAge, numWifeAge, sumWifeAge)
  set end of rpt to InsertRow("Avg. Age at Childbirth", numFathAge, ¬
      sumFathAge, numMothAge, sumMothAge)
	
  -- end the <tbody> and <table> elements
  set end of rpt to "</tbody>" & return
  set end of rpt to "</table>" & return
	
  -- end the report by ending <div> element
  set end of rpt to "</div>"
	
  -- create a report and open it in a browser window
  tell front document of application "GEDitCOM II"
    set newreport to make new report with properties {name:"Generation Ages", ¬
        body:rpt as string}
    show browser of newreport
 end tell
	
end WriteToReport

WriteToReport() Subroutine

Formatting a report for output in GEDitCOM II means to format the data using html elements all enclosed within a single div element. You can use any html methods you want. Here the report title is put in an h1 section element and all results are placed in a table element. The subroutine to create this report is in Listing 3.

The report will be stored in the rpt variable. The script starts by creating a single element list variable with the <div> element (and a return character). Each new text needed for the report will be added as another element at the end of the list. When done, the list is converted to a string variable with the command rpt as string, which combines all elements one after another. An alternative method is to use a string variable. These two approaches, side-by-side are:

   set rpt {"text 1"}               set rpt to "text 1"
   set end of rpt to {"text 2"}     set rpt to rpt & "text 2"
     ...                              ...
   set rpt to rpt as string

The list version on the left is faster because adding an element to the end of a list is faster then combining a string with itself (e.g., set rpt to rpt & "text 2") many times. For this small script the difference would not be noticeable, but it is good practice to use the most efficient methods whenever possible.

The process is straightforward, assuming you understand html elements. A name for the report is put into an h1 element; the name includes the file name. All data is in a three-column table where the first column labels the data and the other two columns give results for husbands and wives. The table starts with a caption for the table. The thead section has header rows to label the three columns. The tbody has two rows to report results for average ages at marriages and average ages when children were born. These rows are formatted using a custom InsertRow() subroutine. Finally, all elements are closed and the report ends with a </div> element.

The final step to to send the report to a GEDitCOM II report and display the report to the user. The report is created with a new report command and properties are used to name the report and set the report text to the contents of the rpt variable (be sure to caste is as string to convert the list to a string). Finally, the report is displayed to the user with the show browser command.

InsertRow(rowLabel, numHusb, sumHusb, numWife, sumWife) Subroutine

InsertRow()
(* Insert table row with husband and wife results *)
on InsertRow(rowLabel, numHusb, sumHusb, numWife, sumWife)
  set tr to "<tr><td>" & rowLabel & "</td><td align='"
  if numHusb > 0 then
    set tr to tr & "right'>" & RoundNum(sumHusb / numHusb, 2)
  else
    set tr to tr & "center'>-"
  end if
  set tr to tr & "</td><td align='"
  if numWife > 0 then
    set tr to tr & "right'>" & RoundNum(sumWife / numWife, 2)
  else
    set tr to tr & "center'>-"
  end if
  set tr to tr & "</td></tr>" & return
  return tr
end InsertRow

This subroutine formats each row of the table. The input parameters are a label for the row and numerical results to be averaged and displayed in the table. The only catch is that numHusb or numWife might be zero if no individuals suitable for averaging were found in the CollectAges() subroutine. Since we do not want to divide by zero, the special case is trapped and the table cell is loaded with "-" rather then a calculated average. Another refinement implemented in this subroutine is to select alignment for the table cells. The label is left justified. All averages are right justified. If no data are available, the "-" is centered. When the subroutine ends, it returns the entire text for the row.

If the table just loads a number (e.g., sumHusb/numHusb), the number may have many digits such as "26.83743". For better formatting, it is preferable to truncate to fewer digits. The RoundNum() subroutine takes a number and formats it with any number of decimal points. Here the average ages are formatted up to two decimal places.


WriteToReport() (for Pages)
(* Write the results not in the global variables to a Pages document *)
on WriteToReport()
  set fname to name of front document of application "GEDitCOM II"
	
  tell application "Pages"
    make document
    set titleStyle to {alignment:center, bold:true, italic:false, ¬
        font size:14, font name:"Helvetica"}
    set captionStyle to {alignment:center, bold:false, italic:true, ¬
        font size:12}
  end tell
	
  -- title
  TextToPages("Generational Age Analysis in " & fname, titleStyle)
  TextToPages(return & return, "")
	
  -- caption
  TextToPages("Summary of spouse ages when married and when children were born", ¬
      captionStyle)
  TextToPages(return & return, "")
	
  -- build table into list of lists. Each item of list is row of the table
  set rpt to {{"Age Item", "Husband", "Wife"}}
  set end of rpt to InsertRowList("Avg. Age at Marriage", numHusbAge, ¬
      sumHusbAge, numWifeAge, sumWifeAge)
  set end of rpt to InsertRowList("Avg. Age at Childbirth", numFathAge, ¬
      sumFathAge, numMothAge, sumMothAge)
	
  tell application "Pages"
    tell front document
      add table data rpt with header row
    end tell
  end tell
	
end WriteToReport

(* Insert table row with husband and wife results in a list element
*)
on InsertRowList(rowLabel, numHusb, sumHusb, numWife, sumWife)
  set tr to {rowLabel}
  if numHusb > 0 then
    set end of tr to RoundNum(sumHusb / numHusb, 2)
  else
    set end of tr to "-"
  end if
  if numWife > 0 then
    set end of tr to RoundNum(sumWife / numWife, 2)
  else
    set end of tr to "-"
  end if
  return tr
end InsertRowList

Report Output Alternatives

One powerful feature of AppleScript is that you are not limited to interacting with GEDitCOM II. Many Macintosh applications are scriptable. Any script for GEDitCOM II can also use features of any other scriptable application. For example, instead of writing the report to a GEDitCOM II report, you can write the report to a word processing document such as a document for Apple's Pages application or for Microsoft Word. In this sample script, the output can be diverted to another application simply by rewriting the WriteToReport() subroutine. This tutorial illustrates the process by showing how to write the report to either a Pages document or an MS Word document.

Send the Report to a Pages Document

AppleScripting new applications can be a challenge. The process it to open that application's scripting dictionary in the Script Editor and try to find the necessary objects to accomplish your needs. Here the goal is to write text to a document, to format the text, and to load results into a table. The new WriteRoReport() for Pages is shown on the right. WARNING: this Pages output option only works for Pages 4.x and older. Apple unfortunately decided to delete nearly all AppleScripting feature of Pages when they released Pages 5.0.

The first section makes a new document and defines some styles to be used below. The next two sections write the report title and the table caption. The task of writing text to a Pages document with any defined style is done in the TextToPages() subroutine. This subroutine appends text to the end of the new document. The second parameter to the subroutine sets the style for the added text and its paragraph. The styles for this report are listed above. You can use any style defined for the character element in the Pages' scripting dictionary.

The next section creates the table for the report. Pages has an AppleScripting command to add a table. The table must be provided in a list of lists. Each element of the list is a row of the table. The list for each row has the data for each column of the table. Here the column labels are in the first row and the calculations are in the next two rows. The InsertRowList() subroutine (shown to the right) helps insert each row. It is identical to InsertRow() except that it creates a list of items rather html elements of the items.

Finally the add table command creates a floating table. If desired, you can move the table by setting properties of the new table. You can see the Pages dictionary for all possible options in editing the table, but the options are somewhat limited.


WriteToReport() (for Microsoft Word)
(* Write the results not in the global variables to n MS Word document *)
on WriteToReport()
  set fname to name of front document of application "GEDitCOM II"
  
  tell application "Microsoft Word"
    make document
    set titleFont to {name:"Times New Roman", font size:14, ¬
        bold:true, italic:false}
    set titlePara to {alignment:align paragraph center}
    set captionFont to {font size:12, bold:false, italic:true}
    set captionPara to ""
    set headerFont to {bold:true, italic:false}
    set tablePara to {alignment:align paragraph left}
    set rowFont to {bold:false}
  end tell
  
  -- title
  TextToWord("Generational Age Analysis in " & fname, titleFont, titlePara)
  TextToWord(return & return, "", "")
  
  -- caption
  TextToWord("Summary of spouse ages when married and when children were born", ¬
      captionFont, captionPara)
  TextToWord(return & return, "", "")
  
  -- first row is column labels
  tell selection of application "Microsoft Word"
    set tableStart to selection end
  end tell
  TextToWord("Age Item" & tab & "Husband" & ¬
      tab & "Wife" & return, headerFont, tablePara)
  
  -- rows for ages when married and when children were borm
  TextToWord(InsertRowSep("Avg. Age at Marriage", numHusbAge, ¬
      sumHusbAge, numWifeAge, sumWifeAge), rowFont, "")
  TextToWord(InsertRowSep("Avg. Age at Childbirth", numFathAge, ¬
      sumFathAge, numMothAge, sumMothAge), "", "")
  
  -- format the table
  tell application "Microsoft Word"
    -- select the table text and convert to MS word table
    tell selection
      set tableEnd to selection end
    end tell
    -- need this approach since separator option in convert to table broken
    set default table separator to tab
    set aDoc to the active document
    set myRange to set range text object of aDoc start tableStart end tableEnd
    set myTable to convert to table myRange table format table format grid3
    
    -- do some table formatting
    set alignment of paragraph format of text object of ¬
         first row of myTable to align paragraph center
    repeat with i from 2 to 11
      repeat with j from 2 to 3
        set acell to get cell from table myTable row i column j
        set alignment of paragraph format of text object of ¬
            acell to align paragraph center
      end repeat
    end repeat
  end tell
end WriteToReport

(* Insert table row with husband and wife results in plain text *)
on InsertRowSep(rowLabel, numHusb, sumHusb, numWife, sumWife)
  set tr to rowLabel & tab
  if numHusb > 0 then
    set tr to tr & RoundNum(sumHusb / numHusb, 2) & tab
  else
    set tr to tr & "-" & tab
  end if
  if numWife > 0 then
    set tr to tr & RoundNum(sumWife / numWife, 2)
  else
    set tr to tr & "-"
  end if
  return tr & return
end InsertRowSep

Send the Report to a Microsoft Word Document

Microsoft Word is fully scriptable and provides an enormous number of options (a text copy of its scripting dictionary posted by Microsoft is 574 pages). It is tricky to get started and some features of the AppleScripting interface do not work (one to create tables was needed for this script). Again, the key task is to write formatted text to a new document. A utility method called TextToWord() was written to accomplish this task. With that utility method, the WriteToReport() subroutine (shown on the right) to create a report in Microsoft Word is very similar to the one for Pages.

The first section makes a new document and defines some font and paragraph styles to be used later. The TextToWord() subroutine is used to add the title and caption. This method takes three parameters - the text to add to the report, the style for the font for that text, and the style for the paragraph to hold the text. The styles are AppleScript records and use options from MS Word's scripting dictionary.

The next section creates the table. The process is to insert table text using tab delimited items in each row, to select all inserted text, and then to invoke MS Word's convert to table command. Here the header rows are inserted and the report rows are inserted using the InsertRowSep() subroutine (shown to the right). It is identical to InsertRow() except that it creates tab-delimited items rather html elements of the items.

The last section selects the text for the table and converts to a table. Although MS Word's dictionary defines an option on the convert to table command to set the separator used in the text between columns, that option does not work. The problem was solved by setting the default table separator to tab before using the convert to table command. The table is created using a built-in MS Word style ("grid3"). Once the table is created, some additional MS Word options are used to set alignments of the cells in the table.