DynDeveloper.com

DynDeveloper.com
Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now! Log In
Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Archives

 
SQL - Send email

In the past, I've sent mail through SQL server using xp_sendMail. That's been depreciated; and the preferred way is to use msdb.dbo.sp_send_dbmail.

This article is just the code needed to send an email using that procedure.

You may wonder why we think it's a good idea to blog this sort of trivia; we do it because we're looking for the shortest possible route to getting an application developed. If all the pieces of code that we normally use (but forget the syntax) are in one place we've achieved that goal

 
SQL - Stored Procedure Template

This article contains code for a stored procedure template.

I work for 4 or 5 customers a day on support calls; I don't have the luxury of setting up SQL Server SSMS the way that I like it and coding blissfully.

A basic task for me would be creating a stored procedure; but I really don't like the templates that come with SQL.

SQL - Earliest Open Series Period

In doing an eConnect transaction today, the customer was integrating documents into a closed period. eConnect sensed that and sent and error.

The customer asked me to update the document date to the earliest open date in that case.

The stored procedure below takes the series (explained in the script) and the target date and returns either the target date (meaning that it's a valid date) or the earliest open date that is later than the target date.

 
ASP.NET - Extending the life of the 'Keep Me Logged In' functionality

Websites like this one often make use of the ASP.NET Forms Authentication schema. That schema has an optional parameter that will allow you to admin how long the 'Keep Me Logged In' functionality will remember the person. It does this using a cookie and setting the cookie timeout.

So, here is the snippet of code that you need to administer this and change it from it's default 48 hour setting

You'll find this code on the ASP.NET menu
Installing the Professional Services Tools Library

A recent blog entry from the Dynamics GP Support and Services blog hilighted the RM Transaction Unapply tool in the PSTL. It also included instructions on how to install the PSTL itself

The complete blog entry is here if you're not a DD member.

I've copied the section that details how to install PSTL below so that we'll have it if we need it (I really hate having to search for stuff like this)

This will help with the error:

Incorrect registration code!, please verify registration code
What's new in eConnect GP2010

A little while ago Chris Roehich blogged about the changes to eConnect in GP 2010. I missed the blog entry at the time; but I'm grateful to David Musgrave for pointing it out to me.

Excerpt:

In this post I will talk about the changes made to the API for eConnect for Microsoft Dynamics GP 2010 and how developers can take advantage of these changes to affect their custom application. If you have been using eConnect 2010 you have already realized that there is a new Windows service running called the eConnect for Microsoft Dynamics GP 2010 Integration Service. This is a change from the version 9 and 10 days where there was a COM+ application for eConnect that appeared under Component Services. Like the eConnect COM+ application, the eConnect 2010 service runs under a Windows user that requires DYNGRP role access in the DYNAMICS and company databases. I will breakdown some of the other changes in the following sections.

The full article is here:

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/09/23/leveraging-the-changes-in-econnect-for-microsoft-dynamics-gp-2010-.aspx

and David's post is here:

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2012/10/15/differences-with-sql-transaction-rollback-behaviour-for-econnect-for-microsoft-dynamics-gp-2010.aspx

This post will be on the Knoweldge Base > eConnect menu
unhandleed script exception illlegal address for field 'product indicator'

Hello

I have a user getting the following error during Purchase order entry in G.P
Any one knows why this is happening.
thanks                           
GP Equipment Orders not writing CreatedBy in POA40003

Hello,

Anyne knows why when we create a Purchase Order in G.P the actual user who created it is not showing up in some cases. Even when we use sql

select top 100 * from POA40003 WHERE PONUMBER = 'xxxxxx'

we just dont see who created it.

any idea will be appreciated.

Thanks

EConnect import into AP

Hi

Does anyone have an example of importing into AP through EConnect.

 

THanks

Vic
Batch Transaction

I need an example of using eConnect and inserting a payable batch transaction with a header and distribution.

I have been able to get all of the serialization working but, I cannot figure out the steps to take to get the xml to import.

I am getting "Sequence contains no elements"

Here is my code so far:

    Dim sFileName As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\Frase.xml"
    Dim oBatchHdr As New taCreateUpdateBatchHeaderRcd
    Dim oMemoryStream As New MemoryStream
    Dim oSerializer As New XmlSerializer(GetType(eConnectType))
    Dim oPymtDistribution As New taPMDistribution_ItemsTaPMDistribution
    Dim oPaymentTransaction As New taPMTransactionInsert
    Dim oConnect As New eConnectType
    Dim sBatchNumber As String = "EXP_RPT_" & Format(Now, "mmddyy")
    Dim oTransType As New PMTransactionType
 
    With oBatchHdr
        .APPROVL =
        .BACHFREQ = 1
        .BACHNUMB = sBatchNumber
        .BCHCOMNT = "Automated Entry"
        .BCHSOURC = "No Clue Source"
        .BACHFREQSpecified = True
        .DOCAMT = 25.0
        .NUMOFTRX = 1
        .ORIGIN = 1
        .SERIES = 4
        '.APPRVLDT =
        '.APRVLUSERID =
        '.BRKDNALL =
        '.CHEKBKID =
        '.CNTRLTOT =
        '.CNTRLTOTSpecified =
        '.CNTRLTRX =
        '.CNTRLTRXSpecified =
        '.EFTFileFormat =
        '.GLPOSTDT =
        '.MSCBDINC =
        '.MSCBDINCSpecified =
        '.PmtMethod =
        '.POSTTOGL =
        '.RECPSTGS =
        '.RECPSTGSSpecified =
        '.RequesterTrx =
        '.TRXSOURC =
        '.USERID =
        '.USRDEFND1 =
        '.USRDEFND2 =
        '.USRDEFND3 =
        '.USRDEFND4 =
        '.USRDEFND5 =
    End With
 
    With oPaymentTransaction
        .BACHNUMB = sBatchNumber
        .DOCAMNT = 25.0
        .DOCDATE = Now
        .DOCNUMBR = "ABC-123"
        .DOCTYPE = 1
        .VENDORID = "FRASE-001"
        '    .APDSTKAM =
        '    .BatchCHEKBKID =
        '    .CAMCBKID =
        '    .CAMPMTNM =
        '    .CAMPYNBR =
        '    .CAMTDATE =
        '    .CARDNAME =
        '    .CASHAMNT =
        '    .CCAMPYNM =
        '    .CCRCTNUM =
        '    .CDOCNMBR =
        '    .CHAMCBID =
        '    .CHEKAMNT =
        '    .CHEKBKID =
        '    .CHEKDATE =
        '    .CHEKNMBR =
        '    .CHRGAMNT =
        '    .CRCARDDT =
        '    .CRCRDAMT =
        .CREATEDIST = 0
        '    .CURNCYID =
        '    .DATELMTS =
        '    .DISAMTAV =
        '    .DISAMTAVSpecified =
        '    .DISCDATE =
        '    .DISTKNAM =
        '    .DSCDLRAM =
        '    .DSCDLRAMSpecified =
        '    .DUEDATE =
        '    .DYSTINCR =
        '    .EXCHDATE =
        '    .EXGTBDSC =
        '    .EXPNDATE =
        '    .EXTBLSRC =
        '    .FRTAMNT =
        '    .FRTSCHID =
        '    .MDFUSRID =
        '    .MSCCHAMT =
        '    .MSCSCHID =
        '    .PCHSCHID =
        '    .PORDNMBR =
        '    .POSTEDDT =
        '    .PRCHAMNT =
        '    .PRCTDISC =
        '    .PRCTDISCSpecified =
        '    .PRVDSLMT =
        '    .PSTGDATE =
        '    .PTDUSRID =
        '    .PYMTRMID =
        '    .RATEEXPR =
        '    .RATETPID =
        '    .RATEVARC =
        '    .RequesterTrx =
        '    .RTCLCMTD =
        '    .SHIPMTHD =
        '    .Tax_Date =
        '    .TAXAMNT =
        '    .TAXSCHID =
        '    .TIME1 =
        '    .TEN99AMNT =
        '    .TIME1 =
        '    .TRDISAMT =
        '    .TRXDSCRN =
        '    .TRXDTDEF =
        '    .USRDEFND1 =
        '    .USRDEFND2 =
        '    .USRDEFND3 =
        '    .USRDEFND4 =
        '    .USRDEFND5 =
        '    .VADCDTRO =
        '    .VADDCDPR =
        '    .VCHNUMWK =
        '    .XCHGRATE =
    End With
 
    With oPymtDistribution
        .ACTINDX =
        .ACTNUMST = "000-1100-00"
        .CRDTAMNT = 12
        .DEBITAMT = 0
        '.DistRef =
        '.DISTTYPE = 6
        .DOCTYPE = 1
        '.DSTSQNUM =
        .VCHRNMBR = "VCHR-110"
        .VENDORID = "FRASE-001"
    End With
 
    oTransType.taPMTransactionInsert = oPaymentTransaction
    'oConnect.PMTransactionType(0) = oBatchHdr
    ReDim oConnect.PMTransactionType(0)
    ReDim oTransType.taPMDistribution_Items(0)
 
    oTransType.taPMDistribution_Items(0) = oPymtDistribution
    oConnect.PMTransactionType(0) = oTransType
 
    Dim fs As New FileStream(sFileName, FileMode.Create)
    Dim writer As New XmlTextWriter(fs, New UTF8Encoding)
    'Serialize using the XmlTextWriter to the file
    Dim serializer As New XmlSerializer(GetType(eConnectType))
    serializer.Serialize(writer, oConnect)
    writer.Close()
 
    Dim oXMLDoc As New Xml.XmlDocument
    Dim bRetCode As Boolean
 
    oXMLDoc.Load(sFileName.ToString)
 
    Dim oConnMethod As New Microsoft.Dynamics.GP.eConnect.eConnectMethods
 
    bRetCode = oConnMethod.CreateTransactionEntity("Data Source=localhost;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TWO;Packet Size=4096;", oXMLDoc.OuterXml)
Catch ex1 As eConnect.eConnectException
    MsgBox(ex1.ToString)
Catch ex As Exception
    MsgBox(ex.Message)
End Try

 

 
Problem with creating a Purchase Order

Hi Everyone,

  I am creating an XML document to import purchase orders into Great Plains using eConnect. I am using eConnect to get the next PO number and that works first and then I serialize a PO line and PO Hdr but when I go to pass the XML string to the Entry Point Method I get the following error:

 

Sql procedure error codes returned:
Error Number = 880  Stored Procedure taPoLine  Error Description = Invalid Purchase Item Taxable (Purchase_IV_Item_Taxable) status (1=Taxable,2=Nontaxable,3=Base on Vendor
Node Identifier Parameters: taPoLine                                          
PONUMBER = PO9150856
VENDORID = IMP006
Related Error Code Parameters for Node : taPoLine                                          
Purchase_IV_Item_Taxable =  Note: This parameter was not passed in, no value for the parameter will be returned.

 

I also noticed that my vb method to populate the line item and hdr does not put in all of the line item attributes in the xml file. below is my code to serialize the PO information. Any help would be apreciated.

Sub SerializePurchaseOrderObject(ByVal filename As String, ByVal sPONumber As String)
        'This subroutine creates an eConnect Purchase Order XML document and
        'writes the XML to a file
        Dim purchaseOrder As New POPTransactionType
        Dim purchaseLine As New taPoLine_ItemsTaPoLine
        Dim purchaseHdr As New taPoHdr
        Dim lineItems(1) As taPoLine_ItemsTaPoLine
        Dim eConType As New eConnectType

        With purchaseLine
            .POTYPE = 1
            .PONUMBER = sPONumber
            .VENDORID = "IMP006"
            .LOCNCODE = "NORWALK"
            .VNDITNUM = "7104"
            .ITEMNMBR = "169550"
            .QUANTITY = 1
            .QTYCANCE = 0
            .FREEONBOARD = 1
            .REQSTDBY = "GROCERY"
            .REQDATE = "11/5/2012"
            .NONINVEN = 0
            .ITEMDESC = "1/2 8 Inch Cake Container"
            .VNDITDSC = "Half 8'' Round Cake Container Cakeware"
            .UNITCOST = 56.95
            .UOFM = "each"
            .Purchase_Item_Tax_Schedu = ""
            .Purchase_IV_Item_Taxable = 2
        End With
        lineItems(0) = purchaseLine
        purchaseOrder.taPoLine_Items = lineItems

        With purchaseHdr
            .POTYPE = 1
            .PONUMBER = sPONumber
            .VENDORID = "IMP006"
            .VENDNAME = "IMPERIAL BAG & PAPER CO., INC."
            .DOCDATE = "08/10/2012"
            .BUYERID = "testuser"
            .SUBTOTAL = "56.95"
            .TAXAMNT = "0"
            .REQDATE = "11/5/2012"
            .TAXAMNT = "0"
        End With

        purchaseOrder.taPoHdr = purchaseHdr
        Dim eConnect As New eConnectType
        ReDim Preserve eConnect.POPTransactionType(0)
        eConnect.POPTransactionType(0) = purchaseOrder

        Dim fs As New FileStream(filename, FileMode.Create)
        Dim writer As New XmlTextWriter(fs, New UTF8Encoding)

        Dim serializer As New XmlSerializer(GetType(eConnectType))
        serializer.Serialize(writer, eConnect)
        writer.Close()

 

End Sub

 

 

this is what my XML file looks like, as you can see it is missing some attributes:

<?xml version="1.0" encoding="UTF-8"?>
<eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <POPTransactionType>
        <eConnectProcessInfo xsi:nil="true"/>
        <taRequesterTrxDisabler_Items xsi:nil="true"/>
        <taUpdateCreateItemRcd xsi:nil="true"/>
        <taUpdateCreateVendorRcd xsi:nil="true"/>
        <taCreateVendorAddress_Items xsi:nil="true"/>
        <taUpdateCreateCustomerRcd xsi:nil="true"/>
        <taCreateCustomerAddress_Items xsi:nil="true"/>
        <taPopIvcTaxInsert_Items xsi:nil="true"/>
        <taPopDistribution_Items xsi:nil="true"/>
        <taAnalyticsDistribution_Items xsi:nil="true"/>
        <taPoLine_Items>
            <taPoLine>
                <PONUMBER>PO9150859</PONUMBER>
                <VENDORID>IMP006</VENDORID>
                <LOCNCODE>NORWALK</LOCNCODE>
                <VNDITNUM>7104</VNDITNUM>
                <ITEMNMBR>169550</ITEMNMBR>
                <REQSTDBY>GROCERY</REQSTDBY>
                <REQDATE>11/5/2012</REQDATE>
                <ITEMDESC>1/2 8 Inch Cake Container</ITEMDESC>
                <VNDITDSC>Half 8'' Round Cake Container Cakeware</VNDITDSC>
                <UOFM>each</UOFM>
            </taPoLine>
        </taPoLine_Items>
        <taPoHdr>
            <PONUMBER>PO9150859</PONUMBER>
            <VENDORID>IMP006</VENDORID>
            <VENDNAME>IMPERIAL BAG & PAPER CO., INC.</VENDNAME>
            <DOCDATE>08/10/2012</DOCDATE>
            <BUYERID>rluz</BUYERID>
            <REQDATE>11/5/2012</REQDATE>
        </taPoHdr>
    </POPTransactionType>
</eConnect>

 

Thanks

John

 

 

 
VS Tools Dex Ranges

Hi,

I am trying to get all apply records for a certain document number, document type combination.  I have followed the VS Tools Programmers guide for Multi-segment keys and have the following code.  The issue is that the string contains all apply records.  Ones with different customers and ones that do not have an apply to document number of 12345.  Can someone see what I am doing wrong?

 

Thanks,

Darren

 

 

// Set Reference to AR Apply Table
RmAppliedOpenTable rmApplyOpenTable = Dynamics.Tables.RmAppliedOpen;
try
 {
      //Set Table Key
      rmApplyOpenTable.Key = 2;
 
     //Start of Range
    rmApplyOpenTable.Clear();
    rmApplyOpenTable.ApplyToDocumentNumber.Value = 12345;
    rmApplyOpenTable.ApplyToDocumentType.Value = 1;
    rmApplyOpenTable.RangeStart();
 
    // End of the range
    rmApplyOpenTable.Fill();
   rmApplyOpenTable.ApplyToDocumentNumber.Value = 12345;
    rmApplyOpenTable.ApplyToDocumentType.Value = 1;
    rmApplyOpenTable.RangeEnd();
 
    //Read through all of the rows in the range
    err = rmApplyOpenTable.GetFirst();
   string applies = "";
int i =0;
    while (err == TableError.NoError)
     {
           if (i == 0)
           {
               applies += rmApplyOpen.ApplyFromDocumentNumber.value;
           }
           else
           {
                 applies += ", " +  rmApplyOpen.ApplyFromDocumentNumber.value;
           }                   
          
         //Get the next line item
          err = rmApplyOpenTable.GetNext();
       }
 
return applies;
rmApplyOpen.close();

 
Authenticate against GP Login

Is there any code that I can pass in a user name and password for a custom login page that can validate the credentials against a GP login. I would need the code for an .NET application. Would I need the Visual Studio tools or can I use GP web services? This would be for version 10

 

John
problem dynamics GP table IV10400

Hi I have a trouble with the table IV10400, I cant insert any new records and I am afraid in use a update to update field seqnumbr, can give me a help with this? I dont know what importance has this field.

 

please any feedback is aprecied.

thanks.
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
Archives | Table Listing | eConnect Error Database | eConnect Schema | Gold Star Locksmith
3