Setting multi-line text box control in a Microsoft Dynamics GP add-in to be empty or blank

When working with multi-line text boxes (Text Tool), rather than single line edit boxes, it is easy to introduce a bug to your application.

Blank edit box in Dynamics GP Addin

The text box contents can be set with the the example code below:

ItemMaint.LocalTxtExtraInfo.Value = result.ExtraInfoDescription

…where the value of the right hand side is written to the control’s value and is thus displayed.

As the record set is scrolled, by for example, the user using the form’s scroll buttons, then this code may be called repeatedly to show this field’s value for each record in turn.

The problem I experienced, occurs when after displaying a value for the first record in the field, a subsequent record happens to be empty. In this case  the empty string is assigned to the control’s value, but the control does not blank itself in the user interface. In fact in debug, looking at the control’s value immediately after setting it to string.empty, it is found that the value has not changed from the previous record. 

So we have found that the field is not updated if an empty string is assigned to it. This seems to be a design decision in the  way the objects have been designed to behave. Hence the field will retain the previous record’s value until the next non-blank value in encountered in the record set.

This not only misleads the user as the incorrect field data is displayed for what should be an empty field, but it also, if the record is “saveable” of writing that wrong value back to the database, thus corrupting the field’s value “permanently” in the database (this is what brought my attention to the issue).

So if this control is used in your addin- always remember to use the following pattern, or equivalent to check for empty values. This uses the fields clear method to correctly empty the field on empty strings.

If result.ExtraInfoDescription.Length = 0 Then
ItemMaint.LocalTxtExtraInfo.Clear()
Else
ItemMaint.LocalTxtExtraInfo.Value = result.ExtraInfoDescription
End If

Note:
This behaviour is not followed by the normal edit boxes, they will blank when string.empty is assigned to them.

 

If you found this helpful, feel free to comment, it helps motivate me to write more!

Dynamics GP basic pricing export to Excel with crosstab SQL

 
The following SQL table function allows exporting of GP prices into Excel.
Assumes max of 6 price breaks, but can be expanded.
 
Usage:
SELECT * FROM [Extract_PricesCrosstabTable_Fast] ('GBP','TRADE','%') ORDER BY ITEMNMBR
 
Function:
-- =============================================
-- Author: Tim Wappat
-- Create date: 2010-08-02
-- Description: Function to crosstab price breaks for price level

-- Faster set based non-cursor version of crosstab for pricing
-- =============================================

CREATE FUNCTION [Extract_PricesCrosstabTable_Fast] (
@CURNCYID VARCHAR(15)
,@PRCLEVEL VARCHAR(11)
,@PATTERN NVARCHAR(31) = '%'
)

RETURNS @ReturnTable TABLE (
[ITEMNMBR] [varchar](31) PRIMARY KEY NOT NULL
,[BREAK1] [varchar](255) NOT NULL
,[PRICE1] [numeric](19, 5) NULL
,[BREAK2] [varchar](255) NOT NULL
,[PRICE2] [numeric](19, 5) NULL
,[BREAK3] [varchar](255) NOT NULL
,[PRICE3] [numeric](19, 5) NULL
,[BREAK4] [varchar](255) NOT NULL
,[PRICE4] [numeric](19, 5) NULL
,[BREAK5] [varchar](255) NOT NULL
,[PRICE5] [numeric](19, 5) NULL
,[BREAK6] [varchar](255) NOT NULL
,[PRICE6] [numeric](19, 5) NULL
)
AS
BEGIN

WITH PriceTableCte (
itemnmbr
,curncyid
,prclevel
,uomprice
,fromqty
,toqty
,[PriceBreak]
)
AS (
SELECT ITEMNMBR
,CURNCYID
,PRCLEVEL
,UOMPRICE
,FROMQTY
,TOQTY
,ROW_NUMBER() OVER (
PARTITION BY ITEMNMBR
,PRCLEVEL
,CURNCYID ORDER BY TOQTY ASC
) AS 'PriceBreak'
FROM IV00108
WHERE ITEMNMBR LIKE @PATTERN
AND PRCLEVEL = @PRCLEVEL
AND CURNCYID = @CURNCYID

)
INSERT @ReturnTable
SELECT ITEMNMBR
,isnull(max(CASE
WHEN PriceTableCte.[PriceBreak] = 1 THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
END), '') AS PriceBreak1
,max(CASE
WHEN PriceTableCte.[PriceBreak] = 1 THEN UOMPRICE
END) AS Price1
,isnull(max(CASE
WHEN PriceTableCte.[PriceBreak] = 2 THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
END), '') AS PriceBreak2
,max(CASE
WHEN PriceTableCte.[PriceBreak] = 2
THEN UOMPRICE
END) AS Price2
,isnull(max(CASE
WHEN PriceTableCte.[PriceBreak] = 3 THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
END), '') AS PriceBreak3
,max(CASE
WHEN PriceTableCte.[PriceBreak] = 3
THEN UOMPRICE
END) AS Price3
,isnull(max(CASE
WHEN PriceTableCte.[PriceBreak] = 4 THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
END), '') AS PriceBreak4
,max(CASE
WHEN PriceTableCte.[PriceBreak] = 4
THEN UOMPRICE
END) AS Price4
,isnull(max(CASE
WHEN PriceTableCte.[PriceBreak] = 5 THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
END), '') AS PriceBreak5
,max(CASE
WHEN PriceTableCte.[PriceBreak] = 5
THEN UOMPRICE
END) AS Price5
,isnull(max(CASE
WHEN PriceTableCte.[PriceBreak] = 6 THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
END), '') AS PriceBreak6
,max(CASE
WHEN PriceTableCte.[PriceBreak] = 6
THEN UOMPRICE
END) AS Price6
FROM PriceTableCte

