Mohammad R. Daoud: October 2008

Saturday, October 25, 2008

Microsoft Dynamics GP (Great Plains) Row Level Filtering (Row Level Security)

Guys,

David Musgrave the Microsoft Dynamics GP Specialist proofed that the GP could have Row Level Security customized by developing the tool for the sample company, the tool is great and contains all what we need to filter GP rows.

You can find this tool under the following link:
http://blogs.msdn.com/developingfordynamicsgp/archive/2008/08/13/row-level-filtering-proof-of-concept.aspx?CommentPosted=true

But unfortunately the tool is still proof of concept, and not officially released, lets hope to have it one day as one of GP modules!

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com

Auto login for Microsoft Dynamics GP

I found the below idea in one of the forums and liked it!

1. Create new text file in “C:\Program Files\Microsoft Dynamics\GP” call it Login.mac
2. Paste the code below inside it and save.
3. Replace “sa” with your username.
4. Replace “123” with your password.
5. Replace “1” in “'(L) Company Names' item 1 # ''” with the company order of your companies selection list
6. Save the file.
7. Go to GP shortcut, right click and properties.
8. Change the target of the shortcut to include “Login.mac”

Old: "C:\Program Files\Microsoft Dynamics\GP$OULABI\Dynamics.exe" Dynamics.set
New: "C:\Program Files\Microsoft Dynamics\GP$OULABI\Dynamics.exe" Dynamics.set Login.mac

9. Open GP from this shortcut.

Logging file 'macro.log'
CheckActiveWin dictionary 'default' form Login window Login
MoveTo field 'User ID'
TypeTo field 'User ID' , 'sa'
MoveTo field Password
TypeTo field Password , '123'
MoveTo field 'OK Button'
ClickHit field 'OK Button'
NewActiveWin dictionary 'default' form 'Switch Company' window 'Switch Company'
ClickHit field '(L) Company Names' item 1 # ''
MoveTo field 'OK Button'
ClickHit field 'OK Button'
CommandExec dictionary 'default' form 'Command_System' command CloseAllWindows
ActivateWindow dictionary 'default' form Toolbar window 'Main_Menu_1'

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com

Fix for Slow Correcting JE Lookups

I found the following post @ Mark's [MVP] blog and thought it will be helpful!

When you do a correcting Journal Entry in GP, the lookup to find the entry to correct can be brutally slow. it's so slow because it's looking up every journal entry you've done in, like, forever. It's so torturously slow that that the CIA is considering it for use at Guantanamo.

Well, today Jivtesh points us to a Microsoft Knowledge Base article that can help fix that. KB 925326 provides a small SQL script that adds a non-clustered index to the GL20000 and GL30000 tables.

That should help speed things up a bit. Of course, if you simply never made any mistakes, it would never be a problem.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com

Copy Microsoft Dynamics GP Setup

To copy Microsoft Dynamics GP setup from company to another company DTS the following tables via SQL

Finance
GL00100 Chart of Accounts
Note Do not copy the GL00101
GL00102 Account Category Master
GL00103 Fixed Allocation Accounts
GL00104 Variable Allocation Accounts
GL00105 Account Index Master
CM00100 Checkbook Master
CM40100 Cash Management Setup
CM40101 Cash Management Transaction Type Setup
GL00200 Budget Master file
GL00201 Budget Master file
GL40000 General Ledger Setup
GL40100 Quick Journal Setup
GL40101 Quick Journal Setup
GL40200 Segment Description Master
SY04100 Bank Master
MC40000 Multicurrency Setup
MC40100 Multicurrency Setup
ASI*.* Advanced Lookup Files

Purchase
PM00100 PM Class Master
PM00101 Vendor Class Accounts
PM00200 Vendor Master
PM00203 Vendor Accounts
PM00300 Address Master
PM40100 PM Setup File
PM40101 PM Setup File
PM40102 PM Setup File
PM40103 PM Setup File
POP00101 Buyer Master
POP40100 Purchasing Setup File
POP40600 Purchasing Non-IV Item Currency Setup
ASI*.* Advanced Lookup Files

