Creating custom Quick Queries in Dynamics SL 2011

Prerequisites:

Give below are the 2 steps (a & b) to create the custom SQL view and integrate it with Dynamics SL database

a.  Create a custom SQL View

        (Whatever information we need from the database using SQL joins, tables etc.,)

b.  Update the Database Views through the SL Database Maintenance

     

        (Note: Start->Control Panel->Administrative Tools->Microsoft Dynamics SL Database Maintenance)

Steps to Create Custom Quick queries:

Step 1: Open SL Application

Step 2: Click Administration

Step 3: Under  Maintenance, Click Quick Query Viewer  

(Note: Quick Query Viewer Screen appears on clicking the Quick Query Viewer)

Step 4: Click New.

• Select SQL View screen appears.
• A list will shows up with list of SQL Views.
• Select the SQLview that we’ve created.

Step 5: Click OK

• The SQL view loads and result set will appear on the Screen.
• Apply additional filters with the help of filter options, if required.

Step 6 : Click Save

• Save Base Query View appears.

SQL View Name:

    It automatically displays the view name.

Base Query view name:

    User to specify the name that  should be unique.

Description:

    Specify the description that you want to appear in the Quick Query Viewer

Related Business Module:                                                          

     It specifies the module that you want to associate the Quick Query View.

Menu Item:

     Check Menu Item, if you want to include the base query view into the menu System.

Screen Number:

      The screen number will be generated based on the view name but the user can change.

           (Note: This screen number is used to specify the user credentials in Access Rights Maintenance)

Company Filter :

      Select one of the following values:

•  None
•  Current
•  All

Company Column:

      Specify the name of the CompanyID only if you select the current or All in Company Filler.

 Drill Down Programs:

      It allows opening the SL application Screen from the base Query view result set to view / Update

      relevant records. 

      Click  the Expand button and  select the Screen number from the list.

 (Note: It allows only maximum of 5 Screens)

Parameters:  

• Enter the parameters to access the drill down screen.
• Click Close

     (Note: It allows only 3 parameters per screen)

SDK:

• Check SDK check box in the  grid. (mandatory step for Drilldown screen)
• Click OK
• Close the screen.

Step 7: Add the Base Query view into the Menu Maintenance.

Step 8: Specify the access rights for the View, who need to  access  view , insert, delete  and update operation. So that we can set the view as accessible by a user , a group or all users.

Hope this helps!