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

 
Accountable Forms Printer setup
We were recently tasked with setting up Accountable Forms Printer and writing a replacement SOP invoice. We had some difficulties with the parameters on the report, and had to work with support. They were very helpful, and provided the resolution below.                       
ddf_Address

This is our generic address function that we call into each report that needs an address block.

The goal here is RAD - Rapid Application Development. If we write a function once we blog it here... so it doesn't have to be written again.

Use it like this:

select
        h.sopnumbe,
        dbo.ddf_Address(h.CUSTNAME, SHIPTO.CNTCPRSN, SHIPTO.ADDRESS1, SHIPTO.ADDRESS2, SHIPTO.ADDRESS3, SHIPTO.CITY, SHIPTO.STATE, SHIPTO.ZIP) as address
    from sop10100 h
        join rm00102 SHIPTO on SHIPTO.custnmbr = h.custnmbr and SHIPTO.ADRSCODE = h.PRSTADCD

 
eConnect - RMApplyType

This is a complete working example of the RMApply eConnect transaction
SSRS - Handling an ALL parameter

I handled a support call today to help a developer handle a parameter that was in a dropdown list that had an 'All' at the top of it. Note that this is not the 'multi select' functionality that SSRS supports, just a dropdown list that has the word 'All' at the top. The screen shot below shows how to handle this issue.
Function to get the total number of logged on users in GP

Hi all,

Is there a global function in GP to get the total number of users logged on without having to actually look at the tables?

Cheers
SOP Transaction Entry Error

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

I have a scenario and am not sure what I am doing wrong here:

 

Client has PO with markdowns and trade discounts. I am calculating the following:

 

At the detail level made the line extended amount XTNDPRCE  = [Qty* (price-markdown) ] . At the header level made SUBTOTAL node be the sum of the calculated line extended amount and the TRDISAMT is the total discount not including the markdown.

 

I am then receiving an eConnect error stating:

 

Error Number = 67 Stored Procedure= taSopHdrIvcInsert Error Description = Subtotal does not match the line item totals Node Identifier Parameters: taSopHdrIvcInsert SOPNUMBE = STORD0017411 SOPTYPE = 2 Related Error Code Parameters for Node : taSopHdrIvcInsert SUBTOTAL = 17219.13 <taSopHdrIvcInsert> <SOPTYPE>2</SOPTYPE> <DOCID>STANDARD</DOCID> <TRDISAMT>692.22</TRDISAMT> <SUBTOTAL>17219.13</SUBTOTAL>

 

The subtotal matches the sum of the extended price.

 

I have attached the XML I am trying to pass to eConnect. If you can give me some insight, I would appreciate it.