Sales
IVC40100 Invoicing Setup
IVC40101 Invoicing Setup
RM00101 Customer Master
RM00102 Customer Master Address
RM00105 National Accounts Master
RM00201 Class Master
RM00301 Salesperson Master files
RM00303 Sales Territory Master
RM40101 RM Setup
RM40201 RM Period Setup
RM40401 RM Document Type Setup
SOP00100 Process Holds Master
SOP00200 Prospect Master
SOP40100 Sales Setup
SOP40200 Type ID setup
SOP40201 Default Process Holds Setup
SOP40300 Document Setup
SOP40400 User Defined Table Setup
SOP40500 Master Number Setup
SOP40600 Non-IV Item Currency Setup
ASI*.* Advanced Lookup Files

Inventory
BM00101 Bill of Materials Header
BM00111 Bill of Materials Component
BM40100 Bill of Materials Setup
IV00101 Item Master
IV00102 Item Quantity Master
IV00103 Vendor Master
IV00104 Kit Master
IV00105 Item Currency Master
IV00106 Item Purchasing
IV00107 Item Price List Options
IV00108 Item Price List
IV00109 Item Serial Number Mask
IV40100 Inventory Control Setup
IV40201 U of M Setup
IV40202 U of M Setup
IV40400 Item Class Setup
IV40401 Item Class Currency Setup
IV40500 Item Lot Category Setup
IV40600 Item Category Setup
IV40700 Item Site Setup
IV40800 Price Level Setup Company Folder
IV40900 Price Group Master
IV41000 Stock Calendar
IV41001 Stock Calendar Exception Days
ASI*.* Advanced Lookup Files

Company
SY00300 Account Format Setup
SY01100 Posting Account Master
SY02200 Posting Journal Destinations
SY02300 Posting Setup
SY03000 Shipping Methods
SY03100 Credit Card Setup
SY03300 Payment Terms Setup
SY40100 Fiscal Period Setup
SY40101 Fiscal Period Setup
TX00101 Tax Schedule Header Master
TX00102 Tax Schedule Master
TX00201 Tax Master
STN*.* Named Printers Setup
ASI*.* Dynamics Explorer Files

U.S. Payroll
When you copy payroll setup information from one company to another company,
the following setup tables are used:

UPR40100 Payroll Unemployment Setup
UPR40101 Payroll Unemployment TSA
UPR40200 SetupUPR40300 Department Setup
UPR40301 Job Title Setup
UPR40500 Accounts Setup
UPR40501 Tax Expense/Withholding Setup
UPR40600 Pay Code Setup
UPR40700 Workers Comp Setup
UPR40800 Benefit Setup
UPR40801 Benefit Based On Setup
UPR40900 Deduction Setup
UPR40901 Deduction Based On Setup
UPR40902 Deduction Sequence Setup
UPR41100 State Code Setup
UPR41200 Class Setup
UPR41201 Class Detail Setup
UPR41400 Local Tax Setup
UPR41401 Local Tax Table Setup
UPR41500 Shift Code Setup
UPR41700 Supervisor Setup
UPR41800 Payroll Maximum Deduction Setup (only in Microsoft Dynamics GP 10.0)
UPR41801 Payroll State/Fed Setup (only in Microsoft Dynamics GP 10.0)
UPR41900 Payroll Earnings Setup (only in Microsoft Dynamics GP 10.0)
UPR41901 Payroll Earnings Paycode (only in Microsoft Dynamics GP 10.0)
UPR41902 Payroll Earnings Deductions (only in Microsoft Dynamics GP 10.0)

Note If you copy the UPR40500 file, the posting accounts will be identical
to those of the company that you are copying.

Payroll Extensions (deduction in arrears, payables integration to payroll,
overtime rate manager)
ORM_UPR_SETP_OT_DTL
ORM_UPR_SETP_OT_HDR
UPR40600_OT
APR_DIA40100
APR_DIA40200
APR_UPR40500
APR_UPR40900
APR_PIP40100

Advanced Payroll
APR40600
APR41100
APR41101
APR41501
APR41601
APR_APR70901
APR_APR70900
APR_UPR40500
APR_APR40101
APR_APR40100

