Explorer impact of multi lookup property changes¶
create test object in M-Files with a multi lookup and single lookup then update metadata
DECLARE @ProcessBatch_ID INT;
EXEC dbo.spMFDropAndUpdateMetadata;
GO
-- -----------------------------------------------------------
-- Test object class table
-- -----------------------------------------------------------
DROP TABLE mfmftest;
EXEC dbo.spMFCreateTable 'MFTest';
EXEC dbo.spMFUpdateTable 'MFMFTest', 1;
SELECT *
FROM MFMFTest;
-- -----------------------------------------------------------
-- Check datatypes in SQL
-- -----------------------------------------------------------
EXEC dbo.spMFClassTableColumns;
SELECT *
FROM ##spMFClassTablecolumns
WHERE class = 'MFTest'
ORDER BY columnname;
-- -----------------------------------------------------------
-- add single lookup item to multi lookup property; ensure there is no other objects in the same class with multi items for this property
-- -----------------------------------------------------------
EXEC dbo.spMFUpdateTable 'MFMFTest', 1;
SELECT *
FROM MFMFTest;
EXEC dbo.spMFClassTableColumns;
SELECT *
FROM ##spMFClassTablecolumns
WHERE class = 'MFTest'
ORDER BY columnname;
-- -----------------------------------------------------------
-- change the datatype to multi lookup and add another item to the property
-- -----------------------------------------------------------
EXEC dbo.spMFUpdateTable 'MFMFTest', 1;
SELECT *
FROM MFMFTest;
EXEC dbo.spMFClassTableColumns;
SELECT *
FROM ##spMFClassTablecolumns
WHERE class = 'MFTest'
ORDER BY columnname;
-- the result should be an error
SELECT *
FROM dbo.MFLog
ORDER BY LogID DESC;
-- Conversion failed when converting the nvarchar value '5,2' to data type int.
-- -----------------------------------------------------------
-- update metadata strcuture with column reset
-- -----------------------------------------------------------
DECLARE @ProcessBatch_ID INT;
EXEC dbo.spMFDropAndUpdateMetadata @IsResetAll = 0
,@WithClassTableReset = 0
,@WithColumnReset = 1
,@IsStructureOnly = 0
,@ProcessBatch_ID = @ProcessBatch_ID OUTPUT
,@Debug = 0;
GO
-- check the result
EXEC dbo.spMFClassTableColumns;
SELECT *
FROM ##spMFClassTablecolumns
WHERE class = 'MFTest'
ORDER BY columnname;