Multiple criteria for relationship

[emkayess2]

I'm trying to create a project directory from a database containing all of the company's contacts. The difficulty is that, in order to determine which contacts should appear in a certain field of the directory db, I need to use TWO fields from the contacts db.

I need to specify ProjectName AND ConsultantType-- for example, on the University Hall Project Directory, in the StructuralEngineer field, I want to find and relate to all people in the contacts db who have University Hall checked as ProjectName AND Structural checked as ConsultantType.

I can get it to work smoothly with only one criteria -- ProjectName OR ConsultantType, but don't know how I can specify both.

Anybody? Thanks!

[Kurt Knippel]

quote:

---

Originally posted by emkayess2:

I'm trying to create a project directory from a database containing all of the company's contacts. The difficulty is that, in order to determine which contacts should appear in a certain field of the directory db, I need to use TWO fields from the contacts db.

I need to specify ProjectName AND ConsultantType-- for example, on the University Hall Project Directory, in the StructuralEngineer field, I want to find and relate to all people in the contacts db who have University Hall checked as ProjectName AND Structural checked as ConsultantType.

I can get it to work smoothly with only one criteria -- ProjectName OR ConsultantType, but don't know how I can specify both.

Anybody? Thanks!

---

A calculated key is the solution. Create a calc in the contact file ProjectName & "-" & ConsultantType. Then put the Project Name, followed by a "-" and the Consultant Type (i.e. University Hall-Structural Engineer) in the match field in the other DB to join them.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.database-resources.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[Kay]

Allow me to nudge in here... same topic, different question. I am building a Client Contacts db for which I wish to use a multiple key "OrganizationCode-ContactInitials" and I'm not sure how to ensure these codes are unique when new records are created. E.g...

Claris-BS is Bob Smith at Claris & is ok.

Claris-AF is Anne Fong at Claris & is ok.

Ocean-BS is Barbara Snelgrove at Ocean West & is ok.

So there will be dupes in OrgCode & dupes in ConInitials BUT I want a flag for the new record Claris-BS for Bart Simpson, so that we know to make Bart "Claris-BS1" or something like that because the whole thing is a dupe.

Several other dbs will access this db. I'm using FMP4 on Mac. Thanks so much, K.

[Kurt Knippel]

quote:

---

Originally posted by kattatonic:

Allow me to nudge in here... same topic, different question. I am building a Client Contacts db for which I wish to use a multiple key "OrganizationCode-ContactInitials" and I'm not sure how to ensure these codes are unique when new records are created. E.g...

Claris-BS is Bob Smith at Claris & is ok.

Claris-AF is Anne Fong at Claris & is ok.

Ocean-BS is Barbara Snelgrove at Ocean West & is ok.

So there will be dupes in OrgCode & dupes in ConInitials BUT I want a flag for the new record Claris-BS for Bart Simpson, so that we know to make Bart "Claris-BS1" or something like that because the whole thing is a dupe.

Several other dbs will access this db. I'm using FMP4 on Mac. Thanks so much, K.

---

NEVER use a claculated key as a unique key. Give each record a unique key field. Then use calculated keys in order to make certain types of relationships easier to deal with.

For unique keys I use Status(CurrentRecordID) & "-" & Status(CurrentTime) & "-" & Status(CurrentDate) stored in a Text field. This basically insures a unique key for each record.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.database-resources.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[Kay]

Thanks Kurt. I really like the key you use and am going to start using it.

My problem then is that the users wish to be able to reference a client record from other dbs with an easy-to-remember code. I was keeping client code & contact code separate and then combining to make reporting on both levels easier, or so I thought. Do you have a suggestion for a setup where they can look up a client from another db without using a value list based on that file and those fields? There will be >1000 of them. Thanks again, Kay.

[Kurt Knippel]

quote:

---

Originally posted by kattatonic:

Thanks Kurt. I really like the key you use and am going to start using it.

My problem then is that the users wish to be able to reference a client record from other dbs with an easy-to-remember code. I was keeping client code & contact code separate and then combining to make reporting on both levels easier, or so I thought. Do you have a suggestion for a setup where they can look up a client from another db without using a value list based on that file and those fields? There will be >1000 of them. Thanks again, Kay.

---

Your users can still keep the information that they are used to using, that information is NOT what you use to build the relationships though. The user should NEVER see or have access to the various keys you use in the development of the solution.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.database-resources.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[LiveOak]

Two things to watch out for:

1) If you include a "-" in the calculated field, make sure the indexing for that field is set to "ASCII"

2) FileMaker only indexes the first 20 characters of a text field. Calculated indices that are the same through the first 20 characters, but different after 20, will still match.

It is best to connect databases with record numbers or some index not used as an entry field. What happens if the the name of the project changes? If you type it in, you will disconnect all the related records. Also typo's become a problem. Best to give each project a unique number and use that. -bd