Canadian Payroll
CPY10010 CDN Payroll Employer Master
CPY10020 CDN Payroll Department Master
CPY10030 CDN Payroll Employee Job Titles
CPY10050 CDN Payroll Employee Class
CPY10051 CDN Payroll Class Attached Pay codes File
CPY10060 CDN Payroll Pay code Master
CPY10061 CDN Payroll Pay code Attached Pay codes
CPY10062 CDN Payroll Income Attached Pay Codes
CPY10063 CDN Payroll Rate Table Codes
CPY10064 CDN Payroll Rate Tables
CPY10070 CDN Payroll WCB Master
CPY10075 CDN Payroll WCB Administration
CPY10080 CDN Payroll User Paid By
CPY10081 CDN Payroll User Drop Down Strings
CPY10082 CDN Payroll Reporting Codes
CPY10170 CDN Payroll Employee Unions
CPY10171 CDN Payroll UnionAttached Pay codes
CPY20200 CDN Payroll Job Master
CPY20201 CDN Payroll Phase Master
CPY20700 P_Security_Group_MSTR
CPY20705 P_Security_Group_Detail
CPY20710 P_Security_User_MSTR

If the following information is the same, you can also copy these files:
CPY20100 CDN Payroll Control Master
CPY20110 CDN Payroll CSB Setup Information
CPY20111 CDN Payroll CSB Pay codes

Human Resource
BE020230 HR_Benefit_SETP
BE021030 BEN2_FMLA_Line
BE031000 BEN_FMLA_INFO
HR2Ben21 HR_Benefit_Tiers_SETP
HR2Ben11 HR_Benefit_Fund
HR2Ben12 HR_Benefit_MDVE_Table
HR2Ben13 HR_Benefit_Life_Premiums
HR2Ben14 HR_Venefit_MDVE_Types
HR2Div02 HR_Division2
HR2Tra01 HR_Train_Course
HR2Tra03 HR_Train_Class
HRCom022 HR_Company2_extra
HRDep022 HR_Department2_Extra
HRDiv022 HR_Division2_Extra
HRPBen05 HRP_BEN_FMLA_Set12Month
HRPro022 HR_Property
HRPppc01 HRP_Position_Pay_Code
HRsax012 HR_Salary_Matrix
HRsax022 HR_Salary_Matrix_Table
HRsax042 HR_Salary_Matrix_Col
HRsax032 HR Salary Matrix rows
HRtra042 HR_Train_Class_Skills
HRtrpc02 HR_Train_Position_Course_Class
HRtrps01 HR_Train_Position_Course
RV010221 HR_Review_LINE_V2
RV020221 HR_Review_Setup_LINE_V2
RV030221 HR_Review_Words_Setup_LINE
SK010230 HR_Skills_Line
TAAC0130 TA_SETP_Accrual_Type
TAPY0130 TA_Payroll_Link
Note The TAPY0130 TA_Payroll_Link table was removed in Microsoft Dynamics GP
9.0 and in Microsoft Dynamics GP 10.0.
TAST0130 TA_Setup
TAST0230 TA_Attendance_reason
TAST0330 TA_Attendance_Types
TAST0532 TA_Pay_Period_accrual_LINE
TATM0130 TA_SETP_Types

Advanced Human Resource
APR_BLM41500
APR_BLM41501
APR_BLM41600
APR_BLM41601
APR_BLM41400
APR_BLM41401
APR_BLM41100
APR_BLM41101
APR_BLM41300
APR_BLM41301
APR_BLM41200
APR_BLM41201
APR_BLM42100
APR_BLM42101
APR_BLM42200
APR_BLM42201
APR_BLM43100
APR_BLM43200
APR_BLM43201
APR_BLM43300
APR_BLM43301
APR_APR40500
EHW40100
EHW40201
EHW40200
EHW40300
EHW40400
EHW40501
EHW40500
CLM40100
CLM40300
CLM40700
CLM40701
CLM40600
CLM40500
CLM40400
CLM40200

PTO Manager
PTO40100
PTO40101
PTO40200
PTO40201

Posted Orginally by MicrosoftSlave in Microsoft Dynamics Forum.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Why does Microsoft Dynamics GP encrypt passwords?

Why does Microsoft Dynamics GP encrypt passwords?

Check post below:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/02/why-does-microsoft-dynamics-gp-encrypt-passwords.aspx

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Convert Numbers To Arabic Words (Tafqeet)