GROUP BY ITEMNMBR
,CURNCYID
,PRCLEVEL
ORDER BY ITEMNMBR;

RETURN
END;

A coutrecords operation on table ‘[Not Found]’ cannot find the table–Dynamics GP

Symptom: User experiencing this error dialog when using navigation lists, but can happen in other areas of Dynamics GP

A countrecords operation on table '[Not found]' cannot find the table

Cause and solutions:

This error is caused by the client machine that is running Dynamics GP loosing network connection at some point during the Dynamics GP session. Dynamics GP holds some long lived connections, especially where temp tables are in use (for example in navigation lists). Indeed it is necessary for GP to keep the connection alive or the temp table will be reclaimed by SQL server as soon as the last connection to it drops. It may also be that you see an associated error similar to the following, with a random looking numeric table name for the global temp table. This is the result of the connection loss.

SQL Server, Invalid object name ‘##293343’

There can be many root causes for a SQL connection to be lost in action. Network infrastructure failures, due to cabling faults in RJ45 connectors, network cards, when the kicks the cables under desk intermittent errors, or someone re-patching live network cabling, network cards starting to fail due to wear and tear etc. These hardware related issues used to cause us issues many years ago, but with a better infrastructure, in recent years it has been software issues in the shape of “sleep settings”. Perhaps there is a poor wifi bridge or similar weak network infrastructure that needs attention, as every time someone starts the microwave in the canteen the wifi drops… – you get the idea, so much to investigate.

The server being rebooted, or SQL server tier being torn down while the user is using GP can also result in the same error. 

The advanced settings of the network card can present various settings, including allowing the operating system to put the network card to sleep, supposed power efficiency? The machine OS sleep settings may be putting the machine into sleep or hibernate after a certain period of time, there may be BIOS settings too.

Users who take long lunches, or leave machines on overnight with GP logged in will more than likely get caught out by sleep settings. When the machines are left inactive they go to sleep loosing the SQL connection and resulting in the above error. I’ve seen more of this since we moved the estate to Windows 10, I don’t think I can draw any real conclusions from that though. I have also experienced users putting their machines to sleep when going for lunch or overnight, manually from the start menu. For this reason Active Directory Group Policy has been created and rolled out to lock down such settings for the GP users.

Although not personally experienced, I have read that this may also be that the ODBC connection settings not being set up correctly. For Dynamics GP, uncheck “Use ANSI quoted identifiers” and “Use ANSI nulls, paddings and warnings”, Uncheck “Perform translation for character data” in the ODBC settings.

See: How To Create An ODBC For Microsoft Dynamics GP 2010 which is applicable for all versions of GP.


Hopefully this has been useful to you – let me know if it has with a comment – motivates me to write more!

Silent install of Microsoft Dynamics GP eConnect


I needed to do a silent install of eConnect for Dynamics GP but couldn’t find the documentation on how to do it, I figured it out and document it here for future reference:


The basic root command construct for a msi silent install is something like:

msiexec /i my.msi /qb+

The switch i is for install then the name of installer and then the silent install options, where it can be tweaked depending upon how silent we need it:

q , qn - No UI

qb - Basic UI. Use qb! to hide the Cancel button.

qr - Reduced UI with no modal dialog box displayed at the end of the installation.

qf - Full UI and any authored FatalError, UserExit, or Exit modal dialog boxes at the end.

qn+ - No UI except for a modal dialog box displayed at the end.

qb+ - Basic UI with a modal dialog box displayed at the end. The modal box is not displayed if the user cancels the installation. Use qb+! or qb!+ to hide the Cancel button.

qb- - Basic UI with no modal dialog boxes. Please note that /qb+- is not a supported UI level. Use qb-! or qb!- to hide the Cancel button.


eConnect needs a SQL user account and an account to run the service under. These can be supplied as parameters like so:


USERDOMAIN={domain} USERNAME={username} USERPASSWORD={passoword} SQL_SERVER_NAME={SQLserverName} SQL_USER_ID={sqlUser} SQL_USER_PASSWORD={password}


So this becomes:

msiexec /i "Microsoft_DynamicsGP18_eConnect_x64_en-us.msi" /qb+ USERDOMAIN={domain} USERNAME={ServiceUsername} USERPASSWORD={password} SQL_SERVER_NAME={SQLserverInstanceName} SQL_USER_ID={sqlUser} SQL_USER_PASSWORD={password}


Which will install eConnect for you with limited UI interaction.

2017-12-10_23-24-36


Do comment if you found this useful – motivates me to write more!