Check box calculation

[rebeccaatemc]

Hey there-

I have a table in my database designed to maintain all of my company's contacts. The table contains a checkbox field with the options "Client", "Consultant", "Vendor" and "Other". Because we have contacts that are both a client and a consultant, I have to maintain the field as a checkbox set.

In my projects table, though, I want to be able to show values lists of clients and consultants separately. i.e. Where the user can choose a client for any given project, I want a drop down list of clients only. My idea was to create a calculation field in the Contacts Table, which I could then use to create a dynamic value list.

e.g. Case(Contact_Type = "Client", Client_Name = "Company_Name"; "")

The problem is that this doesn't ultimately include the contacts for which both "Client" and "Consultant" is checked. Does that make sense? Any suggestions?

Thanks for the help.

[comment]

You could use something like:

Case ( not IsEmpty ( FilterValues ( "Client" ; ContactType ) ) ; ContactID )

Or, define a relationship from Projects to a new occurence of Contacts, based on matching type.

Edited December 13, 2006 by Guest

[rebeccaatemc]

Perfect! Thanks so much.