eConnect - Retrieve a Sales Document

This actually took me quite a while to figure out, there are no good examples on how to retrieve 'multi key' documents and no good help on figuring out what the keys are.

The example will document using eConnectOut to retrieve a Sales_Transaction document using the SOPNUMBE and SOPTYPE. We show how to use 'INDEX1FROM' and 'INDEX1TO', as well as how to use the WHERECLAUSE.

When retrieved, the sales document looks like this:

 

<root>
    <eConnect ACTION="0" Requester_DOCTYPE="Sales_Transaction" DBNAME="TWO" TABLENAME="SOP10100" DATE1="1900-01-01T00:00:00" SOPNUMBE="TEST01" SOPTYPE="3">
        <SO_Trans>
            <SOPNUMBE>TEST01</SOPNUMBE>
            <SOPTYPE>3</SOPTYPE>
            <ORIGTYPE>0</ORIGTYPE>
            <ORIGNUMB></ORIGNUMB>
            <DOCID>STDINV</DOCID>
            <DOCDATE>2017-04-12T00:00:00</DOCDATE>
            <GLPOSTDT>1900-01-01T00:00:00</GLPOSTDT>
            <QUOTEDAT>1900-01-01T00:00:00</QUOTEDAT>
            <QUOEXPDA>1900-01-01T00:00:00</QUOEXPDA>
            <ORDRDATE>1900-01-01T00:00:00</ORDRDATE>
            <INVODATE>2017-04-12T00:00:00</INVODATE>
            <BACKDATE>1900-01-01T00:00:00</BACKDATE>
            <RETUDATE>1900-01-01T00:00:00</RETUDATE>
            <ReqShipDate>2017-04-12T00:00:00</ReqShipDate>
            <FUFILDAT>2017-04-12T00:00:00</FUFILDAT>
            <ACTLSHIP>2017-04-12T00:00:00</ACTLSHIP>
            <DISCDATE>2017-04-12T00:00:00</DISCDATE>
            <DUEDATE>2017-05-12T00:00:00</DUEDATE>
            <REPTING>0</REPTING>
            <TRXFREQU>0</TRXFREQU>
            <TIMEREPD>0</TIMEREPD>
            <TIMETREP>0</TIMETREP>
            <DYSTINCR>0</DYSTINCR>
            <DTLSTREP>1900-01-01T00:00:00</DTLSTREP>
            <DSTBTCH1></DSTBTCH1>
            <DSTBTCH2></DSTBTCH2>
            <USDOCID1>BKORD</USDOCID1>
            <USDOCID2></USDOCID2>
            <DISCFRGT>0.00000</DISCFRGT>
            <ORDAVFRT>0.00000</ORDAVFRT>
            <DISCMISC>0.00000</DISCMISC>
            <ORDAVMSC>0.00000</ORDAVMSC>
            <DISAVAMT>0.00000</DISAVAMT>
            <ORDAVAMT>0.00000</ORDAVAMT>
            <DISCRTND>0.00000</DISCRTND>
            <ORDISRTD>0.00000</ORDISRTD>
            <DISTKNAM>0.00000</DISTKNAM>
            <ORDISTKN>0.00000</ORDISTKN>
            <DSCPCTAM>0</DSCPCTAM>
            <DSCDLRAM>0.00000</DSCDLRAM>
            <ORDDLRAT>0.00000</ORDDLRAT>
            <DISAVTKN>0.00000</DISAVTKN>
            <ORDATKN>0.00000</ORDATKN>
            <PYMTRMID>Net 30</PYMTRMID>
            <PRCLEVEL>SPECIAL</PRCLEVEL>
            <LOCNCODE>WAREHOUSE</LOCNCODE>
            <BCHSOURC>Sales Entry</BCHSOURC>
            <BACHNUMB>STEVE</BACHNUMB>
            <CUSTNMBR>AARONFIT0001</CUSTNMBR>
            <CUSTNAME>Aaron Fitz Electrical</CUSTNAME>
            <CSTPONBR></CSTPONBR>
            <PROSPECT>0</PROSPECT>
            <MSTRNUMB>452</MSTRNUMB>
            <PCKSLPNO></PCKSLPNO>
            <PICTICNU></PICTICNU>
            <MRKDNAMT>0.00000</MRKDNAMT>
            <ORMRKDAM>0.00000</ORMRKDAM>
            <PRBTADCD>PRIMARY</PRBTADCD>
            <PRSTADCD>WAREHOUSE</PRSTADCD>
            <CNTCPRSN>Bob Fitz</CNTCPRSN>
            <ShipToName>Aaron Fitz Electrical</ShipToName>
            <ADDRESS1>11403 45 St. South</ADDRESS1>
            <ADDRESS2></ADDRESS2>
            <ADDRESS3></ADDRESS3>
            <CITY>Chicago</CITY>
            <STATE>IL</STATE>
            <ZIPCODE>60603-0776</ZIPCODE>
            <COUNTRY>USA</COUNTRY>
            <PHNUMBR1>31255501020000</PHNUMBR1>
            <PHNUMBR2>31255501020000</PHNUMBR2>
            <PHONE3></PHONE3>
            <FAXNUMBR>31255501020000</FAXNUMBR>
            <COMAPPTO>0</COMAPPTO>
            <COMMAMNT>0.00000</COMMAMNT>
            <OCOMMAMT>0.00000</OCOMMAMT>
            <CMMSLAMT>0.00000</CMMSLAMT>
            <ORCOSAMT>0.00000</ORCOSAMT>
            <NCOMAMNT>0.00000</NCOMAMNT>
            <ORNCMAMT>0.00000</ORNCMAMT>
            <SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
            <TRDISAMT>0.00000</TRDISAMT>
            <ORTDISAM>0.00000</ORTDISAM>
            <TRDISPCT>0</TRDISPCT>
            <SUBTOTAL>32000.00000</SUBTOTAL>
            <ORSUBTOT>32000.00000</ORSUBTOT>
            <REMSUBTO>32000.00000</REMSUBTO>
            <OREMSUBT>32000.00000</OREMSUBT>
            <EXTDCOST>0.00000</EXTDCOST>
            <OREXTCST>0.00000</OREXTCST>
            <FRTAMNT>0.00000</FRTAMNT>
            <ORFRTAMT>0.00000</ORFRTAMT>
            <MISCAMNT>0.00000</MISCAMNT>
            <ORMISCAMT>0.00000</ORMISCAMT>
            <TXENGCLD>1</TXENGCLD>
            <TAXEXMT1></TAXEXMT1>
            <TAXEXMT2></TAXEXMT2>
            <TXRGNNUM></TXRGNNUM>
            <TAXSCHID>USASTCITY-6*</TAXSCHID>
            <TXSCHSRC>2</TXSCHSRC>
            <BSIVCTTL>0</BSIVCTTL>
            <FRTSCHID></FRTSCHID>
            <FRTTXAMT>0.00000</FRTTXAMT>
            <ORFRTTAX>0.00000</ORFRTTAX>
            <FRGTTXBL>3</FRGTTXBL>
            <MSCSCHID></MSCSCHID>
            <MSCTXAMT>0.00000</MSCTXAMT>
            <ORMSCTAX>0.00000</ORMSCTAX>
            <MISCTXBL>3</MISCTXBL>
            <BKTFRTAM>0.00000</BKTFRTAM>
            <ORBKTFRT>0.00000</ORBKTFRT>
            <BKTMSCAM>0.00000</BKTMSCAM>
            <ORBKTMSC>0.00000</ORBKTMSC>
            <BCKTXAMT>0.00000</BCKTXAMT>
            <OBTAXAMT>0.00000</OBTAXAMT>
            <TXBTXAMT>0.00000</TXBTXAMT>
            <OTAXTAMT>0.00000</OTAXTAMT>
            <TAXAMNT>0.00000</TAXAMNT>
            <ORTAXAMT>0.00000</ORTAXAMT>
            <ECTRX>0</ECTRX>
            <DOCAMNT>32000.00000</DOCAMNT>
            <ORDOCAMT>32000.00000</ORDOCAMT>
            <PYMTRCVD>0.00000</PYMTRCVD>
            <ORPMTRVD>0.00000</ORPMTRVD>
            <DEPRECVD>0.00000</DEPRECVD>
            <ORDEPRVD>0.00000</ORDEPRVD>
            <CODAMNT>0.00000</CODAMNT>
            <ORCODAMT>0.00000</ORCODAMT>
            <ACCTAMNT>32000.00000</ACCTAMNT>
            <ORACTAMT>32000.00000</ORACTAMT>
            <SALSTERR></SALSTERR>
            <SLPRSNID></SLPRSNID>
            <UPSZONE></UPSZONE>
            <TIMESPRT>0</TIMESPRT>
            <PSTGSTUS>0</PSTGSTUS>
            <VOIDSTTS>0</VOIDSTTS>
            <ALLOCABY>1</ALLOCABY>
            <NOTEINDX>575.00000</NOTEINDX>
            <CURNCYID>Z-US$</CURNCYID>
            <CURRNIDX>1007</CURRNIDX>
            <RATETPID></RATETPID>
            <EXGTBLID></EXGTBLID>
            <XCHGRATE>0.0000000</XCHGRATE>
            <DENXRATE>0.0000000</DENXRATE>
            <EXCHDATE>1900-01-01T00:00:00</EXCHDATE>
            <TIME1>1900-01-01T00:00:00</TIME1>
            <RTCLCMTD>0</RTCLCMTD>
            <MCTRXSTT>0</MCTRXSTT>
            <TRXSORCE></TRXSORCE>
            <COMMNTID></COMMNTID>
            <REFRENCE></REFRENCE>
            <POSTEDDT>1900-01-01T00:00:00</POSTEDDT>
            <PTDUSRID></PTDUSRID>
            <USER2ENT>sa</USER2ENT>
            <CREATDDT>2012-11-03T00:00:00</CREATDDT>
            <MODIFDT>2012-11-03T00:00:00</MODIFDT>
            <Tax_Date>2017-04-12T00:00:00</Tax_Date>
            <APLYWITH>0</APLYWITH>
            <WITHHAMT>0.00000</WITHHAMT>
            <SHPPGDOC>0</SHPPGDOC>
            <CORRCTN>0</CORRCTN>
            <SIMPLIFD>0</SIMPLIFD>
            <CORRNXST>0</CORRNXST>
            <DOCNCORR></DOCNCORR>
            <SEQNCORR>0</SEQNCORR>
            <SALEDATE>1900-01-01T00:00:00</SALEDATE>
            <EXCEPTIONALDEMAND>0</EXCEPTIONALDEMAND>
            <Flags>0</Flags>
            <BackoutTradeDisc>0.00000</BackoutTradeDisc>
            <OrigBackoutTradeDisc>0.00000</OrigBackoutTradeDisc>
            <GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
            <INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
            <INTEGRATIONID></INTEGRATIONID>
            <Commissions/>
            <Distribution>
                <SOPTYPE>3</SOPTYPE>
                <SOPNUMBE>TEST01</SOPNUMBE>
                <SEQNUMBR>16384</SEQNUMBR>
                <DISTTYPE>2</DISTTYPE>
                <DistRef></DistRef>
                <ACTINDX>6</ACTINDX>
                <DEBITAMT>32000.00000</DEBITAMT>
                <ORDBTAMT>32000.00000</ORDBTAMT>
                <CRDTAMNT>0.00000</CRDTAMNT>
                <ORCRDAMT>0.00000</ORCRDAMT>
                <CURRNIDX>1007</CURRNIDX>
                <TRXSORCE></TRXSORCE>
                <POSTED>0</POSTED>
            </Distribution>
            <Distribution>
                <SOPTYPE>3</SOPTYPE>
                <SOPNUMBE>TEST01</SOPNUMBE>
                <SEQNUMBR>32768</SEQNUMBR>
                <DISTTYPE>1</DISTTYPE>
                <DistRef></DistRef>
                <ACTINDX>113</ACTINDX>
                <DEBITAMT>0.00000</DEBITAMT>
                <ORDBTAMT>0.00000</ORDBTAMT>
                <CRDTAMNT>32000.00000</CRDTAMNT>
                <ORCRDAMT>32000.00000</ORCRDAMT>
                <CURRNIDX>1007</CURRNIDX>
                <TRXSORCE></TRXSORCE>
                <POSTED>0</POSTED>
            </Distribution>
            <Payments/>
            <Holds/>
            <UserDefined/>
            <Deposit/>
            <Notes/>
            <Line>
                <SOPNUMBE>TEST01</SOPNUMBE>
                <SOPTYPE>3</SOPTYPE>
                <CMPNTSEQ>0</CMPNTSEQ>
                <LNITMSEQ>16384</LNITMSEQ>
                <ITEMNMBR>1-A3261A</ITEMNMBR>
                <ITEMDESC>Multi-Core Processor</ITEMDESC>
                <NONINVEN>0</NONINVEN>
                <DROPSHIP>0</DROPSHIP>
                <UOFM>Each</UOFM>
                <LOCNCODE>WAREHOUSE</LOCNCODE>
                <UNITCOST>16000.00000</UNITCOST>
                <ORUNTCST>16000.00000</ORUNTCST>
                <UNITPRCE>32000.00000</UNITPRCE>
                <ORUNTPRC>32000.00000</ORUNTPRC>
                <XTNDPRCE>32000.00000</XTNDPRCE>
                <OXTNDPRC>32000.00000</OXTNDPRC>
                <REMPRICE>32000.00000</REMPRICE>
                <OREPRICE>32000.00000</OREPRICE>
                <EXTDCOST>16000.00000</EXTDCOST>
                <OREXTCST>16000.00000</OREXTCST>
                <MRKDNAMT>0.00000</MRKDNAMT>
                <ORMRKDAM>0.00000</ORMRKDAM>
                <MRKDNPCT>0</MRKDNPCT>
                <MRKDNTYP>0</MRKDNTYP>
                <INVINDX>18</INVINDX>
                <CSLSINDX>137</CSLSINDX>
                <SLSINDX>113</SLSINDX>
                <MKDNINDX>128</MKDNINDX>
                <RTNSINDX>449</RTNSINDX>
                <INUSINDX>27</INUSINDX>
                <INSRINDX>18</INSRINDX>
                <DMGDINDX>141</DMGDINDX>
                <ITMTSHID></ITMTSHID>
                <IVITMTXB>2</IVITMTXB>
                <BKTSLSAM>0.00000</BKTSLSAM>
                <ORBKTSLS>0.00000</ORBKTSLS>
                <TAXAMNT>0.00000</TAXAMNT>
                <ORTAXAMT>0.00000</ORTAXAMT>
                <TXBTXAMT>0.00000</TXBTXAMT>
                <OTAXTAMT>0.00000</OTAXTAMT>
                <BSIVCTTL>0</BSIVCTTL>
                <TRDISAMT>0.00000</TRDISAMT>
                <ORTDISAM>0.00000</ORTDISAM>
                <DISCSALE>0.00000</DISCSALE>
                <ORDAVSLS>0.00000</ORDAVSLS>
                <QUANTITY>1.00000</QUANTITY>
                <ATYALLOC>1.00000</ATYALLOC>
                <QTYINSVC>0.00000</QTYINSVC>
                <QTYINUSE>0.00000</QTYINUSE>
                <QTYDMGED>0.00000</QTYDMGED>
                <QTYRTRND>0.00000</QTYRTRND>
                <QTYONHND>0.00000</QTYONHND>
                <QTYCANCE>0.00000</QTYCANCE>
                <QTYCANOT>0.00000</QTYCANOT>
                <QTYONPO>0.00000</QTYONPO>
                <QTYORDER>1.00000</QTYORDER>
                <QTYPRBAC>0.00000</QTYPRBAC>
                <QTYPRBOO>0.00000</QTYPRBOO>
                <QTYPRINV>0.00000</QTYPRINV>
                <QTYPRORD>0.00000</QTYPRORD>
                <QTYPRVRECVD>0.00000</QTYPRVRECVD>
                <QTYRECVD>0.00000</QTYRECVD>
                <QTYREMAI>1.00000</QTYREMAI>
                <QTYREMBO>0.00000</QTYREMBO>
                <QTYTBAOR>0.00000</QTYTBAOR>
                <QTYTOINV>1.00000</QTYTOINV>
                <QTYTORDR>0.00000</QTYTORDR>
                <QTYFULFI>1.00000</QTYFULFI>
                <QTYSLCTD>1.00000</QTYSLCTD>
                <QTYBSUOM>1.00000</QTYBSUOM>
                <EXTQTYAL>0.00000</EXTQTYAL>
                <EXTQTYSEL>0.00000</EXTQTYSEL>
                <ReqShipDate>2017-04-12T00:00:00</ReqShipDate>
                <FUFILDAT>2017-04-12T00:00:00</FUFILDAT>
                <ACTLSHIP>2017-04-12T00:00:00</ACTLSHIP>
                <SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
                <SALSTERR></SALSTERR>
                <SLPRSNID></SLPRSNID>
                <PRCLEVEL>RETAIL</PRCLEVEL>
                <COMMNTID></COMMNTID>
                <BRKFLD1>0</BRKFLD1>
                <BRKFLD2>0</BRKFLD2>
                <BRKFLD3>0</BRKFLD3>
                <CURRNIDX>1007</CURRNIDX>
                <TRXSORCE></TRXSORCE>
                <ORGSEQNM>0</ORGSEQNM>
                <ITEMCODE></ITEMCODE>
                <PURCHSTAT>1</PURCHSTAT>
                <DECPLQTY>1</DECPLQTY>
                <DECPLCUR>3</DECPLCUR>
                <ODECPLCU>3</ODECPLCU>
                <QTYTOSHP>0.00000</QTYTOSHP>
                <XFRSHDOC>0</XFRSHDOC>
                <EXCEPTIONALDEMAND>0</EXCEPTIONALDEMAND>
                <TAXSCHID>USASTCITY-6*</TAXSCHID>
                <TXSCHSRC>2</TXSCHSRC>
                <PRSTADCD>WAREHOUSE</PRSTADCD>
                <ShipToName>Aaron Fitz Electrical</ShipToName>
                <CNTCPRSN>Bob Fitz</CNTCPRSN>
                <ADDRESS1>11403 45 St. South</ADDRESS1>
                <ADDRESS2></ADDRESS2>
                <ADDRESS3></ADDRESS3>
                <CITY>Chicago</CITY>
                <STATE>IL</STATE>
                <ZIPCODE>60603-0776</ZIPCODE>
                <COUNTRY>USA</COUNTRY>
                <PHONE1>31255501020000</PHONE1>
                <PHONE2>31255501020000</PHONE2>
                <PHONE3></PHONE3>
                <FAXNUMBR>31255501020000</FAXNUMBR>
                <Flags>0</Flags>
                <BackoutTradeDisc>0.00000</BackoutTradeDisc>
                <OrigBackoutTradeDisc>0.00000</OrigBackoutTradeDisc>
                <GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
                <INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
                <INTEGRATIONID></INTEGRATIONID>
                <CONTNBR></CONTNBR>
                <CONTLNSEQNBR>0.00000</CONTLNSEQNBR>
                <CONTSTARTDTE>1900-01-01T00:00:00</CONTSTARTDTE>
                <CONTENDDTE>1900-01-01T00:00:00</CONTENDDTE>
                <CONTITEMNBR></CONTITEMNBR>
                <CONTSERIALNBR></CONTSERIALNBR>
                <LineTax/>
            </Line>
        </SO_Trans>
    </eConnect>
