I posted this in the schema section, but maybe it belongs here instead... sorry for the duplication.

***

I have sort of a difficult challenge I've taken on for my first project. Essentially I'm writing my own family tree database. The main table is the people table and all the people in the tree are in this one table. I've defined separate relationships for parents, siblings, and spouses. For spouses I have a separate bridge table for "marriages". For siblings I've decided to let common parentage (either with one or two parents) be the defining characteristic of a sibling. That's the background.

For parents I have four fields in my main person table. On each for father, mother, adoptive father, and adoptive mother. I've set up a relationship where each of the parent fields are filled in with the unique ID of the parent of the record you're on. The user interface I'd like for this is to do typeahead to populate a dropdown menu. I could do this just fine except that I can only type ahead and auto assign the value if it's text. And the field needs the unique integer.

The closest I've come so far is having a popup menu with all the names in the person table. I choose from there and the corresponding record loads the numeric value into the proper field. A popup menu however won't work as I have over 2000 people in the database. That's why typeahead is key.

Does anyone know how I can have a field where I type ahead based on text field in the related table but ultimately a numeric value is assigned to the appropriate field? I'm assuming I need some script to do it. Remember, the value I'm looking up to deposit in the field is from another row in the same table for which I'm making an entry.

Thanks in advance.

I deleted your other posts and sent you a Private Topic.

Lee