Function to Convert Numbers To Arabic Words (Tafqeet):

You Can Call This Function By:

Select Tafkeet(9875)

Code Below:

CREATE FUNCTION [dbo].[DI_Tafkeet] (@TheNo numeric(18,3))
returns varchar(1000) as

begin
if @TheNo <= 0   return 'zero'

declare @TheNoAfterReplicate varchar(15)
set @TheNoAfterReplicate = right(replicate('0',15)+cast(floor(@TheNo) as varchar(15)),15)
declare @ComWithWord varchar(1000),@TheNoWithDecimal as varchar(400),@ThreeWords as int
set @ThreeWords=0
set @ComWithWord = ' فقط '
declare   @Tafket TABLE (num int, NoName varchar(100))
INSERT INTO @Tafket VALUES (0,'')
INSERT INTO @Tafket VALUES (1,'واحد')
INSERT INTO @Tafket VALUES (2,'اثنان')
INSERT INTO @Tafket VALUES (3,'ثلاثة')
INSERT INTO @Tafket VALUES (4,'اربعة')
INSERT INTO @Tafket VALUES (5,'خمسة')
INSERT INTO @Tafket VALUES (6,'ستة')
INSERT INTO @Tafket VALUES (7,'سبعة')
INSERT INTO @Tafket VALUES (8,'ثمانية')
INSERT INTO @Tafket VALUES (9,'تسعة')
INSERT INTO @Tafket VALUES (10,'عشرة')
INSERT INTO @Tafket VALUES (11,'احدى عشر')
INSERT INTO @Tafket VALUES (12,'اثنى عشر')
INSERT INTO @Tafket VALUES (13,'ثلاثة عشر')
INSERT INTO @Tafket VALUES (14,'اربعة عشر')
INSERT INTO @Tafket VALUES (15,'خمسة عشر')
INSERT INTO @Tafket VALUES (16,'ستة عشر')
INSERT INTO @Tafket VALUES (17,'سبعة عشر')
INSERT INTO @Tafket VALUES (18,'ثمانية عشر')
INSERT INTO @Tafket VALUES (19,'تسعة عشر')
INSERT INTO @Tafket VALUES (20,'عشرون')
INSERT INTO @Tafket VALUES (30,'ثلاثون')
INSERT INTO @Tafket VALUES (40,'اربعون')
INSERT INTO @Tafket VALUES (50,'خمسون')
INSERT INTO @Tafket VALUES (60,'ستون')
INSERT INTO @Tafket VALUES (70,'سبعون')
INSERT INTO @Tafket VALUES (80,'ثمانون')
INSERT INTO @Tafket VALUES (90,'تسعون')
INSERT INTO @Tafket VALUES (100,'مائة')
INSERT INTO @Tafket VALUES (200,'مائتان')
INSERT INTO @Tafket VALUES (300,'ثلاثمائة')
INSERT INTO @Tafket VALUES (400,'أربعمائة')
INSERT INTO @Tafket VALUES (500,'خمسمائة')
INSERT INTO @Tafket VALUES (600,'ستمائة')
INSERT INTO @Tafket VALUES (700,'سبعمائة')
INSERT INTO @Tafket VALUES (800,'ثمانمائة')
INSERT INTO @Tafket VALUES (900,'تسعمائة')
INSERT INTO @Tafket
SELECT FirstN.num+LasteN.num,LasteN.NoName+' و '+FirstN.NoName FROM
(SELECT * FROM @Tafket WHERE num >= 20 AND num <= 90) FirstN
CROSS JOIN
(SELECT * FROM @Tafket WHERE num >= 1 AND num <= 9) LasteN

INSERT INTO @Tafket
SELECT FirstN.num+LasteN.num,FirstN.NoName+' و '+LasteN.NoName FROM (SELECT * FROM @Tafket WHERE num >= 100 AND num <= 900) FirstN
CROSS JOIN
(SELECT * FROM @Tafket WHERE num >= 1 AND num <= 99) LasteN

