Article

The newest version of bpm’online 7.13 introduces over 200 innovations that accelerate business transformation and growth, as well as over 50 marketplace apps to extend the functionality of bpm’online products.

Register today!

Share

0 comments
Log in or register to comment
Article

Bpm'online is proud to announce that it has been recognized as a Leader in the Midmarket CRM Suite and Sales Force Automation categories, as well as named the One to Watch in the Enterprise CRM Suite in the CRM Market Awards 2018 by CRM Magazine, one of the most highly regarded media in the CRM industry.

Read the full press release>>

Share

0 comments
Log in or register to comment
Article

We believe that this recognition is driven by significant development of our sales offering empowered with intelligent tools and technologies designed to accelerate sales operations and drive revenue growth.

Share the joy of this moment with us - get your complimentary copy of the report today!

Download the report >> 

Share

0 comments
Log in or register to comment
Article

Question

Where do the SysPageSchemaUId and MiniPageSchemaUId fields refer to (the SysModuleEdit table)? Where does the SysEntitySchemaUId field from the SysModuleEntity table refer to?

Answer

The SysModuleEdit table:

The table describes the connection of configuration objects with their edit pages. 

For example, the [Activity] object can have one or several edit pages described in the table. Basically, the "Object"-"The page schema, displaying the object content" connection is set here.

Primary table columns:

SysModuleEntityId - link to the SysModuleEntity table, described below. Basically, the link to the "Activity" object schema is described in it. All the schemas are contained in the SysSchema table.

 

TypeColumnValue  - if the column is empty, it means that this object has a single edit page. If the TypeColumnValue field is populated, it is the identifier of type that differentiates between the edit pages. For example, the Activity edit pages can be differentiated by the ActivityType column and the Id of this specific table (ActivityType) is recorded in TypeColumnValue. The differentiation can be by any reference column of an object, but only by one at a time. It is usually the "Type" column.

CardSchemaUId - the link to the UId field of the SysSchema table, namely to the edit page schema. For example, the link to UId of the ActivityPageV2 schema, but the string must necessarily be as follows:

SELECT * FROM SysSchema WHERE Name = 'Activity' AND ExtendParent = 0

In other words, it should be the base, parent page schema. The same refers to all SysSchema-related links. They always refer to a parent schema (ExtendParent = 0), so that inheritance could work.

The rest of text columns are text views of pages and tasks.

 

SysPageSchemaUId  - the link to the old aspx pages (SysSchema), not used now.

 

MiniPageSchemaUId  - used for creating mini pages, read more at https://academy.bpmonline.com/documents/technic-sdk/7-12/creating-pop-s…

The SysModuleEntity table:

The connection table for SysModuleEdit, the final connection is as follows:

SysSchema (object schema) – SysModuleEntity – SysModuleEdit – SysSchema (page schema(s))

Primary table columns:

SysEntitySchemaUId - the link to the SysSchema table, namely to the object schema. For example, to the UId field of the following record:

SELECT * FROM SysSchema WHERE Name = 'Activity' AND ExtendParent = 0

TypeColumnUId - is populated for cases, when there are many edit pages for a single object. The UId of the column is specified here from the metadata of the differentiating object, ideologically it is tightly connected with the TypeColumnValue  column of the SysModuleEdit table.

Share

0 comments
Log in or register to comment
Question

Question

I need to perform calculations based on invoices in the [Forecast] section

Answer

1. Modify the ForecastBuilder schema. Replace the following code in the openForecastPage() method:

var valuePairs = [
 {
  name: "EntitySchemaUId",
  value: "ae46fb87-c02c-4ae8-ad31-a923cdd994cf"
 },
 {
  name: "EntitySchemaName",
  value: "Opportunity"
 }
];

for:

var valuePairs = [
 {
  name: "EntitySchemaUId",
  value: "bfb313dd-bb55-4e1b-8e42-3d346e0da7c5"
 },
 {
  name: "EntitySchemaName",
  value: "Invoice"
 }
];

2. Apply changes to the stored tsp_RecalculateForecastFact procedure.