<?xml version="1.0" encoding="UTF-8"?>
<eConnect xmlns:dt="urn:schemas-microsoft-com:datatypes">
    <SOPTransactionType>
        <eConnectProcessInfo>
            <ConnectionString>Data Source=JSOMAAPP01;Initial Catalog=TEST;Persist Security Info=False;Integrated Security=SSPI</ConnectionString>
            <EConnectProcsRunFirst>True</EConnectProcsRunFirst>
        </eConnectProcessInfo>
        <taSopHdrIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <DOCID>STANDARD</DOCID>
            <TRDISAMT>692.22</TRDISAMT>
            <SUBTOTAL>17219.13</SUBTOTAL>
            <SHIPMTHD>COMMON CARRIER</SHIPMTHD>
            <SOPNUMBE>STORD0017412</SOPNUMBE>
            <LOCNCODE>PLANT1</LOCNCODE>
            <DOCDATE>02/06/2014</DOCDATE>
            <CUSTNMBR>11860</CUSTNMBR>
            <CSTPONBR>83781</CSTPONBR>
            <BACHNUMB>EDI-02/06/14-10</BACHNUMB>
            <ORDRDATE>10/14/2013</ORDRDATE>
            <PRSTADCD>SPRINGFIELD</PRSTADCD>
            <COMMENT_1>b/h Mon del Wed</COMMENT_1>
        </taSopHdrIvcInsert>
        <taSopUserDefined>
            <SOPTYPE>2</SOPTYPE>
            <SOPNUMBE>STORD0017412</SOPNUMBE>
            <USRDAT01>10/28/2013</USRDAT01>
            <USERDEF1></USERDEF1>
            <USERDEF2></USERDEF2>
            <USRDEF03></USRDEF03>
            <USRDEF04></USRDEF04>
            <USRDEF05>287</USRDEF05>
        </taSopUserDefined>
        <taSopLineIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <SOPNUMBE>STORD0017412</SOPNUMBE>
            <CUSTNMBR>11860</CUSTNMBR>
            <DOCDATE>02/06/2014</DOCDATE>
            <ITEMNMBR>22301</ITEMNMBR>
            <LOCNCODE>PLANT1</LOCNCODE>
            <UNITPRCE>21.97</UNITPRCE>
            <XTNDPRCE>2768.22</XTNDPRCE>
            <QUANTITY>126.00</QUANTITY>
            <MRKDNAMT>0</MRKDNAMT>
            <LNITMSEQ>16384</LNITMSEQ>
            <DOCID>STANDARD</DOCID>
            <PRSTADCD>SPRINGFIELD</PRSTADCD>
            <UpdateIfExists>1</UpdateIfExists>
            <UOFM>case</UOFM>
            <USRDEFND3>1</USRDEFND3>
        </taSopLineIvcInsert>
        <taSopLineIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <SOPNUMBE>STORD0017412</SOPNUMBE>
            <CUSTNMBR>11860</CUSTNMBR>
            <DOCDATE>02/06/2014</DOCDATE>
            <ITEMNMBR>22300</ITEMNMBR>
            <LOCNCODE>PLANT1</LOCNCODE>
            <UNITPRCE>21.97</UNITPRCE>
            <XTNDPRCE>5536.44</XTNDPRCE>
            <QUANTITY>252.00</QUANTITY>
            <MRKDNAMT>0</MRKDNAMT>
            <LNITMSEQ>32768</LNITMSEQ>
            <DOCID>STANDARD</DOCID>
            <PRSTADCD>SPRINGFIELD</PRSTADCD>
            <UpdateIfExists>1</UpdateIfExists>
            <UOFM>case</UOFM>
            <USRDEFND3>2</USRDEFND3>
        </taSopLineIvcInsert>
        <taSopLineIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <SOPNUMBE>STORD0017412</SOPNUMBE>
            <CUSTNMBR>11860</CUSTNMBR>
            <DOCDATE>02/06/2014</DOCDATE>
            <ITEMNMBR>22310</ITEMNMBR>
            <LOCNCODE>PLANT1</LOCNCODE>
            <UNITPRCE>21.97</UNITPRCE>
            <XTNDPRCE>2768.22</XTNDPRCE>
            <QUANTITY>126.00</QUANTITY>
            <MRKDNAMT>0</MRKDNAMT>
            <LNITMSEQ>49152</LNITMSEQ>
            <DOCID>STANDARD</DOCID>
            <PRSTADCD>SPRINGFIELD</PRSTADCD>
            <UpdateIfExists>1</UpdateIfExists>
            <UOFM>case</UOFM>
            <USRDEFND3>3</USRDEFND3>
        </taSopLineIvcInsert>
        <taSopLineIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <SOPNUMBE>STORD0017412</SOPNUMBE>
            <CUSTNMBR>11860</CUSTNMBR>
            <DOCDATE>02/06/2014</DOCDATE>
            <ITEMNMBR>22324</ITEMNMBR>
            <LOCNCODE>PLANT1</LOCNCODE>
            <UNITPRCE>21.97</UNITPRCE>
            <XTNDPRCE>2768.22</XTNDPRCE>
            <QUANTITY>126.00</QUANTITY>
            <MRKDNAMT>0</MRKDNAMT>
            <LNITMSEQ>65536</LNITMSEQ>
            <DOCID>STANDARD</DOCID>
            <PRSTADCD>SPRINGFIELD</PRSTADCD>
            <UpdateIfExists>1</UpdateIfExists>
            <UOFM>case</UOFM>
            <USRDEFND3>4</USRDEFND3>
        </taSopLineIvcInsert>
        <taSopLineIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <SOPNUMBE>STORD0017412</SOPNUMBE>
            <CUSTNMBR>11860</CUSTNMBR>
            <DOCDATE>02/06/2014</DOCDATE>
            <ITEMNMBR>22307</ITEMNMBR>
            <LOCNCODE>PLANT1</LOCNCODE>
            <UNITPRCE>21.97</UNITPRCE>
            <XTNDPRCE>1384.11</XTNDPRCE>
            <QUANTITY>63.00</QUANTITY>
            <MRKDNAMT>0</MRKDNAMT>
            <LNITMSEQ>81920</LNITMSEQ>
            <DOCID>STANDARD</DOCID>
            <PRSTADCD>SPRINGFIELD</PRSTADCD>
            <UpdateIfExists>1</UpdateIfExists>
            <UOFM>case</UOFM>
            <USRDEFND3>5</USRDEFND3>
        </taSopLineIvcInsert>
        <taSopLineIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <SOPNUMBE>STORD0017412</SOPNUMBE>
            <CUSTNMBR>11860</CUSTNMBR>
            <DOCDATE>02/06/2014</DOCDATE>
            <ITEMNMBR>22944</ITEMNMBR>
            <LOCNCODE>PLANT1</LOCNCODE>
            <UNITPRCE>26.44</UNITPRCE>
            <XTNDPRCE>1182.72</XTNDPRCE>
            <QUANTITY>48.00</QUANTITY>
            <MRKDNAMT>1.8</MRKDNAMT>
            <LNITMSEQ>98304</LNITMSEQ>
            <DOCID>STANDARD</DOCID>
            <PRSTADCD>SPRINGFIELD</PRSTADCD>
            <UpdateIfExists>1</UpdateIfExists>
            <UOFM>case</UOFM>
            <USRDEFND3>6</USRDEFND3>
        </taSopLineIvcInsert>
        <taSopLineIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <SOPNUMBE>STORD0017412</SOPNUMBE>
            <CUSTNMBR>11860</CUSTNMBR>
            <DOCDATE>02/06/2014</DOCDATE>
            <ITEMNMBR>22721</ITEMNMBR>
            <LOCNCODE>PLANT1</LOCNCODE>
            <UNITPRCE>27.04</UNITPRCE>
            <XTNDPRCE>778.75</XTNDPRCE>
            <QUANTITY>30.00</QUANTITY>
            <LNITMSEQ>114688</LNITMSEQ>
            <DOCID>STANDARD</DOCID>
            <PRSTADCD>SPRINGFIELD</PRSTADCD>
            <UpdateIfExists>1</UpdateIfExists>
            <UOFM>case</UOFM>
            <USRDEFND3>7</USRDEFND3>
        </taSopLineIvcInsert>
    </SOPTransactionType>
