DynDeveloper.com - Your Dynamics GP Questions, Answered

	SOP Scripts
	eConnect
	taSopDeleteDocumentWrapper
	taSopLineDeleteWrapper
	taSOPLineIvcInsert Wrapper
	taSopLineIvcInsertComponent
	taSOPLotAuto - complete procedure
	Functions
	ddf_Address
	ddf_AllHoldsOnASOPDoc
	General
	Getting Email addresses from Dynamics GP
	Move a SOP transaction from open to history
	Posting Sequences
	SOP40200 'Allow' fields query
	sopGetIDNumber
	sopTrxDeleteWork
	Unvoid a SOP document
	Stored Procedures
	dd_SOP10102_UPD
	dd_SOPGetNextDocNumber/ sopGetIDNumber.
	dd_SOPPayment_INS
	dd_SOPRound - Round a SOP Multicurrency document to two decimal places
	dd_SOPTotal
	dd_SOPTotalTaxes
	dd_taSopVoidDocumentWrapper

Archives

Loop through files in a folder

In writing Dynamics integrations, I'm frequently asked to read a directory of files and process them somehow. Sometimes they're XML, sometimes text.

This piece of code is the main loop for that task. It's not ground breaking or brilliant; but it saves me 30-60 minutes of coding to have it ready so that I can copy and paste into an app.

The basic logic is this:

Create 'done' and 'error' folders to move the files into after processing

Loop

Process the file

Move the file to 'done'

Trap errors

Move the file to 'error

Handle the error

File Handler class

A great majority of the tasks I'm asked to do I've done many times before, so having a library of code is very helpful.

This is our FileHandler class, it handles mundane SYSTEM.IO tasks. Like most of the code on this site it's not ground breaking or beautiful, we've got it here to save time on the next project

SOP Document with Taxes

This article gives an example of how to code a SOP document when the imported document only supplies header level taxes.

I'm freqently given web imports to bring into Dynamics GP, it's not unusual for them to have the taxes stated at the order level and not at the line level.

The task, then, is to back into the taxes by doing TAXAMOUNT/ORDERSUBTOTAL = TAXRATE

When you have the tax rate you can apply it to each line and build the tax import. This method will incur rounding errors, so the last detail line has to handle that.

Notes:

We bring in the DOCID, TAXSCHID and the TAXDTLID from the config file. I've never had a client want more than one tax detail ID.

Formatting SQL

If you've ever opened up a Dynamics GP Native stored procedure, you know that they're frequently on one line. That make them kind of hard to read through.

I've come across several web sites that will 'beautify' SQL code, I'll post them here as I come across them again.

For now, here is one

Telerik - Manually building a hierarchal grid

This code example shows how to build a Telerik hierarchal grid programmatically

We make use of the TelerikGrid class located here:
http://dyndeveloper.com/thread.aspx?Threadid=1386

Web Services - PayablesInvoice

This is not my code... I'm a little weak at C, but a friend sent it in and I thought it would be valuable.

This code will create a Payables Invoice for Web Services, with custom code to handle Multi Companies

eConnect - taSopHdrRecalc

The taSopHdrRecalc stored proc is called by eConnect in a few places, I came across it when submitting a taCreateSopPaymentInsertRecord.

I have the code below in production at a client, it's a wrapper around the taSopHdrRecalc procedure; it adds error handling minor business logic.

Telerik - UserAddingRow example

Like most of our articles, you're not going to find in-depth explanations of complicated code paradigms here...

The intent is to post code examples that will enable rapid development. If you have a code example for something, you don't have to go searching for it.

This is an example of how to use the Telerik UserAddingRow event

Telerik - RadGridView CellDoubleClick

This short code example shows how to code the WinForms RadGridView CellDoubleClick event

The main thing that I have trouble remembering is how to get a reference to the fields; this helps me remember.

Telerik - Hide the Edit button in a RadGrid

Web controls take so much longer to get working, it really helps to have code examples...

This example shows how to hide the edit button in a RadGrid that has 'in place' editing

Telerik - Sample GridView with Insert and Delete; Edit disabled

This code example will show how to code a Telerid AJAX ASP,NET grid with Insert and Delete options, the Edit functionality is disabled

eConnect - Handling tax details when the source data does not include any

