hi.

background: a publisher's contact database.

i'm trying to create instances of a contact table that only show 1 kind of contact (1 for editors, one for authors). there's currently a field that holds the "contact catergory", but it's not quite adequate to create a relationship on. for example, it holds information such as "US Editor" or "Foreign Editor". I need a key that is just "Editor" to create the relationship based on another global field (editor key or author key).

I thought I'd make a calculation field and use patterncount such that any record with "Editor" in the "catergory", regardless of what kind editor, would just get plain old "editor" in this calculation. but i can't get it to work.

any advice?

Am I right in assuming that these contacts live in a related table?

In this case, they will hold a foreign key to the "main" table.

My approach would be:

1. Add a calc'ed field like "IDforEditors", with "case (patterncount (categoryField ; "Editor" ) ; foreignKey ; "" ).

2. Add a table occurence of the related table, which you link with the "Main" table's primary key to the calculated foreign key. Do likewise for all other categories you want to filter on ("IDforAuthors" etc.).

3. To only show one related record for each category, just display the fields from each TO on your Main tables layout (outside of a portal), or if you only need the data in some other calculation, just refer the fields with that TO - in either case you will only get data from one (the first) record, which I think is what you wanted.

Hope this helped.

cheers,

eos