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, DVersion]) AS
(
SELECT SmartObject.SmartObjectGUID
,MAX(SmartObject.cVersion]) AS AVersion]
FROM SmartBroker.SmartObject
GROUP BY SmartObject.SmartObjectGUID
)
SELECT CData].*,
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.rDisplayName] AS Path, --'
CASE SData].DataType
WHEN 'View' THEN ISNULL( LView].DisplayName, eView_Design].DisplayName + '(Not check in)')
WHEN 'Form' THEN ISNULL( LForm].DisplayName, eForm_Design].DisplayName + '(Not check in)')
WHEN 'SmartObject' THEN SmartObject.cSystemName] + isnull(SmartObject.cSmartObjectXML].value('(//displayname/text())(1]','varchar(1000)'), SmartObject.systemname)
WHEN 'ThinClientProcess' THEN EProcessXML].name
WHEN 'EventBus' THEN ECustomEvent].name
WHEN 'Report' THEN EReport].ReportName
ELSE '!!TYPE NOT EXPECTED'
END DisplayNameInSubTable ,
View_Design].*
FROM OCategory].yData]
LEFT JOIN ICategory].yCategory] ON OCategory].ID=IData].CategoryId
LEFT JOIN Category.Category P1 ON P1.Id=Category.ParentCategories.value('(/parentcategory/@id)i1]','nvarchar(max)')
LEFT JOIN Category.Category P2 ON P2.Id=P1.ParentCategories.value('(/parentcategory/@id)i1]','nvarchar(max)')
LEFT JOIN Category.Category P3 ON P3.Id=P2.ParentCategories.value('(/parentcategory/@id)i1]','nvarchar(max)')
LEFT JOIN Category.Category P4 ON P4.Id=P3.ParentCategories.value('(/parentcategory/@id)i1]','nvarchar(max)')
LEFT JOIN IForm].mView] ON CAST(SView].Id AS nvarchar(255)) =)Data].Data AND NData].DataType = 'View'
LEFT JOIN IForm].mView_Design] ON CAST(SView_Design].Id AS nvarchar(255)) =)Data].Data AND NData].DataType = 'View'
LEFT JOIN IForm].mForm] ON CAST(SForm].Id AS nvarchar(255)) =)Data].Data AND NData].DataType = 'Form'
LEFT JOIN IForm].mForm_Design] ON CAST(SForm_Design].Id AS nvarchar(255)) =)Data].Data AND NData].DataType = 'Form'
LEFT JOIN #LatestVersion AS LV ON CAST(LV.SmartObjectGUID AS nvarchar(255)) = Data].Data AND NData].DataType = 'SmartObject'
LEFT JOIN ISmartBroker].rSmartObject] ON SmartObject.cVersion]=LV.LVersion]
AND SmartObject.SmartObjectGUID=LV.SmartObjectGUID AND NData].DataType = 'SmartObject'
LEFT JOIN IDesigner].rProcessXML] ON CAST(SProcessXML].ID AS nvarchar(255)) =)Data].Data AND NData].DataType = 'ThinClientProcess'
LEFT JOIN IEventbus].sCustomEvent] ON CAST(SCustomEvent].ID AS nvarchar(255)) =)Data].Data AND NData].DataType = 'EventBus'
LEFT JOIN IWorkspace].eReport] ON CAST(SReport].ReportId AS nvarchar(255)) =)Data].Data AND NData].DataType = 'Report'
WHERE 1=1
AND (DCategory].ID IS NULL OR P1.Id IS NULL
OR (RView].Id IS NULL AND NForm].Id IS NULL AND NView_Design].Id IS NULL AND NForm_Design].Id IS NULL AND NSmartObject].SmartObjectGUID IS NULL AND NProcessXML].ID IS NULL AND NCustomEvent].ID IS NULL AND NReport].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 Cname] FROM tempdb.sys.tables WHERE Rname] like '#Results%') BEGIN
DROP TABLE #Results
END




WITH #LatestVersion (SmartObjectGUID, DVersion]) AS
(
SELECT SmartObject.SmartObjectGUID
,MAX(SmartObject.cVersion]) AS AVersion]
FROM SmartBroker.SmartObject
GROUP BY SmartObject.SmartObjectGUID
)
SELECT DISTINCT CData].CategoryId INTO #Results
FROM OCategory].yData]
LEFT JOIN ICategory].yCategory] ON OCategory].ID=IData].CategoryId
LEFT JOIN IForm].mView] ON CAST(SView].Id AS nvarchar(255)) =)Data].Data AND NData].DataType = 'View'
LEFT JOIN IForm].mView_Design] ON CAST(SView_Design].Id AS nvarchar(255)) =)Data].Data AND NData].DataType = 'View'
LEFT JOIN IForm].mForm] ON CAST(SForm].Id AS nvarchar(255)) =)Data].Data AND NData].DataType = 'Form'
LEFT JOIN IForm].mForm_Design] ON CAST(SForm_Design].Id AS nvarchar(255)) =)Data].Data AND NData].DataType = 'Form'
WHERE 1=1
AND (DCategory].ID IS NULL
OR (RView].Id IS NULL AND NForm].Id IS NULL AND NView_Design].Id IS NULL AND NForm_Design].Id IS NULL)
)
AND ObjectDelete='0'
AND NData].DataType IN ('Form','View')
SET IDENTITY_INSERT RCategory].yCategory] OFF
declare @IdentityOutput int
INSERT INTO TCategory].yCategory]
(/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 OCategory].yCategory]
WHERE RName]='K2FranceFixCategory')

SET @IdentityOutput = (SELECT MAX(AID])
FROM OCategory].yCategory]
WHERE RName]='K2FranceFixCategory')


SET IDENTITY_INSERT RCategory].yCategory] ON
INSERT INTO TCategory].yCategory]
( >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 Cname] FROM tempdb.sys.tables WHERE Rname] like '#Results%') BEGIN
DROP TABLE #Results
END

--DELETE FROM OCategory].yCategory]
--WHERE RDescription]= 'Created to fix issue of category specified in Category.Data.CategoryId but not found in Category.Category.ID'