I'm configuring an "add user" page in a CMS where a user can be assigned to several organizations.

There is a table listing those organizations from which data is pulled and displayed with checkboxes on the "add user" page.

In the users table, there is a field that will contain the organizations' ID numbers to which the user belongs to.

So, for example, John Smith can be added to the system and he belongs to two organizations. As a result, the CMS manager will check the two appropriate boxes.

Both values for those boxes should be combined and inserted into one field with a delimiter.

Is there any way to do this "on the fly" with the insert record function, or do I have to create a "confirm" page that uses a join function to create the appropriate value to be inserted?

Thanks.

Yes what you are trying to do is possible but outside of the scope of dreamweaver, you will have to create a page to handle the joining (or maybe with vbscript or javascript client side).

But just an observation, normally you would have a user table, organisation table and then a link table to show which users are connected to which organisation. I think that you will find it much easier later on if you do it this way rather than using a delimited seperator in a field in your user table.

So the link table would be as follows:

Link_id
User_id_fk
Org_id_fk

Just my opinion and how I would do it. If you choose to continue with the delimited field post back the name of your checkbox field and I will knock up the code for you.

Basically, a hidden field can build the code as the user selects the checkboxes.

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

I can understand how a link table would be more effective in this case. I imagine that the table would consist of three fields: a primary key field with unique autonumber, a userid field linked to the userid table and a orgid field linked to the org table.

If I were to take this approach, how could I add a user and simultaneously update both tables? After all, I'd be adding a user to the userid table and a link record to the other table.

It might be better if I make it a two-step process:

1. Add the user and insert the record into that table.

2. On redirect, make the org associations and subsequently insert that record into the link table.

Unless anyone has a better idea, this may be the best way to go.

No, I agree with your plan.

u could always do all the code on one page but for future changes it is always nice to have seperate functions on seperate pages.

give me a shout if you come across any problems inserting on the second page.

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

[OK, so now that I've determined a plan of action, the question now relates to inserting multiple records into one table.

There's several threads already on this subject but none of them seem to address the issue of if one of those field values happens to be a repeat area.

Since the listing of organizations is populated by the orgs table, a recordsert is established on the page and the listing (along with checkboxes with their orgID value) is nested within a repeat region.

Sometimes, only two records will be added simultaneously, other times, five. The data will always dome from the repeat region area.

What would be the code to essentially take all these form values (with identical names) and assign them a unique record ID.

For example you may have:

<input type="hidden" name="linkuser" value="234">
<input type="checkbox" name="linkorg" value="<%=orgid%>">

repeated several times with the orgid variable being the only difference. Because of the repeat region, all these checkboxes and hidden variables would essentially have the same "name". The hidden variables would always have the same value (associated with the user, and the checkbox values would be the only thing to differentiate the actual records (aside form the actual recordID number)

So what bit of code would insert a variable number of records that would insert the data into the fields based on a repeat region?

Make sense?

You will need to handle this by coding the insert action into a repeat region.

This would normally be done on a seperate page.

Normally, i code this in vbscript. I will write some dummy code for you to use where you can just replace your fields. Will post here when done



Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Thanks Vince,

I'll probably use the mm_insert routine to get the code to execute on the same page as the form.

I'm sure that I could eventually figure it out myself but this headcold (thanks to my daughter) has given me a brain freeze for the last 3-4 days...

It's amazing that there isn't a single site I found that has a simple tutorial to get this done. Sure there is the "update/delete multiple records in one table" and "insert/update/delete records in multiple tables" but nothing that inserts several tables in one table.

That would be a good addition to the tutorials here!

Looking forward to the dummy code.