Some time ago I was devising a database solution with XMod Pro when it became apparent that the data from a multi-select list needed to be stored in a separate table from the one being edited. I had a case where members could be in one or many working groups. Ideally, the link between members and working groups should have been stored in a link table and not the master Members table.
XMod Pro stores the selected values from a form’s <ListBox> control in the field assigned to the DataField property. The selected values are delimited by the character assigned to the SelectedItemsSeparator property.
Of course, you can store an ID for each list item in the field and you can still filter the data in SQL reasonably well.
But what if there are potentially hundreds of values? Things can start to get a bit messy in your filtering and your structure is not ideal. You may also find you run into difficulties when you want to join data from other tables. Or, you may find yourself working with a structure that has already been agreed and you cannot change it. There are times when you need to work with list data that’s not stored in a single field of delimited values but in a separate linking table that has each rows’ ID paired with that of the master table.
In the project mentioned above I eventually decided I could do everything with the standard method and that there wasn’t sufficient budget to justify the time taken to implement the more elegant solution. However, Kelly had suggested a solution to the problem on the forum and others had since implemented it with success but I had not seen a working example. I was keen to try it out for myself so I could use it later and share it with others in the community who were clearly struggling with the same problem.
The requirements can clearly vary a great deal. For example, do you want to store the data in the table you are editing AND the linking table? This may make sense because it will offer additional means of querying the data. And you may want to pass other information into the sub table when the form is updated. As you will see when you start to look at this example, XMod Pro allows a great deal of flexibility because you can build powerful dynamic SQL stored procedures that will enable you to do any number of data operations in a single submit.
So, for this problem we can take the list of delimited values created by the XMod Pro Add Form list box and strip them out and insert them into any table we choose. We don’t have to insert them into the same table as the one we are editing with the XMod Pro form. In fact the field does not even have to exist in that table! As long as the field that is bound to the list box control is a parameter in the SQL for the CommandText property of the <SubmitCommand> tag, then XMod Pro will happily work with it! And for the XMod Pro Edit Form we simply have to bring the values back in the same format in the <SelectCommand>. XMod Pro will recognize the list of delimited values in the DataField and highlight them in the <ListBox> items. The XMod Pro side is brilliantly simple once you have mastered the dynamic SQL.
For this example I have stripped everything right down to the bare essentials. There are three tables, a list of books which just contains a field for book title, a list of keywords which can be chosen for a book and a linking table that holds key words that are associated with each book.
I have put this demo online for you to play with here: Go to this page and login with cdguest/guestuser. You can then add or edit book titles and choose key words from the list with CTRL+click. You can also look at the data from the link table to see how the key words are related to book titles. If you look at the books table structure you will see there is no key words field on that table.
I have created a SQL script that you can run into your DotNetNuke database which creates the tables and stored procedures for this demo. It also inserts some demo keywords in the key words table. If you are not using the DotNetNuke database you can simply run the script into the database you do want to use and add ConnectionString properties to your data commands in your XMod Pro forms and templates. But for this demo the easiest way is to open host/SQL, click the run as script check box and paste the script into the window and run it. The script also contains the delete commands you can use to remove the objects once you are done testing.
Note. The script that brings the data in for the Edit Form uses the XML method described here: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ Of course you can use any of the methods you prefer in the above discussion. I chose the XML Path method because it appeared to be the simplest.
SQL Script:
-- Script to create DB objects for example of how to use multi-select lists in Xmod Pro with data in
-- a separate table
-- A.Beare - Caraway Design, Nov 2010
/****** Object: Table [dbo].[CD_BookTitles] Script Date: 11/06/2010 15:05:35 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CD_BookTitles]') AND type in (N'U'))
DROP TABLE [dbo].[CD_BookTitles]
GO
/****** Object: Table [dbo].[CD_BookTitles] Script Date: 11/06/2010 15:05:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CD_BookTitles](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CD_Keywords] Script Date: 11/06/2010 15:07:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CD_Keywords]') AND type in (N'U'))
DROP TABLE [dbo].[CD_Keywords]
GO
/****** Object: Table [dbo].[CD_Keywords] Script Date: 11/06/2010 15:07:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CD_Keywords](
[ID] [int] IDENTITY(1,1) NOT NULL,
[keyword] [nchar](10) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CD_TitleKeyWords] Script Date: 11/06/2010 15:08:21 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CD_TitleKeyWords]') AND type in (N'U'))
DROP TABLE [dbo].[CD_TitleKeyWords]
GO
/****** Object: Table [dbo].[CD_TitleKeyWords] Script Date: 11/06/2010 15:08:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CD_TitleKeyWords](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BookID] [int] NULL,
[keywordID] [int] NULL
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[CD_getBooks] Script Date: 11/06/2010 15:12:22 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CD_getBooks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CD_getBooks]
GO
/****** Object: StoredProcedure [dbo].[CD_getBooks] Script Date: 11/06/2010 15:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec getBooks 9
-- procedure to get a book title from the book ID
-- used by the book edit form <SelectCommand>
create procedure [dbo].[CD_getBooks]
@BookID int
as
SELECT b1.Id, b1.Title,
( SELECT cast(keywordID as varchar(10)) + ','
FROM CD_TitleKeyWords b2
WHERE b2.BookID = b1.ID
ORDER BY keywordID
FOR XML PATH('') ) AS keywords
FROM CD_BookTitles b1
where ID=@BookID
GROUP BY Id,Title
GO
/****** Object: StoredProcedure [dbo].[CD_insert_books] Script Date: 11/06/2010 15:13:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CD_insert_books]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CD_insert_books]
GO
/****** Object: StoredProcedure [dbo].[CD_insert_books] Script Date: 11/06/2010 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- insert a new book
create procedure [dbo].[CD_insert_books]
@title varchar(50),
@keywordIDList varchar(max)
as
begin
/* INSERT the new book title */
/* and get the ID value */
INSERT INTO [CD_BookTitles] ([Title]) VALUES(@Title)
declare @MasterID int
set @MasterID=SCOPE_IDENTITY() -- gets the new ID
/* next loop the list of key words and insert them into the link table */
declare @pos int
declare @piece varchar(max)
-- Tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@keywordIDList),1) <> ','
set @keywordIDList = @keywordIDList + ','
set @pos = patindex('%,%' , @keywordIDList)
while @pos <> 0
begin
set @piece = left(@keywordIDList, @pos - 1)
-- to test uncomment the print statement
-- print 'Master ID is ' + cast(@MasterId as varchar(10)) + ' item ID is ' + cast(@piece as varchar(500))
-- insert the keywords into the link table
insert into CD_TitleKeyWords ([BookID],[keywordID]) values (@MasterID,@piece)
set @keywordIDList = stuff(@keywordIDList, 1, @pos, '')
set @pos = patindex('%,%' , @keywordIDList)
end
end
GO
/****** Object: StoredProcedure [dbo].[CD_update_books] Script Date: 11/06/2010 15:13:43 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CD_update_books]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CD_update_books]
GO
/****** Object: StoredProcedure [dbo].[CD_update_books] Script Date: 11/06/2010 15:13:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- update the books
create procedure [dbo].[CD_update_books]
@BookID int,
@title varchar(50),
@keywordIDList varchar(max)
as
begin
-- update the title
-- first delete the existing keywords
DELETE FROM CD_TitleKeyWords where CD_TitleKeyWords.BookID=@BookID
-- now update the title
UPDATE CD_BookTitles set Title=@title where ID=@Bookid
/* next loop the list of key words and insert them into the link table */
declare @pos int
declare @piece varchar(max)
-- Tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@keywordIDList),1) <> ','
set @keywordIDList = @keywordIDList + ','
set @pos = patindex('%,%' , @keywordIDList)
while @pos <> 0
begin
set @piece = left(@keywordIDList, @pos - 1)
-- this is for testing
-- print 'Master ID is ' + cast(@MasterId as varchar(10)) + ' item ID is ' + cast(@piece as varchar(500))
-- insert the new key words
insert into CD_TitleKeyWords ([BookID],[keywordID]) values (@BookID,@piece)
set @keywordIDList = stuff(@keywordIDList, 1, @pos, '')
set @pos = patindex('%,%' , @keywordIDList)
end
end
GO
-- insert some test key words
INSERT INTO CD_Keywords (keyword) values ('fish')
Go
INSERT INTO CD_Keywords (keyword) values ('cat')
Go
INSERT INTO CD_Keywords (keyword) values ('dog')
Go
INSERT INTO CD_Keywords (keyword) values ('house')
Go
INSERT INTO CD_Keywords (keyword) values ('hobbit')
Go
INSERT INTO CD_Keywords (keyword) values ('tree')
Go
INSERT INTO CD_Keywords (keyword) values ('animal')
Go
INSERT INTO CD_Keywords (keyword) values ('horse')
Go
INSERT INTO CD_Keywords (keyword) values ('people')
Go
INSERT INTO CD_Keywords (keyword) values ('nature')
Go
INSERT INTO CD_Keywords (keyword) values ('mineral')
Go
INSERT INTO CD_Keywords (keyword) values ('vegetable')
Go
INSERT INTO CD_Keywords (keyword) values ('plant')
Go
INSERT INTO CD_Keywords (keyword) values ('boat')
Go
INSERT INTO CD_Keywords (keyword) values ('country')
Go
INSERT INTO CD_Keywords (keyword) values ('nation')
Go
INSERT INTO CD_Keywords (keyword) values ('county')
Go
INSERT INTO CD_Keywords (keyword) values ('monster')
Go
INSERT INTO CD_Keywords (keyword) values ('sail')
Go
INSERT INTO CD_Keywords (keyword) values ('sea')
Go
There is one XMod Pro form in this example for the Books table. You can get the code for the add and edit sections here:
Add Form Example:
<AddForm>
<SubmitCommand CommandText="exec CD_insert_books @Title,@keywords" />
<ControlDataSource id="dskeywords" CommandText="select id,keyword from CD_keywords" />
<table>
<tr>
<td valign="top"><strong>Title</strong></td><td valign="top"><strong>Key Words</strong></td>
</tr>
<tr>
<td valign="top">
<TextBox id="txtTitle" DataField="Title" DataType="string" MaxLength="50" Width="200" />
<Validate Type="required" Target="txtTitle" Message="You must enter a Book Title!" />
</td >
<td valign="top">
<ListBox id="lstkeywords" DataSourceID="dskeywords" DataField="keywords" DataTextField="keyword" DataValueField="id" SelectedItemsSeparator="," datatype="string" selectionmode="multiple" />
</td>
</tr>
</table>
<div>
<AddButton Text="Add" CssClass="CommandButton xmp-button" /> <CancelButton Text="Cancel" CssClass="CommandButton xmp-button" />
</div>
</AddForm>
Edit Form Example:
<EditForm>
<SelectCommand CommandText="exec CD_getBooks @ID" />
<SubmitCommand CommandText="exec CD_update_books @ID,@Title,@keywords" />
<ControlDataSource id="dskeywords" CommandText="select id,keyword from CD_keywords" />
<table>
<tr>
<td valign="top"><strong>Title</strong></td>
<td valign="top"><strong>Key Words</strong></td>
</tr>
<tr>
<td valign="top">
<TextBox id="txtTitle" DataField="Title" DataType="string" MaxLength="50" Width="200" />
<Validate Type="required" Target="txtTitle" Message="You must enter a Book Title!" />
</td>
<td valign="top">
<ListBox id="lstkeywords" DataSourceID="dskeywords" DataField="keywords" DataTextField="keyword" DataValueField="id" SelectedItemsSeparator="," DataType="string" SelectionMode="multiple" />
</td>
</tr>
</table>
<div>
<UpdateButton Text="Update" CssClass="CommandButton xmp-button" /> <CancelButton Text="Cancel" CssClass="CommandButton xmp-button" />
</div>
<TextBox id="ID" DataField="ID" DataType="int32" Visible="False" />
</div>
</EditForm>
There are also two lists.
The Books list is here:
<xmod:Template UsePaging="True" addroles="guests" editroles="guests">
<ListDataSource CommandText="SELECT [ID], [Title] FROM CD_BookTitles"/>
<DetailDataSource CommandText="SELECT [ID], [Title] FROM CD_BookTitles WHERE [ID]=@ID">
<Parameter name="ID"/>
</DetailDataSource>
<DeleteCommand CommandText="DELETE FROM CD_BookTitles WHERE [ID]=@ID">
<Parameter name="ID"/>
</DeleteCommand>
<Pager PageSize="20" />
<HeaderTemplate>
<table style="border-collapse:collapse;" cellpadding="10" cellspacing="10">
<thead>
<tr>
<th>Title</th>
<th> </th>
</tr>
</thead>
<tbody>
</HeaderTemplate>
<ItemTemplate>
<tr style="margin-bottom: 5px;">
<td>[[Title]]</td>
<td>
<xmod:EditLink Text="Edit" CssClass="CommandButton">
<Parameter Name="ID" Value='[[ID]]' />
</xmod:EditLink>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
<tr>
<td><xmod:AddButton Text="New Record" /></td>
<td><a href="http://www.carawaydesign.com/Examples/multi-select-list-link-table-demo-for-xmod-pro/Books-Keywords-Link-Table.aspx">See link data</a></td>
</tr>
</tbody>
</table>
</FooterTemplate>
</xmod:Template>
And the list that shows the relationship between book titles and key words is here:
<xmod:Template id="BooksKeyWords" UsePaging="True">
<ListDataSource CommandText="SELECT
CDTKW.BookID,
CDB.Title,
CDTKW.keywordID,
CDKW.keyword
FROM CD_TitleKeyWords CDTKW
left outer join CD_keywords CDKW on CDTKW.keywordID=CDKW.ID
inner join CD_BookTitles CDB on CDB.id=CDTKW.BookID" />
<Pager PageSize="20" />
<HeaderTemplate>
<table style="border-collapse:collapse;" cellpadding="8" cellspacing="8" width="650">
<thead>
<tr>
<th>Book ID</th>
<th>Title</th>
<th>Keyword ID</th>
<th>Keyword</th>
</tr>
</thead>
<tbody>
</HeaderTemplate>
<ItemTemplate>
<tr style="margin-bottom: 5px;">
<td>[[BookID]]</td>
<td>[[Title]]</td>
<td>[[KeyWordID]]</td>
<td>[[Keyword]]</td>
</tr>
</ItemTemplate>
<FooterTemplate>
<tr>
<td>
<a href="http://www.carawaydesign.com/Examples/multi-select-list-link-table-demo-for-xmod-pro.aspx">Back</a>
</td>
</tr>
</tbody>
</table>
</FooterTemplate>
</xmod:Template>
It is worth being aware that in this case a comma is used as the field separator. If you wish to change this you must change it in both the Add/Edit form <ListBox> controls AND the SQL stored procedures that update the data.
I hope you find this example useful. Good luck.
Gus