Prerequisites
The section objects should be accessible for portal users.
Open access to custom objects in portal
1. On dev environment run script to create [int_RegisterSectionInPortal] stored procedure.
Note. Procedure uses string_split function which is available only under compatibility level 130 and above (see details here).
int_RegisterSectionInPortal stored procedure
create or alter proc [int_RegisterSectionInPortal] (@EntityName NVARCHAR(MAX), @PortalId uniqueidentifier = 'C8565240-1DA3-4A68-BD4E-280F17B0D32E') as begin transaction; set nocount on; set transaction isolation level read uncommitted; set xact_abort on; begin try if @EntityName = '' OR @EntityName IS NULL throw 50001, 'Parameter @EntityName is empty', 1; declare @PortalSysModuleEntity table (Id uniqueidentifier, BaseRecordId uniqueidentifier, TypeColumnUId uniqueidentifier, SysEntitySchemaUId uniqueidentifier); declare @SchemaName table (Name nvarchar(250) NOT NULL); declare @temp table (Id uniqueidentifier); insert into @SchemaName select value from string_split(@EntityName, ',') where rtrim(value) <> ''; if (select COUNT(*) from @SchemaName) < 1 throw 50002, 'Parameter @EntityName has wrong format. It should contain entities comma separated names.', 1; insert into @PortalSysModuleEntity (Id, BaseRecordId, TypeColumnUId, SysEntitySchemaUId) select NEWID(), e.Id, TypeColumnUId, e.SysEntitySchemaUId from SysModuleEntity e where e.SysEntitySchemaUId in ( select [uid] from SysSchema sh inner join @SchemaName n on n.Name = sh.Name where ExtendParent = 0 ) insert into SysModuleEntity(Id, TypeColumnUId, SysEntitySchemaUId, CreatedOn) select Id, TypeColumnUId, SysEntitySchemaUId, GETUTCDATE() from @PortalSysModuleEntity select Id as SysModuleEntity from @PortalSysModuleEntity; insert into SysModule ( [CreatedOn] ,[Caption] ,[SysModuleEntityId] ,[Image16] ,[Image20] ,[FolderModeId] ,[GlobalSearchAvailable] ,[HasAnalytics] ,[HasActions] ,[HasRecent] ,[Code] ,[HelpContextId] ,[ProcessListeners] ,[SysPageSchemaUId] ,[ModuleHeader] ,[Attribute] ,[CardSchemaUId] ,[SectionModuleSchemaUId] ,[SectionSchemaUId] ,[CardModuleUId] ,[TypeColumnValue] ,[Image32Id] ,[LogoId]) output inserted.Id into @temp select GETUTCDATE() ,s.[Caption] + ' Portal' ,p.Id ,s.[Image16] ,s.[Image20] ,s.[FolderModeId] ,s.[GlobalSearchAvailable] ,s.[HasAnalytics] ,s.[HasActions] ,s.[HasRecent] ,s.[Code] ,s.[HelpContextId] ,s.[ProcessListeners] ,s.[SysPageSchemaUId] ,s.[ModuleHeader] ,s.[Attribute] ,s.[CardSchemaUId] ,s.[SectionModuleSchemaUId] ,s.[SectionSchemaUId] ,s.[CardModuleUId] ,s.[TypeColumnValue] ,s.[Image32Id] ,s.[LogoId] from SysModule inner join SysModuleEntity sme on sme.Id = SysModule.SysModuleEntityId inner join @PortalSysModuleEntity p on p.BaseRecordId = sme.Id inner join SysModule s on s.SysModuleEntityId = p.BaseRecordId select Id as SysModule from @temp; delete from @temp; insert into SysModuleEdit (ActionKindCaption, ActionKindName, CardSchemaUId, HelpContextId, MiniPageSchemaUId, PageCaption, SearchRowSchemaUId, SysModuleEntityId, SysPageSchemaUId, TypeColumnValue, UseModuleDetails, CreatedOn) output inserted.Id into @temp select e.ActionKindCaption, e.ActionKindName, e.CardSchemaUId, e.HelpContextId, e.MiniPageSchemaUId, e.PageCaption, e.SearchRowSchemaUId, p.Id, e.SysPageSchemaUId, e.TypeColumnValue, e.UseModuleDetails, GETUTCDATE() CreatedOn from SysModuleEdit e inner join @PortalSysModuleEntity p on p.BaseRecordId = e.SysModuleEntityId where e.SysModuleEntityId in ( select Id from SysModuleEntity e where e.SysEntitySchemaUId in ( select [uid] from SysSchema sh inner join @SchemaName n on n.Name = sh.Name where ExtendParent = 0 )) select Id as SysModuleEdit from @temp; delete from @temp; insert into SysModuleEntityInPortal(SysPortalId, SysModuleEntityId, CreatedOn) output inserted.Id into @temp select @PortalId, p.Id, GETUTCDATE() from @PortalSysModuleEntity p select Id as SysModuleEntityInPortal from @temp; delete from @temp; commit transaction end try begin catch select ERROR_NUMBER() as ErrorNumber ,ERROR_MESSAGE() as ErrorMessage; if @@TRANCOUNT > 0 rollback transaction; end catch; if @@TRANCOUNT > 0 rollback transaction;
2. On dev environment execute a stored procedure to register sections in the portal.
Stored procedure [int_RegisterSectionInPortal] takes as an argument comma separated list of section table names.
Example
exec dbo.[int_RegisterSectionInPortal] 'lcBwJob,lcBwLine,lcBwVeh,lcBwCc,lcBwDevice'
3. Add sections to the portal workplace (see figure 0)
4. Bind new data to a package with a filter by ids from output(see figure 1-2) or by CreatedOn = Today (see figure 3) for the following system tables:
- SysModuleEntity
- SysModule
- SysModuleEdit
- SysModuleEntityInPortal
Figure 0. Add sections to portal workplace
Figure 1. Retrieve new records ids from the output
Figure 2. Bind data by ids from output
Figure 3. Bind data by date