if left(@TheNoAfterReplicate,3) > 0
set @ComWithWord = @ComWithWord + ISNULL((select NoName from @Tafket where num=left(@TheNoAfterReplicate,3)),'')+ ' ترليون'
if left(right(@TheNoAfterReplicate,12),3) > 0 and left(@TheNoAfterReplicate,3) > 0
set @ComWithWord=@ComWithWord+ ' و '
if left(right(@TheNoAfterReplicate,12),3) > 0
set @ComWithWord = @ComWithWord +ISNULL((select NoName from @Tafket where num=left(right(@TheNoAfterReplicate,12),3)),'') + ' بليون'
if left(right(@TheNoAfterReplicate,9),3) > 0

begin
set @ComWithWord=@ComWithWord + case when @TheNo>999000000 then ' و' else '' end
set @ThreeWords=left(right(@TheNoAfterReplicate,9),3)
set @ComWithWord = @ComWithWord + ISNULL((select case when   @ThreeWords>2 then NoName end from @Tafket where num=left(right(@TheNoAfterReplicate,9),3)),'') + case when @ThreeWords=2 then ' مليونان' when   @ThreeWords between 3 and 10 then ' ملايين' else ' مليون' end
end

if left(right(@TheNoAfterReplicate,6),3) > 0
begin
set @ComWithWord=@ComWithWord + case when @TheNo>999000 then ' و' else '' end
set @ThreeWords=left(right(@TheNoAfterReplicate,6),3)
set @ComWithWord = @ComWithWord + ISNULL((select case when @ThreeWords>2 then NoName end from @Tafket where num=left(right(@TheNoAfterReplicate,6),3)),'')+ case when @ThreeWords=2 then ' الفان' when @ThreeWords between 3 and 10 then ' الاف' else ' الف' end
end

if right(@TheNoAfterReplicate,3) > 0
begin

if @TheNo>999
begin
set @ComWithWord=@ComWithWord + ' و'
end

if right(@TheNoAfterReplicate, 2) = '01' or right(@TheNoAfterReplicate, 2) = '02'
begin
--set @ComWithWord=@ComWithWord + case when @TheNo>1000 then ' و' else '' end
--set @ThreeWords=left(right(@TheNoAfterReplicate,6),3)
set @ComWithWord = @ComWithWord + ' ' + ISNULL((select noname from @Tafket where num=right(@TheNoAfterReplicate, 3)),'')
end

set @ThreeWords=right(@TheNoAfterReplicate,2)

if @ThreeWords=0
begin
--   set @ComWithWord=@ComWithWord + ' و'
   set @ComWithWord = @ComWithWord + ISNULL((select NoName from @Tafket where @ThreeWords=0 AND num=right(@TheNoAfterReplicate,3)),'')
end

end

set @ThreeWords=right(@TheNoAfterReplicate,2)
set @ComWithWord = @ComWithWord +   ISNULL((select NoName from @Tafket where @ThreeWords>2 AND num=right(@TheNoAfterReplicate,3)),'')
set @ComWithWord = @ComWithWord +' '+ case when @ThreeWords=2 then ' ديناران' when @ThreeWords between 3 and 10 then ' دنانير' else ' دينار' end
if right(rtrim(@ComWithWord),1)=',' set @ComWithWord = substring(@ComWithWord,1,len(@ComWithWord)-1)
if right(@TheNo,len(@TheNo)-charindex('.',@TheNo)) >0 and charindex('.',@TheNo)<>0
    begin
        set @ThreeWords=left(right(round(@TheNo,3),3),3)
        SELECT @TheNoWithDecimal= ' و' + ISNULL((SELECT NoName from @Tafket where num=left(right(round(@TheNo,3),3),3) AND @ThreeWords >3),'')
        set @TheNoWithDecimal = @TheNoWithDecimal+ case when @ThreeWords=2 then ' فلسان' when @ThreeWords between 3 and 10 then ' فلسات' else ' فلس' end
set @ComWithWord = @ComWithWord + ' و '+ CONVERT(varchar(max),@ThreeWords)+ case when @ThreeWords=2 then ' فلسان' when @ThreeWords between 3 and 10 then ' فلسات' else ' فلس' end --@TheNoWithDecimal
END
set @ComWithWord = @ComWithWord + ' لا غير '

return rtrim(@ComWithWord)
end

GO

UPDATE: Fixed some issues in covering numbers (101, 201, 301 …)