Replace the opportunity table for the invoice table, change the stage and data conditions. The source code of the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[tsp_RecalculateForecastFact]
@ForecastId UNIQUEIDENTIFIER = NULL,
@CurrentUserContactId UNIQUEIDENTIFIER = NULL
AS
IF @ForecastId IS NULL
BEGIN
	RETURN;
END
DECLARE @PlanIndicatorId UNIQUEIDENTIFIER
DECLARE @FactIndicatorId UNIQUEIDENTIFIER
DECLARE @FactPercentIndicatorId UNIQUEIDENTIFIER
DECLARE @PotentialIndicatorId UNIQUEIDENTIFIER
DECLARE @CompletedId UNIQUEIDENTIFIER
DECLARE @ForecastItemId UNIQUEIDENTIFIER
DECLARE @DimensionId UNIQUEIDENTIFIER
DECLARE @DimensionValueId UNIQUEIDENTIFIER
DECLARE @PeriodId UNIQUEIDENTIFIER
DECLARE @StartDate DATE
DECLARE @DueDate DATE
DECLARE @MaxDueDate DATE
DECLARE @ColumnName NVARCHAR(100)
DECLARE @SQLText NVARCHAR(MAX)
DECLARE @PlanAmount DECIMAL(18,2)
DECLARE @FactAmount DECIMAL(18,2)
DECLARE @PotentialAmount DECIMAL(18,2)
DECLARE @FactPotentialAmountTable TABLE (PlanAmount DECIMAL(18,2), FactAmount DECIMAL(18,2), PotentialAmount DECIMAL(18,2))
SET @PlanIndicatorId = '{CBD311C7-6E1B-4324-BF21-192681349DDF}'
SET @FactIndicatorId = '{52CAE26F-84F6-42A0-AAEF-97790AF3B8D9}'
SET @FactPercentIndicatorId = '{E0D66FFB-A3E3-4DA9-BCB7-95D27033286E}'
SET @PotentialIndicatorId = '{A004FC7A-D63D-4E3C-9356-0AD77B2600F3}'
--SET @CompletedId = '{60D5310C-5BE6-DF11-971B-001D60E938C6}'
SET @CompletedId = '{698D39FD-52E6-DF11-971B-001D60E938C6}' --Paid PaymentStatusId from InvoicePaymentStatus
DELETE FROM ForecastItemValue 
WHERE ForecastIndicatorId IN (@FactIndicatorId, @FactPercentIndicatorId, @PotentialIndicatorId)
AND EXISTS (SELECT 1 
			FROM ForecastItem fi 
			WHERE ForecastItemValue.ForecastItemId = fi.Id
				AND fi.ForecastId = @ForecastId 
			) 
SET @MaxDueDate = (SELECT Convert(Date, MAX(StartDate), 104) FROM Invoice o WHERE o.PaymentStatusId = @CompletedId)
DECLARE Cur CURSOR STATIC LOCAL FOR
SELECT
fi.Id ForecastItemId,
d.Id DimensionId, 
fi.DimensionValueId DimensionValueId,
p.Id PeriodId,
p.StartDate StartDate,
p.DueDate DueDate,
d.[Path] + 'Id'
FROM ForecastItem fi
INNER JOIN Forecast f ON f.Id = fi.ForecastId
INNER JOIN ForecastDimension fd ON fd.Id = fi.ForecastDimensionId
INNER JOIN Dimension d ON d.Id = fd.DimensionId
INNER JOIN Period p ON p.PeriodTypeId = f.PeriodTypeId
WHERE f.Id = @ForecastId
AND ISNULL(d.[Path],'') <> ''
AND p.StartDate <= @MaxDueDate
 
SET NOCOUNT ON;
 
