Dynamics GP HITB Reset Tool

https://mbs.microsoft.com/customersource/northamerica/GP/downloads/service-packs/MDGP10_HITB


http://mahmoudsaadi.blogspot.com/2014/04/hitb-essentials-series-hitb-reset-tool.html

• Get an Inventory Offset Account to post the GL-Subledger variances to
• Run the SQL script to create the objects
• Copy the cnk file into the GP folder and launch GP
• Launch GP>Tools>Utilities>Inventory>HITB Reset Tool

LS Retail - NAV - Change Line color/font based on infocode subcode

C99001565 POS Dataset Utility
Add the following code to assign a skin or font to an array number. Call on the array value if your infocode subcode is found on the pos line.

SETJOURNALMODE

  COL0 := 14;
  COL1 := 15;

INITJOURNALMENULINES

JournalLineFonts[COL0] := '#SL_COL0';
JournalLineFonts[COL1] := '#SL_COL1';


JournalLineButtonSkins[COL0] := '#SL_COL0';
JournalLineButtonSkins[COL1] := '#SL_COL1';

GETJOURNALLINECOLOR


    InfCd.RESET;
    InfCd.SETFILTER("Receipt No.","Receipt No.");
    InfCd.SETRANGE("Line No.","Line No.");
    IF InfCd.FIND('-') THEN BEGIN
      IF InfCd.Information = '10' THEN BEGIN
          CurrColor := COL0;
        EXIT;
      END;
      IF InfCd.Information = '20' THEN BEGIN
          CurrColor := COL1;
        EXIT;
      END;
     END;

Dynamics GP 2010 - Restore Copy of Live Over Test database

BACKUP DATABASE [TWO] TO  DISK = N'F:\DBS\TWO_temp.bak' WITH NOFORMAT, INIT,  NAME = N'TWO-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO



Use Master
Alter Database TEST
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE [TEST] FROM  DISK = N'F:\DBS\TWO_temp.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

USE TEST

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
  declare @Statement varchar(850)
  select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
  then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3))
  else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
  where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
    and b.INTERID = db_name() and COLUMN_DEFAULT is not null
 and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
  order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
  exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'
  from SY00100
  exec (@Statement)
