I should know this but...(structuring one-to-many table)

[virtuosova]

I know I should know this...but I'm tripping over myself I'm sure.

I have a table named "contacts" that has these fields:

user_id

contact_type

fname

lname

etc.

I also have a table named "contact types" that has these fields:

type_id

contact_type

(Note: a contact type would something like Broker, Leader Manager, Finance Director, etc.)

One contact may have multiple (up to 3) contact types. For example, a contact may be the Broker, Lead Manager and Sales Associate whereas another may just be a Sales Associate.

Eventually, I'll have a data page that allows people to select anyone based on their contact type and have the results appear from the contact table

This is where I'm stuck: I can't figure out how I should structure the contacts table or (is it done in a data page and how?) so that when I add a new contact, I can assign that contact to multiple contact types.

I know this is probably database 101, but I think I've over-thought it so much that I can't see the answer staring at me.

Any help would be sincerely appreciated.

Lisa

[bahar_vm]

Hi Lisa,

Your tables structure is correct. You can now create a submission DataPage which uses "contacts" table as data source then in this form use the contact type field element as a multi-selection list box and select its data source as the "contact types" table. To enable multi-selection list box please follow the instruction at the following Java Script article viewtopic.php?f=14&t=12154.

So when in web form you can select multiple contact types and submit the contact entry to the table and it stores the contact types as separated by comma: broker, sales associate, ...

Then you will create a Report DataPage using the same contacts table which has a search form and in the search form you can include the contact type field again as a list box which allows users to search for one or multiple contact types and get the results.

Hope this helps.

Bahar M.