Page 1 of 1

Family Tree with Images

PostPosted: Tue Apr 20, 2021 3:25 am
by chrisb
ever since reading a thread on the Base forum in February 2021 penned by user PurplePix which referenced a family tree created in Calc it has been my intention to build a family tree database.
i have zero interest in my own family tree & all relationships are fictitious.

i used:
windows 10
libreoffice 6.4.4.2 (x64)
menu:Tools>Options>Load/Save: ODF format version 1.2 Extended
hsqldb 2.5.1

small but significant ongoing modifications made in libreoffice appear to have rendered openoffice/libreoffice compatibility a thing of the past.
the attachment will function in openoffice but there is a significant delay in execution of simple refresh macros & individual form controls may be incorrectly positioned in the form 'fTree' (resize the window).

i only use hsqldb in conjunction with this forum but hsqldb 2.5.1 is a mature database with a comprehensive set of reliable functions although sadly it lacks window functions.

a tree begins with the root & sprouts branches as it grows.
in this db the root is 'Harrison Pink' & the branches are biological parents.
each time we move up a level (generation) the branches potentially double so from root to great grand parent we have 4 levels & if every person has both a father & a mother 15 people (5 levels = 23 people).

we should not lose sight of the fact that our main interest is in the biological ancestors of root.
biological ancestors may have brothers/sisters who themselves may have formed adult relationships & have families of their own.
it's the choice of the user in how wide the net is spread.

my data model is:
Relationships.png
Relationships

issues i hope to have overcome:
an adult relationship may fail, children may be involved, partners of failed relationships may form new relationships & new relationships may produce more children.

------------------------------
output forms/report:
i have ended up with 5 output forms which when used in combination provide a fairly comprehensive analysis of individual relationships.
each form is independent of the others & utilises a filter table in order to restrict output to that of one preselected individual.
the forms are called fAscendants, fDescendants, fFamilyLinks, fImmediateLinks & fTree. it's pretty obvious what they display.
one report rImmediateLinks. same data as form fImmediateLinks but shows all persons who have had one or more relationships. enables quick scrolling of links.

------------------------------
input form 'fPeopleAndRoles_input':
people need to be input before they can be utilised.
roles should probably remain as is.

------------------------------
input form 'fFamilyRelationships_input':
all relationships are made/broken here.
when this form is first opened the list boxes display the bound field values (bug in both libre/open office), just hit the Refresh icon in the upper navigation bar.

'Family Title & Appointed Head':
'FamilyName' is defined as unique, helps us to visibly identify a family & is also used for sorting in the query 'qINFO_Family'.
'FamilyName' is determined like this:
all letters in UPPERCASE.
first 3 letters of 'FamilyHead' last name + underscore + first letter of 'FamilyHead' first name + underscore + first 3 letters of partner last name + underscore + first letter of partner first name.
it may be that by using the above formula we end up with duplicate names e.g. single parent families, 'WHI_J'(Jack White), 'WHI_J'(John White), 'WHI_J'(Joe White) so just use your ingenuity ('WHI_JA', 'WHI_JOH', 'WHI_JOE')

'FamilyHead' should be the alpha partner (the male in a mixed gender relationship).
'DateFrom' is the date when the family was first formed.
'DateTo' is the date when the family was dissolved i.e. we have less than two living/remaining family members or the couple in a 'Married/Partners' relationship split.

Relationship Between PersonOne And PersonTwo:
there are 2 types of relationship 'Married/Partners' & 'Parent/Child'.

a relationship must be created between each child & each partner.

'DateFrom' is the date when 'PersonTwo' joined the selected family.
'DateTo' is the date when 'PersonTwo' left the selected family.
'PersonTwoNote' is a short note which indicates the reason 'PersonTwo' left the selected family. if 'PersonTwo' is a child with two parents then an identical note should be entered for both relationships.

