01-28-2010 06:47 AM
01-28-2010 06:56 AM
As a clarification alias works on all fields other than the one you want to make as the primary key.
01-28-2010 08:28 AM
The issue is that when the key is aliased, it does not match the value we have in our metadata for the key name of the TLX_SERVICETASK table. What I would need to do is determine the key name for the base table from our metadata, then parse that column name from the view description and determine if it has an alias. If it does then the primary key would need to be set to this alias not the actual column name in the table.
I will see about getting this added to the hot fix.
Sorry, I do not have a timeframe I can give you for this. It depends largely on the number of hot fixes needing to be built and tested and the priority of them.
I will see about having someone get in contact with you about the time frame.
01-28-2010 08:43 AM
Thanks for the info. The REAL ISSUE is that not all SQL views have a Primary Key.
With this new feature/requirement we would be forced to eliminate any SQL views in which a primary key is not available. This is a significant issue and would require a major programming effort to accommodate. I am glad we are enabling SQL Views to be part of the entity model (I have requested that feature last year) but not everything needs that functionality and this new requirement breaks code that has been working since SQL views were enabled.
01-28-2010 09:01 AM
I will discuss removing the restriction on the view containing a primary key, and instead just giving a warning that the view should not be made into an entity.
I will have to get back to you on this.
01-28-2010 10:01 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.
If you have access to a SLX 7.5.1 DB, might I suggest a simpler and possibly safer solution, which I successfully tested this morning:
The customization works in 7.5.2 LAN. My view doesn't contain an embedded select, but it does contain a unique ID, as well as UNION to combine results on queries from two different tables (which seems to be problematic for the SLX Provider).
I agree it does need to work for views without unique IDs or with more complex queries.
02-22-2010 02:25 PM
Hot Issue 1-72882 was created for this issue in 7.5 SP2.
The resolution allows the user to select the Primary Key from the list of fields exposed by the View if the primary key can not be determined.
The user can opt not too select a primary key at all, the view will still be enabled but it can not be used Application Architect as an Entity.
02-22-2010 04:10 PM
Great that should meet our needs. I am assuming we will be able to do the sub selects mentioned earlier in the thread and the uppercase requirment for SQL views will also be addressed in this Hot-Issue.
Thanks for the feedback and hopefully it will come out soon. We have several clients that want to move to SP2 but can't until we have the views working.