UPDATE: Fixed decimals not to be included as per many requests.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

How to search all columns of all tables in a database for a keyword?

BY http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

This procedure accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Finding Table and Field Information in Microsoft Dynamics GP

By David Musgrave

To get information about tables and fields in Microsoft Dynamics GP, you can
use any of the following 10 methods:

1.
Open the Microsoft Dynamics GP window that contains the data you are
interested in, and then select Tools -> Integrate -> Table Import to see the
tables associated with the Dexterity Form.

2.
Open the Microsoft Dynamics GP window that contains the data you are
interested in, and then select Tools -> Customise -> Customise Current
Window. Once you’re in Modifier Layout mode, look at the window object
properties. Usually the most important table for a form is linked as the
AutoLink table. Then close the layout window and look at the Tables tab of
the Form Definition window to see the attached tables (these will be the same
as shown in method 1).

Note: Using this method can create additional windows in the Modifier that
have not actually been modified. You should check whether the window already
exists in the Modifier before using this method. That way, you will know
whether the window can be deleted.

3.
Try using SQL logging by adding the following lines into the DEX.INI file:

SQLLogSQLStmt=TRUE

SQLLogODBCMessages=TRUE

SQLLogAllODBCMessages=TRUE

Then delete the DEXSQL.LOG just before performing the actions you are
interested in and look at the DEXSQL.LOG file immediately after.

4.
Load the SDK (Software Developers Kit). The kit contains transaction flow
documents that show which tables to use for specific transactions. It also
contains PDF E-R (Entity-Relationship) diagrams that show the tables and
their relationships.

5.
Select Tools -> Resources -> Tables, and use the Table Resource window to
look up tables.

6.
Use a tool such as SnapShot (available from the Development page of
Winthrop Dexterity Consultants). This tool can display and export information
about tables. However, its main function is to provide platform and account
framework independent data transfer, backup, and migration.

7.
Load Dexterity (from the Tools folder on the second Microsoft Dynamics GP
CD), open the dictionary, and look at the form definition to get the attached
tables. This will be the same list as provided in methods 1 and 2.

8.
Open the window and print the associated report to the screen. Next select
Tools -> Customise -> Modify Current Report. Then look at the tables attached
to the report from the Report Definition.

Note: Using this method can create additional reports in the Report Writer
that have not actually been modified. You should check whether the report
already exists in the Report Writer before using this method. That way, you
will know whether the window can be deleted.

9.
This is probably the most powerful of all the methods listed and is
normally available only to the Dexterity developer. Add the following lines
into the DEX.INI file in the application folder to turn Debug mode on.
(Please do not use these settings for live systems.)

ScriptDebugger=TRUE

ScriptDebuggerProduct=0

The zero represents the product ID for Microsoft Dynamics GP (as shown in
the DYNAMICS.SET launch file). If you are interested in another product, you
can use the product ID for that product.

Launch Microsoft Dynamics GP. You should now see a Debug menu on the right
side of the menu bar. Get to where you want to start logging and profiling.
Select Debug -> Profile Scripts, Debug -> Clear Profile, Debug -> Log Scripts
and select a filename. Then perform the actions you want to log. Next select
Debug -> Log Scripts to stop the logging, Debug -> Save Profile to save the
profile, and Debug -> Profile Scripts to turn off profiling.

Now look at the script log and the script profile files. The script log
shows all the Dexterity calls with their parameters and hierarchy. The script
profile shows you the scripts called, how many times they were called, and
how much time was spent inside the call. Here is the trick: The bottom half
of the script profile shows all the tables that were touched and what actions
took place.

Note: This step only logs Dexterity-based table actions. If a stored
procedure is called, Dexterity cannot see what is happening. Therefore,
Dexterity will not log those table actions.

10.
As a final option, you can turn on SQL activity tracking from Enterprise
Manager to see what actions Microsoft SQL Server is doing.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Microsoft Great Plains Integration with Legacy Systems

Microsoft Great Plains Integration with Legacy Systems

If you are developer who is asked: how do we implement Great Plains integration/interface with your legacy or other system – read this and you will have the clues on where to look further.

