SQL Views as entities in 7.5p2

[gmartinez]

In 75p2 when I try to enable a view in the DB Manager, I get a slx warning saying that the view does not contain a primary key, and that I must add one before I can enable the view. How do I achieve this?

 

I am looking to add this view to the entity model, so if there are also, extra steps to make at the entity level, please let me know or send me a doc on that.

[jperona]

In 7.5.2, the logic of adding a view was modified to support Entity Model import in Application Architect.  It requires the primary table of the view be already be imported into SLX and that it's primary key be set in RESYNCTABLEDEFS.  After talking with the developer I believe we need to review this for additional scenarions.

[Jeffron]

I am having the same issue.  To work around it, I created a table using the SalesLogix Database Manager with the same fields as my view.  I then bundled up the rows created in sectabledefs and resynctabledefs for the table (leaving out the create/modify fields that weren't in my view).  You do need to have a primary key field even if your view doesn't have one.  I did this by adding a field in my view creation and naming it <tablename>ID (this corresponds to the key field in resynctabledefs, if you have a primary key in your view, update the keyfield to the corresponding primary key in your view, also, remove the MODIFYDATE from the datestampfield in this table).  I then dropped the table in SalesLogix Database Manager.  Next I created the view in SQL Server Management Studio.  After applying the bundle that contains the records from sectabledefs and resynctable defs the view should be enabled.

Next in Application Architect, do a New Entity from existing table and select your view.  If you don't have a primary key defined in the resynctable defs, you wont be able to create an entity for the view, so make sure you define this as outlined above.  Follow the wizard and you should have a new entity in Application Architect for your view.

Using this method (I think this is simular to the way views were consumed in the LAN previous to version 7) worked for me.  I joined the view to the account entity and can view my view data in a grid.

[Jeffron]

I'm having issues with adding a custom code snippet propery to the custom entity I described above.  I don't know if it is related to the hack way I got this view to work.

I simply added a new code snippet propery to my custom entity.  All I am doing to test this is returning a string (result = "Hello".  I then added the custom property to a datagrid that was working fine before this point by displaying data from my view.  When I try to display the datagrid, I get a "Cannot resolve property <property name> for entity <entity name>."

I added the same custom property to the opportunity entity and it works fine.  Is this something with custom entities in general, or with the way I created it?  Anyone have this issue?

[RJLedger]

Bundling up ResyncTabledefs and/or SectableDefs is a very dangerous process/method. There are other fields in there (Fieldindex/Fieldoffset, etc) that are unique to every SalesLogix db. You also have to deal w/the issue of (all) Secprofile records as well (or at least the encoded blobs in them).

The only "safe" way of dealing w/SQL views is to:

   A - create them

   B - Enable them (this creates the unique ResyncTabledefs/Sectabledefs entries)

   C - Manually fix the "keyfield" for the view 

[gmartinez]

John,

For this simple view below, saleslogix administrator is not allowing me to Enable it. When I try to enable it, I get the Primary key not identified warning.

create view sysdba.testview (accountid, totalcount, totalassets)

as

select a.accountid as accountid,

sum(p.active_participants) 'totalcount',

sum(p.total_plan_assets) 'totalassets'

from

account a

left join plan p on (a.accountid = p.accountid)

where a.accountid = 'A0FXUA100001'

group by a.accountid

[RJLedger]

If you take a look for the "table entry" (SELECT * FROM sysdba.RESYNCTABLEDEFS WHERE TABLENAME = 'testview') record you will probably see that the KEYFIELD field is NULL... You have to Update the record and add in a legal value for a keyfield. If you have remotes, be sure and do this via EXECUTESQL in the Admin (or some other tool that connects to the db via the SalesLogix Provider.

[gmartinez]

I dont have an entry in RESYNCTABLEDEFS because I have not been able to Enable it in saleslogix. Enabling it, is what adds the Resynctabledef and sectabledefs entries. I have not been able to do so yet.

[RJLedger]

I have not tried to enable a SQL view in 7.5.2 (have done so in prev versions) so maybe it's possible it's broken?

[pzeimet · Accepted solution]

The issue with the following view being enabled in Admin was caused by Defect # 1-72600 (7.5 SP3).  The parsing logic for the view was expecting the view syntax in upper case.

There are two workarounds for this issue.

1) Drop the view and recreate it in all upper case.

2) Modify the view using SQL Server Management Studio or the like and save.  Then, remove the change and re-save.  This will update the view definition stored in the database to upper case.

The following criteria are required for enabling a view in Admin.  I will use the view below as an example:

1)  The main table for the view must be enabled in SalesLogix Admin. (In this case ACCOUNT).

2)  The Primary Key for that table must be included in the columns exposed by the view.  (ACCOUNT.ACCOUNTID)

3)  Until the defect above is resolved, the view definition must be in upper case.

CREATE VIEW SYSDBA.TESTVIEW (ACCOUNTID, TOTALCOUNT, TOTALASSETS)

AS

SELECT

A.ACCOUNTID,

SUM(P.ACTIVE_PARTICIPANTS) AS TOTALCOUNT,

SUM(P.TOTAL_PLAN_ASSETS) AS TOTALASSETS

FROM SYSDBA.ACCOUNT AS A

LEFT OUTER JOIN SYSDBA.[PLAN] AS P ON A.ACCOUNTID = P.ACCOUNTID

WHERE (A.ACCOUNTID = 'A0FXUA100001')

GROUP BY A.ACCOUNTID

Paul Zeimet

Development Manager

Sage SalesLogix