People like me come to the forum looking for help solve a problem in the absence of good documentation.
In 2016 I began making a Family tree in a calc spreadsheet, my first encounter with the forum I was told I should be using a commercial Database program. My need at that time and even today did not warrant a program that could handle thousands of people entries. I looked at making a small database to meet my needs, but I was not able to successfully make any progress. I continued with the spreadsheet, gathering data that always had the possibility to become a database in the future.
A cousin used a commercial data base, and with software and computer upgrades lost access to all his information. Open Office being open source seemed to offer a great alternative.
While searching the internet, I have found many people looking to make a simple Family Tree in Excel and other program suites. In the early stages my brother was running Excel so I had both an Open Office and Excel versions in both programs. Not having access to newer versions of Excel, I convinced my brother to switch to the Open Office Calc version as new features were added.
I came to the forum looking to add photos to my main Family Display page, I was told then that pictures in spreadsheets were not possible, and again I should be using a Database. With help from Villeroy and Chrisb who made a Database in 2021 based on what I was doing which I found in 2023. I had help along the way with SQL and aliased code for the Display sheet and photos (which I can't find in the forum) so I knew photos in a spread sheet was possible, but lacked the skill to put them on the Lineage page. Chrisb came to the rescue and posted code that even though different from what I needed, showed it was possible to achieve what I was trying to do.
After many hours wrestling with the code, I was able to make it work for me. In working on my Family Tree I see there is a need for something like I have put together. Mine handles 1500 people and 750 Families, it uses simple data entry in two (People and Families) sheets. Many have said Calc is unable to handle complex situation, I disagree. A spreadsheet has no problem with numbers and text, pictures are possible with a small Database. Chrisb has posted a Family Tree Database (Purple2.odb), there is Gramps and my project which started in 2016 has just over 1000 people and 370 Families, 452 Images (stored in a folder on my computer). I am sure I will make improvements over time, but for now I believe it would be good to look through the requests I have put in to understand some of the challenges.
To understand my last request this is part of what my code looks like, the brackets in the comment sections are values not required and I will look at cleaning up the code to see if I can leave them out. The output is all in one line, so I added the Child## to give me a reference what was produced by each line of code. My Lineage sheet goes out five generations, I am only taking the images out three generations to prevent it from becoming cluttered. The Family Display, Who is and Lineage sheets can also be text only if that is what I want.
/*This is where the magic begins */
select *
from
( --target child and parents
select "FamilyID", "P_FathersID", "P_MothersID" , "Childof","PersonPhoto", "Child01" "a"
from "FamilyTreePhotos"
where "FamilyID" = (select "F_FamilyID" from "tbl_FILTER_Lineage" where ID_F = 1)
) a
/* the columns you need are identified in the SELECT statement */
left join /*Family 01 Father FID = 178 P_Father = (503) P_Mother = (504) Childof = 59 PersonPhoto = 178 b = (205) */
(select "FamilyID", "P_FathersID", "P_MothersID" ,"Childof" ,"PersonPhoto" "Photo01Father", "Child02" "b" from "FamilyTreePhotos") b on b."FamilyID" = a."P_FathersID"
left join /*Family 01 Mother FID = 204 P_Father = (584) P_Mother = (582) Childof = 83 PersonPhoto = 204 c = ( ) */
(select "FamilyID" , "P_FathersID", "P_MothersID" ,"Childof","PersonPhoto" "Photo01Mother", "Child03" "c" from "FamilyTreePhotos") c on c."FamilyID" = a."P_MothersID"
/* in the individual SELECT statements, and because of how my table was constructed (FamilyID on the left that included the Father, Mother and Children - used in the Family display sheet for image display) and PersonID, Childof (connection to family) and photo on the right. created a level of complexity for the Lineage sheet project. */
/* Things to remember, alias letters can not be reused in different SELECT statements. Progression is achieved, for example (b ON b."FamilyID" = a."P_FathersID" ) b included everything in the first 'LEFT JOIN'
up to the 'FROM'. Next the 'ON b.' takes one column from the SELECT "FamilyID" and assigns it the value of a."P_FathersID" from the initial SELECT where 'a' was assigned.
Earlier requests
viewtopic.php?t=81349&hilit=PurplePix Identify cell reference with same data
viewtopic.php?t=96928&hilit=PurplePix Connect a Calc cell to an inserted Database field
viewtopic.php?t=100395 extract next record from query to populate spre
viewtopic.php?t=104387&hilit=PurplePix Blank cells in table prevent photo display
This problem for me is solved, that you for everyone that offered suggestions, especially Chrisb for his magic code.
Code: Select all
select *
from
( --target child and parents
select "FamilyID", "P_FathersID", "P_MothersID" , "Childof","PersonPhoto", "Child01" "a"
from "FamilyTreePhotos"
where "FamilyID" = (select "F_FamilyID" from "tbl_FILTER_Lineage" where ID_F = 1)
) a /*Family 01 Father FID = 70 P_Father = 178 P_Mother = 204 Childof = (27) PersonPhoto = (Needed) a = (205) */
/* FAMILY 1 abc */
left join /*Family 01 Father FID = 178 P_Father = (503) P_Mother = (504) Childof = 59 PersonPhoto = 178 b = (205) */
(select "FamilyID", "P_FathersID", "P_MothersID" ,"Childof" ,"PersonPhoto" "Photo01Father", "Child02" "b" from "FamilyTreePhotos") b on b."FamilyID" = a."P_FathersID"
left join /*Family 01 Mother FID = 204 P_Father = (584) P_Mother = (582) Childof = 83 PersonPhoto = 204 c = ( ) */
(select "FamilyID" , "P_FathersID", "P_MothersID" ,"Childof","PersonPhoto" "Photo01Mother", "Child03" "c" from "FamilyTreePhotos") c on c."FamilyID" = a."P_MothersID"
/* FAMILY 2 def */
left join /*Family 02 Family FID = 59 P_Father = 137 P_Mother = 176 Childof = 0 PersonPhoto = (Needed) d = (180 ) */
(select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child04" "d" from "FamilyTreePhotos") d on d."FamilyID" = b."Childof"
left join /*Family 02 Father FID = 137 P_Father = (193) P_Mother = (210) Childof = 47 PersonPhoto = 137 e = ( ) */
(select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo02Father" , "Child05" "e" from "FamilyTreePhotos") e on e."FamilyID" = d."P_FathersID"
left join /*Family 02 Mother FID = 176 P_Father = (490) P_Mother = (491) Childof = 130 PersonPhoto = 176 f = ( ) */
(select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo02Mother", "Child06" "f" from "FamilyTreePhotos") f on f."FamilyID" = d."P_MothersID"
/* FAMILY 3 ghi */
left join /*Family 03 Family FID = 83 P_Father = 233 P_Mother = 227 Childof = (36) PersonPhoto = (Needed) g = (180 ) */
(select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child07" "g" from "FamilyTreePhotos") g on g."FamilyID" = c."Childof"
left join /*Family 03 Father FID = 233 P_Father = (672) P_Mother = (159) Childof = 92 PersonPhoto = 233 h = ( ) */
(select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo03Father" , "Child08" "h" from "FamilyTreePhotos") h on h."FamilyID" = g."P_FathersID"
left join /*Family 03 Mother FID = 277 P_Father = (785) P_Mother = (786) Childof = 95 PersonPhoto = 277 i = ( ) */
(select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo03Mother", "Child09" "i" from "FamilyTreePhotos") i on i."FamilyID" = g."P_MothersID"
/* FAMILY 4 jkl */
left join /*Family 04 Family FID = 47 P_Father = 113 P_Mother = 126 Childof = (0) PersonPhoto = (Needed) j = (133 ) */
(select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child07" "j" from "FamilyTreePhotos") j on j."FamilyID" = e."Childof"
left join /*Family 04 Father FID = 113 P_Father = (314) P_Mother = (313) Childof = 25 PersonPhoto = 113 k = ( ) */
(select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo04Father" , "Child08" "k" from "FamilyTreePhotos") k on k."FamilyID" = j."P_FathersID"
left join /*Family 04 Mother FID = 126 P_Father = (352) P_Mother = (351) Childof = 138 PersonPhoto = 126 l = ( ) */
(select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo04Mother", "Child09" "l" from "FamilyTreePhotos") l on l."FamilyID" = j."P_MothersID"
/* FAMILY 5 mno */
left join /*Family 05 Family FID = 130 P_Father = 362 P_Mother = 359 Childof = (47) PersonPhoto = (130) m = ( ) */
(select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child10" "m" from "FamilyTreePhotos") m on m."FamilyID" = f."Childof"
left join /*Family 05 Father FID = 362 P_Father = (982) P_Mother = (983) Childof = 129 PersonPhoto = 362 n = ( ) */
(select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo05Father" , "Child11" "n" from "FamilyTreePhotos") n on n."FamilyID" = m."P_FathersID"
left join /*Family 05 Mother FID = 369 P_Father = (976) P_Mother = (977) Childof = 128 PersonPhoto = 126 o = ( ) */
(select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo05Mother", "Child12" "o" from "FamilyTreePhotos") o on o."FamilyID" = m."P_MothersID"
/* FAMILY 6 pqr */
left join /*Family 06 Family FID = 92 P_Father = 655 P_Mother = 231 Childof = (0) PersonPhoto = (Needed) p = ( ) */
(select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child13" "p" from "FamilyTreePhotos") p on p."FamilyID" = h."Childof"
left join /*Family 06 Father FID = 655 P_Father = ( ) P_Mother = ( ) Childof = 90 PersonPhoto = 655 q = ( ) */
(select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo06Father" , "Child14" "q" from "FamilyTreePhotos") q on q."FamilyID" = p."P_FathersID"
left join /*Family 06 Mother FID = 231 P_Father = (663) P_Mother = (664) Childof = 91 PersonPhoto = 231 r = ( ) */
(select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo06Mother", "Child15" "r" from "FamilyTreePhotos") r on r."FamilyID" = p."P_MothersID"
/* FAMILY 7 stu */
left join /*Family 07 Family FID = 95 P_Father = 267 P_Mother = 268 Childof = (36) PersonPhoto = (Needed) s = ( ) */
(select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child13" "s" from "FamilyTreePhotos") s on s."FamilyID" = i."Childof"
left join /*Family 07 Father FID = 267 P_Father = (764) P_Mother = (765) Childof = 93 PersonPhoto = 267 t = ( ) */
(select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo07Father" , "Child14" "t" from "FamilyTreePhotos") t on t."FamilyID" = s."P_FathersID"
left join /*Family 07 Mother FID = 268 P_Father = (160) P_Mother = (766) Childof = 94 PersonPhoto = 268 u = ( ) */
(select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo07Mother", "Child15" "u" from "FamilyTreePhotos") u on u."FamilyID" = s."P_MothersID"