Group Administration Page (GAP): Filters & FTQL

  1. What are the Advanced Filters, and what is FTQL? faq id: 1189
  2. What is FTQL's syntax? faq id: 1190
  3. What conditional operators are part of FTQL? faq id: 1191
  4. How do I use Wildcards (% and *) in statements? faq id: 1192
  5. How do I use the LIKE condition in a statement? faq id: 1193
  6. How do I use the NOT LIKE statement? faq id: 1194
  7. How do I use equality (=) and inequality (>, <, >=, <=, and <>) operators in statements? faq id: 1197
  8. I have discovered things that I can do with Advanced Filters and FTQL that are not part of the FAQ. Why not? faq id: 1201
  9. Advanced Filtering is complicated. Is there an easier way to do this? faq id: 1202
  10. What are more examples of ways that I could use Advanced filtering? faq id: 1203

Questions and Answers

  1. What are the Advanced Filters, and what is FTQL? faq id: 1189

    Advanced Filters and Family Tree Query Language (FTQL) are tools being developed by the Family Tree DNA Information Technology department for Family Tree DNA's many advanced users. Both tools are currently in the ALPHA stage of development. The Family Tree Information Technology department is offering the opportunity to explore and test Advanced Filters and FTQL.

    FTQL is a powerful tool for finding the information you need in GAP Reports. Software may be said to have a scale between ease-of-use and power.

    Ease-of-Use <- 1 2 3 4 5 -> Power

    The current planned release version of FTQL would be rated about a 4 on this scale.

    The advantage of learning to use the Advanced Filters is that it allows you to focus on data subsets. This has the potential to streamline your project work. However, no guarantee is made as to the usefulness or usability of these tools. Feedback is, of course, greatly appreciated.

  2. What is FTQL's syntax? faq id: 1190

    In most Advanced Filter fields, FTQL follows two basic patterns; text and date.

    • Searches of text fields follow a text pattern: OPERATOR 'queryText'.
      • Example: LIKE 'Smith'
    • Searches of date fields follow a date pattern: OPERATOR #queryDate#.
      • Example: = #2009/08/06#

  3. What conditional operators are part of FTQL? faq id: 1191

    The current ALPHA version of FTQL has eight conditional statements. They are:

    • LIKE: Matches your text query to a field.
    • NOT LIKE: Exclude matches to your text query.
    • =: Matches your date query to a field.
    • >: Matches dates that are greater than your query to a field.
    • <: Matches dates that are less than your query to a field.
    • >=: Matches dates that are greater than or equal to your query to a field.
    • <=: Matches dates that are less than or equal to your query to a field.
    • <>: Excludes dates that match your query to a field.

  4. How do I use Wildcards (% and *) in statements? faq id: 1192

    Wildcards allow you to search for partial matches in a text field. FTQL allows you to use either the * or the % wildcard. They both work the same way.

    • You may use a wildcard at the start of a query term.
      • Example: LIKE '*datter' or LIKE '%datter'
    • You may use a wildcard at the end of a query term.
      • Example: LIKE '3010*' or  LIKE '3010%'
    • You may use a wildcard at both the start and the end of a query term.
      • Example: LIKE '*beth*' or LIKE '%beth%'

  5. How do I use the LIKE condition in a statement? faq id: 1193

    The LIKE condition allows you to search for all or part of a participant's name, e-mail address, origin information, lab tests, Y-chromosome DNA SNP results, and many other fields. The query term should always be in single quotes, ' '. There are two wildcard symbols, * and %. They may be placed at the start of a string, the end of a string, or both.

    Example: The administrator of the DIDMOW surname project, Penelope BOBTROT, has received an e-mail from a project participant's sister. Her name is Molly, and she has questions about her brother Simon's test results. Among the 123 DIDMOW project participants, Penelope is not sure to which kit Molly is referring. Penelope decides to filter the table on the Member Information page for any name containing Simon. She clicks the Show Advanced Filter button and enters in the Name field:

    LIKE '*Simon*' (She could have entered LIKE '%Simon%' with the same result.)

    She clicks the Run Filter button. The results look like this.

    Kit Number Name Email Address Note Release Kit Back/
    Kit Mailed    		 Remove
    From
    Group
    B1822 Phillip Simon Didmow PSD@didmow.org Test was funded by..
    		 YES 2005/06/07
    B2829 Simon Paul Didmow simon@didmow.org This may be an NPE.. YES 2006/12/25
    B2835 Simon W. Didmow mygenealogy@ftmail.net Sister Molly controls.. YES 2009/05/03

  6. How do I use the NOT LIKE statement? faq id: 1194

    The NOT LIKE condition allows you to search and exclude a term from the results. The query term should always be in single quotes, ' '. There are two wildcard symbols, * and %. They may be placed at the start of a string, the end of a string, or both.

    Example: Penelope BOBTROT, the administrator of the DIDMOW Surname project, is sending friendly reminders to those who have not returned their test kits. The unreturned kits in the project include kits that she has on hand for family gatherings and kits that other people have ordered through the project. She only wishes to see a list of those that have been ordered by other people. On the Unreceived Kits page, she clicks the Show Advanced Filter button and enters her own e-mail address in the Email field:

    NOT LIKE 'penebobtrot@didmow.org'

    She clicks the Run Filter button. The results look like this.

    Kit Number Name Email Address Order Date
    B1822 TBD Didmow bb3@ftmail.net 2005/06/07
    B2829 Dean Layin Didmow dlayin@ftemail.org 2006/10/25
    B2835 Ernest I. Didmow ernieidid55@didmow-motors.com 2006/10/26

  7. How do I use equality (=) and inequality (>, <, >=, <=, and <>) operators in statements? faq id: 1197

    The equality and inequality conditions are most often used for number and date searches. The equals (=) condition allows you to search for a match, while the not equals (<>) condition allows you to exclude a match. The various other inequality (<, >, <=, and >=) conditions allow for partial matches.

    Example: Penelope BOBTROT, the administrator of the DIDMOW Surname project, has just returned from vacation. She would like to check what lab results have been returned since she left on July 13th. On the Received Lab Results page, she clicks the Show Advanced Filter button and enters in the Return Date field:

    >= #2010/07/13#

    She clicks the Run Filter button. Then she sorts on the Return Date field by clicking on it once. The results look like this.

    Kit Number Name Lab Procedure Return Date
    B3230 Moses Didmow Y-DNA61-67 Markers 2010/07/13
    B3230 Moses Didmow Y-DNA38-47 Markers 2010/07/13
    B3230 Moses Didmow Y-DNA48-60 Markers 2010/07/13

  8. I have discovered things that I can do with Advanced Filters and FTQL that are not part of the FAQ. Why not? faq id: 1201

    Yes, the Family Tree DNA Information Technology department is developing new features. Until they feel that something is stable enough to be tested, it will not be included in this FAQ. Please experiment, and if you find something that you like, feedback is, of course, greatly appreciated.

  9. Advanced Filtering is complicated. Is there an easier way to do this? faq id: 1202

    No, there is not currently a simple filter. However, the Family Tree DNA Information Technology department is developing a Basic Filter that will be less powerful but also less complex.

  10. What are more examples of ways that I could use Advanced filtering? faq id: 1203

    Yes, we have seven more examples of how you might use Advanced filtering and FTQL.

    Example 1: Olamay HYDMOI, administrator of the ZIMO Surname Project, has received an e-mail response to her monthly project newsletter. It is from Thelma PARTISIPELY who manages several kits. Thelma would like to know which of the kits that she manages are part of the ZIMO project. Olamay filters the information on the Member information page by the sponsor's e-mail address. She clicks the Show Advanced Filter button and enters in the Email field:

    LIKE '*zimoresearch@blueyonder.co.uk*' (She could have entered
    LIKE '%zimoresearch@blueyonder.co.uk%' with the same result.)

    She clicks the Run Filter button. The results look like this.

    Kit Number Name Email Address Note Release Kit Back/
    Kit Mailed    		 Remove
    From
    Group
    B952 Edward Zimo, Sr. zimoresearch@blueyonder.co.uk Test was funded by.. YES 2003/07/07
    B1050 Dr. Tyson T. Zimo zimoresearch@blueyonder.co.uk Test was funded by.. YES 2005/11/20
    B1051 Manny B. Zimo zimoresearch@blueyonder.co.uk Test was funded by.. YES 2005/11/15

    Example 2: The co-administrator of the TIPTOP project, Peter TIPTOP, receives a message from a woman who gave up a child for adoption in 1948. She tells Peter that she believes the name of the adopting couple was TIPTOP, and she asks him if he has participants in his project who were adopted. Peter needs to review the TIPTOP project notes. On the GAP Note page he clicks the Show Advanced Filter button and enters in the Note field:

    LIKE '*adopted*' (He could have entered LIKE '%adopted%' with the same result.)

    He clicks the Run Filter button. The results look like this.

    Kit Number Name Note
    B1350 M. J. TIPTOP Adopted in N.J. born c. 1948. He knows his mother was a DIMOSH.

    Example 3: Winnie WOLLYEA, administrator of the mtDNA haplogroup R0a2 project, is has been contacting project members to obtain additional information about their maternal ancestry. She decides to focus on one country at a time. On the Maternal Ancestry page, she searches for those with maternal ancestry in the Netherlands by putting in the Country field:

    LIKE 'Netherlands'

    She clicks the Run Filter button. The results look like this.

    Kit Number Name Email Country Maternal Ancestor Name Map Location Latitude Longitude MitoSearch/
    GEDCOM
    B108 Margaret TIMMITY Netherlands   Amsterdam 53.47 4.82
    B1259 T. von Lin Netherlands        
    B2188 Dorothy Popipe Netherlands        

    Example 4: The administrator of the Nimheckistan Geographic project, Aamir DAHBI, is updating the Public Project Website for his project. Because the project accepts those with autosomal DNA (Family Finder), mitochondrial DNA, and Y-chromosome DNA results linked geographically to Nihmeckistan, Aamir needs to be sure that only those who have maternal origins in Nimheckistan are displayed on the public mtDNA results table. He simplifies this by filtering for those with maternal origins in other locations. He will then be able to set their results to not display. On the Public Results Display Settings page, he clicks the Show Advanced Filter button and enters in the Maternal Country field:

    NOT LIKE 'Nimheckistan'

    He clicks the Run Filter button. The results look like this.

    Kit Number Name Paternal Country Maternal Country Display Y-DNA Display mtDNA
    B3030 Anonymous PatSample Nimheckistan Algeria YES YES
    B3031 M. L. Lukhari Nimbleckistan Germany YES YES
    B4535 Dr. Abnekar Nimheckistan Unknown YES YES

    Example 5: Winnie WOLLYEA, the administrator of the mtDNA haplogroup R0a2 project, is evaluating the 14544A mutation to see if it always occurs with the 12040G mutation. She decides to filter her project members' mitochondrial DNA (mtDNA) results to check for cases where someone does not have 12040G. On the mtDNA Results page, she clicks the Show Advanced Filter button and puts in the Coding Region Mutations field:

    NOT LIKE '*12040G*' (She could also have entered NOT LIKE '%12040G%' with the same result.)

    She clicks the Run Filter button. The results look like this.

    Kit Number Name Subgroup Haplogroup HVR1 Mutations HVR2 Mutations Coding Region Mutations
    B2002 B. Minnius Group B R02c 16126C,16304C, 16311C,16362C 58C,60.1T,64T, 263G 309.1C 750G,1438G,2355G,2442C, 2706G,3847C,4769G,7028T, 8860G,11719A,13188,14544A, 14766T,15326G,15674C
    B2837 Judi Mitzle Group B R02c 16126C,16304C, 16362C 58C,60.1T,64T, 263G 309.1C 750G,1438G,2355G,2442C, 2706G,3847C,4769G,7028T, 8860G,11719A,13188,14544A, 14766T,15326G,15674C
    B2977 Bina Madigie Ungrouped R02c 16126C,16304C, 16311C,16362C 58C,60.1T,64T, 263G 750G,1438G,2355G,2442C, 2706G,3847C,4769G,7028T, 8860G,11719A,13188,14544A, 14766T,15326G,15674C

    Example 6: Each day, the co-administrator of the TIPTOP project, Peter TIPTOP, likes to send a quick note to project members whose results have been returned. To obtain a list of the results, he goes to the Received Lab Results page, and he clicks the Show Advanced Filter button. He enters in the Return Date field:

    = #2010/08/03#

    He clicks the Run Filter button. The results look like this.

    Kit Number Name Lab Procedure Return Date
    B2001 Andrew Tiptop DCLADE-Q 2010/08/03
    B2137 Bennett Tiptop DCLADE-I 2010/08/03
    B3897 Dr. Emanuel Tiptop DCLADE-E 2010/08/03

    Example 7: Olamay HYDMOI, administrator of the ZIMO Surname Project, has been asked to speak at her local genealogical society about running a surname project and the early days of genetic genealogy. As part of her lecture, she decides to talk about the first year of her project, 2001. Olamay creates a table of results that were returned that year. On the Received Lab Results page, she clicks the Show Advanced Filter button and enters in the Return Date field:

    <= #2001/12/31#

    She clicks the Run Filter button. The results look like this.

    Kit Number Name Lab Procedure Return Date
    B102 Bernie Zimo Y-DNA1-12 Markers 2001/10/13
    B508 Mich Zimo Y-DNA1-12 Markers 2001/03/29
    B789 Zane Zimo Y-DNA1-12 Markers 2001/05/29