Relationship based on a Global Field???

[BeckhamTX]

I am trying to ctreate a relationship with two criteria. The first criteria works fine for me, but the second one does not. I am trying to match a classification field in one table to a global field in the other table (Because I want the relationship to pull only those people classified as "X" not y,z, etc...)

I read on another post that the fields in a relationship must be indexed, which a global is not. is there anyway to create a relationship like this?

Example:

person 1 belongs to ABC corp, and is classified as X

person 2 belings to ABC corp and is classified as Y.

I already have it set to match company to company, but I can not get it to pull just those classified as X from ABC corp.

[transpower]

Use three tables: 1) person, 2) company, 3) classification.

[QuinTech]

Hi, Beckham. I don't share Transpower's conviction that you need three tables; most developers usually use one table for each type of object, and it is not clear to me that "Classification" in your case is a different type of object. I think it may very well be a property of a person, in which case it would probably be most appropriate for it to be a field in the Person table, which it seems you already have.

Getting back to your question: You say "a global field in the other table." Since this field would, by definition, have the same value for each record, how does the relationship narrow down your choices? A little more info on exactly what you're trying to do would help us troubleshoot it.

Jerry

[LiveOak]

Global fields can be used successfully on the "left" side of the relationship. I sounds like you need to create a "compound" key composed of both the CompanyID and Classification (I wouldn't use company name for the match).

You can set a global text field to 1234X, call it:

gCompClass

and create a relationship to a calculated text field (set as stored and indexed) defined as:

= CompanyID & Classification (in FM7, you can define the relationship to "And" these two fields and skip the calculated field)

CompanyID can be just a number field set as a serial number incrementing by one and set so it can't be modified.

-bd

[BeckhamTX]

QuinTech

In response to your question about how it would narrow down my choices:

In one table, the classification could be X, Y, or Z.

The relationship I want is to pull all people classified as X only. I want to pull the data from the second table into a portal in the first table only if the person is classified as X. if they are Y or Z, the portal should be empty for that person.

[QuinTech]

OK, then i think Brent's got your answer above. What you're looking for is a common function, but you have to match FROM a global TO a regular field.

J

[transpower]

But suppose that classification is not a minor attribute...then as I've said you're better off with making a separate table for it. That way you can have a portal there for the corresponding people, and you could perform various summary statistics for each classification.

[QuinTech]

Well said, Transpower. Only Beckham knows if this structure is warranted.

J