Saturday, February 22, 2014

Stampede is back!! Or, is it? You decide.

 

Contest-winner

Check out Pam’s post about the re-emergence of Stampede this fall. But really, ‘Stampede’, that is so last decade. I bet you can come up with a better name!

So begins the contest of what to name the new conference. Get all of the details from Pam’s post:

The Power of a Name. Help us Name the New Fall Event

Mark the date, November 10-12. Let’s all go to Fargo in the (brrrrr) fall!

Until next post!

Leslie

Thursday, February 20, 2014

Dex.ini switches – my complete list!

sporting_many_hats_400_clr_12992
As if configuring Dynamics GP isn’t already enough fun, you can enhance your experience even more by modifying the Dex.ini file. Have fun with it! The dex.ini file is inside the Data folder of your Dynamics GP desktop application. Use Notepad, or any other text editor, to open it. Open it, edit it, but be careful with it.

Make sure you read all of the information you can find before you change that first switch. These switches are often overrides to standard behavior and shouldn't be used without careful consideration.
The Dex.ini file is described by the powers that be in the Dynamics documentation universe as the file that “contains setup and operating information about Microsoft Dynamics GP.” It is also known as the ‘defaults’ file'.

dex.ini

Many years ago, I became enamored with all that you could do with a simple .ini switch.
It started when I discovered a dozen or so switches you could use to change the way the initial release of Integration Manager behaved. That, and because using .ini switches was kind of sneaky Smile - sneaky appeals to my peculiar sense of humor.

As time marched on I learned about more and more switches. Sadly, however, there wasn’t a place I could go to learn about all of them, so I created one. This is my collection.  I add to it whenever I run across a new one. I would love it if you would help me keep this list up to date. Below are settings you can use in the Dex.ini file to change the behavior of Dexterity, Dynamics GP, or both. To use them, you can either edit each workstation’s dex.ini file directly, or do it the right way by using the Support Debugging Tool’s Dex.ini Configuration Manager.
Have fun, but be careful, there is no ‘undo’!

.ini setting Description
ADCProcessor=TRUE You will see this line in the Dex.ini file if you are using Manufacturing and have the checkbox marked in ADC Preferences.
AdvLookups=FALSE
New Users created will NOT be granted access to the Alternate lookup windows in the SmartList dictionary and will instead be assigned the old ‘green bar’ lookup windows.
AllowBCPTest=FALSEPrevents utilities from running the BCP (bulk copy program) test.
AllowLongTableNames=TRUEWill allow you to create long table names in Dexterity. Otherwise, you are limited to eight characters. This is set in the Dexterity options menu.
AllowWrongDex=TRUEWill allow Dynamics GP to launch with mis-matched versions of dexterity. DANGER DANGER this should only be used for troubleshooting, not as a way of getting around the error. Never use this in a production environment.
ApplicationName=nameChanges the name the runtime engine displays when it is launched. Without this setting the name “Dexterity Runtime” is displayed. These days, the window doesn’t stay visible very long, so it’s utility is questionable.
AutoDisplayUpdate=TRUEAutomatically redisplays the process monitor queue.
AutoInstallChunks=TRUECauses Dynamics to automatically include the *.cnk file without displaying the 'Add New Code' dialog during launch. The could save you some time if you need to roll out changes to several workstations.
BTInterface=NoLoadThis applied to the old Btrieve file handler (PSQL 2000) as to whether the interface would load when Dynamics was launched.
Buildphantom=TRUEAllows creation of a Manufacturing Order for a Finished Good Phantom Item
BuildSQLMessages=TRUEThis one will copy the Dexterity messages to a SQL table on next login and then it will set it back to FALSE.  Once in a SQL table the messages can be used in stored procedures. The table is DYNAMICS.dbo.MESSAGES
C:\DPS1\DEX.INI DPSInstance=1
C:\DPS2\DEX.INI DPSInstance=2
C:\DPS3\DEX.INI DPSInstance=3
To allow multiple process servers on a single machine. One line for each process server.

CompilerWarningLevel=2
This setting will disable the warnings for all scripts when you are working in Dexterity. Although this option prevents warnings about literal strings, you shouldn’t be using literal strings because it is not considered a best practice. Instead, use the pragma(disable warning LiteralStringUsed); instead of using this setting.

DebugFonts=TRUE
This setting causes Dexterity to generate a trace file named "debuglog.txt". This file lists the fonts that it considered and why particular fonts were chosen or rejected.

DebugRW=XXX

This is a long one! Where XXX equals the sum of the values you want to trace from below.
Value = Name Description
1 = QueryOK Specifies if the report will use a single query or not
2 = Sanscript Logs the run report statement as the Report Writer sees it
4 = RW Query Logs all API calls from RW to the Data Manager
8 = RW Setup If used with RW Run, logs all data returned by Data Manager
16 = RW Steps Logs internal RW steps in processing the report
32 = RW Run Logs all RW runtime calls to the Data Manager
64 = DM SQL Logs internal Data Manager structures and SQL Generation
256 = RW Frames Logs the beginning of each report frame
512 = Tab Delimited Logs output as tab delimited output
Output will appear in a log filed named DebugRW.txt next to the application dictionary.
Example: If you want to log if a report is using a query and the SanScript and SQL code used, then add the following line to your Dex.ini file:
DebugRW=67 ; 1 + 2 + 64
Use 895 if you want to track everything.

To help in trouble shooting problems related to the generation of reports, before printing a report you may choose to mark the Multi-Login Report option on the Report Definition window. This will force the system to perform individual table operations instead of creating a query. If you have the SQLLogSQLStmt = TRUE setting included in your DEX.INI. The individual select statements are then included in your DEXSQL.LOG file and can be analyzed to uncover any potential problems.
 
DebugUnknownFile=TRUEReturns a Btrieve or Ctree error code to help track problems with table errors back in the day of Btrieve and Ctree. (version 7.5 and previous)

