12-09-2009 10:47 AM
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.
Solved! Go to Solution.
12-22-2009 07:44 AM
John Perona
Director, Infor CRM DevelopmentInfor CRM
12-22-2009 09:17 AM
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.
12-22-2009 09:24 AM
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?
12-22-2009 09:26 AM
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
12-30-2009 08:33 AM
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'
fromaccount a
left join plan p on (a.accountid = p.accountid)
where a.accountid = 'A0FXUA100001'
group by a.accountid
12-30-2009 10:12 AM
12-30-2009 10:20 AM
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.
12-30-2009 12:50 PM
01-11-2010 09:07 AM
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
SELECTA.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