1. Great Plains Integration Manager - this is rather end-user tool - it is very intuitive, it validates 100% of business logic, brings in/updates master records (accounts, employees, customers, vendors. etc.) brings in transactions into work tables. The limitation of Integration Manager - it does use GP windows behind the scenes without showing them - so it is relatively slow - you can bring 100 records - but when you are talking about thousands - it is not a good option. By the way you can program Integration Manager with VBA.

2. eConnect – it is type of Software Development Kit with samples in VB.Net. Obviously the development environment should be Visual Studio.Net. eConnect will allow you to integrate master records - such as new customers, vendors, employees, etc., plus you can bring transactions into so called Great Plains work tables (eConnect doesn't allow you to bring open or historical records - you need to post work records in Great Plains, the same limitation applies to Integration Manager above) eConnect is rather for ongoing integration. It was initially created for eCommerce application integration to Great Plains.

3. SQL Stored Procedures. Obviously you have unlimited control and possibilities with SQL queries. You need to know Great Plains tables structure and data flow. Launch Great Plains and go to Tools->Resource Description->Tables. Find the table in the proper series. If you are looking for the customers – it should be RM00101 – customer master file. If you need historical Sales Order Processing documents – they are in SOP30200 – Sales History Header file, etc. Do not change existing tables - do not create new fields, etc. Also you need to realize that each GP table has DEX_ROW_ID - identity column. Sometimes it is good idea to use inbound/outbound XML in the parameters - then you can deploy web service as a middle party between two systems.

4. Data Transformation Services (DTS) – Good tool for importing your third party data into staging tables in GP - then you can pull them in using either stored procs of Integration Manager. You can also deploy this tool for EDI export/import.

5. Great Plains Dexterity Custom Screens. Sometimes users prefer to have seamlessly integrated into GP interface custom screens - for parameters settings and initiating integration. Dexterity is a good option, however remember - it is always better to create new custom screen versus customizing existing one - due to the future upgrade issues. Also - Dexterity is in phasing our by Microsoft Business Solutions.

6. Modifier/VBA custom buttons on the existing screens - alternative to Dexterity is you are comfortable with VBA and ADO.

7. MS Access – if you are doing one time conversion and your legacy has old ODBC compliant platform - you can use MS Access to create linked tables there - or import into MS Access.

8. SQL Linked Servers – you can do direct SQL queries to other ODBC compliant platform via SQL Linked Server (including ORACLE, UNIDATA, Pervasive SQL, Ctree, etc) - you may need to familiarize yourself with OPENROWSET command in Transact SQL. This is also good option if you need cross-platform Crystal Report - pulling data from SQL Server and third party databases on the same report.

9. Warning - do not place existing GP tables into Replication! - you will have upgrade issues.

By Andrew Karasev

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Auto login for Microsoft Dynamics GP!

I found the below idea in one of the forums and liked it!

1. Create new text file in “C:\Program Files\Microsoft Dynamics\GP” call it Login.mac
2. Paste the code below inside it and save.
3. Replace “sa” with your username.
4. Replace “123” with your password.
5. Replace “1” in “'(L) Company Names' item 1 # ''” with the company order of your companies selection list
6. Save the file.
7. Go to GP shortcut, right click and properties.
8. Change the target of the shortcut to include “Login.mac”
Old: "C:\Program Files\Microsoft Dynamics\GP$OULABI\Dynamics.exe" Dynamics.set
New: "C:\Program Files\Microsoft Dynamics\GP$OULABI\Dynamics.exe" Dynamics.set Login.mac
9. Open GP from this shortcut.

Logging file 'macro.log'

CheckActiveWin dictionary 'default' form Login window Login
MoveTo field 'User ID'
TypeTo field 'User ID' , 'sa'
MoveTo field Password
TypeTo field Password , '123'
MoveTo field 'OK Button'
ClickHit field 'OK Button'
NewActiveWin dictionary 'default' form 'Switch Company' window 'Switch Company'
ClickHit field '(L) Company Names' item 1 # ''
MoveTo field 'OK Button'
ClickHit field 'OK Button'
CommandExec dictionary 'default' form 'Command_System' command CloseAllWindows
ActivateWindow dictionary 'default' form Toolbar window 'Main_Menu_1'

Mohammad R. Daoud