Archives

 

A customer has asked me today to change the decimal places to zero on a SOP Invoice in a Word Template. Note that this technique applies to the native Report Writer report also

Here we document the technique; it basically involves going into Report Writer and resetting a Format Definition. Oddly, when you change it in Report Writer it changes it in the Word Template automatically.

 

Today I had an eConnect integration return this ugly error...

Sql procedure error codes returned:
Error Number = 4794  Stored Procedure taPopEnterMatchInvHdr  Error Description = Vendor Document numbers for Invoices must be unique
Node Identifier Parameters: taPopEnterMatchInvHdr                             
POPRCTNM = VCH00104170
Related Error Code Parameters for Node : taPopEnterMatchInvHdr                             
VENDORID = GILCON01      
VNDDOCNM = 12-421

So I wrote the stored procedure below to check for a duplicate Vendor/Vendor Doc combination before I send the document to eConnect.

If the VendorID/Vendor Doc combination exists a row(s) will be returned, otherwise no rows

These topics apply to both Microsoft Dynamics GP and Small Business Financials (SBF).
Overview
The following is a brief description of the fields and data flow that occurs during Deposit entry in Bank Reconciliation. This section may be used as a guide for entering deposits using the following tables:

The idea with populating a Telerik ASPX GridDropDownList is to do it in the ItemDataBound event. It's not too complicated, but it's a lot easier if you have a piece of sample code to copy in and start editing.

Edited 10/9/2012 With better code

We're developing a new application here that requires a RadGrid in EditForms mode; and when in insert mode we need some of the dropdowns to cascade. There are fields for Project and Category, when we select Project we need Category to populate with all the catetories for that project.

This article will document the process needed to achieve that. The basic technique is to use template columns instead of the native GridDropDownColumn, and to handle the ItemDataBound event and the SelectedIndexChanged events. We've set this grid up in AJAX mode so it doesn't 'flash' as things happen.

Also, we've set up the 'Empy Message' property on two of the dropdowns

The end result will look like this in 'insert' mode:

I'm trying to follow a tutorial on how to edit Word Templates for Dynamics reports, and they say to click on the 'Field List button'.

I don't have one of those...

Here's how to find it.

This is our documentation of the IVCreateUOFMScheduleType eConnect type. We show a complete working code example for how to create a unit of measure in Dynamics GP.

We use the taIVCreateUOFMScheduleLine_Items and taIVCreateUOFMScheduleHeader nodes.

This is a working XML document for theDynamics GP IVCreateUOFMScheduleType eConnect type

For a Dynamics developer, looping through a text file is a very common task. We're sent a file from some source and asked to import it into our company.

Today's task is to import a pipe delimited file into GL. The file looks (in part) like this:

00001|Current portion of Notes Pay. |01312012|123.000 |000-2900-00
00001|Current portion of Notes Pay. |01312012|-123.000 |000-2800-00
00002|Monthly Amortization Charge. |01212012|982.92000 |000-6300-00
00002|Monthly Amortization Charge. |01212012|-982.92000 |000-1610-00

 

We're going to make use of the TextFieldParser class to do the heavy lifting here. We'll cover a basic TextFieldParser example, and then get into a very nice piece of code that will save you tons of time.

Like all our code; we write these articles in order to save you development time. You'll find this one on the .NET Development menu under General.

This will be the shortest article on record <smiles>

I'm always getting a file from a customer with negative and positive amounts. Do the negatives go in the DEBITAMT or the CRDTAMNT?

Here's a nice little script sent in by Tonya from AceMicrotech; it will give you the name of the Dynamics users that don't have a corresponding SQL login - the kind of thing that might happen if you're migrating servers or have an 'incident' in the SQL security panel. <smiles>

 

Opening source query...
Establishing source record count...
Beginning integration...
DOC 1 ERROR: Unable to cast COM object of type 'MSScriptControl.ErrorClass' to interface type 'MSScriptControl.IScriptError'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{70841C78-067D-11D0-95D8-00A02463AB28}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).
DOC 1 ERROR: Argument 'Number' is not a valid value. - Argument 'Number' is not a valid value.
DOC 2 ERROR: Unable to cast COM object of type 'MSScriptControl.ErrorClass' to interface type 'MSScriptControl.IScriptError'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{70841C78-067D-11D0-95D8-00A02463AB28}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).
DOC 2 ERROR: Argument 'Number' is not a valid value. - Argument 'Number' is not a valid value.
ERROR: Max Errors Exceeded at 2, Integration Canceled
ERROR: Integration canceled during document integration.
Integration Failed
Integration Results
521 documents were read from the source query.
2 documents were attempted:
0 integrated without warnings.
0 integrated with warnings.
2 failed to integrate.

This sounds like a really mundane topic for an article, but you'll be glad it's here. This took me hours to figure out.

While designing a Word Template for a modified SOP Blank Invoice report, I needed a new column so I added one in the grid.