</root>

 

 

Related Articles

... and you 'll find more on the eConnect Menu

 

 

 

Imports Microsoft.Dynamics.GP.eConnect
Imports Microsoft.Dynamics.GP.eConnect.Serialization
Imports System.IO
Imports System.Xml
Imports System.Xml.Serialization
 
Public Class RetrieveSalesTransaction
    Public Sub retrieveSalesTransaction()
        'Instantiate an eConnectMethods object
        Dim eConnectObject As New eConnectMethods
 
        '**Create an eConnect requestor document that specifies a single customer**
        'Create the requestor node
        Dim myRequest As New eConnectOut()
        'note: the properties of eConnectOut are documented here: http://msdn.microsoft.com/en-us/library/ff623993.aspx
        With myRequest
            .DOCTYPE = "Sales_Transaction"
            .OUTPUTTYPE = 2
            .INDEX1FROM = "INVS3041"
            .INDEX1TO = "INVS3041"
            .INDEX2FROM = "3"
            .INDEX2TO = "3"
            .FORLIST = 1
            '.WhereClause = "SOPNUMBE='INVS3041' AND SOPTYPE=3"
        End With
 
        'Create the requestor schema document type
        'Since the eConnect document requires an array, create an
        'array of RQeConnectOutType
        Dim econnectOutType() As RQeConnectOutType = New RQeConnectOutType(0) {New RQeConnectOutType}
        econnectOutType(0).eConnectOut = myRequest
 
        'Create the eConnect document type
        Dim eConnectDoc As New eConnectType()
        eConnectDoc.RQeConnectOutType = econnectOutType
 
        '**Serialize the eConnect document**
        'Create a memory stream for the serialized eConnect document
        Dim memStream As New MemoryStream()
 
        'Create an Xml Serializer and serialize the eConnect document
        'to the memory stream
        Dim serializer As New XmlSerializer(GetType(eConnectType))
        serializer.Serialize(memStream, eConnectDoc)
 
        'Reset the position property to the start of the buffer
        memStream.Position = 0
 
        '**Load the serialized Xml into an Xml document**
        Dim xmldoc As New XmlDocument()
        xmldoc.Load(memStream)
 
        'Create an eConnect connection string
        Dim connectionString As String
        connectionString = "data source=localhost; initial catalog=TWO;integrated security=SSPI; persist security info=False;packet size=4096"
 
        'Retrieve the specified document
        Dim oeConnectMethods As New eConnectMethods
 
        Dim SOPDoc = oeConnectMethods.GetEntity(connectionString, xmldoc.OuterXml)
 
    End Sub
 
End Class

                       


RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences