This may seem odd to most people, but I figured I'd share my code, nonetheless. To work around odd customer requirements, I have to put two UDT instances on one page. They will both have the same fields, but are used for different types of items the user is tracking. For a bit of background, the reason they need this is because they have different formatting rules for the two types of data to be tracked using the UDT module. So, to get around this, we decided to add two UDT instances with the same fields. Then the customer said that they needed the ability to move a row from one UDT to the other. Now, I've seen this capability before (SourceForge Enterprise Edition uses it to move data between trackers and it's great), so I had no problem with implementing it. Unfortunately, we don't have the time to add the features in before our upcoming release, so I created a stored procedure to do a simple move.
CREATE PROCEDURE {databaseOwner}{objectQualifier}UserDefinedTable_MoveRow
@RowId INT,
@ModuleId INT
AS
-- update row
UPDATE {objectQualifier}UserDefinedRows
SET ModuleId = @ModuleId
WHERE UserDefinedRowId = @RowId
-- update fields
UPDATE {objectQualifier}UserDefinedData
SET UserDefinedFieldId =
(
SELECT DISTINCT new.UserDefinedFieldId
FROM {objectQualifier}UserDefinedFields old,
{objectQualifier}UserDefinedFields new
WHERE old.FieldTitle = new.FieldTitle
AND old.UserDefinedFieldId =
{objectQualifier}UserDefinedData.UserDefinedFieldId
AND new.ModuleId = @ModuleId
)
WHERE UserDefinedRowId = @RowId
-- remove fields that don't exist in new module
DELETE FROM {objectQualifier}UserDefinedData
WHERE UserDefinedRowId = @RowId
AND UserDefinedFieldId NOT IN
(
SELECT UserDefinedFieldId
FROM {objectQualifier}UserDefinedFields
WHERE ModuleId = @ModuleId
)
GOIf/when I actually implement this for real, I plan on implementing copy and cut options on the main view with a paste option in the action menu. Currently, the move just matches fields up by their title. That's probably the best (read: only) way to accurately do it without providing some sort of in-between screen to map data to the new module. (Hmm... now, that's an idea.)