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
