Jump to content
  • 0

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


virtuosova

Question

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

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...