</eConnect>

 
check batch hung up

hi, I have an issue with AP.    During printing of  a batch of 2 checks, they sent it to the wrong printer and interrupted the process.   Now there is a batch out there with 2 checks that are voided.  And we can’t clear out the batch.

These checks did not post as far as I can tell, and there is nothing in OPEN or HISTORY tables, just the PM10300 table and the sy00500 table.

The batch status from sy00500 shows “110 = Batch is printing computer checks.”.  We tried voiding, but it isn't clearing the batch out, and the 2 checks in pm10300 show voided already.

When I run the query

SELECT    *

FROM         PM30300

WHERE     (VCHRNMBR = '00000000000010526') OR

                     (VCHRNMBR = '00000000000010527')

It returns nothing,

Could you possibly give me some direction on what to do here?

 

 
Insert into CM20200 using SQL

Hello,

I am developing an application to move expenses from Sharepoint to CM20200.  The record saved in the CM20200 table but the transaction cannot be found using a smartlist builder or be posted.  Can someone help me with require fields or do I need to insert into another CM table? 

Thank you!

Mimi

 
Updating SOP Lines

From what I've read sounds like its hopeless to navigate through and update SOP Lines using SOPEntry.LineScroll. 

One suggestion was to save the sales trx, modify it and bring it back up on the sop entry window. 

1. Can I modify by writing to Dynamics.Forms.SopEntry.Tables?

2.Am able to save using Dynamics.Forms.SopEntry.SopEntry.SaveButton.RunValidate();

 

    but not sure how to bring existing document back up with window still open?

Thanks

 
Integration Manager Error - Simple ODBC setup

Trying to setup a simple ODBC source in Integration Manager. I have done this process before and have never had any problems. The ODBC DSN is setup and working properly. I have tables with data and views that will be used for the integration.

During IM simple ODBC get this message - No tables Exist - check your data source

I just tested some other simple ODBC's that had been working fine and every previous ODBC returns same message............

In other words all my ODBC's return 'no tables found' message (frustrating!!!)

 
eConnect POP Receiving with Lot Numbers

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

I keep getting this error when trying to submit an eConnect receipt. I can't seem to figure this one out.

Error Number = 3389 Stored Procedure= taPopRcptLineInsert Error Description = The Serial/Lot Quantity in the POP10330 does not match the Quantity Shipped in the POP10500

Node Identifier Parameters: taPopRcptLineInsert

Here is the xml that is being generated:

  <?xml version="1.0" ?>
- <eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
- <POPReceivingsType>
  <eConnectProcessInfo xsi:nil="true" />
  <taRequesterTrxDisabler_Items xsi:nil="true" />
  <taUpdateCreateItemRcd xsi:nil="true" />
  <taUpdateCreateVendorRcd xsi:nil="true" />
  <taCreateVendorAddress_Items xsi:nil="true" />
- <taPopRcptLotInsert_Items>
- <taPopRcptLotInsert>
  <POPRCTNM>RCT19564</POPRCTNM>
  <ITEMNMBR>ATT31AW197</ITEMNMBR>
  <SERLTNUM>200245</SERLTNUM>
  <SERLTQTY>8800.00</SERLTQTY>
  <RCPTLNNM>16384</RCPTLNNM>
  <LOCNCODE>TAYLOR</LOCNCODE>
  </taPopRcptLotInsert>
