Question

Dashboard / Object Schema for SQL View

16:11 May 25, 2022

I am attempting to create a list dashboard for an SQL view. When adding columns to the dashboard list a blank dialog appears with no content. Details as follows, using a Creatio CRM trial. The Object schema Parent object I have set to 'BaseEntity' which from what I can gather is the Base object. Console errors appear as soon as the dashboard list object is selected.

DROP VIEW IF EXISTS "UsrAdvisorHourlyActivityView";
 
CREATE VIEW "UsrAdvisorHourlyActivityView" AS
SELECT "ContactId" "UsrContactId", 
       DATE("StartDate") "UsrHandledOn",
       EXTRACT(hour from "StartDate") "UsrHour",
       ac."Name" "UsrType",
       COUNT(ac."Name") "UsrVolume"
FROM "Activity" a
JOIN "ActivityCategory" ac ON a."ActivityCategoryId" = ac."Id"
GROUP BY "UsrContactId", "UsrHandledOn", "UsrHour", "UsrType";

core-base.js:711 user: oslergs+20220426@gmail.com/268b5da7-2ede-4b32-8bbe-9af04721ebba
 file: https://120304-crm-bundle.creatio.com/0/core/hash/ng-core/src/polyfills-es5.js?hash=d68f5581113c4a18ba28d150078addf6
 line: 1
 column: 16730
 message: Uncaught Error: Script error for "UsrAdvisorHourlyActivityView"
http://requirejs.org/docs/errors.html#scripterror 
 date: Wed May 25 2022 13:49:37 GMT+0100 (British Summer Time)
 stack: Error: Script error for "UsrAdvisorHourlyActivityView"

performanceLogger.js:350          POST https://120304-crm-bundle.creatio.com/0/DataService/json/SyncReply/SelectQuery 500 (Internal Server Error)
...
baseobject.js?hash=d68f5581113c4a18ba28d150078addf6:120 Error while sending request 
	response status: 500 (Internal Server Error)
	request url: ../DataService/json/SyncReply/SelectQuery
	method: POST
	request data: {"rootSchemaName":"","operationType":0,"includeProcessExecutionData":true,"filters":{"items":{"113b5012-338e-4779-ba2b-16e90eec318d":{"items":{},"logi...

polyfills-es5.js?hash=d68f5581113c4a18ba28d150078addf6:1 Uncaught constructor {errorCode: 'ArgumentNullOrEmptyException', message: 'Value for argument "name" must be specified.', stackTrace: undefined, errors: Array(0)}
...
core-base.js:711 user: oslergs+20220426@gmail.com/268b5da7-2ede-4b32-8bbe-9af04721ebba
 file: https://120304-crm-bundle.creatio.com/0/core/hash/ng-core/src/polyfills-es5.js?hash=d68f5581113c4a18ba28d150078addf6
 line: 1
 column: 16730
 message: Uncaught Value for argument "name" must be specified. 
 date: Wed May 25 2022 13:49:37 GMT+0100 (British Summer Time)
 stack: undefined

2 comments

Oleg Drobina

17:54 May 25, 2022

Hello Gareth,

Don't forget that when creating a view you also need to create an object in configurations that represents this view. And when creating an object you select BaseEntity as a parent and there are 6 columns added when selecting this object: Id, CreatedOn, CreatedById, ModifiedOn, ModifiedById and ProcessListeners. These columns should be also included to the CREATE VIEW statement and the dashboard is not working because 500 error is returned upon the SELECT query sent to this view and it's returned because of these missing columns. Adding them should fix the issue.

Best regards,

Oscar

Gareth Osler

19:13 May 26, 2022

Oscar Dylan,

The error was actually a combination of this and I also neglected to publish the object schema (I missed the console error saying failed to load as well).

From what I can gather through trial and error all that seems to be needed of the inherited columns is the row 'Id', I wouldn't like to leave the other fields without a value though.

Thanks for your time.

Show all comments