Login / Register  search  syndication  about

          George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

Sync Views out To Remotes

The scenario is you have created a SQL view for use in a Crystal Report.  Your end users are excited about the report but then you started getting phone calls from your remote users that the report will not run.  You realize that the view you used to help organize your data did not synchronize out to your remote user base.  After some looking around on CustomerFX.com you realize that views do not Synchronize out Remote databases.  Your options are Cut New Remote databases or some how get the views to sync out.  Then you discovered Kris Halsrud's blog on this exact topic written on May 13th called Distributing SQL Views to Remote SalesLogix Databases

Kris's post provided the answer to what I was looking for in preparation for the SalesLogix Reporting class I am preparing.  This issue is something I have been trying to overcome throughout my SalesLogix reporting career.  Since my goal is to write to the "Beginner" level of SalesLogix reporting I thought I would break down the process that Kris describes for the new Administrator or Report Writer.

The concept of what Kris writes about is that since changes to a detail view in SalesLogix synchronize out to remote databases, we can write a script that runs whenever a detail view opens.  This script basically checks to make sure the view exists.  If the view does not, the script creates the view.  Once the change is saved and released, the script will synchronize out to the remotes and your report will run.  

In order to accomplish what I am writing about today you must have access to the Sage SalesLogix Architect and the tables that support the view must be available in the remote database.

Our target detail view is the SLX Crystal Report Viewer.  This is the screen that comes up when you select the report button on your nav bar.

You can open this view in the Architect using the File>Open Plugin menu item.

When the "Choose Plugin" screen opens, click on the System listing in the tree control on the left side of the view, and look for and select the the "SLX Crystal Report Viewer" from the list on the right.

 

When the form opens in the Architect look for and select the Script Tab. 

There is some form information in gray text on the top of the script.  Just below you see the word "Globals".  Insert the following text under that word.

Const SLXPW = "your_sysdba_password"

The phrase in the script, "your_sysdba_password",  will need to be changed to match your SQL server sysdba password.

Scroll down the script and search for the sub "AxFormOpen".

Directly under the "AXFormOpen" line enter the following text:

FXCheckViews
application.BasicFunctions.ProcessWindowMessages

Any existing text will come after these two lines. If more then one view is required to be created then you could use different versions of the FXCheckViews text.  It could look something like this:

 FXCheckViews
 FXCheckViews2
 application.BasicFunctions.ProcessWindowMessages

 Now scroll all the way to the bottom of the script and insert the following code:

' CFX Begin Script
Sub FXCheckViews
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'NAMEOFVIEW' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view NAMEOFVIEW as SELECTSTATEMENT")
    Set objrs = nothing
End Sub

 

Function GetNewRecordset()
    Set GetNewRecordset = CreateObject("ADODB.Recordset")
    Set GetNewRecordset.ActiveConnection = GetNativeConnection()
    GetNewRecordset.CursorLocation = 3 'adUseClient
    GetNewRecordset.CursorType = 3 'adOpenStatic
    GetNewRecordset.LockType = 3 'adLockOptimistic
End Function

 

Function ExecuteNativeSQL(ByVal SQL)
Dim objConn
Dim lRecs
    On Error Resume Next
    lRecs = -1
    Set objConn = GetNativeConnection
    objConn.Execute SQL, lRecs, 128 'adExecuteNoRecords
    If Err.Number <> 0 Then RaiseError "executing SQL statement"
    On Error Goto 0
    Set objConn = Nothing
    ExecuteNativeSQL = lRecs
End Function

 

Function GetNativeConnection()
Dim objRS
Dim connstring
Dim cn
    Set objRS = Application.GetNewConnection.Execute("slx_getNativeConnInfo()")
    connstring = objRS.Fields(0).Value & ";Password=" & SLXPW
    objRS.Close
    Set objRS = Nothing
    Set cn = CreateObject("ADODB.Connection")
        With cn
            .ConnectionString = connstring
            .Open
        End With
    If Err.Number <> 0 Then RaiseError "opening ADO connection object"
    On Error Goto 0
    Set GetNativeConnection = cn
    Set cn = nothing
End Function

The first sub in the script you just inserted needs some adjustment. 

Sub FXCheckViews
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'NAMEOFVIEW' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view NAMEOFVIEW as SELECTSTATEMENT")
    Set objrs = nothing
End Sub

The text 'NAMEOFVIEW' appears twice in the sub routine and should be replaced with name of the view as it appears in the SQL database.

The text SELECTSTATEMENT should be replaced with the actual SQL statement used in the view.  This needs to be formatted to display on one line.  I formated this by copying the SQL Statement into a text document and deleted out all of the carriage returns.   Then I pasted the formatted text into the script making sure to just replace the SELECTSTATEMENT  text.  Here is an example:

Sub FXCheckViews
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'vAccountDescription' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view vAccountDescription as SELECT a.ACCOUNTID, a.DESCRIPTION from ACCOUNT")
    Set objrs = nothing
End Sub

Notice how the word SELECTSTATEMENT is gone, the SQL Statement is on one line only.

If you have more then one view being created you want to create a sub routine for each.  So in the example of above where there is the FXCheckViews and FXCheckViews2, we want the sub FXCheckViews and another sub for FXCheckViews2.  Together these would look something like this.  

Sub FXCheckViews
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'vAccountDescription' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view vAccountDescription as SELECT ACCOUNTID, DESCRIPTION from ACCOUNT")
    Set objrs = nothing
End Sub

Sub FXCheckViews2
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'vContactType' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view vContactType as SELECT ContactID, Type from Contact")
    Set objrs = nothing
End Sub

Any good SQL script will work.  This really opens up you ability to use SQL to organize your data for use in SalesLogix reporting or Group building. 

 

What's This?
  
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.


Related Content
   Preserving Custom Roles, Secured Actions, and Assignments in Infor CRM (formerly Saleslogix) Upgrades
When upgrading between levels of Infor CRM one of the tasks that is commonly needed is to run a batch/exe
Posted on Dec 16, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Easily Showing and Hiding Tabs at Runtime in Infor CRM (Saleslogix)
Showing and hiding tabs in the Infor CRM (Saleslogix) client should be an easy thing. It should be someth
Posted on Dec 16, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Checking Infor CRM (Saleslogix) Picklists for valid data
This week I received a question about a particular picklist. One of the picklist items, when chosen, wou
Posted on Dec 05, 2014 by Dale Richter to Infor CRM Questions & Answers
 
   InforCRM (formerly SalesLogix) Web Issues with McAfee VSE 8.8 Patch 4
We have see an issue with SalesLogix v8.0 and v8.1 web clients where randomly all users are bounced due t
Posted on Dec 05, 2014 by Mark Duthoy to SalesLogix Support
 
   Avoiding the Dirty Data Message When Programatically Redirecting to a Record in Infor CRM (Saleslogix)
In my last post I wrote about using the OnClientClick property of a control (or button) to run JavaScript
Posted on Dec 02, 2014 by Ryan Farley to Ryan Farley's Blog
 
Comments

 

Twitter Trackbacks for Sync Views out To Remotes - The Reporting Blog [customerfx.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Sync Views out To Remotes - The Reporting Blog         [customerfx.com]        on Topsy.com

September 18, 2009 3:57 PM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register