DevAssistHide=TRUE

Prevents the Developer’s Assistant window from opening when you go into Debug mode in Dexterity

DexHelpPath=pathname

Path to the Dexterity help files.

Dictionary Version=10.00.1061

The current version of the Dynamics.dic file.

DISALLOWSNLOTAOF=TRUE

Prevents users from being able to "create" new lot numbers or serial numbers while transferring an item from one site to another. Version 10 now has a setup option that disallows this, but the box is not automatically checked. GP is good about not changing the old functionality (really, I mean this) so be sure to check your setup screens after each update or service pack for new setup choices – watch for those new checkboxes!

DisplayTableErrors=TRUE, ALL or OPEN
When the Dexterity Database Management Subsystem encounters an unknown table error this will display the message "Unknown Error = number", where number indicates the error that occurred. The ID that's displayed can be used to determine the cause of the error.
TRUE – Displays only unknown errors.
ALL – Displays all table errors except the two most common: “duplicate” and “not found”.
OPEN - Displays all table errors for an open operation.
DOT4_001winspool=500;750Horizontal and vertical adjustments to printed documents. DOT4, in this case is the port the printer is attached to Printspool= horizontal;vertical. Below is the window that goes with the .ini setting at left. clip_image002
DPSInstance=1Tells the runtime engine which instance of the Dynamics Process Server to use.
duUseResourceDlls=FALSE
duExtractSqlResources=TRUE
Extracts all SQL objects (tables, views, stored procedures, etc.) when an installation / upgrade of GP is done. These details can be found in the SQL->Install subfolder inside GP Application Folder. Open GP Utilities, login and you will notice the SQL Resource extraction occurring
DynHelpPath=pathname Path to Dynamics help files
EmailStmtStatusPath=c:\documents and settings\user_name\desktop
The path for the delivery of e-mail statements

EmpLookup=2

Causes the Employee Lookup to default to Last Name

EmpLookup=3

Causes the Employee Lookup to default to First Name

EmpLookup=4

Causes the Employee Lookup to default to Social Security Number

EnablePerUserIni=TRUE
TRUE will enable the dex.ini per user function
FALSE is the default because it is not included as a default
ExplorerFormatCurrency=FALSEExports foreign currency (ie £ ) to Excel from SmartLists as numbers instead of text.
ExportOneLineBody=TRUEAll fields and text entered in the body of the report will export to a single line when the report is printed to a file. Allows you to enter many fields vertically instead of having to enter them horizontally in the report layout.
All fields and text entered in the body of the report will export to a single line when the report is printed to a file. Using this setting allows you to enter many fields vertically instead of having to enter them horizontally in the report layout. This was what we had to do back in the pre-SQL days. If you don't exactly have your arms around the SmartList Export Solutions, it's still a nifty way to 'export' Excel formulas. Be sure to change your Excel formula options to R1C1 reference style and you won't need to keep track of any Excel templates. My clients enjoy just being able to run a Report Writer report to get all of the formulas they need. That, and I already had it written so it was easier. Here is what a simple report looks like with embedded formulas:
clip_image004
EXTPRICINGQTYFREEISADDER = TRUEThe EXTPRICINGQTYFREEISADDER option is included in the extended pricing component to let you type a negative value in the Maximum Quantity Free box. The extended pricing component uses a negative value in this box to let the Maximum Quantity Free field behave in an additive manner instead of as a multiplier. Refer to KB article 910125.
FAGroupPath=Pathname\filename
FAPhysicalInventoryPath= Pathname\filename
FAPhysInvInfoPath= Pathname\filename
FAAssetImportPath= Pathname\filename
FAAssetIDExportPath= Pathname\filename
FAAssetLabelExportPath= Pathname\filename
FAMacroPath= Pathname\filename
FASampleDataPath= Pathname\filename
A group of settings that determine the import/export file names for user preferences in Fixed assets. Setup>>Fixed Assets>>User Preferences
  


dex.ini switchdescription

FHCheckRanges =TRUE
This setting specifies whether to log the instances when Dexterity decides which type of range to use for tables that use the SQL database type. The log will list all instances when Dexterity detected a range that was not “well-behaved” and whether Dexterity used an exclusive or inclusive range. The FHRANGE.LOG text file to be generated in the same location as Dexterity or the runtime engine.

FileHandler=SQL

This use to be the DatabaseType and was used to determine in part what kind of tables to create when Ctree and Btrieve (PSQL 2000) were supported.

FLEXICODER_UPDATE_SEGMENTS=FALSE

If using the Flexicoder product from eOne Solutions, this determines whether the segment table is updated.

FormDictionaryPath=pathname
ReportDictionaryPath=pathname

These are new for version 10 and welcomed with open arms! These lines tell dexterity where the forms/reports should default to when installing a new .cnk file. Switch it to a shared location for a shared forms/reports.dic and any new dictionary applications that are installed will default to that location instead of the client installation folder. The default location is to store Forms and Reports dictionaries in the Data folder of the GP folder.

HideInvisibleLayoutFields=FALSE

Indicates whether the invisible window fields should be displayed in the Modifier.

HideInvisibleLayoutFields=FALSE

Remembers the setting for the Modifyer Layout window as to whether Invisible fields will be displayed.

IdleLoopValue= number

A numeric value that represents a setting to control how background tasks interface with the CPU.

IdleLoopValue=0

Voluntarily give up the CPU when sitting idle and allow background processes to execute only as often as the OS issues a timer event. Under this setting, the runtime asks to be notified every 50 milliseconds or 20 times a second. Using this setting will cause some processes to be slower than in previous releases, but it will not allow the CPU to be pegged at 100% utilization.

IdleLoopValue=-1

