How can I create a filter for the lookup "Group" inside "CasePage" using my custom field?

Hi Pedro,

If you need to create a filter of the Group lookup on the case page then why do you replace the SysAdminUnitPageV2? And which case and account do you expect to see on the SysAdminUnitPageV2 for some role (for example for "System administrators")? Also I cannot see any filtration logic on the SysAdminUnitPageV2 module code you've shared.

You need to create a filter on the CasePage using an example provided here.

Best regards,

Oscar

Hi Oscar,

Thank you for the response.

My question consists of two parts:

1) I've created a replacing page of the "SysAdminUnitPageV2", so I could add my custom boolean field "Case" and the pre-built lookup field "Account". Since the "Account" field is not custom, I only added the field on my "SysAdminUnitPageV2" page through the “diff” array (image below). Every time I change and save my "Account" value, it will automatically update the value in the database for my specific record. However, the same does not happen to my custom field "Case".

I’ve created the “Case” field the same way "Account" field was created, but I think I'm missing some page logic that is responsible to store my “Case” values in the database.

2) The lookup filter that I want to add is for the "Group" lookup field inside the "CasePage". This filter should use the "Case" field, previously added to the "SysAdminUnit" object. With the example you provided I was able to complete this second part.

Best Regards,

Pedro Pinheiro

Pedro Pinheiro,

In the Fig.4 modify the "bindTo": "Case" to "bindTo": "UsrCase", save the schema and refresh the "role" page after that. Try saving some value and let me know about the result.

Best regards,

Oscar

Oscar Dylan,

I've made the changes, but my page cannot find the UsrCase column. I think it's because this page does not have the "entitySchemaName" attribute. But even if I try to add the "entitySchemaName" attribute the page will throw an exception.

Error Translation: "It was not possible to find the column through the following configuration."

Best Regards,

Pedro Pinheiro

Pedro Pinheiro,

To achieve your first result you need to:

1) Create a replacing object selecting the VwSysAdminUnit object as a parent

2) Create the lookup column with the "UsrCase" code and "Case" title inside the object from step 1.

3) Save and publish the object from step 1.

As a result here you should get this result:

4) Execute the following query in the database (please note that if you already have customizations in the VwSysAdminUnit table (new columns inside it) then the query that should be executed in your case can differ (all the columns of the already existing replacement should be mentioned in the query, study it before running)):

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VwSysAdminUnit]'))
DROP VIEW [dbo].[VwSysAdminUnit]
GO
CREATE VIEW [dbo].[VwSysAdminUnit]
AS
SELECT [SysAdminUnit].[Id]
	,[SysAdminUnit].[CreatedOn]
	,[SysAdminUnit].[CreatedById]
	,[SysAdminUnit].[ModifiedOn]
	,[SysAdminUnit].[ModifiedById]
	,[SysAdminUnit].[Name]
	,[SysAdminUnit].[Email]
	,[SysAdminUnit].[Description]
	,[SysAdminUnit].[ParentRoleId]
	,[SysAdminUnit].[ContactId]
	,[SysAdminUnit].[IsDirectoryEntry]
	,[TimeZone].[Id] AS [TimeZoneId]
	,[SysAdminUnit].[UserPassword]
	,[SysAdminUnitType].[Id] AS [SysAdminUnitTypeId]
	,[SysAdminUnit].[AccountId]
	,[SysAdminUnit].[Active]
	,[SysAdminUnit].[LoggedIn]
	,[SysAdminUnit].[SynchronizeWithLDAP]
	,[LDAPElement].[Name] as [LDAPEntry]
	,[LDAPElement].[LDAPEntryId]
	,[LDAPElement].[LDAPEntryDN]
	,[SysAdminUnit].[SysCultureId]
	,[SysAdminUnit].[ProcessListeners]
	,[SysAdminUnit].[PasswordExpireDate]
	,[SysAdminUnit].[HomePageId]
	,[SysAdminUnit].[ConnectionType]
	,[ConnectionType].[Id] AS [UserConnectionTypeId]
	,[SysAdminUnit].[ForceChangePassword]
	,[SysAdminUnit].[LDAPElementId]
	,[SysAdminUnit].[DateTimeFormatId]
	,[SysAdminUnit].[Id] as [SysAdminUnitId]
	,[SysAdminUnit].[SessionTimeout] as [SessionTimeout]
	,[SysAdminUnit].[UsrCaseId] as [UsrCaseId]
