ORDA – Genealogy – Episode two

In the first episode of this trilogy, we demonstrated the basics of ORDA using list boxes, entities, and entity selections. Today, we’re going a bit deeper by running some queries that will take advantage of recursive relationships. We’ll also have a quick look at how to display list boxes nicely using the “meta info expression” property.

Download Genealogy – Episode two

use case scenario

I have selected one person in a very large list, and now I want to find their parents and grandparents. I also want their siblings … and children if they have any! Why not aunts and uncles … and cousins?
Will all that take an eternity? Let’s find out.

let’s start with the beginning!

The people entity selection will contain people born afterJanuary 1st, 1960, and will be saved inside the Form object:

Form.people:=ds.PEOPLE.query("Birthday >= :1";!1960-01-01!).orderBy("Birthday asc, Lastname asc, Firstname asc")

Note this highly recommended query format, it uses placeholders (:1) and parameters (!1960-01-01!).

SET UP THE LIST BOX

As we saw in the previous post, the entity selection (Form.people) is set up at the beginning as the entity selection property.

The list box, based on an entity selection, has a couple of new properties. We’ll describe three of them: Current item, Selected items, and Meta info expression.

 

The Current item as well as the Selected items will be automatically updated when the user will change the selection of the listbox:

The Current item will contain the selected entity (OR the last one added, in case of multiple selections). If there is no item selected in the list, this entity will be null.

blank

 

The Selected items is an entity selection. It’ll contain all of the selected items in the list, whatever the way they’ve been selected (shift+click, control+click, etc.). This entity selection will always exist, but can be empty.

blank

 

The Meta info expression is described below at the end of this post.

FAMILLY MEMBERS

During the on selection change event of the main list box, we need to find the parents, the children, and the siblings of the selected people:

Form.parents:=GetParents (Form.persons)
Form.children:=GetChildren (Form.persons)
Form.siblings:=GetSiblings (Form.persons)

Then, once we’ve created these entity selections, finding grandparents, uncles and aunts, as well as cousins, is really easy. Grandparents are the parents of the parents, uncles and aunts are the siblings of parents, and cousins are the children of the uncles and aunts!

Form.grandParents:=GetParents (Form.parents)
Form.auntsAndUncles:=GetSiblings (Form.parents)
Form.cousins:=GetChildren (Form.auntsAndUncles)

Queries

Finding related members is really easy when you use ORDA. Each “Get” method receives an entity selection as parameter and returns another entity selection (based on the content or the received one).

   Parents

The GetParents method receives Form.parents as $1 parameter. Let’s name it $_persons.
(The underscore is here only for visual help to make an easy distinction between “entity” and “entity selection”).

Just a reminder, Mother and Father are the entities defined in the relationship definitions. They’re the names of the “N to 1” relationships.

The magic of ORDA is that it can return either a single entity ($person.Mother, $person.Father, $person.Partner) when used with a single entity ($person) OR an entity selection when used with another entity selection ($_persons).

$_persons:=$1
$_parents:=$_persons.Mother.or($_persons.Father)
$0:=$_parents

   children

The same applies for entity selections defined for the “1 to n” relationships.

// Note: the text below fits on a single line
$_children:=$_persons.ChildrenAsFather (…)
.or($_persons.ChildrenAsMother) (…)
.orderBy("Birthday asc")

   Siblings

For siblings, we need to use to multiple relationships. First to find Father, then to find Children of Father, followed by a logical “or” applied to the Mothers, then a logical “minus” to remove people from their siblings lists (the parents of Paul have two sons, Paul and John but Paul has only one brother, John…)

// Note: the text below fits on a single line
$_siblings:=$_persons.Father.ChildrenAsFather (…)
.or($_persons.Mother.ChildrenAsMother(…)
.minus($_persons)

Final result

Now that we’ve found the members of the family by category, we’ll display them in other list boxes (with the same kind of information as the main list box). Then, all we have to do is duplicate the list boxes and change their entity selection property names to Form.grandParents, Form.parents, Form.siblings, Form.children, etc.

 

blank

Meta info expression

The Meta info expression is a 4D Method that (usually) receives This as first parameter and returns an object. In the pictures above, this method is used to draw blue and pink row backgrounds.

The returned object has a couple of properties that are defined here. In this case, the only property we use is fill.

$person:=$1
$meta:=New object()
$even:=(($person.indexOf()%2)=0) // is it an even or and odd row ?
If ($person.Gender=True) // women
  If ($even)
    $meta.fill:="#FFF0F0" // women light
  Else
    $meta.fill:="#FFE8E8" // women a bit darker
  End if
Else // man
  If ($even)
    $meta.fill:="#E8F8FF" // men light
  Else
    $meta.fill:="#E0F0FF"// men a bit darker
  End if
End if
$0:=$meta

Conclusion

This database contains basically SEVEN lines of code!

The first query to fill Form.people, then one line of code inside the six “Get…” methods. Simple and powerful.

So why do I have the feeling that I forgot something? Oh yes … is it fast? The average time to find the six categories of relatives for a single person within a group of many thousands of other people is about 1 ms (tested on an iMac 4.2Ghz Core i7 16Gb)

blank

If the people selection is much larger (let’s say, 7284), the average time is 6 ms.

blank

Try it yourself. Download the demo and enjoy ORDA !

Roland Lannuzel
• Product Owner & 4D Expert •After studying electronics, Roland went into industrial IT as a developer and consultant, building solutions for customers with a variety of databases and technologies. In the late 80’s he fell in love with 4D and has used it in writing business applications that include accounting, billing and email systems.Eventually joining the company in 1997, Roland’s valuable contributions include designing specifications, testing tools, demos as well as training and speaking to the 4D community at many conferences. He continues to actively shape the future of 4D by defining new features and database development tools.