end
else begin
  declare @cStatement varchar(255)
  declare G_cursor CURSOR for
  select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
    then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
    else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
  from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
    where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
      and b.INTERID = db_name() and COLUMN_DEFAULT is not null
    order by a.TABLE_NAME
  set nocount on
  OPEN G_cursor
  FETCH NEXT FROM G_cursor INTO @cStatement
  WHILE (@@FETCH_STATUS <> -1)
  begin
    exec (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
  end
  close G_cursor
  DEALLOCATE G_cursor
  set nocount off
end

sp_changedbowner 'DYNSA'

SSRS Keeps prompting for username and password when trying to access the website

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ef2f893-648b-46d3-9339-9fd06075570a/ssrs-prompting-for-usernamepassword-when-trying-to-access-the-report-installed-on-a-server-from?forum=sqlreportingservices

1. Open the rsreportserver.config(By default, it can be found from C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer)
2. Comment the <RSWindowsNegotiate/> under <AuthenticationTypes> element. 

Dynamics NAV CAL - How to use Special Character ' in text string outputs

4 single quotes will turn into 1 single quote

Example

Message('''' + MyWord + '''');

will display  'MyWord'

Dynamics GP - Macro REM or Comment a line

You can just use the ' character at the beginning of lines to stop them from running

Also include this line at the beginning to suppress all error messages

logging file 'macro.log'

Dynamics GP - Customer Phone number does not print on SOP Invoice

• In the Customer address card drill into the address itself
• In the area for "Ship To", Print Phone/Fax Number
• select "Phone1"

Or use this script to set all customer addresses to "Phone1"

• update rm00102 set print_phone_numbergb = 1

• Neither of the above options will update existing SOP documents.
• After making the change on the address card, you must navigate to the sales document, reselect the ship to address id, and roll down the address changes for it to pick up the new address option.

Dynamics GP - VBA cannot be initialized

Your dynamics GP Registration does not have Modifier enabled.
Check your keys and confirm the module is enabled.

Dynamics GP - Modifier and VBA Visual Basic - ODBC Connection - GP2015 After 14.00.066

• Before starting, ensure you check this
• Tools > References > Check the checkbox in front of "Microsoft ActiveX Data Objects 2.5 Library"

'---------------------------------------------------------------------------------------------------
Option Explicit

Private Sub CheckAccounts_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
Dim RetVal As String
On Error GoTo CheckAccounts_BeforeUserChanged_Err

    Dim IntercompanyID As String
    Dim SystemDatabase As String
    Dim StockCountNumber As String
    Dim GetNOAccountResult As String
 
    'Retrieve and display the UserInfo
    IntercompanyID = GETRunningUser("IntercompanyID")
    SystemDatabase = GETRunningUser("SystemDatabaseName")

Exit Sub

Private Function GetNOAccount(ByVal coName As String, ByVal StockID As String) As String
    Dim cn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim SqlStr As String

    On Error GoTo GetNOAccount_Err

    'Retrieve an ADO connection for the current user
    Set cn = UserInfoGet.CreateADOConnection()

    'Set the connection properties
    cn.CursorLocation = adUseClient

    'Set the current database, using the IntercompanyID property
    cn.DefaultDatabase = UserInfoGet.IntercompanyID

    SqlStr = ""

    'Create a command to select result
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = SqlStr
    Set rst = cmd.Execute

    SqlStr = " select top 1 * from dbo.ResultFile"
    cmd.CommandText = SqlStr
    Set rst = cmd.Execute

    'Display the rows retrieved
'rst![ColumnName]
'    MsgBox ">>" + rst!ResultText
    GetNOAccount = rst!ResultText
 
    'Close the connection
    cn.Close
Exit Function

GetNOAccount_Err:

    Select Case Err.Number
    Case 91
        'Object variable or With block variable not set (Error 91)
        MsgBox "Error 91:" + Str(Err.Number) + "<" + Err.Description + ">"
        Resume
    Case Else
        MsgBox "Unknown Error:" + Str(Err.Number) + "<" + Err.Description + ">"
        Exit Function
    End Select
 
End Function

Private Function GETRunningUser(ByVal request As String) As String
    Dim UserInfoObj As UserInfo

    Dim CompanyName As String
    Dim IntercompanyID As String
    Dim SystemDatabase As String
    Dim UserDate As Date
    Dim UserID As String
    Dim UserName As String

    'Get the UserInfo object
    Set UserInfoObj = VbaGlobal.UserInfoGet()

    'Retrieve and display the UserInfo
    Select Case request
       Case "CompanyName"
            GETRunningUser = UserInfoObj.CompanyName
       Case "IntercompanyID"
            GETRunningUser = UserInfoObj.IntercompanyID
       Case "SystemDatabaseName"
            GETRunningUser = UserInfoObj.SystemDatabaseName
       Case "UserDate"
            GETRunningUser = UserInfoObj.UserDate
       Case "UserID"
            GETRunningUser = UserInfoObj.UserID
       Case "UserName"
            GETRunningUser = UserInfoObj.UserName
      Case Else
            GETRunningUser = ""
    End Select

End Function

'--------------------------------------------------------------------------------------------------------------------

Dynamics GP - MBs File Exchange does not prompt for Active X or install File Transfer Manager

https://mbs.microsoft.com/customersource/northamerica/AX/support/support-news/fileexchangeissue

• Press F12
• switch Emulation to 10

Dynamics GP - VBA - Microsoft Visual Basic - Run-time error '70': Permission denied

This error occurs when trying to use the ADODB UserinfoGet function.

This issue is resolved if any hotfix after and including Hotfix GP2015 (14.00.0661)
https://mbs.microsoft.com/customersource/northamerica/GP/downloads/service-packs/MDGP2015_PatchReleases

Dynamics GP - Web Client prints extra header and footer

https://community.dynamics.com/gp/b/dynamicsgp/archive/2016/09/13/additional-lines-added-to-page-when-printing-from-dynamics-gp-web-client

• Click on the Gear>Print>Page Setup
• Set all header and footer options to -Empty-

SQL - View to Determine Table Sizes. Get Table Sizes in SQL.

Original Post
http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

-------------------------------------------------------------------------------------------------------
SELECT        TableName, SchemaName, RowCounts, TotalSpaceKB, UsedSpaceKB, UnusedSpaceKB
FROM            (SELECT        TOP (100) PERCENT t.name AS TableName, s.name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages)
                                                    - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
                          FROM            sys.tables AS t INNER JOIN
                                                    sys.indexes AS i ON t.object_id = i.object_id INNER JOIN
                                                    sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN
                                                    sys.allocation_units AS a ON p.partition_id = a.container_id LEFT OUTER JOIN
                                                    sys.schemas AS s ON t.schema_id = s.schema_id
                          WHERE        (t.name NOT LIKE 'dt%') AND (t.is_ms_shipped = 0) AND (i.object_id > 255)
                          GROUP BY t.name, s.name, p.rows
                          ORDER BY TableName) AS Size
ORDER BY UsedSpaceKB desc
--------------------------------------------------------------------------------------------------

Dynamics GP - Manufacturing Account Movement - Accounts Required

• Minimum Accounts Required for Manufacturing
• RM Inventory Accounts
• FG Inventory Accounts
• FG WIP Material Accounts
• Mfg Costing>Rounding Difference Account
• Damages account
• Overhead Applied accounts
• Examples include:
• Labor
• Admin
• Line
• Machine
• Electricity
• Maintenance
• Full Manufacturing Process
• Create MO
• Schedule
• Build Picklist
• Allocate Raw Materials
• Issue Raw Materials and other costs
• Create Finished Goods Receipt
• Data Entry
• Close MO
• Quick MO with Backflush
• Create MO
• Save and Build Picklist
• Automatically Schedule and build picklist
• Automatically Allocate and Issue Raw Materials (Mfg Setup)
• Close MO
• Automatically receive all Finished Goods
• Automatically Backflush all raw materials and costs
• Credit RM Inventory Account, Debit FG WIP Material Account
• Credit FG WIP Material Account, Debit FG Inventory Account

Dynamics NAV - LS Retail - Windows 7 - Error when pressing hotkey - search:crumb=locationC%3A%5CUser%5C...........%5CDesktop

• These errors occur on Windows 7 due to broken search indexes.
• Follow this guide to repair it 
• http://www.guidingtech.com/18966/fix-windows-7-search-indexing-issues/
• Then do a clean boot

For LS NAV 2017, if the hotkeys do not work, you have to set the rows and columns for the Fixed menu. It has to be a fixed key menu.

Dynamics GP - Automated Check Links - Automatic Login and Check Links Macro and Login and Reconcile Macro

http://mohdaoud.blogspot.com/2008/10/auto-login-for-microsoft-dynamics-gp_2192.html

Copy the macro into the C:\Program Files (x86)\Microsoft Dynamics\GP2015\ Folder

Create a Batch file with the following line. Schedule the Batch file to be executed using windows scheduler, or system scheduler
-----------------------------------------------------------------
cd C:\Program Files (x86)\Microsoft Dynamics\GP2015\
"C:\Program Files (x86)\Microsoft Dynamics\GP2015\Dynamics.exe" Dynamics.set Loginchecklinks.mac
-----------------------------------------------------------------

Autologin Macro GP2015
-----------------------------
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 , 'password'
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'

------------------------------

Check Links Macro

-------------------------------
CheckActiveWin dictionary 'default'  form 'SY_Check_Links' window 'Check Links'
NewActiveWin dictionary 'default'  form sheLL window sheLL
  CommandExec dictionary 'default'  form 'Command_System' command 'SY_Check_Links'
ActivateWindow dictionary 'default'  form 'SY_Check_Links' window 'Check Links'
ActivateWindow dictionary 'default'  form 'SY_Check_Links' window 'Check Links'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 1  # 'Financial'
  ClickHit field 'File Series' item 2  # 'Sales'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 2  # 'Sales'
  ClickHit field 'File Series' item 3  # 'Purchasing'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 3  # 'Purchasing'
  ClickHit field 'File Series' item 4  # 'Inventory'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 4  # 'Inventory'
  ClickHit field 'File Series' item 7  # 'System'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 7  # 'System'
  ClickHit field 'File Series' item 8  # 'Company'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 8  # 'Company'
  CommandExec dictionary 'default'  form 'SY_Check_Links' command 'OK Button_w_Check Links_f_SY_Check_Links'
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination'
  MoveTo field 'OK Button'
  ClickHit field 'OK Button'
-------------------------------------------------------

Inventory Reconcile Macro

------------------------------------------------------
NewActiveWin dictionary 'default'  form sheLL window sheLL
  CommandExec dictionary 'default'  form 'Command_Inventory' command 'IV_Reconcile'
ActivateWindow dictionary 'default'  form 'IV_Reconcile' window 'IV_Reconcile'
ActivateWindow dictionary 'default'  form 'IV_Reconcile' window 'IV_Reconcile'
  CommandExec dictionary 'default'  form 'IV_Reconcile' command 'Process Button P_w_IV_Reconcile_f_IV_Reconcile'
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination'
  MoveTo field 'OK Button'
  ClickHit field 'OK Button'
NewActiveWin dictionary 'DEX.DIC'  form 'Report Destination' window 'Report Type'
  MoveTo field '(L) OK'
  ClickHit field '(L) OK'
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination'
NewActiveWin dictionary 'DEX.DIC'  form 'Report Destination' window 'Report Type'
  MoveTo field '(L) OK'
  ClickHit field '(L) OK'
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination'

Dynamics GP Manufacturing - Prevent MO Receipts from posting if there is insufficient Raw Material or component stock

Add This code to the Manufacturing Order Receipt Entry window.
This only works if Inventory Override Adjustments are disabled

------------------------------------------------------------------------------------------------
Private Sub Window_BeforeModalDialog(ByVal DlgType As DialogType, PromptString As String, Control1String As String, Control2String As String, Control3String As String, Answer As DialogCtrl)
   
'View>Immediate to capture exact wording of error prompts
'Debug.Print PromptString
'Debug.Print "Button 1: " & Control1String
'Debug.Print "Button 2: " & Control2String
'Debug.Print "Button 3: " & Control3String
   
   
    Dim ErrMsg
    ErrMsg = "There is insufficient stock of at least one component. Ensure that adequate component stock is available before posting a receipt."
   
    If PromptString = "You haven't backflushed the planned quantity for at least one component.  Do you want to continue?" Then
        MsgBox ErrMsg, vbExclamation
        Answer = dcButton2
    End If
   
    If PromptString = "At least one component has a shortage that has been overridden.  Do you want to continue?" Then
    MsgBox ErrMsg, vbExclamation
    Answer = dcButton2
    End If
   
    If PromptString = "A quantity shortage exists for this item.  Would you like to use the available quantity or cancel?" Then
    MsgBox ErrMsg, vbExclamation
    Answer = dcButton2
    End If
End Sub