Now the row is too tall, I can't make it shorter. I was able to see the issue by selecting 'cell > select row'

Notice the date column...

After quite a while, I figured it out

 

Exception:
Server was unable to process request. ---> Violation of PRIMARY KEY constraint 'PKCM20100'. Cannot insert duplicate key in object 'CM20100'.
The statement has been terminated.

GetType: System.Web.Services.Protocols.SoapException
Source: OED2
StackTrace: at OED2.OEDeConnect.createSOPPayment(Int16 SOPTYPE, String SOPNUMBE, Int32 SEQNUMBR, Double PaymentAmount, Int16 PaymentType, String CheckNumber, String CCName, String CCAuthCode, String CCExpnDate, DateTime dtPaymentDate, Int16 PaymentActionType)
at OED2.SOPCreatePayment.RadGridView1_UserDeletingRow(Object sender, GridViewRowCancelEventArgs e)
TargetSite: System.String createSOPPayment(Int16, System.String, Int32, Double, Int16, System.String, System.String, System.String, System.String, System.DateTime, Int16)

This sounds like a straight forward error, I didn't think I'd have too much problem solving it... but I was missing a piece.

This is happening when deleting SOP payments, and only when I delete more than one payment on an order.

When I look in the table, there is a record there with '0' for the CMRECNUM, and it seems like the second delete is trying to do the same thing - hence the dup key issue    

This is a fully functioning example for a Payables PMTransactionType document