FROM [SysAdminUnit]
INNER JOIN [SysAdminUnitType] ON [SysAdminUnitType].[Value] = [SysAdminUnit].[SysAdminUnitTypeValue]
LEFT JOIN [ConnectionType] AS [ConnectionType] ON [ConnectionType].[Value] = [SysAdminUnit].[ConnectionType]
LEFT JOIN [TimeZone] AS [TimeZone] ON [TimeZone].[Code] = [SysAdminUnit].[TimeZoneId]
LEFT JOIN [LDAPElement] ON [LDAPElement].[Id] = [SysAdminUnit].[LDAPElementId]
GO
CREATE TRIGGER [dbo].[ITR_VwSysAdminUnit_I]
ON [dbo].[VwSysAdminUnit]
	INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [SysAdminUnit](
	[Id]
	,[CreatedOn]
	,[CreatedById]
	,[ModifiedOn]
	,[ModifiedById]
	,[Name]
	,[Email]
	,[Description]
	,[ParentRoleId]
	,[ContactId]
	,[IsDirectoryEntry]
	,[TimeZoneId]
	,[UserPassword]
	,[SysAdminUnitTypeValue]
	,[AccountId]
	,[Active]
	,[LoggedIn]
	,[SynchronizeWithLDAP]
	,[LDAPEntry]
	,[LDAPEntryId]
	,[LDAPEntryDN]
	,[SysCultureId]
	,[ProcessListeners]
	,[PasswordExpireDate]
	,[HomePageId]
	,[ConnectionType]
	,[ForceChangePassword]
	,[LDAPElementId]
	,[DateTimeFormatId]
	,[SessionTimeout]
	,[UsrCaseId])
SELECT [Id]
	,[CreatedOn]
	,[CreatedById]
	,[ModifiedOn]
	,[ModifiedById]
	,[Name]
	,[Email]
	,[Description]
	,[ParentRoleId]
	,[ContactId]
	,[IsDirectoryEntry]
	,(SELECT COALESCE(
		(SELECT [TimeZone].[Code] FROM [TimeZone]
			WHERE [TimeZone].[Id] = [INSERTED].[TimeZoneId]), ''))
	,[UserPassword]
	,ISNULL((SELECT [SysAdminUnitType].[Value] FROM [SysAdminUnitType]
		WHERE [SysAdminUnitType].[Id] = [INSERTED].[SysAdminUnitTypeId]), 4)
	,[AccountId]
	,[Active]
	,ISNULL([LoggedIn], 0)
	,[SynchronizeWithLDAP]
	,(SELECT COALESCE(
		(SELECT [LDAPElement].[Name] FROM [LDAPElement]
			WHERE [LDAPElement].[Id] = [INSERTED].[LDAPElementId]), ''))
	,(SELECT COALESCE(
		(SELECT [LDAPElement].[LDAPEntryId] FROM [LDAPElement]
			WHERE [LDAPElement].[Id] = [INSERTED].[LDAPElementId]), ''))
	,(SELECT COALESCE(
		(SELECT [LDAPElement].[LDAPEntryDN] FROM [LDAPElement]
			WHERE [LDAPElement].[Id] = [INSERTED].[LDAPElementId]), ''))
	,[SysCultureId]
	,[ProcessListeners]
	,[PasswordExpireDate]
	,[HomePageId]
	,COALESCE([INSERTED].[ConnectionType],
		(SELECT [ConnectionType].[Value] FROM [ConnectionType]
		WHERE [ConnectionType].[Id] = [INSERTED].[UserConnectionTypeId]), 0)
	,ISNULL([ForceChangePassword], 0)
	,[LDAPElementId]
	,[DateTimeFormatId]
	,[SessionTimeout]
	,[UsrCaseId]
