Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Bronze Super Contributor
Posts: 349
Registered: ‎03-24-2009

Re: SQL Views as entities in 7.5p2

Sorry the suggestion did not work.

Sage, both column and table aliasing is important as we use SQL Views for aggregates, complex keys, duplicate column names from different tables, duplicate column names from the same table as in a self join (Account.AccountId = Account.ParentId), dynamic columns (meaning there is no underlying database field), etc. - all of these examples require aliases.
Timmus Agersea
Black Moth CRM
Loyal Listener
Posts: 17
Registered: ‎04-15-2009

Re: SQL Views as entities in 7.5p2

As a clarification alias works on all fields other than the one you want to make as the primary key.

 

Ray

Ray A. Walther
TimeLinx Software, LLC

281-257-0950 (Direct)
Ray.Walther (Skype)
Bronze Super Contributor
Posts: 349
Registered: ‎03-24-2009

Re: SQL Views as entities in 7.5p2

Sorry for the tangent. Glad to hear the issue is specific so hopefully easier to fix.
Timmus Agersea
Black Moth CRM
Employee
Posts: 79
Registered: ‎06-25-2009

Re: SQL Views as entities in 7.5p2

Ray, 

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.

 

Paul Zeimet

Development Manager

Sage SalesLogix

Paul Zeimet
Development Manager
Infor CRM
Loyal Listener
Posts: 17
Registered: ‎04-15-2009

Re: SQL Views as entities in 7.5p2

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. 

 

Ray

 

 

Ray A. Walther
TimeLinx Software, LLC

281-257-0950 (Direct)
Ray.Walther (Skype)
Employee
Posts: 79
Registered: ‎06-25-2009

Re: SQL Views as entities in 7.5p2

Thanks Ray,

 

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.

Paul Zeimet
Development Manager
Infor CRM
Bronze Super Contributor
Posts: 153
Registered: ‎04-08-2009

Re: SQL Views as entities in 7.5p2


@Jeffron wrote:

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:

 

  1. Create the view on a SLX 7.5.1 DB, and the 7.5.2 DB.
  2. Log into SLX Administrator on 7.5.1 and enable the view. You'll get a warning that there's no primary key so you can't create a child table, but for this that doesn't matter.
  3. In SQL Management Studio, select the resynctabledefs record for the View.
  4. Write an insert statement to insert the values.  Add your key field to the KEYFIELD field.  Run it on the SLX 7.5.2 DB. This enables the view.

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.

 

Employee
Posts: 79
Registered: ‎06-25-2009

Re: SQL Views as entities in 7.5p2

Ray,

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.

 

 

Paul Zeimet
Development Manager
Infor CRM
Loyal Listener
Posts: 17
Registered: ‎04-15-2009

Re: SQL Views as entities in 7.5p2

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.

 

Thanks Again.

Ray

Ray A. Walther
TimeLinx Software, LLC

281-257-0950 (Direct)
Ray.Walther (Skype)
Bronze Super Contributor
Posts: 349
Registered: ‎03-24-2009

Re: SQL Views as entities in 7.5p2

Thanks for the quick turn around Paul!
Timmus Agersea
Black Moth CRM