Use as much time as possible to execute background processes. Under this scenario, the CPU would never voluntarily be given up to other applications when sitting idle. This is the default setting and would cause the runtime to behave the same way as it has in previous versions of the Dexterity
runtime.

IdleLoopValue=some positive number [maximum 500]

This value is interpreted as the number of times to process a part of a background task before voluntarily giving up the CPU. This can somewhat control the amount of CPU utilization taken up by background tasks. A typical range for the IdleLoopValue when setting it to a positive number would be
between 5 and 10. Generally, you would never go above 200, even though the maximum is 500.

Initial=FALSE

This line is associated with the automatic creation of palettes; it was used only Release 1 of Dynamics. It will default as equal to FALSE, you can delete it without ill effect.

IsWebClient=TRUE

The setting can set to TRUE or FALSE to override the Runtime_GetClientType() function library command

LastTaxCodeUpdate=02/28/2008

The date of the last payroll tax code update, informational only

LastYearEndUpdate=11/14/2007

The date of the last payroll year-end update, informational only.

LayoutLayout1=162,20,833,616
LayoutToolbox=1,1,156,382
LayoutPropertiesSheetTab=1
LayoutPropertiesSheet=940,63,270,349
LayoutPropertiesSheetVisible=FALSE
LayoutLayout2=169,28,615,391
 

Used to save the adjusted window size of the Layout window within Report Writer.

Letters Directory=c:\Program Files\Microsoft Dynamics\GP\Data\Letters\

Sets the path to the Letters parent folder that contains the letter templates used with the Letter Writing Assistant.

ListsFastExcelExport=TRUE

An unsupported switch that speeds up exports for all of the Navigation lists that show in the navigation pane. This switch is similar to the SmartlistEnhancedExcelExport=TRUE switch except the later applies only to SmartList objects

MagnifyScreenOutput=100

When a report is printed to the screen, this is the magnification setting.

MainProduct=TRUE

If you are using Dex to develop a stand alone app dev tool

MaxSWScrollbarSize=XXX

To allow the scrollbars to get bigger than 25 lines.
MFGRollupPhantomLabor = TRUE

To roll costs on Phantom Bills of Material in MFG
MinPMCheck=50.00

Used in conjunction with PSTL free tool to set the minimum payables check amount. This is used in conjunction with the Select Checks window. It doesn’t limit the check if you use Edit Check Batch or some other window to print your checks. No check for under the amount specified will be created. In this example the minimum check would be $50.00

NextEntryID=
NextGroupID=

When a call background statement is executed in sanScript, Dex creates an entry for that script in the background script queue and assigns it an EntryID. Similarly, when Dex executes the begingroup statement in sanScript, it creates a group item, assigns it a GroupID, and adds it to the background or remote queue. As Dex is running, those IDs are assigned sequentially. So the first call background will be assigned an EntryID of 1, the next would have an EntryID of 2, etc… The same is true for GroupID’s and begingroup. When Dynamics or Dexterity is closed, it writes the current value for those IDs to the dex.ini file. When Dynamics or Dexterity starts up, it reads those items and starts with those values for future background scripts or groups.

NoPrintDialogs=TRUE

Print Dialog boxes will not appear. This is a Print Dialog box:

NoteWindow=37,37,450,299

Used to save the adjusted window size of the Record Notes Window.

OldRelationshipWindow=TRUE

Just kidding, this one doesn't work anymore. For the oldies among us, remember when Report Writer would let you create invalid relationships? Like you could build a relationship between a string field and an integer field. Hey, sometimes you needed to! This switch allowed the old Relationship Definition window to work again. This switch was used prior to version 8.

OLE_Application1=C:\RepairCBDChain.exe

If you run Microsoft Dynamics GP 10.0 in a Citrix environment, the copy (CTRL+C) and paste (CTRL+V) functionality does not work in Microsoft Dynamics GP. You can no longer copy or paste between a local application and a session or between different applications in a session. See KB 958404 for the complete explanation of using this switch.

OLEPath=pathname

Used to indicate where OLE objects that are attached to Dynamics record notes should be stored. Thanks to Matthew, I have a new solution to this one. Apparently, you CAN use the UNC path so long as you have the folders pre-created for each company. Read explanation below:
You only need one line in the dex.ini to accommodate multiple GP companies. If you have three companies in GP (CMP1-CMP3) the line in the dex.ini would still be, for example,
OLEPath=\\ServerName\GPShare\OLE\
For this path to work with the three companies, you would need to manually add the company directories under the OLE directory on the share. For example, the folders would look like this:
GPShare\OLE\CMP1\OLENotes
GPShare\OLE\CMP2\OLENotes
GPShare\OLE\CMP3\OLENotes
If you're using a local drive or a mapped drive instead of UNC for the OLEPath, this directory structure is automatically created for you by GP.
If you don't have the folder structure set up first, UNC will not work. IP addresses won't work either. You will need to use a mapped drive for the system to create them on the fly.
 

OLEPathHR=pathname/

Used to indicate where OLE objects attached to notes in the HR module are stored. This must be a mapped drive, UNC will not work.

PAPRINTFEESBEFORECC=TRUE

Will cause fees to print before the cost categories on a Project Accounting invoice

Pathname=DYNAMICS/dbo/

Location of the Pathnames table (SY02100). When Ctree and Btrieve (PSQL 2000) were supported this line contained a path with either a drive mapping or UNC path to the Dynamics data directory. Today this setting points to the database containing the SY02100 table.

POReturnsMod_ReturnedQTYCheck=TRUE

Enables additional shipment matching functionality in the POret.cnk file. This additional functionality is on the Match Shipments to Invoice Window (opened from the Purchasing Invoice Entry window). The Match Shipments to Invoice Window checks the previously returned quantity to ensure that the quantity returned against a shipment line is not greater than the quantity originally shipped.

PrintReportDefinitionOptions=2

