Look up??

[effectsguy]

Hello, thanks in advance for any help.

I'm trying to get a field to look up information based on the relation.

Ex: I have 2 databases, one person contact and one company contact. I need the company database to pull a person's name but only if it meets a requirement. There will be multiple persons that belong to a single company. But there will only be one Key Contact person. I'd like to tell the company database to pull from these related batch of records, but only display the key contact name in the field. I've got it set up as an If statement, but it only works for the first record. If any record after that is the key contact, it doesn't work.

Any thoughts? I just can't get it.

Thanks.

[Genx]

Well what defines the key contact person and why can't you isolate that single person via the relationship?

[effectsguy]

I have a Key Contact Check Box. That triggers the look up. Which works great for the first record. But fails if the key contact is not the first record.

[Genx]

You can either a) Sort the relationship to put the contact with the key contact box checked at the top

OR...

Create an additional relationship where

id = id

constKeyContact = checkboxKeyContact

Where constKeyContact is a calculation that has the value that is selected in the checkbox to define the contact as the key contact.

i.e. if you check the key contact box and the checked value is literally "Key Contact" then constKeyContact would have "Key Contact" as the value -- meaning that you always isolate the record(s) that are related with the first criteria (id = id), but then due to the second criteria (constKeyContact = checkboxKeyContact) you are isolating the one record that is the Key Contact and can just use isolatedKeyContact::name as your calc.

Or as i said you can just sort by the checkbox field (in the relationships graph, double-click the relationship, choose sort, and sort by your checkbox). -- here you use If( isolatedKeyContact::checkBoxField = "Key Contact" ; isolatedKeyContact::name )

[comment]

There will be multiple persons that belong to a single company. But there will only be one Key Contact person.

This suggests the selection should be done at the company level, by entering the key contact's ID into a KeyContactID field.

[Genx]

... or that.

[effectsguy]

Thanks, everyone.

Genx, your solution worked. I was missing that "constant" that caused the extra level of filtering.