am attempting to make a connection to a Dynamics GP SQL Server Database using the currently logged in credentials from GP. (for contexthttp://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/10/02/why-does-microsoft-dynamics-gp-encrypt-passwords.aspx)

Using the code provided from the documentation of GPConnNet.dll I should be able to get a connection but have been unable to do so for non-sa users, sa and dynsa work fine. I am receiving a login failed sql server error. I've tried both overloads of the Connect method, using the SqlConnection and an OdbcConnection.

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(connectionString);
SqlConnection sqlConn = new SqlConnection();
if (sqlConn.State != ConnectionState.Open)
{
   
GPConnection.Startup();
   
var gpconn = new GPConnection();
    gpconn
.Init(<Key1>, <Key2>);
try
{
    sqlConn
.ConnectionString = string.Format("database={0}", cb.InitialCatalog);
    gpconn
.LoginCompatibilityMode = false;
    gpconn
.Connect(sqlConn, cb.DataSource, cb.UserID, cb.Password);
   
if (gpconn.ReturnCode != 1)
         
throw new AuthenticationException("Could not authenticate with the GP credentials.");
}
catch (System.Runtime.InteropServices.SEHException)
{
   
throw new AuthenticationException("Could not authenticate with the GP credentials.");
}
}

The information in the connection string is coming from the Microsoft Dexterity toolkit.

public class GPUser
{
   
public readonly static string DataBase = Dynamics.Globals.IntercompanyId.Value;
   
public readonly static string UserID = Dynamics.Globals.UserName.Value;
   
public readonly static string Password = Dynamics.Globals.SqlPassword.Value;
   
public readonly static string DataSource = Dynamics.Globals.SqlDataSourceName.Value;
   
public readonly static string ApplicationName = string.Format("{0}{1}", App.ProductName, "(gp)");
   
public static string Server
   
{
        get
       
{
           
//Returns the Server from the ODBC DSN
       
}
   
}
   
public static SqlConnectionStringBuilder ConnectionString
   
{
        get
       
{
           
return new SqlConnectionStringBuilder
           
{
               
DataSource = Server,
               
UserID = UserID,
               
Password = Password,
               
ApplicationName = ApplicationName,
               
InitialCatalog = DataBase
           
};
       
}

   
}
}

Is there something security related that is required on the user? Is there something in the GPConnection code that I'm missing?

Thanks

Hi:

Using GP 2010's user interface (Tools->Setup->Inventory->Unit of Measure Scheudle) I am able to create a schedule with a base unit of measure named Each, quantity=1, equivalent = Each, and then another line named Quarter with quantity=.25 and equivalent = Each, so 4 Quarters equals 1 Each.

However, in Econnect I am unable to create a second detail line with an EQUOMQTY value that is less than 1.  I get the following error, which I believe is wrong, because it clearly says that the value cannot be <= 0, but clearly the value is greater than 0.

Error Number = 4771 Stored Procedure= taIVCreateUOFMScheduleLine Error Description = The Equivalent Unit of Measure Quantity (EQUOMQTY) can not be <= 0

 Node Identifier Parameters: taIVCreateUOFMScheduleLine

 UOMSCHDL = MYUOM

 UOFM = QUARTER

 EQUIVUOM = EACH

 EQUOMQTY = 0.25

Is this a known bug, or am I doing something wrong? See code below. It works perfectly fine if the second detail line is equal or greater than 1.

 

 

 

  Dim uom As New taIVCreateUOFMScheduleHeader

        Dim uomDetail1 As New taIVCreateUOFMScheduleLine_ItemsTaIVCreateUOFMScheduleLine
        Dim uomDetail2 As New taIVCreateUOFMScheduleLine_ItemsTaIVCreateUOFMScheduleLine

        Dim uomtype As New IVCreateUOFMScheduleType

        With uom
            .UMSCHDSC = "MYUOM"
            .UOMSCHDL = "MYUOM"
            .BASEUOFM = "EACH"
            .UMDPQTYS = 5
        End With

        With uomDetail1
            .UOMSCHDL = "MYUOM"
            .UOFM = "EACH"
            .EQUIVUOM = "EACH"
            .EQUOMQTY = 1.0
        End With
        With uomDetail2
            .UOMSCHDL = "MYUOM"
            .UOFM = "QUARTER"
            .EQUIVUOM = "EACH"
            .EQUOMQTY = 0.25
        End With

        Dim eConnect As New eConnectType

        Dim uomDetails(1) As taIVCreateUOFMScheduleLine_ItemsTaIVCreateUOFMScheduleLine
        uomDetails(0) = uomDetail1
        uomDetails(1) = uomDetail2
        uomtype.taIVCreateUOFMScheduleLine_Items = uomDetails

        uomtype.taIVCreateUOFMScheduleHeader = uom

 

 

Hi Everyone,

I'm using eConnect to create a Project within GP, but having a problem getting it to set-up correctly.

This creates a record within the correct table and all looks good, apart from the Billing Currency ID details, which while correct hasn't pulled through the correct currency conversion multiplier to create a valid record within PA01202.

I found the following details on this table >> http://dyndeveloper.com/DynColumn.aspx?ModuleID=PA&TableName=PA01202 << and that page says that the Project status should be Open. If I try and add the project with PASTAT (status) as Open I get an error from eConnect.

"Invalid Status (PASTAT<>4) Acceptable values for creating a new project is 4=Estimate"

So, my question is - how can I get the correct details loaded in to PA01202 ?

Regards,
David Watkins

Edit 9/16/15 View the main article for this error here 

Hi everyone.

We are having an issue with our POS app for GP.

We have recently started using eConnect (we used to alter GP table's records manually before) and now when we try to save an existing document while its being edited on GP we get an eConnect exception saying that the document is currently being edited by another user.

Of course we know this is the right behavior and we'd like to forbid the user to open a document that's being edited by some other user.

How can we know, using eConnect, that a document is being edited? We'd like to check that and show an error message in that case.

 

Additionally, how can we mark a document as being edited using eConnect? We'd need this too cause if we add the restriction I mentioned above and we don't do this too, it would only work for documents being edited in GP's Sales Transaction Entry form.

 

We'd appreciate any information you may provide.

 

Regards,

Santiago

Hi everyone.


I'm having trouble creating SOP documents using eConnect. It will only transfer 3 items max, no matter if I add 5 or 100, only the first 3 make it to GP.

But if I save it, the open it, add more items and save it again, all of them are reflected in GP's sales transaction entry window.

It seems to be restricting the number of items I can add when I create the document, but if I'm updating it, it will let me add as many as I want.


It's really weird cause the taSopLineInsert_Items array has every item I add, I checked it before passing the SOPTransactionType object to the XMLSerializer, it has all items but the resulting XML document only has 3 items when creating the SOP Doc...


Any ideas?

Thanks in advance!

Hi,

I'm having a problem regarding serial numbers when updating SOP transactions.

First time it all works smoothly but if I edit the document and try to save it again I get an error saying that this serial item has already been sold.

I've tried setting UpdateIfExists to 1 but that didn't make any difference.


Any ideas?

Hi

I have a client who is missing a stored proc named taRMCustMstrPeriodSum and taRMCustMstrPeriodSumDetail. I tried using database utilites to restore the stored procs no luck and also ptsl. no luck. Any one out there have a non encrypted version of it?

 

Dynamics gp 10.

 

 

Thanks

Vic

The idea with populating a Telerik ASPX GridDropDownList is to do it in the ItemDataBound event. It's not too complicated, but it's a lot easier if you have a piece of sample code to copy in and start editing.

Edited 10/9/2012 With better code

We're developing a new application here that requires a RadGrid in EditForms mode; and when in insert mode we need some of the dropdowns to cascade. There are fields for Project and Category, when we select Project we need Category to populate with all the catetories for that project.

This article will document the process needed to achieve that. The basic technique is to use template columns instead of the native GridDropDownColumn, and to handle the ItemDataBound event and the SelectedIndexChanged events. We've set this grid up in AJAX mode so it doesn't 'flash' as things happen.

Also, we've set up the 'Empy Message' property on two of the dropdowns

The end result will look like this in 'insert' mode:

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3