Value List with only field data from current record?

[HazMatt]

FileMaker Pro 5.0 for Mac.

I have a Contacts database which holds company data, including up to 6 individual contacts per company. I'd like to create a value list that pops up only the six contact names in that particular record, not the entire database.

Is this something that the "Only related values" option could help me out with? Because I'd be dealing within the same database for my application. Thanks.

[Steven H. Blackwell]

This is a conditional value list based on a relationship between the current file and the contacts file where the primary key in the current (company??) file matches a foreign key in each of the contact's records.

Old Advance Man

[danjacoby]

You're on the right track. This is actually the one situation where I recommend a repeating field -- for the "up to 6 individual contacts". Then create a self-relationship (i.e. Company::Company). Then, as long as each "Company" field has a unique entry (which can be validated), have the value list be only related values of the Contacts field.

[HazMatt]

OK, cool. But I have a big problem. My contacts database consists of company information AND all 6 individual contacts. It looks like I should have made a separate database for contacts.

But, I'm trying to put together a 6 Repetition Calculation field [Contact Names] that brings together [Contact 1 Name], [Contact 2 Name], [Contact 3 Name], [Contact 4 Name], [Contact 5 Name], and [Contact 6 Name]. I've been trying to use the "Extend (non-repeating

[HazMatt]

So... does anybody have any ideas

[danjacoby]

Calc field =

Contact1Name & "<paragraph mark>" &

Contact2Name & "<paragraph mark>" &

Contact3Name & "<paragraph mark>" &

Contact4Name & "<paragraph mark>" &

Contact5Name & "<paragraph mark>" &

Contact6Name

(Note: the <paragraph mark> here is the paragraph mark used to create a hard return)

This returns up to six values, separated by returns.

Now create a self-relationship, relating the key field to itself (probably the Company field).

Create a value list that consists of "Values from a field", "Only related values" -- using the self-relationship and the new calc field.

No repetitions or repeating fields necessary. And (as long as the calc field is unstored) you can change any or all of the six values in a record, and the value list will change accordingly.

HTH,

Dan

[HazMatt]

Very cool. It works!

Although a little unwieldy, the solution works good.

One thing I got screwed up with was when you said "<paragraph mark>". I figured out a little later that it was actually the symbol I wanted! (duh!) Anyway, here's a little test to see if we can get a real symbol:

My Mac keyboard's option-7: