Symptoms
We face a problem inside sharepoint online. We are unable to work with K2 Smartforms inside Sharepoint Online.
The error message is in the K2 SmartForms Viewer :
Object reference not set to an instance of an object.
Diagnoses
During the web-meeting we see that the error seems to comes from the error calling the SmartObject
SmartObject ExplorerAll SmartObjectsSharePoint_Integration_Workflow_Helper_MethodsMethodsGetForms
After different SQL queries, we found that several category specified in Category.Data.CategoryId but not found in Category.Category.ID.
These categories was linked to a SharePoint List for test generated just after the first installation of K2.
Maybe it was linked to these procedure followed for a previous ticket (to fix issue "Data at the root level is invalid")
For this ticket: we fix the issue with the customer by :
1) Go to the AppCatalog
2) choose "Manage App Activations"
3) Remove all sites
4) Re-Add necessary Site collection
Maybe it was the origin of the issue but we can't do more test because the customer have only this production database.
Resolution
To analyse issue:
--Categories with join on the corresponding data table with check if category is not missing on the table Category.Category:
WITH #LatestVersion (SmartObjectGUID, [Version]) AS
(
SELECT SmartObject.SmartObjectGUID
,MAX(SmartObject.[Version]) AS [Version]
FROM SmartBroker.SmartObject
GROUP BY SmartObject.SmartObjectGUID
)
SELECT [Data].*,
ISNULL(CASE P4.DisplayName
WHEN NULL THEN ''
WHEN 'Public Folder' THEN ''
ELSE P4.DisplayName + ''
END,'') +
ISNULL(CASE P3.DisplayName
WHEN NULL THEN ''
WHEN 'Public Folder' THEN ''
ELSE P3.DisplayName + ''
END,'') +
ISNULL(CASE P2.DisplayName
WHEN NULL THEN ''
WHEN 'Public Folder' THEN ''
ELSE P2.DisplayName + ''
END,'') +
ISNULL(CASE P1.DisplayName
WHEN NULL THEN ''
WHEN 'Public Folder' THEN ''
ELSE P1.DisplayName + ''
END,'') + Category.[DisplayName] AS Path, --'
CASE [Data].DataType
WHEN 'View' THEN ISNULL( [View].DisplayName, [View_Design].DisplayName + '(Not check in)')
WHEN 'Form' THEN ISNULL( [Form].DisplayName, [Form_Design].DisplayName + '(Not check in)')
WHEN 'SmartObject' THEN SmartObject.[SystemName] + isnull(SmartObject.[SmartObjectXML].value('(//displayname/text())[1]','varchar(1000)'), SmartObject.systemname)
WHEN 'ThinClientProcess' THEN [ProcessXML].name
WHEN 'EventBus' THEN [CustomEvent].name
WHEN 'Report' THEN [Report].ReportName
ELSE '!!TYPE NOT EXPECTED'
END DisplayNameInSubTable ,
[View_Design].*
FROM [Category].[Data]
LEFT JOIN [Category].[Category] ON [Category].ID=[Data].CategoryId
LEFT JOIN Category.Category P1 ON P1.Id=Category.ParentCategories.value('(/parentcategory/@id)[1]','nvarchar(max)')
LEFT JOIN Category.Category P2 ON P2.Id=P1.ParentCategories.value('(/parentcategory/@id)[1]','nvarchar(max)')
LEFT JOIN Category.Category P3 ON P3.Id=P2.ParentCategories.value('(/parentcategory/@id)[1]','nvarchar(max)')
LEFT JOIN Category.Category P4 ON P4.Id=P3.ParentCategories.value('(/parentcategory/@id)[1]','nvarchar(max)')
LEFT JOIN [Form].[View] ON CAST([View].Id AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'View'
LEFT JOIN [Form].[View_Design] ON CAST([View_Design].Id AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'View'
LEFT JOIN [Form].[Form] ON CAST([Form].Id AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'Form'
LEFT JOIN [Form].[Form_Design] ON CAST([Form_Design].Id AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'Form'
LEFT JOIN #LatestVersion AS LV ON CAST(LV.SmartObjectGUID AS nvarchar(255)) = [Data].Data AND [Data].DataType = 'SmartObject'
LEFT JOIN [SmartBroker].[SmartObject] ON SmartObject.[Version]=LV.[Version]
AND SmartObject.SmartObjectGUID=LV.SmartObjectGUID AND [Data].DataType = 'SmartObject'
LEFT JOIN [Designer].[ProcessXML] ON CAST([ProcessXML].ID AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'ThinClientProcess'
LEFT JOIN [Eventbus].[CustomEvent] ON CAST([CustomEvent].ID AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'EventBus'
LEFT JOIN [Workspace].[Report] ON CAST([Report].ReportId AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'Report'
WHERE 1=1
AND ([Category].ID IS NULL OR P1.Id IS NULL
OR ([View].Id IS NULL AND [Form].Id IS NULL AND [View_Design].Id IS NULL AND [Form_Design].Id IS NULL AND [SmartObject].SmartObjectGUID IS NULL AND [ProcessXML].ID IS NULL AND [CustomEvent].ID IS NULL AND [Report].ReportId IS NULL)
)
AND ObjectDelete='0'
ORDER BY 7,4
----------------------------------------------------------------------Fix issue of category missing
--This script will create one main directory "K2FranceFixCategory" with several sub categories corresponding to the missing categories with the name "K2FranceFix{Number}"
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#Results%') BEGIN
DROP TABLE #Results
END
WITH #LatestVersion (SmartObjectGUID, [Version]) AS
(
SELECT SmartObject.SmartObjectGUID
,MAX(SmartObject.[Version]) AS [Version]
FROM SmartBroker.SmartObject
GROUP BY SmartObject.SmartObjectGUID
)
SELECT DISTINCT [Data].CategoryId INTO #Results
FROM [Category].[Data]
LEFT JOIN [Category].[Category] ON [Category].ID=[Data].CategoryId
LEFT JOIN [Form].[View] ON CAST([View].Id AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'View'
LEFT JOIN [Form].[View_Design] ON CAST([View_Design].Id AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'View'
LEFT JOIN [Form].[Form] ON CAST([Form].Id AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'Form'
LEFT JOIN [Form].[Form_Design] ON CAST([Form_Design].Id AS nvarchar(255)) =[Data].Data AND [Data].DataType = 'Form'
WHERE 1=1
AND ([Category].ID IS NULL
OR ([View].Id IS NULL AND [Form].Id IS NULL AND [View_Design].Id IS NULL AND [Form_Design].Id IS NULL)
)
AND ObjectDelete='0'
AND [Data].DataType IN ('Form','View')
SET IDENTITY_INSERT [Category].[Category] OFF
declare @IdentityOutput int
INSERT INTO [Category].[Category]
([Name],
[DisplayName],
[Description],
[ParentCategories],
[SystemId])
SELECT 'K2FranceFixCategory',
'K2FranceFixCategory',
'Created to fix issue of category specified in Category.Data.CategoryId but not found in Category.Category.ID',
'',
'1' WHERE NOT EXISTS (SELECT 1
FROM [Category].[Category]
WHERE [Name]='K2FranceFixCategory')
SET @IdentityOutput = (SELECT MAX([ID])
FROM [Category].[Category]
WHERE [Name]='K2FranceFixCategory')
SET IDENTITY_INSERT [Category].[Category] ON
INSERT INTO [Category].[Category]
( [Id],
[Name],
[DisplayName],
[Description],
[ParentCategories],
[SystemId])
SELECT CategoryId,
'K2FranceFix' + cast(row_number() over (order by CategoryId asc) AS nvarchar(255)),
'K2FranceFix' + cast(row_number() over (order by CategoryId asc) AS nvarchar(255)),
'Created to fix issue of category specified in Category.Data.CategoryId but not found in Category.Category.ID',
'' ,
'1'
FROM #Results
ORDER BY 1
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#Results%') BEGIN
DROP TABLE #Results
END
--DELETE FROM [Category].[Category]
--WHERE [Description]= 'Created to fix issue of category specified in Category.Data.CategoryId but not found in Category.Category.ID'
