Jump to content

creating a key with patterncount function

This topic is 5969 days old. Please don't post here. Open a new topic instead.

Recommended Posts


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?

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

This topic is 5969 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.