Indicates which of the six checkboxes are checked in Report Writer when the ‘Print Definitions’ button is selected. The value is the sum of each checkbox that is checked. The six checkboxes and their values include:
1 = General Information
2 = Calculated Fields
4 = Table Relationships
8 = Sort Segments
16 = Restrictions
32 = Layout Information
If only General Information and Sort Segments should be printed the value here would be 9.
 

QueueMoreInfo=TRUE

clip_image006
Adds a More Info button to the Process Monitor window which displays the window above when selected.

ReportDictionaryPath=pathname
FormDictionaryPath=pathname

The lines in the dex.ini are for if you add a new dictionary (say I gave you a new customization) then those lines tell dexterity where the forms/reports should default to.  So you could switch it to a shared location for a shared forms/reports.dic and everything will default there in the future. The default location is to store Forms and Reports dictionaries in the Data folder of the GP folder.

ReportLayout=158,155,814,617

Used to save the adjusted window size of the Layout window within Report Writer.

ReportViewMode=1


REVALUEINDETAIL=TRUE

This gives you the ability for the cost adjustment entries to post in detail to the general ledger. The default behavior is to create a single summarized journal entry. The April Hot Fix also includes the ability to get the Purchase Receipt Update Detail (PRUD) report to print in detail. If the ‘Print’ check box is marked for the Cost Variance Journal in the Posting Setup window for the Sales, Inventory and Purchasing series, then this report is also printed whenever there is a cost adjustment and there are general ledger transactions that need to be posted. By default, this report prints in summary. To force this report to print in detail, there are two modified reports that are available at the Great Plains Reports Library located at: https://mbs.microsoft.com/customersource/support/downloads/reportslibrary/gp_reportslibrary.htm that can be downloaded and added to your reports dictionary. After that is completed, you must also grant access to the modified report. After those reports and the dex.ini entry is in place, these reports will provide additional detail supporting the cost changes that are occurring within the Inventory Control module. The reports will provide:
• outflow (sales or inventory) document number
• Item number affected
• quantity of the item on the outflow transaction
• old unit cost of the item on the outflow transaction
• new unit cost of the item on the outflow transaction
• calculated difference between the two costs
• extended amount (quantity * cost difference)
The PRUD report is a “one time” report that is produced at the time of posting and can’t be regenerated so it is essential that this report be printed to paper or file anytime it is produced.
 

RuntimeAsserts =TRUE

Forces the runtime engine to display a dialog box for any assertion that fails.

RwFontChangeSizing=False


RWLayoutPropertiesSheet=822,25,270,349

Used to save the coordinates of the Properties window within Report Writer.

RWLayoutPropertiesSheetTab=2

Used to save which tab is selected in the Properties window within Report Writer

RWLayoutPropertiesSheetVisible=TRUE

Used to indicate if the Properties window within Report Writer is visible.

SAMPLEDATEMMDDYYYY=00000000

To prevent the dialog box from being displayed and to use the current system date

SAMPLEDATEMMDDYYYY=MMDDYYYY

To prevent the dialog box from being displayed and to use a user-specified date

SAMPLEDATEMSG=FALSE

To prevent the dialog box from being displayed while leaving the date as April 12, 2017.

ScriptDebugger=TRUE

Turn Script Debugger Features on in Runtime Mode.

ScriptDebuggerProduct=<PROD ID>

Specify Product ID of product to be debugged to allow Open Script.

ScriptEditorFontName = FontName

Entering a font name here will cause Dexterity to use that font in the Dexterity Script Editor or Script Debugger window. Without the switch you can only choose Courier, Helvetica, Times or System.

ScriptEditorFontStyle=Bold,Italic,Underline (use whichever ones you like)

This switch allows you to set the Bold, Underline, or Italic setting of the font used in the Script Editor. 
While the Script Editor window will respect the settings for the ScriptEditorFontName and use that font, the Dexterity Options window DOES NOT know about the change.  If it finds a font in the Dex.ini it doesn't know about, it will switch your Dex.ini to use the default Courier font.  So if you need to go into the Options window you'll probably have to change your Dex.ini again to reflect the font you'd like to use.  This is the price of using this particular undocumented feature.

ScriptLogEnhanced=TRUE

Enabling this feature will add a Timestamp to the beginning of each line in the Script Log and will also flag scripts running in the background with a “B”.
Note: This setting is only supported on version 10.00 Service Pack 4 or later.
semicolon (;) in front of the ST_MachineID= setting

Turns off Named Printers for that workstation

ShowAdvancedMacroMenu=TRUE

Display additional menu under Tools >Macro.
When you get that message that says your macro failed on line 65,423 you can use this to open your macro and jump right to the line of failure for analysis. Being a frequent user of the 'Mail Merge' macro, I use this frequently to find out what went wrong in the middle of my macro. Another cool thing about this is that it lets you start a macro at any line, no more macro surgery. Also, there's no separate .cnk to install. A simple .ini switch does the trick!

ShowAllMenuItems=TRUE

Shows all menu items even if the user does not have access to them. This doesn’t change the user’s access to the windows; it just will display the menu items even if they have been disabled.

ShowReportName=FALSE

Keeps the Screen Output window from displaying the current report's display name.

ShowResIDs=TRUE

This line will allow Dexterity to show the Resource ID of the current script in the script editor as well as showing the column ID & Res Type in the Dexterity Resource Explorer
SkipVersionChecks=TRUE
 

To disable version compatibility checking. DANGER

SmartlistEnhancedExcelExport=TRUE

Uses a different method to export data to Excel. It’s much faster, but certain things do not export correctly, some foreign currency symbols for one. Also it strips off leading zeros. Test it first before deploying.
SmartlistMatchCasePrefs=TRUE

This change to the Dex.ini file enables the following two options for the SmartList security settings:
– Mark the ‘Match Case’ checkbox by default when performing searches.
– Disable the ‘Match Case’ checkbox on the SmartList Search window.

SQLCursorBlockSize=100 Removing this setting

To prevent the Loop stopping at 25 records when looping through a range of records with Dexterity.

SQLDataSource = data_source_name

This setting causes the specified data source name to appear in the Data Source field in Dexterity's SQL Login window. A user may choose another value from the drop-down list, should he or she want to connect to a different data source.
The SQLDataSource setting won’t affect a login window you create unless you use the
Defaults_Read() function and the set statement to make this setting’s value appear as the default value for the data source field.

SQLDropTableOnDelete=TRUE

Will cause the drop functionality, which removes the data AND the table structure from the database, when the delete table statement is used with a SQL table. Unless the Table_SetCreateMode() is set to true, the dropped tables won't be created automatically then next time a user tries to access them. DANGER

SQLDynamicScrollJump = number

number = The maximum number of rows a cursor will move when a user moves the scroll box, the default is 100.

SQLLastDataSource= data_source_name

The name of the most recently accessed data source. That value will then appear as the default in the predefined Login window the next time that window is used on the current computer.

SQLLastUser = user_ID

The user ID of the most recent user to connect to a data source using the current computer.

SQLLogAllODBCMessages=TRUE

This setting does nothing. The only setting you need for ODBC logging is SQLLogODBCMessages=TRUE (see below). This is a left over setting that isn’t used anymore.

SQLLoginCompatibilityMode=TRUE

In previous versions, SQL Logins were tried with the new 9.0 encryption, the pre 9.0 encryption, plain text login, and then the new encryption model.  This would give the user 4 login attempts if they accidently typed in the wrong password.  With release 10, only the current encryption will be tried.  If the user needs to use the “old” methods – for example just logging in after an update from 8.0 or if the password was changed via SSMS to plain text, adding the dex.ini switch SQLLoginCompatibilityMode=TRUE will revert to the previous behavior.

SQLLoginTimeout = number_of_seconds

Limits the length of time (in seconds) your application will wait for a data source login attempt to be completed. A login doesn’t have to be successful to be completed; rather, an attempt to log in must be made and a status code defining the result of that attempt must be returned.
The default is 15 seconds if this entry is not included in the dex.ini. To cause your application to wait indefinitely for a status code to be returned, set this value to 0 (zero).

SQLLogODBCMessages=TRUE

Log ODBC messages returned from SQL Server to DEX.SQL file.

SQLLogPath=path

This setting allows you to specify the location of the DEXSQL.LOG file created when either or both the SQLLogODBCMessages or the SQLLogSQLStmt defaults file settings are active.

SQLLogSQLStmt=TRUE

Log SQL statements being sent to SQL Server to DEX.SQL file

SQLNumLoginTries = number

The maximum number of login attempts allowed before GP exits. The default is three. Use negative one (-1) for an unlimited number of tries.

SQLPassword = password

This setting allows you to define the default password that will appear in the password field when logging into a data source using Dexterity’s predefined SQL Login window. This setting won't affect a login window you create for use with your application unless you use the Defaults_Read() function and the set statement to make this setting's value appear as the default value.

SQLProcsTimeout=0

Including this setting in your DEX.INI file will control the period of time Dexterity will wait for a SQL stored procedure to execute. When set to 0 it will wait indefinitely. A valid range of values for this setting are 0 to 9999. If this setting is not included, the default value is 300 seconds.

SQLQueryTimeout=0

Including this setting in your DEX.INI file will control the period of time Dexterity will wait for a SQL query to execute. When set to 0 it will wait indefinitely. A valid range of values for this setting are 0 to 9999. If this setting is not included, the default value is 300 seconds.

SQLRptsTimeout=0

Including this setting in your DEX.INI file will control the period of time Dexterity will wait for a report to generate. When set to 0 it will wait indefinitely. A valid range of values for this setting are 0 to 9999. If this setting is not included the default value is 300 seconds.

SQLUser = user_ID

This setting allows you to define a specific user ID as the default value to use in the User ID field of Dexterity’s predefined SQL Login window. The SQLUser setting won't affect a login window you create for use with your application unless you use the Defaults_Read() function and the set statement to make this setting's value appear as the default value for your own user ID field.

ST_ lines that appear in the dex.ini file. Removing all of these settings.

Stops named printers from being used on a workstation.

ST_Debug=LOG

Creates a log file in the local code folder named ST_DEBUG.LOG that can be used to troubleshoot Named Printers when the program does not use the printer that you specified in the Named Printers Options dialog box. Microsoft Dynamics GP may use the wrong printer even though you specifically changed the printer in the Named Printers Options dialog box. In v 10 this log file is created in the \Data subfolder inside the local code folder.

ST_MachineID=machine_id

The machine ID for named printers.

ST_SetDefault = FALSE

Turns off the setting of default printers in named printers

ST_SetDefault=TRUE

Sets the default machine ID for named printers.

ST_SOP_Shortcut=4105,0,0,0

Stores settings for the SOP QuickPrint Feature (which is part of Named Printers)

StdInternationalInfo=TRUE

This setting allows the operating system settings for date, time, and currency formatting to be overridden.
TRUE = Use Dexterity standard formats for date, time, and currency values mm/dd/yy.
FALSE = Use control panel settings for date, time, and currency values

StdInternationalInfoDateCentury=

This setting allows control over the year portion of the date values used by StdInternationalInfo
TRUE = Four digits will be used for the year portion of date values
FALSE = Two digits will be used for the year portion of date values

StdInternationalInfoDateZeroPrefix

This setting allows control over the date format for SdtInternationalInfo
TRUE = Single-digit day and month values will be prefixed with a 0 (zero)
FALSE = Single-digit day and month values will not be prefixed

SuppressChangeDateDialog=True

Use this to prevent the dialog from opening asking you if you want to change the date when it hits midnight. The problem was that the dialog would open and crash any integrations you were running overnight. Be careful with this one, not only does the dialog not display, but the computer date doesn’t change either – so be sure to watch this if running overnight integrations.

SuppressSound=TRUE

Stop Dexterity and Dynamics from generating any sounds. This is popular if you don't want the whole office to hear the computer 'beeping' when you make mistakes. It was also popular back in the 'Welcome to Dynamics' .wav file days.

Synchronize=FALSE

Indicates whether Dynamics Utilities has synchronized the Dynamics.dic to the account framework that is defined in Dynamics Utilities. FALSE signifies that the Dynamics.dic has been synchronized. If a user needs to resynchronize the dictionary, this line should be modified to equal TRUE and then launch Dynamics Utilities.

taShipmentNotificationAllowVoids = true

If this switch exists in the dex.ini and is set to true, invoices will be able to be voided, even if they have Purchase Order Commitments.

taShipmentNotificationAlwaysPost = true

If this switch exists in the dex.ini and is set to true, invoices will always be allowed to post, even if the Shipped flag is not marked.

taShipmentNotificationAlwaysTransfer = true

If this switch exists in the dex.ini and is set to true, orders will always be allowed to be transferred to invoices, even if the Shipped flag in the taShipmentNotification table is not marked.
The following Additional Window is available if you do not want to use [taShipmentNotificationAlwaysTransfer = true]. If you want a user other then ‘sa’ to have access, you must set up security. The following example is one way to set up security
Go to Shortcuts and click Add >> Add Window. Click on Shipment Notification >> Purchasing >> Shipment Notification and click Add and then click Done.
clip_image008
1. You can now use the following window to specifically select which documents can transfer without doing a Purchase Invoice.
clip_image010




Tolerance=0

The Tolerance command will ignore the font defaults and force the system to use the font utilized in Great Plains.

Tolerance=-1

The Tolerance command will ignore the font defaults and force the system to use a true type font.

Tolerance=100

The Tolerance command will ignore the font defaults and force the system to use the default font set in Great Plains.

TPELogging=TRUE

This is related to the new Word Template AddIn
Used to gather information where an exception has occurred. It points the combine process where I will go in and debug the TPE.".
The log is created in the %temp% directory where the actual Word document is rendered and its content looks something like this:
10 hours. 57 minutes.13 seconds. 901 milliseconds. :20050: Combine.Unhandled
TPE was indicating that there was an issue with an exception not being handled when the XML data from Report Writer was being combined with the actual Word template.
This allowed Rob and his team to start tracking down the issue with my template from the source code and provide me with an alternate route to solve my problem.
As a final note, it seems there is an issue as well with TPE not clearing some process after choosing to remove the exception from the Report Processing Status window. In turn, this prevents shutting down GP via the Exit option, having to use the Windows Task Manager to force the termination of the DYNAMICS.EXE process. Rob's team is on this too.

 

TXFileDate=31 Dec 2006

Indicates the date of the last payroll tax table update, information only. Only available from the machine on which the tax update was performed

UpdateLogin=https://mbsupdates.microsoft.com/taxupdate/login.aspx

The entry that should be in the .ini file so the payroll tax updates will connect correctly.

UseUniqueHelp=FALSE

This setting allows you to override the default value for the UseUniqueHelp property for fields when implementing context-sensitive online help. FALSE indicates that the UseUniqueHelp property will default to False for new fields.

WDC_DEBUG=LOG
Debug messages to Log file
WDC_DEBUG=TRUE
Debug messages to Screen

This will create a WDC_SDBG.log file in your local code folder to use debugging Advanced Security Issues

WDC_SecurityDisableWarning=TRUE

This keeps GP from asking if you want to open advanced security instead of regular security when you select regular security from System setup

WindowGridHeight = height in pixels

This setting allows you to define the height in pixels of a grid block in the Layout window.

WindowGridWidth = width in pixels

This setting allows you to define the width in pixels of a grid block in the Layout window.

WindowHeight=n

The height of the Main Window in Dynamics if WindowMax=FALSE

WindowMax=TRUE

This parameter is used to verify the size of the Main Window in Dynamics. If the setting is set to TRUE, it is full screen. If the setting is FALSE, adjustments have been made and additional Window parameters will be listed below the WindowMax setting.

WindowPosX=n

The X position of the Main Window in Dynamics if WindowMax=FALSE

WindowPosY=n

The Y position of the Main Window in Dynamics if WindowMax=FALSE

WindowWidth=n

The width of the Main Window in Dynamics if WindowMax=FALSE

Word Macro File=c:\Program Files\Microsoft Dynamics\GP\Data\Letters\WordIntegration.dot

Sets the path to the WordIntegration.dot documents template that is used with the Letter Writing Assistant.

Workstation=location_translation_ID

Location translation ID that is found in the Location Translation table. This line would be modified if a user has created an additional location translation in the Location Translation Setup window. This line will default as equal to WINDOWS.

Workstation2 = dictionary_location_ID

The dictionary_location_ID indicates a set of dictionaries in the Dynamics.set file that you want a given workstation to use. Each set determines the location of the main dictionary and its forms and reports dictionaries.

WysiwygLayout=TRUE

Saves the setting in Modifier as to whether the field names are displayed. TRUE means the field names are not displayed. FALSE means the field names are displayed.

ZoomFontColor=Green

Saves settings in the Display area of the User Preference settings.

ZoomFontUnderline=True

Saves settings in the Display area of the User Preference settings.


Until next post!
Leslie

Saturday, February 1, 2014

What’s in a date? PM Transactions