FROM [INSERTED]
END
GO
CREATE TRIGGER [dbo].[ITR_VwSysAdminUnit_U]
ON [dbo].[VwSysAdminUnit]
	INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE [SysAdminUnit]
SET [SysAdminUnit].[CreatedOn] = [INSERTED].[CreatedOn]
	,[SysAdminUnit].[CreatedById] = [INSERTED].[CreatedById]
	,[SysAdminUnit].[ModifiedOn] =[INSERTED].[ModifiedOn]
	,[SysAdminUnit].[ModifiedById] = [INSERTED].[ModifiedById]
	,[SysAdminUnit].[Name] = [INSERTED].[Name]
	,[SysAdminUnit].[Email] = [INSERTED].[Email]
	,[SysAdminUnit].[Description] = [INSERTED].[Description]
	,[SysAdminUnit].[ParentRoleId] = [INSERTED].[ParentRoleId]
	,[SysAdminUnit].[ContactId] = [INSERTED].[ContactId]
	,[SysAdminUnit].[IsDirectoryEntry] = [INSERTED].[IsDirectoryEntry]
	,[SysAdminUnit].[TimeZoneId] =
		(SELECT COALESCE(
			(SELECT [TimeZone].[Code] FROM [TimeZone]
				WHERE [TimeZone].[Id] = [INSERTED].[TimeZoneId]), ''))
	,[SysAdminUnit].[UserPassword] = [INSERTED].[UserPassword]
	,[SysAdminUnit].[SysAdminUnitTypeValue] =
		(SELECT [SysAdminUnitType].[Value] FROM [SysAdminUnitType]
			WHERE [SysAdminUnitType].[Id] = [INSERTED].[SysAdminUnitTypeId])
	,[SysAdminUnit].[AccountId] = [INSERTED].[AccountId]
	,[SysAdminUnit].[Active] = [INSERTED].[Active]
	,[SysAdminUnit].[LoggedIn] = [INSERTED].[LoggedIn]
	,[SysAdminUnit].[SynchronizeWithLDAP] = [INSERTED].[SynchronizeWithLDAP]
	,[SysAdminUnit].[LDAPEntry] =
		CASE
			WHEN (UPDATE(SynchronizeWithLDAP) AND [INSERTED].[SynchronizeWithLDAP] = 1 AND [INSERTED].[LDAPElementId] is not null)
					OR (UPDATE(LDAPElementId) AND [INSERTED].[LDAPElementId] is not null)
				THEN (SELECT [LDAPElement].[Name] FROM [LDAPElement] WHERE [LDAPElement].[Id] = [INSERTED].[LDAPElementId])
			WHEN UPDATE(SynchronizeWithLDAP) AND [INSERTED].[SynchronizeWithLDAP] = 0
				THEN ''
			ELSE [SysAdminUnit].[LDAPEntry]
		END
	,[SysAdminUnit].[LDAPEntryId] = 
		CASE
			WHEN (UPDATE(SynchronizeWithLDAP) AND [INSERTED].[SynchronizeWithLDAP] = 1 AND [INSERTED].[LDAPElementId] is not null)
					OR (UPDATE(LDAPElementId) AND [INSERTED].[LDAPElementId] is not null)
				THEN (SELECT [LDAPElement].[LDAPEntryId] FROM [LDAPElement] WHERE [LDAPElement].[Id] = [INSERTED].[LDAPElementId])
			WHEN UPDATE(SynchronizeWithLDAP) AND [INSERTED].[SynchronizeWithLDAP] = 0
				THEN ''
			ELSE [SysAdminUnit].[LDAPEntryId]
		END
	,[SysAdminUnit].[LDAPEntryDN] = 
		CASE
			WHEN (UPDATE(SynchronizeWithLDAP) AND [INSERTED].[SynchronizeWithLDAP] = 1 AND [INSERTED].[LDAPElementId] is not null)
					OR (UPDATE(LDAPElementId) AND [INSERTED].[LDAPElementId] is not null)
				THEN (SELECT [LDAPElement].[LDAPEntryDN] FROM [LDAPElement] WHERE [LDAPElement].[Id] = [INSERTED].[LDAPElementId])
			WHEN UPDATE(SynchronizeWithLDAP) AND [INSERTED].[SynchronizeWithLDAP] = 0
				THEN ''
			ELSE [SysAdminUnit].[LDAPEntryDN]
		END
	,[SysAdminUnit].[SysCultureId] = [INSERTED].[SysCultureId]
	,[SysAdminUnit].[ProcessListeners] = [INSERTED].[ProcessListeners]
	,[SysAdminUnit].[PasswordExpireDate] = [INSERTED].[PasswordExpireDate]
	,[SysAdminUnit].[HomePageId] = [INSERTED].[HomePageId]
	,[SysAdminUnit].[ConnectionType] = COALESCE([INSERTED].[ConnectionType],
		(SELECT [ConnectionType].[Value] FROM [ConnectionType]
		WHERE [ConnectionType].[Id] = [INSERTED].[UserConnectionTypeId]), 0)
	,[SysAdminUnit].[ForceChangePassword] = [INSERTED].[ForceChangePassword]
	,[SysAdminUnit].[LDAPElementId] = [INSERTED].[LDAPElementId]
	,[SysAdminUnit].[DateTimeFormatId] = [INSERTED].[DateTimeFormatId]
	,[SysAdminUnit].[SessionTimeout] = [INSERTED].[SessionTimeout]
	,[SysAdminUnit].[UsrCaseId] = [INSERTED].[UsrCaseId]
