The function Xlate allows you to retrieve data from another table on a one-to-one basis: The key is always to a single row in the joined table.

The relational index allows to retrieve data from another table on a one-to-many basis: the key of the master table is used to retrieve into a field the keys in the source table whose rows have a field with the master row as a value. Then I assume you can use XLATE to retrieve other relevant data from the source table.

What I want to do is to have a table of individuals related to a table of names through the key of the individual's row in the individuals table. A person does not have just one name. Besides a legal name that no one may ever use (except by an angry parent or a court system), a person has other names by which she or he is known by various circles of people, e.g., nicknames, street names, affectionate names, maiden names, married names, cultural names, etc. even in American culture, but especially in other cultures. I would like the database to reflect this complexity of human life.

Is the relational index the only way to show this relationship between an individual and names in OpenInsight? In Filemaker Pro 3 and beyond, one would simply create a relation between two fields (whether in different datafiles or in the same one) and create a portal to display multi-rows.

How about a multivalued field with a btree or cross reference index on it? Allow user to enter a list of alt names, find their record by any of them.

Bob

Well..

First XLATE is not just a one-to-one join. For example:

Assume you have a multi-valued field which contains alternate keys to rows in the same (or a different) table. Call it ALT_KEYS. Assume you want to retrieve the Full Names for these and you have a Single Valued Symbolic that builds this. Assume this is Full_Name. Then:

 @ANS=XLATE(,{ALT_KEYS],"Full_Name",'X')

 ALT_NAMES=@ANS   ;* if you need to save it away

would return the name info. You can build arbitrarily complex things this way by doing indirect references as well.

How these keys got there is immaterial. You could have used relational indexing to maintain the connection or you can do it programatically yourself. All you need to supply is a common data element between the rows. Sometimes you might need to provide a table of alternate keys to a table. Assume these keys are saved in a separate table (call it MYTABLE_XREF). Assume that the Real Keys are stored in Field 1 (call it REAL_KEY). If this field is multi-valued then you will have to resolve the ambiguity yourself in the code that reads a real record. A user can supply any of the alternate keys which should resolve to the same unique record. You simply stuff this into a control on a form and call the READ event to populate the form with data.

HTH

Don Miller

C3 Inc.

View this thread on the Works forum...