if for any reason a 'Married/Partners' relationship ends then the family should be dissolved & new families should be created for those parents who retain custody of one or more children.
i decided to take this course of action because i have found the form 'fFamilyLinks' to be very practical/transparent & for a database of this type clarity trumps ease of input.
e.g. open the form 'fFamilyLinks' & select 'Daffodil Judith' who was married, divorced, remarried.

------------------------------
Queries:
queries beginning with 'qF' are used as the data-source for forms.
queries beginning with 'qINFO' were used to aid development, you may find them useful.
queries beginning with 'qLB' are used by list boxes.
i prefer not to embed sql within forms for 2 reasons, accessibility and integrity, if a form corrupts i don't lose the code.

heavy use of UNION & DISTINCT mean that this db is inefficient although unless it contains a large number of records (which is unlikely) will not be noticeable.
------------------------------

GENERAL:
this is a split db & the zip contains all necessary files.
DACM's embedded macro (eternally grateful for that) is included along with two other macros, one to refresh the output forms & the other to resize the window of 'fTree' (because libreoffice messed this up).

if anybody wants to create their own tree then follow the instructions set out in the text file 'DeleteDataResetAutoKeys.txt' which can be found in the extracted folder, it clears all unnecessary table data.
use capitalised names for people (John, Joe etc.). i used uppercase names in order to distinguish adopted children but using all upper case can create display issues.

i first tried the so called stable versions of LibreOffice_7.0.5_Win_x64, LibreOffice_7.0.4_Win_x64, LibreOffice_6.4.6_Win_x64, LibreOffice_6.4.4_Win_x64 which were all incredibly unstable/unusable before settling for libreoffice Version: 6.4.4.2 (x64) which itself is far from perfect.

there has been a steady increase in the number of bugs over the past few months.
each new version of libreoffice seems to contain most if not all of the bugs which were present in the previous version while new bugs have been consistently introduced.
the issues i experienced are not related to 'UserProfile'.
libreoffice was always uninstalled, the pc was restarted, the entire libreoffice folder was deleted from AppData then another restart followed by a fresh install.
there has been no development of the Base component for quite a while now but people are tinkering with other modules which is likely the source of this consistent deterioration. it's a very worrying situation.

 Edit: 10 May 2021 replaced previous attachment. 

the attachment below contains the exact same data as the previous attachment.
it seems to be a prerequisite that a database of this type contains images.
i have added a folder which contains images & assigned each person one image. each image is 121 width x 178 height (pixels).
in each of the output forms we display the image which represents the selected individual.
in the report we show each persons assigned image.
if a person has not been assigned an image then we show a default image.

the database contains 68 people (35 males, 33 females) & sourcing images free of charge was difficult. ideally i would have liked portrait pics (head & shoulders) but that proved impossible.
the image folder includes 6 unused pics (5 male, 1 female).
showing an image really does seem to enhance presentation even though in the case of this demo those images are pretty meaningless.

LIBREOFFICE USERS PLEASE READ THIS PARAGRAPH:
images are assigned in the form 'fPeople_input'. open the form.
both navigation bars show a number followed by an asterisk e.g. 41*.
the asterisk indicates that we have more than 41 records & that we must move to the last record before we can safely scroll through the record set.
in the leftmost navigation bar hit 'Last Record'.
the record marker points to 'ALEXANDER White', DOB = '1997-07-13' & the image sub-form shows the name & image of 'Stephen White'.
hit 'Next Record'
'ALEXANDER White' has become 'ALEXANDER YELLOW' & DOB has now become '1990-04-15', these are the correct values & if we move the record pointer back to 'ALEXANDER YELLOW' the sub-form shows the correct name & image. it's now safe to edit data.
i have two other split libreoffice databases which exhibit this nasty bug.
the workaround is: if an asterisk is visible then hit 'New Record' & never hit 'Last Record'.
to the best of my knowledge this bug is not present in openoffice.

GENERAL:
made some minor changes to forms, added 5 queries & 1 macro & updated other macros all in order to accommodate the images.

https://www.mediafire.com/file/y98qazl9 ... s.zip/file