cat calendar
Recently, I worked with a group that needed to make changes to the dates of some of their posted payables transactions. Of course, you can’t do that through the user interface, so it was SQL to the rescue. While the initial job sounded easy, they were astonished to see just how many different date fields were in the tables. This post will review the various date fields in the following three tables:
Physical Name
Display Name
Date Fields
PM20000
PM Transaction OPEN File
10
PM30200
PM Paid Transaction History File
11
PM30300
PM Apply To History File
6
For each table, I’ll give the physical name for each relevant field and a short explanation of what I know about that date field. Some of the fields are obvious. Some, well, not so much.
I need a better explanation for the Purchase Date. Please add a comment to this post if you have more information on how this field is populated.

  PM20000

  PM Transaction Open File - 10 dates

VCHRNMBR
Voucher Number
VENDORID
Vendor ID
DOCTYPE
Document type of transaction. (Invoice, Return, Payment, etc.)
DOCDATE
Document date on the invoice or payment
DISCDATE
Date by which the invoice must be paid to earn the terms discount
DUEDATE
Date the invoice is due. After this date it is delinquent
POSTEDDT
System date when user pushed the [Post] button when the doc was originally posted.
MODIFDT
User date when transaction was last modified
DINVPDOF
The apply date from the final document applied to the invoice. This is the date the invoice or pmt became fully applied. Voided is fully applied, written-off can be fully applied. A document in the Open table should not have a value in this field, because no documents in the open table should be ‘paid off’.
PSTGDATE
Posting date for the invoice or payment; set by user on batch window or doc date expansion window
Tax_Date
Date that should be used to calculate and/or report sales tax, defaults to Document date; set by user on window
PRCHDATE
Physical date of transfer of goods/services
DEX_ROW_TS
Dex Row Timestamp – system date & time when last modified


  PM30200

  PM Paid Transaction History File - 11 dates

VCHRNMBR
Voucher Number
VENDORID
Vendor ID
DOCTYPE
Document type of transaction. (Invoice, Return, Payment, etc.)
DOCDATE
Document date on the invoice or payment
DISCDATE
Date by which the invoice must be paid to earn the terms discount
DUEDATE
Date the invoice is due. After this date it is delinquent
POSTEDDT
System date when user pushed the [Post] button when the doc was originally posted.
MODIFDT
user date when trx last modified an ‘apply’ is a modification, a void is not.
DINVPDOF
Apply date of the document that caused the invoice or pmt to be fully applied. Voided is fully applied, written-off can be fully applied. The DATE1 field for the final apply record in the PM30300 table becomes the DINVPDOF.
PSTGDATE
Posting date for the invoice or payment; set by user on batch window or doc date expansion window
VOIDPDATE
The Posting Date (GL) for the Void -The sub ledger void date becomes the DINVPDOF
Tax_Date
Date that should be used to calculate and/or report sales tax, defaults to Document date; set by user on window
PRCHDATE
Physical date of transfer of goods/services
DEX_ROW_TS
Dex Row Timestamp – system date & time when last modified, includes voids.


  PM30300

  PM Apply to History File - 6 dates

VENDORID
Vendor ID
VCHRNMBR
Voucher number of the payment document. The ‘apply from’ voucher number.
APFRDCNM
Document number of the payment ( check number). The ‘apply from’ Document number
DOCTYPE
Document type of the payment. The ‘apply from’ document type.
APTVCHNM
Voucher number of the invoice being paid. The ‘apply to’ voucher number.
APTODCNM
Document number of the invoice being paid. The ‘apply to’ invoice number
APTODCTY
The type of document the pmt is being applied to; invoice, debit memo, etc. The ‘apply to’ document type.
DOCDATE
Document date of the payment being applied; check date. The ‘apply from’ document date.
DATE1
The date the sub ledger uses for the ‘apply date’. This is set by the user on the apply window and used by the system in the HITB and to determine when the 1099 amount is reportable.
GLPOSTDT
Posting date to the general ledger. Set by user on the apply window
APTODCDT
Document date of the invoice being paid. The ‘apply to’ document date
ApplyToGLPostDate
Original GL posting date of the invoice being paid. The ‘apply to’ GL posting date.
ApplyFromGLPostDate
Original GL posting date of the payment document. The ‘apply from’ GL posting date.
Until next post!
Leslie

TSQL - Fun with Dates!

clock_and_calendar_400_clr_9588

Are you constantly needing to come up with different relative dates in reports, SmartLists and the like? I found this great list of date calculations from Pinal Dave and Vivek Jamwal. I’m duplicating it here because 1) I’ll be able to find it, and 2) I wanted to share my find with the Dynamics GP community.

Brenner Klenzman provided the formulas marked ** so that they would render correctly for GP data.

Please note: some of the formulas in the table can be replaced using the new functions in SQL 2012 – links below table.

At the end of this post, I have included several links to various DateTime related articles and additional notes. Also included, are links to articles about the new DateTime functions in SQL 2012!

Enjoy, and thanks again to Pinal Dave, Vivek Jamwal and Brenner Klenzman.

Date

Code

Results on
02-05-2014

Current date and time 

SELECT Getdate()

 

2014-02-05 07:10:49.533

First day of current month

SELECT Dateadd(m,

Datediff(m, 0, Getdate()), 0)

2014-02-01 00:00:00.000

**Last day of current month

SELECT Dateadd(m, Datediff(m, 0, Getdate()) + 1, 0) - 1

 

2014-02-28 00:00:00.000

First day of previous month

SELECT Dateadd(m, -1, Dateadd(m, Datediff(m, 0,

Getdate ()), 0))

2014-01-01 00:00:00.000

Last day of  previous month

SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0,

Getdate ()), 0))

2014-01-31 00:00:00.000

First day of  next month  

SELECT Dateadd(m, 1, Dateadd(m, Datediff(m, 0,

Getdate )), 0))

2014-03-01 00:00:00.000

Last day of next month  

SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0

Dateadd (m, 2,Getdate())), 0))

2014-03-31 00:00:00.000

Last day of month 2  months ago

SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0,

Dateadd(m, -1, Getdate())), 0))

 