When doing an eConnect integration, it is common to get data from a web site that includes only the header level taxes.

eConnect requires that we provide taxes on a detail level, so the technique is to back into the line item taxes and tax details. Since we'll be dividing and rounding to do that, there will be rounding errors at the end; we'll make a final 'adjustment' in the tax amounts to deal with the errant pennies.

The problem:

The web site sends you 'MyTaxSchedule' and the header level tax of $xx.xx, the tax rate is 8.5%

We know that MyTaxSchedule is made up of:

MyStateDetail (6%)
MyCountyDetail (1%)
MyCityDetail (.5%)

For each line in the order, we have to figure what the tax should be for each of the three tax details (so, for a two line order, we have six tax detail lines).

Then, at the end, we sum up our work and make a correction to deal with the pennies.

The code below is straight out of production; the data source that was used was an XML file that was serialized into a Class (a very cool technique); the main class is called oGreatPlainsIntegration. The GreatPlainsIntegration class contains a Batch class. That class has a Customer class, the Customer class has the DocumentHeader class; the DocumentHeader class contains the DocumentLines class. I didn't invent that; that's the way the web site sends it.

SQL - Function to format a Social Security Number

More articles like this can be found on our SQL Data Access menu.

Dynamics GP stores phone number and Social Security Numbers without the dashes, this function will format an SSN for you

Use it like this:

select

lastname,

dbo.f_FP_SSN(socscnum) as SSN

from dbo.upr00100

eConnect - How to use the Post and Pre stored procedures to expand an integration

One of the more frequent questions that we get here is how to take a standard integration and do something a little extra. Maybe add lines to the MDA or Extender tables, or to update fields that are not covered by the integration.

This article will show you how to do that using the Post and Pre stored procedures that ship with eConnect

The text is largely out of the documentation, but I've added some points for clarity. The main benefit of having this text here is that we can easily refer to it when answering questions.

SQL - Table Value Constructors

I often warn people that the article is not new or ground breaking, it's a memory aid. There are some SQL tasks that are hard for me to remember; this one is worse because I can't remember what it's called to look it up. <smiles>

At any rate, I'll post it here and put it on the SQL menu and when I need it again it'll be here.

The following example shows how to use SQL Table Value Constructors (SQL 2008 and above)

This allows you to insert a series of constants into a table. I use it a lot when building small lookup tables, I put it right after the 'Create Table' statement

Telerik - EditMode="InPlace" example

This is an example of how to code Insert/Update/Delete using a Telerik RadGrid

Telerik - Update grid example, bind a dropdown list in a template column example

This article will show how to update a Telerik ASP.NET AJAX grid. We'll look at gathering data from both template and non-template columns.

We'll also show how to bind a drop down list in the template column.

VS Tools/VBA - Populating a dropdown list

This is one of the cooler pieces of code that I've come across in a while.

This technique is coded using VS Tools, but it was ported from VBA code so it'll run just fine in VBA. You'll have to edit the data access code in that case to use ADODB.

The object here is to place a dropdown list on a NATIVE Dynamics form (not one that you added using .NET) and then to populate the dropdown with data.

This piece of code uses 'Pass through SanScript' to do the deed. It's not too complicated and it works just fine, well worth studying.

If I've not explained it clearly please let me know, I'll help.

VS Tools - Getting the UserID/SQLServer/Database

I've written on this in the past, but I've fine tuned my approach. My older approach had trouble with the WinForms test harness that I added to some VS Tools projects to test forms. This works better and doesn't have that drawback.

The issue is that we need the currently logged on user and the SQL Server/Database to run stored procedures from within VS Tools.

You can't go looking for it too early in the GpAddIn.vb class, the code might run before the user has logged in and selected a database.

This article detail the approach

Unable to increment next document number from Sales Type ID Setup or Sales Document Setup after 1000 attempts

There is a system exception being thrown that I haven’t seen before and is preventing transactions from processing. Information is below.

Detail:

Source Procedure: taGetSopNumber

Error Number: 3419

Error Description: Unable to increment next document number from Sales Type ID Setup or Sales Document Setup after 1000 attempts

Stack:

at Microsoft.Dynamics.GP.eConnect.GetSopNumber.ExecuteSopGetNextProcedure(Int32 SOPTYPEKey, String DOCIDKey)