FROM [SysAdminUnit]
INNER JOIN [INSERTED] ON [SysAdminUnit].[Id] = [INSERTED].[Id]
END
GO
CREATE TRIGGER [dbo].[ITR_VwSysAdminUnit_D]
ON [dbo].[VwSysAdminUnit]
	INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM [SysAdminUnit]
WHERE EXISTS(SELECT * FROM [DELETED] WHERE [SysAdminUnit].[Id] = [DELETED].[Id])
END
GO

This query is for MS SQL, if your application is hosted using the PostgreSQL database then your query should be written using PostgreSQL syntax (you can copy the OOB VwSysAdminUnitPostgreSql SQL-script from the LDAP package, add your custom columns inside the ready script and execute it after that).

5) In the replaced SysAdminUnitPageV2 module add the following code to the diff array of the module:

{
					"operation": "insert",
					"parentName": "Header",
					"propertyName": "items",
					"name": "UsrCase",
					"values": {
						"bindTo": "UsrCase",
						"dataValueType": this.Terrasoft.DataValueType.LOOKUP,
						"layout": {
							"column": 0,
							"row": 1,
							"colSpan": 24
						}
					},
				}

6) Add the entitySchemaName: "VwSysAdminUnit", part to the replaced SysAdminUnitPageV2 module (below you can find an example of the correctly replaced module):

define("SysAdminUnitPageV2", [],
	function() {
		return {
			entitySchemaName: "VwSysAdminUnit",
			details: /**SCHEMA_DETAILS*/{}/**SCHEMA_DETAILS*/,
			diff: /**SCHEMA_DIFF*/[
				{
					"operation": "insert",
					"parentName": "Header",
					"propertyName": "items",
					"name": "UsrCase",
					"values": {
						"bindTo": "UsrCase",
						"dataValueType": this.Terrasoft.DataValueType.LOOKUP,
						"layout": {
							"column": 0,
							"row": 1,
							"colSpan": 24
						}
					},
				}
			]/**SCHEMA_DIFF*/,
			attributes: {},
			methods: {}
		};
});

7) Save the module and refresh the page

As a result you will get a working lookup that references the "Case" object:

and

PS: also please share code itself, but not its screenshots, sometimes copying some ready code can save the time:)

Best regards,

Oscar