2013-12-31 00:00:00.000

Last day of prev month in the previous year

SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0,

Dateadd(m, -12, Getdate())), 0))

2013-01-31 00:00:00.000

Monday of current week

SELECT Dateadd(wk, Datediff(wk, 0, Getdate()), 0)

2014-02-03 00:00:00.000

Sunday of current week  

SELECT Dateadd(wk, Datediff(wk, 0, Getdate()), 6)

2014-02-09 00:00:00.000

Fourth Monday of current month  

SELECT CASE

WHEN( 2 - Datepart(dw, Dateadd(m, Datediff(m, 0, Getdate()), 0)))) >= 0

THEN Dateadd(wk, 3, ( Dateadd(m, Datediff(m, 0, Getdate()), 0)) + (2 - Datepart(dw, ( Dateadd(m, Datediff(m, 0 ,

Getdate()), 0)))))

ELSE Dateadd(wk, 4, ( Dateadd(m, Datediff(m, 0, Getdate()), 0 )) + ( 2 - Datepart(dw, ( Dateadd (m, Datediff(m, 0,     Getdate()), 0))))) END

2014-02-24 00:00:00.000

Second Thursday of current month  

SELECT CASE

WHEN (5 - Datepart(dw, (Dateadd(m, Datediff(m, 0, Getdate()), 0)))) >= 0

THEN Dateadd(wk, 1, ( Dateadd(m, Datediff(m, 0, Getdate()), 0) ) + ( 5 - Datepart(dw, (Dateadd(m, Datediff(m, 0,                   Getdate()), 0)))))

ELSE Dateadd(wk, 2, (Dateadd(m, Datediff(m, 0, Getdate()), 0 )) + ( 5 - Datepart(dw, (Dateadd (m, Datediff(m, 0,

Getdate()), 0)))))

END

2014-02-13 00:00:00.000

 

First day of current year  

SELECT Dateadd(yy, Datediff(yy, 0, Getdate()), 0)

2014-01-01 00:00:00.000

**Last day of current year

SELECT Dateadd(yy, ( Datediff(yy, 0, Getdate())+ 1 ), 0) - 1

 

2014-12-31 00:00:00.000

First day of previous year  

SELECT Dateadd(yy, -1, Dateadd(yy, Datediff(yy, 0, Getdate()), 0))

 

2013-01-01 00:00:00.000

**Last day of previous year

SELECT Dateadd(yy, ( Datediff(yy, 0, Getdate())), 0) - 1

 

2013-12-31 00:00:00.000

First day of April in the current year  

SELECT Dateadd(m, 3, Dateadd(yy, Datediff(yy, 0, Getdate()), 0))

 

2014-04-01 00:00:00.000

 

First day of April in the previous year  

SELECT Dateadd(m, 3, Dateadd(yy, Datediff (yy, 0, Dateadd(yy, -1, Getdate())), 0))

 

2013-04-01 00:00:00.000

**

With thanks to Brenner Klenzman at Willoware for the correct statements.

 

Notes from Pinal Dave: All of the code above, (except Brenner’s, emphasis added) uses Getdate(), or the current date, as a starting point. To test these, just copy what’s in the ‘Code’ column above into a SQL query and execute it. If you need to use a parameter instead, you can replace Getdate() with your parameter name. Be careful with the calculations for weeks. The examples above are assuming that the first day of the week is Sunday. You can check whether this is the same on your SQL Server by running the following query:

        SELECT @@datefirst 

  •  If it returns 7, then your first day of the week is Sunday.
  • Don’t forget to test this code before you rely on it.

Notes from Brenner Klenzman: Some standard SQL date functions can cause problems when used with GP data.

For example: On Feb 3rd of 2014, GETDATE()does not yield 02/03/2014, it actually includes the time down to milliseconds.  When executed, it yields:

2014-02-03 04:27:55.053

If you select documents >= getdate()-7, you are not going to get documents from 01/27/2014 because all documents in GP on that date actually have the date: 2014-01-27 00:00:00.000

Likewise, “Last day of current month” does not yield a GP-compatible “last day” if you use: select DATEADD(ms, -3, DATEADD(m, 0, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))) This formula yields:

2014-02-28 23:59:59.997

What you really want is:

2014-02-28 00:00:00.000

Additionally, if you have a query where 3 milliseconds is being subtracted, you get the same result when you subtract 2 milliseconds. For example: subtracting either 2 or 3 milliseconds yields 2013-12-31 24:59:59.997.  If you subtract 1 millisecond, you get 2014-01-01 00:00:00.000. The final digit in milliseconds is always 0, 3 or 7 (i.e. .880 or .883 or .887), you will never get 884. http://msdn.microsoft.com/en-s/library/ms187819.aspx

Thank you to Pinal Dave and Brenner Klenzman for information included in this post.

Boring notes about DateTime:

The SQL datetime data type is only accurate to 1/333 of a second (3.33ms) or 333 nanoseconds. A nanosecond is 1/100th of a second. A nanosecond is a unit known as a ‘tick’. A particular date is the number of ticks since 12:00 midnight on 1/1/0001 A.D.. The SQL DateTime’s accuracy is to the nearest increment of .000, .003 or .007 seconds. That’s why, in Brenner’s example, he got the same answer when he subtracted 2 milliseconds that he did when he subtracted 3 milliseconds.

SQL 2008 introduced the datetime2 data type. This data type is accurate to 1/100th of a second. Nevertheless, the datetime2 data type is not used in Dynamics GP.

Here are some links to other helpful DateTime articles:

SQL datetime formatting function–convert to string

Part 1 Working with SQL Server DateTime Variables

Part 2 Displaying Dates and Times in Different Formats

Part 3 Searching for Particular Date Values and Ranges

Part 4 Date Math and Universal Time

SQL SERVER 2012 – Date and Time Functions

Until next post!

Leslie