A great feature, introduced in SalesLogix 7.0 is the ability to embed SQL views in SalesLogix, just as if they were a standard SalesLogix table.
SQL views, can be a tremendously flexible solution to roll-up/aggregate data from various data sources into a consolidated source. Obviously. use of the SQL views is predicated on the ability for users to access the data sources where the view originates. For this reason, utilizing SQL views is not a viable solution for remote users (Unless you utilize web access or an authenticate network connection with access to the data sources.
To enable SQL views in SalesLogix you need to do the following:
- Create a SQL view in the SalesLogix database owned by SYSDBA. This view can access another SQL view on the same server or other servers, via SQL Linked Server functionality.
- Once you have created the view in the SalesLogix database, log in to either the Architect or Admin and bring up the Database Manager. You should now see you view listed in the list of tables. The icon next to the view will be different than the other icons, as you can see in the screen shot below where I have a view called “Mynewview”
- If you right click on the view and choose “Properties” you will see the Table Properties dialog appear:
- On this screen, click the Enable button. You will receive a warning message. Click OK to proceed
Once you click OK and get back to the main database manager screen, you will see the icon next to the view has now changed. This means that the view is now “enabled” and ready to be used in SalesLogix just like any other table.
What the above procedure does is to create entries in the SECTABLEDEFS and RESYNCTABLEDEFS for the view name and all of the fields in the view.
A couple of things to note:
- There is no way to “disable” a view. To disable the view what you need to do is to delete the entries from the SECTABLEDEFS and RESYNCTABLEDEFS tables where the TABLENAME = [You SQL view’s name]
- Likewise, there is no way to update an enabled view. You can modify the underlying SQL view, as long as you do not add, remove, or change column names. As long as you are not editing the output columns, modifications to the view are immediately reflected when next viewed in SalesLogix
- Even though enabling a SQL view writes entries into the SECTABLEDEFS, you can not set the DATETIMETYPE field in the SECTABLEDEFS to have the SalesLogix OLEDB provider not apply UTC conversion to any date fields. SalesLogix will always apply UTC offset to dates from views, so your SQL views should take that into account so dates are returned and displayed correctly.
- If your view contains a field SECCODEID, the provider will apply security to the data in the view based on the SECCODEID. If that field is empty or contains an unexpected SECCODEID the data will not be visible.
- In order to use the data in a grid with an edit view, you must manually update the KEYFIELD field in the RESYNCTABLEDEFS and use this field as the key field between the grid and edit views.