- <taPopRcptLotInsert>
  <POPRCTNM>RCT19564</POPRCTNM>
  <ITEMNMBR>ATT31AW197</ITEMNMBR>
  <SERLTNUM>200246</SERLTNUM>
  <SERLTQTY>8800.00</SERLTQTY>
  <RCPTLNNM>16384</RCPTLNNM>
  <LOCNCODE>TAYLOR</LOCNCODE>
  </taPopRcptLotInsert>
- <taPopRcptLotInsert>
  <POPRCTNM>RCT19564</POPRCTNM>
  <ITEMNMBR>ATT31AW197</ITEMNMBR>
  <SERLTNUM>200247</SERLTNUM>
  <SERLTQTY>8800.00</SERLTQTY>
  <RCPTLNNM>16384</RCPTLNNM>
  <LOCNCODE>TAYLOR</LOCNCODE>
  </taPopRcptLotInsert>
- <taPopRcptLotInsert>
  <POPRCTNM>RCT19564</POPRCTNM>
  <ITEMNMBR>ATT31AW197</ITEMNMBR>
  <SERLTNUM>200248</SERLTNUM>
  <SERLTQTY>8800.00</SERLTQTY>
  <RCPTLNNM>16384</RCPTLNNM>
  <LOCNCODE>TAYLOR</LOCNCODE>
  </taPopRcptLotInsert>
- <taPopRcptLotInsert>
  <POPRCTNM>RCT19564</POPRCTNM>
  <ITEMNMBR>ATT31AW197</ITEMNMBR>
  <SERLTNUM>200249</SERLTNUM>
  <SERLTQTY>8800.00</SERLTQTY>
  <RCPTLNNM>16384</RCPTLNNM>
  <LOCNCODE>TAYLOR</LOCNCODE>
  </taPopRcptLotInsert>
  </taPopRcptLotInsert_Items>
  <taPopRcptSerialInsert_Items xsi:nil="true" />
- <taPopRcptLineInsert_Items>
- <taPopRcptLineInsert>
  <POPTYPE>1</POPTYPE>
  <POPRCTNM>RCT19564</POPRCTNM>
  <PONUMBER>PO23249</PONUMBER>
  <ITEMNMBR>ATT31AW197</ITEMNMBR>
  <VENDORID>APP001</VENDORID>
  <QTYSHPPD>44000.00</QTYSHPPD>
  <AUTOCOST>1</AUTOCOST>
  <POLNENUM>16384</POLNENUM>
  <receiptdate>2014-02-21</receiptdate>
  </taPopRcptLineInsert>
  </taPopRcptLineInsert_Items>
  <taPopRcptMultiBin_Items xsi:nil="true" />
  <taPopRcptLineTaxInsert_Items xsi:nil="true" />
  <taPopRctUserDefined xsi:nil="true" />
  <taPopDistribution_Items xsi:nil="true" />
  <taAnalyticsDistribution_Items xsi:nil="true" />
- <taPopRcptHdrInsert>
  <POPRCTNM>RCT19564</POPRCTNM>
  <POPTYPE>1</POPTYPE>
  <VNDDOCNM>12345</VNDDOCNM>
  <receiptdate>2014-02-21</receiptdate>
  <BACHNUMB>RECLOG022114</BACHNUMB>
  <VENDORID>APP001</VENDORID>
  <VENDNAME>Appleton Papers, Inc.</VENDNAME>
  <PYMTRMID>2% - 30 Days, Net 31</PYMTRMID>
  <SHIPMTHD>PREPAID</SHIPMTHD>
  </taPopRcptHdrInsert>
  <taMdaUpdate_Items xsi:nil="true" />
  </POPReceivingsType>
  </eConnect>
updating freight in SOPEntry does not update distributions

The sample project provided by Microsoft - "EstimateFreight" - does NOT work for Invoices or Fulfillment Orders because the distribution table is not updated:

         Dynamics.Forms.SopEntry.SopEntry.LocalOriginatingFreightAmount.Value =
                Convert.ToDecimal(estimatedFreightString);

            // Force the validation of the new freight amount
            Dynamics.Forms.SopEntry.SopEntry.LocalOriginatingFreightAmount.RunValidate();
            Dynamics.Forms.SopEntry.SopEntry.LocalOriginatingFreightAmount.ForceValidate(true);

In my solution, tried adding a SOPEntry.PullFocus().  But it doesn't help.  Shouldn't it do all processing that would be done if I typed the freight in manually?

Note: the distribution problem occurs if there was an original value in Freight and its modified.  Seems OK if freight was 0.0 prior to writing.
Winforms - RadGrid - Force a GridViewTextBoxColumn to upper case
This simple code snippet will force a GridViewTextBoxColumn to upper case                       
Winforms - Move to the new row after data entry
While the user is entering data into the Winforms grid, it's often convenient to move the cursor back up to the AddNewRow line after each new rwo add. This code sample will show you how to do that                       
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