OPEN Cur
FETCH NEXT FROM Cur INTO @ForecastItemId, @DimensionId, @DimensionValueId, @PeriodId, @StartDate, @DueDate, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @DueDate = DATEADD(DAY, 1, @DueDate)
	DELETE FROM @FactPotentialAmountTable
	SET @SQLText = N' SELECT (SELECT SUM(ISNULL(fiv.[Value], 0)) FROM [ForecastItemValue] fiv WHERE fiv.[ForecastItemId] = @P5 AND fiv.[PeriodId] = @P6 AND fiv.[ForecastIndicatorId] = @P7 ) PlanAmount, (SELECT SUM(ISNULL(o.[Amount], 0)) FROM [Invoice] o WHERE o.[PaymentStatusId] = @P1 AND o.[StartDate] >= @P2 AND o.[StartDate] < @P3 AND o.' + @ColumnName + N' = @P4 ) FactAmount, (SELECT SUM(ISNULL(o.[Amount], 0)) FROM [Invoice] o INNER JOIN [InvoicePaymentStatus] os ON os.[Id] = o.[PaymentStatusId] WHERE os.[FinalStatus] = 0 AND o.[StartDate] >= @P2 AND o.[StartDate] < @P3 AND o.' + @ColumnName + N' = @P4 ) PotentialAmount'
	INSERT INTO @FactPotentialAmountTable exec sp_executesql @SQLText,
	N'@P1 UNIQUEIDENTIFIER, @P2 DATE, @P3 DATE, @P4 UNIQUEIDENTIFIER, @P5 UNIQUEIDENTIFIER, @P6 UNIQUEIDENTIFIER, @P7 UNIQUEIDENTIFIER',
	@P1 = @CompletedId, @P2 = @StartDate, @P3 = @DueDate, @P4 = @DimensionValueId, @P5 = @ForecastItemId,
	@P6 = @PeriodId, @P7 =  @PlanIndicatorId
	SELECT @PlanAmount = PlanAmount, @FactAmount = FactAmount, @PotentialAmount = PotentialAmount 
	FROM @FactPotentialAmountTable
	IF (@FactAmount <> 0)
	BEGIN
		INSERT INTO ForecastItemValue
		(
			Id,
			CreatedOn,
			CreatedById,
			ModifiedOn,
			ModifiedById,
			ProcessListeners,
			ForecastIndicatorId,
			[Value],
			PeriodId,
			ForecastItemId
		)
		VALUES
		(
			NEWID(),
			GETUTCDATE(),
			@CurrentUserContactId,
			GETUTCDATE(),
			@CurrentUserContactId,
			0,
			@FactIndicatorId,
			@FactAmount,
			@PeriodId,
			@ForecastItemId
		)
		IF (@PlanAmount <> 0)
		BEGIN
			INSERT INTO ForecastItemValue
			(
				Id,
				CreatedOn,
				CreatedById,
				ModifiedOn,
				ModifiedById,
				ProcessListeners,
				ForecastIndicatorId,
				[Value],
				PeriodId,
				ForecastItemId
			)
			VALUES
			(
				NEWID(),
				GETUTCDATE(),
				@CurrentUserContactId,
				GETUTCDATE(),
				@CurrentUserContactId,
				0,
				@FactPercentIndicatorId,
				CAST((@FactAmount * 100 / @PlanAmount) AS DECIMAL(18, 2)),
				@PeriodId,
				@ForecastItemId
			)
		END
	END
 
	IF (@PotentialAmount <> 0)
	BEGIN
		INSERT  INTO ForecastItemValue
		(
			Id,
			CreatedOn,
			CreatedById,
			ModifiedOn,
			ModifiedById,
			ProcessListeners,
			ForecastIndicatorId,
			[Value],
			PeriodId,
			ForecastItemId
		)
		VALUES
		(
			NEWID(),
			GETUTCDATE(),
			@CurrentUserContactId,
			GETUTCDATE(),
			@CurrentUserContactId,
			0,
			@PotentialIndicatorId,
			@PotentialAmount ,
			@PeriodId,
			@ForecastItemId
		)
	END
FETCH NEXT FROM Cur INTO @ForecastItemId, @DimensionId, @DimensionValueId, @PeriodId, @StartDate, @DueDate, @ColumnName
END
CLOSE Cur
DEALLOCATE Cur

 You can set the condition columns that create separate tabs in the following table:

SELECT * FROM Dimension

They have Account, ResponsibleDepartment, Owner and LeadType specified by default.

Like

0 comments
Log in or register to comment