Monday, April 1, 2013

Creating custom Quick Queries in Dynamics SL 2011


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.

    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)

  • Enter the parameters to access the drill down screen.
  • Click Close
     (Note: It allows only 3 parameters per screen)

  • 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!

No comments:

Post a Comment