at Microsoft.Dynamics.GP.eConnect.GetSopNumber.GetNextSopNumber(Int32 SOPTYPEKey, String DOCIDKey, SqlConnection connection)

at Microsoft.Dynamics.GP.eConnect.GetNextDocNumbers.GetNextSOPNumber(IncrementDecrement incdec, String DOCIDKey, SopType docType, String BackOfficeConnString)

at Microsoft.Dynamics.GP.eConnect.SOPTransactionType.GetSOPDocumentNumber(XElement sopTypeNode, XElement docIdNode)

at Microsoft.Dynamics.GP.eConnect.SOPTransactionType.ParseTransctionHeader(String[]& nodeNames)

at Microsoft.Dynamics.GP.eConnect.SOPTransactionType.ParseTransactionLines()

at Microsoft.Dynamics.GP.eConnect.MessageManager.ParseTransactionTypes(EntityMessage message)

at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction)

at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateEntity(String connectionString, String sXML)

at Microsoft.Dynamics.GP.EConnectAdapter.ExecuteWrite(String xml, String typeName, Context context, BusinessEvent eventType, BusinessObjectEventArgs args, EventLocation eventLocation)

at Microsoft.Dynamics.GP.EConnectAdapter.Create(BusinessObject businessObject, Context context, Policy policy)

at Microsoft.Dynamics.GP.GreatPlainsBusinessService.PersistForCreate(BusinessObject businessObject, Context context, Policy policy)

at Microsoft.Dynamics.Common.BusinessService.Create(BusinessObject businessObject, Context context, Policy policy)

HTTP could not register URL http://+:80/Microsoft/Dynamics/GP/eConnect/mex/ because TCP port 80 is being used by another application.

I'm getting the error below when trying to start the GP2010 eConnect Service on a new install:

Action:
Service Startup and Construction of Service Host

Current User Name:

Input parameters:

Exception type:
System.ServiceModel.AddressAlreadyInUseException

Exception message:
HTTP could not register URL

http://+:80/Microsoft/Dynamics/GP/eConnect/mex/ because TCP port 80 is being used by another application.

Stack Trace:
at System.ServiceModel.Channels.SharedHttpTransportManager.OnOpen()
at System.ServiceModel.Channels.TransportManager.Open(TransportChannelListener channelListener)
at System.ServiceModel.Channels.TransportManagerContainer.Open(SelectTransportManagersCallback selectTransportManagerCallback)
at System.ServiceModel.Channels.TransportChannelListener.OnOpen(TimeSpan timeout)
at System.ServiceModel.Channels.HttpChannelListener.OnOpen(TimeSpan timeout)
at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
at System.ServiceModel.Dispatcher.ChannelDispatcher.OnOpen(TimeSpan timeout)
at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
at System.ServiceModel.ServiceHostBase.OnOpen(TimeSpan timeout)
at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
at System.ServiceModel.Channels.CommunicationObject.Open()
at Microsoft.Dynamics.GP.eConnect.WindowsService.CreateServiceHost()
Exception type:
System.Net.HttpListenerException

Exception message:
The process cannot access the file because it is being used by another process

Stack Trace:
at System.Net.HttpListener.AddAll()
at System.Net.HttpListener.Start()
at System.ServiceModel.Channels.SharedHttpTransportManager.OnOpen()

GL General understanding

So I am using a C# project to call Econnect. I am new to GL. My question is when i create an invoice and save it with econnect then econnect populates SOP10102((GL Dist Work and Hist), how does it determine the GL accounts that are inserted into SOP10102.

Secondarily how does it save the values for the Item Account Maintenance?

I Get these results

ACTDESCR ACTNUMST

Accounts Receivable 000-1200-00

Sales 000-4100-00 (See this on the Item Account Maintenance)

Commissions - Sales 300-5130-00

Commissions Payable 000-2120-00

SELECT gl.ACTDESCR ,gl105.ACTNUMST,sop.* FROM SOP10102 as sop(nolock)
JOIN GL00100 as gl (nolock) on sop.ACTINDX = gl.ACTINDX
JOIN GL00105 as gl105 (nolock) on gl.ACTINDX = gl105.ACTINDX
WHERE SOPNUMBE = 'STDINV2494'