How to Build and Add Quick Queries to the QQ Menu in Dynamics SL
Quick Query for Dynamics SL is a terrific tool to generate highly flexible data inquiries from your Dynamics SL database. This article provides an example of how to build and add a Quick Query to the QQ Menu.
Queries from 3rd party applications or data from other databases can be added to Quick Query (QQ). In the example below, I was asked to add the email addresses from the 3rd party application eBanking to the Vendor Addresses query.
The first thing I did was launch Menu Maintenance which is a great source to identify the view used for a QQ. As shown below the one I wanted is QQ_poaddress.
I then launched SQL Server Management query and located the view in the application DB. By right clicking on the view I scripted the view as a Create to a new query window.
I then joined the existing query to the table XXDDepositor as shown highlighted in the script below. I created the view QQ_poaddeBank instead of altering QQ_poaddress. This is best practice as it keeps the query from being lost the next time SL is upgraded.
CREATE VIEW [dbo].[QQ_poaddeBank] AS
SELECT A.VendId AS [Vendor ID], CASE WHEN CHARINDEX(‘~’ , Vendor.Name) > 0 THEN CONVERT (CHAR(60) , LTRIM(SUBSTRING(Vendor.Name, 1 , CHARINDEX(‘~’ , Vendor.Name) – 1)) + ‘, ‘ + LTRIM(RTRIM(SUBSTRING(Vendor.Name, CHARINDEX(‘~’ , Vendor.Name) + 1 , 60)))) ELSE Vendor.Name END AS [Vendor Name], A.OrdFromId AS [Vendor Address ID], A.Descr AS [Description], CASE WHEN CHARINDEX(‘~’ , A.Name) > 0 THEN CONVERT (CHAR(60) , LTRIM(SUBSTRING(A.Name, 1 , CHARINDEX(‘~’ , A.Name) – 1)) + ‘, ‘ + LTRIM(RTRIM(SUBSTRING(A.Name, CHARINDEX(‘~’ , A.Name) + 1 , 60)))) ELSE A.Name END AS [Vendor Address Name], CASE WHEN CHARINDEX(‘~’ , A.Attn) > 0 THEN CONVERT (CHAR(60) , LTRIM(SUBSTRING(A.Attn, 1 , CHARINDEX(‘~’ , A.Attn) – 1)) + ‘, ‘ + LTRIM(RTRIM(SUBSTRING(A.Attn, CHARINDEX(‘~’ , A.Attn) + 1 , 60)))) ELSE A.Attn END AS [Attention], A.Addr1 AS [Address 1], A.Addr2 AS [Address 2], A.City, A.State AS [State/Province], A.Zip AS [Zip/Postal Code], A.Country AS [Country/Region], ‘(‘ + SUBSTRING(A.Phone, 1, 3) + ‘)’ + SUBSTRING(A.Phone, 4, 3) + ‘-‘ + RTRIM(SUBSTRING(A.Phone, 7, 24)) AS [Phone/Ext], ‘(‘ + SUBSTRING(A.Fax, 1, 3) + ‘)’ + SUBSTRING(A.Fax, 4, 3) + ‘-‘ + RTRIM(SUBSTRING(A.Fax, 7, 24)) AS [Fax/Ext], A.EmailAddr AS [Email Address], isnull(e.EM2Name,”) as [eBanking Vendor Name1] , isnull(e.EM2Addr,”) as [eBanking eMail1], isnull(e.EM3Name,”) as [eBanking Vendor Name2], isnull(e.EM3Addr,”) as [eBanking eMail2], isnull(e.EM4Name,”) as [eBanking Vendor Name3], isnull(e.EM4Addr,”) as [eBanking eMail3],
A.TaxRegNbr AS [Tax Registration Number], A.TaxLocId AS [Tax Location ID], A.TaxId00 AS [Tax ID 01], A.TaxId01 AS [Tax ID 02], A.TaxId02 AS [Tax ID 03], A.TaxId03 AS [Tax ID 04], ONVERT(DATE,A.Crtd_DateTime) AS [Create Date], A.Crtd_Prog AS [Create Program], A.Crtd_User AS [Create User], CONVERT(DATE,A.LUpd_DateTime) AS [Last Update Date], A.LUpd_Prog AS [Last Update Program], A.LUpd_User AS [Last Update User], A.NoteID, A.S4Future01, A.S4Future02, A.S4Future03, A.S4Future04, A.S4Future05, A.S4Future06, CONVERT(DATE,A.S4Future07) AS [S4Future07], CONVERT(DATE,A.S4Future08) AS [S4Future08],
A.S4Future09, A.S4Future10, A.S4Future11, A.S4Future12, A.User1, A.User2, A.User3, A.User4, A.User5, A.User6, CONVERT(DATE,A.User7) AS [User7], CONVERT(DATE,A.User8) AS [User8]
FROM POAddress A with (nolock) left outer join XDDDepositor e (nolock) on a.VendId = e.VendID and A.OrdFromId = e.VendAcct INNER JOIN Vendor with (nolock) ON A.VendId = Vendor.VendId
Next I right clicked and selected Properties for both QQ_poaddeBank and QQ_poaddress. As SL was configured for Windows Authentication, I mimicked the settings found on the Permissions tab in QQ_poaddress into the new query for: BFGROUP, [07718158D19D4f5f9D23B55DBF5DF1] and MSDSL adding them to QQ_poaddeBank using the Search button and then getting focus on each of them in turn. (see below)
I returned to SL and navigated to Administration/System Manager/Maintenance and selected Quick Query viewer. Clicking on the “New” icon opened a dropdown list and and selected QQ_poaddeBank.
The results of the Query are now displayed as shown below.
I then right clicked in the column header which gave me the option to ‘Choose columns”.
The fields can be moved back and forth using the Add and Remove buttons to determine which columns default when the QQ is opened. Click the OK button when finished.
This returns you to the QQ viewer where you can click the “Save” icon. Doing so opens the window shown below.
I accepted all the defaults except the Description which I changed to “Vendor Address with eBanking emails”. Click the “OK” button.
I then opened the Administration/System Manager/Screen Maintenance and found the QQPOADD item that was automatically created in the previous step. The client wanted the query in the Accounts Payable section, so I changed the module from “QQ” to “AP” and saved.
I then opened Access Rights Maintenance and added QQPOADD to the everyone group. Add your query to the appropriate group or groups for you checking the appropriate rights.
Finally, I added the new QQ to the QQ menu in the Financials/AP section. Select the appropriate group and right click on “Accounts Payable as shown below and select “New Link”.
Complete the screen as shown below and “Save”
The next time users who have rights will be able to select and run the QQ. If you have questions or need assistance with additional queries, please let us know.
Please contact Raffa Technology Services by calling (202) 822-5000 or by completing the form on this page.
Post by Thomas Schonthaler