4 thoughts on “List all events in eventable with facts > 1”

1. The request form destroyed whatever formatting there was and collapsed your sql into a single line. Tried running it as is but it errored out. So I threw it into an AI Chat to reformat and it identified a bunch of errors and spat out the following which, to my surprise, actually worked, I think as you intended. Here’s one result (which may be misrendered once posted):

   OwnerID Name type fact SUBSTR(et.date, 4, 8) NameID etype date counts
   819 Decker, John Gilbert Birth – 1861 857 – 1 – 1861/11/18 18611118 857 1 1861 2

   Here’s the formatted sql:
   
-- All Facts greater than 1
SELECT
et.ownerid,
nt.surname || ", " || nt.given AS Name,
ft.name || " – " || SUBSTR(et.date, 4, 4) AS type,
nt.nameid || " – " || et.eventtype || " – " || SUBSTR(et.date, 4, 4) || "/" || SUBSTR(et.date, 8, 2) || "/" || SUBSTR(et.date, 10, 2) AS fact,
SUBSTR(et.date, 4, 8),
nt.nameid,
et.eventtype AS etype,
SUBSTR(et.date, 4, 4) AS date,
COUNT(nt.nameid || "-" || et.eventtype || " – " || SUBSTR(et.date, 4, 4) || "/" || SUBSTR(et.date, 8, 2) || "/" || SUBSTR(et.date, 10, 2)) AS counts
FROM eventtable et
JOIN nametable nt
ON et.ownerid = nt.ownerid
JOIN facttypetable ft
ON et.eventtype = ft.facttypeid
WHERE
nt.nametype = 0
GROUP BY
fact
HAVING
COUNT(nt.nameid || "-" || et.eventtype || " – " || SUBSTR(et.date, 4, 4) || "/" || SUBSTR(et.date, 8, 2) || "/" || SUBSTR(et.date, 10, 2)) > 1
ORDER BY
nt.surname || ", " || nt.given,
type;


   Your request says something about Alternate Names and Marriage events. I’m guessing that you are missing them. That’s because:

   Alternate Names are not in the EventTable as other fact types are but are in the NameTable with IsPrimary=0 (1 is the Primary Name). So you might be getting Alt Name instead of the Primary Name for some people and vice-versa for others.

   Marriage and other family-type fact type events are linked to the FamilyID, not the PersonID, so that both spouses can be found. So your query is likely bringing out the name of a person who is not one of the actual spouses.

   You might want to look back at some posts that have been made about listing “All Facts for a Person” to see if you can adapt one to your purpose.

   1. I initially created a query to find alternate name typos in my database. I had a relative who collected funeral cards. There was a shoe box full of funeral cards. It took me all day to scan all of the funeral cards. Then I attached the cards to the people in my database. The one problem I had was not all funeral homes put the maiden name on the card for the wives. I decided to add alternate names to my database. The alternate names given name was the given and maiden name. The alternate names surname was the husbands surname. The alternate names prefix was the given name of the husband with paranthesis around it. I then decided to add the marriage date to the alternate name record because that is when the maiden name changed to the married name. I redid my initial query to get alternate name dates that do not equal the marriage dates. I created this query:

      — Alternate Name Typos 4.sql
      — Copy all records to an excel spreadsheet
      — Filter Alternate name columns on blanks to get missing alternate names

      DROP TABLE IF EXISTS AlternateNames4;
      CREATE TABLE AlternateNames4 AS

      SELECT
      n2.[given]
      , n2.surname
      , (select n9.[given]
      from nametable n9
      where FM.MotherID = n9.[ownerid]
      and n9.[nametype] = 5) as maiden
      , FM.MotherID as MtrhID
      , ” as checked
      , n2.[Given] || ” ” || n2.surname as altgiven
      , n1.[Surname] as altsurname
      , “(” || n1.[Given] || “)” as Suffix
      , substr(Em.Date, 8, 2) || ‘/’ ||substr(Em.Date, 10, 2) || ‘/’ || SUBSTR(Em.Date,4,4) as MarriedDate
      , (Select (substr(n3.Date, 8, 2) || ‘/’ ||substr(n3.Date, 10, 2) || ‘/’ || SUBSTR(n3.Date,4,4))
      FROM nameTable n3

      LEFT JOIN NameTable N4
      ON FM.FatherID = N4.OwnerID

      where n3.[ownerid] = fm.MotherID
      and n3.[nametype] = 5
      and n3.suffix = “(” || n4.given || “)”
      ) as Altdate
      , fm.fatherid
      , N2.given || ‘ ‘ || N2.surname || ” ” || n1.surname || ” (” || n1.given || “) – ” || FM.MotherID || ” (Married)” AS AlternateName

      FROM FamilyTable FM
      left JOIN EventTable Em
      ON FM.FamilyID = Em.ownerID
      AND Em.EventType = 300
      LEFT JOIN NameTable N1
      ON FM.FatherID = N1.OwnerID
      AND +N1.IsPrimary
      LEFT JOIN NameTable N2
      ON FM.MotherID = N2.OwnerID
      AND +N2.IsPrimary

      where fm.[motherid] <> 0
      –where FM.MotherID = 473
      — order by father
      ;

      Select *
      from AlternateNames4
      order by MtrhID
      ;
      I did a ctrl + a to select everyone in the results and did a ctrl + v in an excel spreadsheet.

      I put these fomulas in the following columns”

      Cell M1 has =COUNTIF(M2:M19642,”X”)
      Column M – If there is a married date and no alternate date
      =IF(AND(I2 <> “”, J2 = “”), “x”, “”)

      Cell N1 has =COUNTIF(N2:N19642,”X”)
      Column N – if there is no alternate name record
      =IF(C2 = “”, “X”,”” )

      Cell O1 has =IF(TRIM(L2) <> TRIM(S2), “X”, “”)
      Column O – if the record from the database is not equal to the individual fields put together from the database
      =IF(TRIM(L2) <> TRIM(S2), “X”, “”)

      Cell P1 has =COUNTIF(P2:P19642,”X”)
      Column P – if the fatherid is blank
      =IF(K2 = “”,”X”,””)

      Cell Q1 has =COUNTIF(Q2:Q19642,”X”)
      Column Q – if there is no marriage date
      =IF(I2 = “”, “X”, “”)

      Cell R1 has =COUNTIF(R2:R19642,”X”)
      Column R – similar to columm m but added if marriage date is blank and alternate date = //. wanting to find records that there is a marriage date that does not equal the alternate date
      =IF(AND(I2 = “”, J2 = “//”),””,IF(TRIM(I2) <> TRIM(J2),”x”,””))

      Cell S1 has =MATCH(REPT(“z”,50),B:B)
      Column S – pieces the individual raw data together
      =TRIM(F2) & ” ” & TRIM(G2) & ” ” & TRIM(H2) & ” – ” & TRIM(D2) & ” (Married)”

      I made sure there were alternate names for all wives first by filtering Column N on blanks

      I filtered on column M next but found there was not enough criteria in it so I create column R.

      I created column O to see how many people did not have marriage dates.

      I created column P to see if there were any fatherids missing.

      Column O is to try to catch typos.

      There is one problem with my query. I have someone who was married by the justice of the peace and then 3 months later got married again in the church. It is bringing the alternate date for the justice of the peace marriage. I am not sure what additional criteria I can add to the query to get the right date.

      1. Hi @momakid,
         Your comment today is outside the subject of the subject of the original tool request and the website presentation messes up the code format. So I would suggest you re-post it in the Discussion Forum. Further advantages are that more eyes will see it there than here and its editor has useful formatting controls.

2. One old script that might serve your purpose with modification is at https://sqlitetoolsforrootsmagic.com/lifelines/

   Still works!

Comments are closed.