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

 
dd_PayrollHeader
Recently wrote a query for payroll header information - so this is one line per employee. The resulting query might not be exactly what I'll need on the next job, but this template will be a good starting place. If I use it again I'll continue to update it.
dd_PayrollDetail

Recently wrote a query for payroll detail information - so this is one line per employee/payroll code.

We've also added in header level withholding, direct deposit information, and comments

The resulting query might not be exactly what I'll need on the next job, but this template will be a good starting place. If I use it again I'll continue to update it.

New features
We're so proud to announce a pile of new features in the Dynamics Table Reference!

We'd love for you to click around and check it out.

  1. The Module page is more attractive, using the Telerik TileList control.
  2. The Module page now has a 'frequently used' area at the top
  3. The Module page now has two search features, Search by Table and Search by Field
  4. Removed some bugs from the Company db/Dynamics db functionality
  5. The Table page now has an icon next to the 'frequently used' tables.
  6. All the new tables and fields from GP 2016 have been imported.
  7. The Column page now has a 'join' column to help those new to Dynamics GP with the table that a field would join to. (example)
ddf_FormattedQty - Formatted Quantities using DECPLQTY
Many of the companies that we work at have items that have varied DECPLQTY - The items might use 0 decimal places for the quantity, but some will use 1, or 2, or more.

This is a very difficult concept for .NET controls to grasp, so usually we just return all five decimal places.

This function will correctly format a decimal quantity to the required number of decimal places, and optionally add in the commas.

The return will be a string.

EDITED 12/23/2016 comma handling for negative numbers
Opening up Dynamics GP forms from a web page

This article will demonstrate a technique that will show you how to open up Dynamics GP forms from a web page (think: SSRS)

It requires the GP client be open on the desktop, but if it's not you'll get a reasonable error dialog.

 
Get the text of all the triggers in a database
This is a pretty simple piece of code, it shows how to get the text of all the triggers in the database. I'm blogging it not for it's value as code, but I needed it today and I had to search for it. By blogging it I can put it on the 'SQL General' menu (see the link above) and I won't have to search for it again <smiles>
Reprint posted invoice
It is very easy to reprint a posted invoice.
SQL Maintenance Queries
I don't spend much time in the DBA role, I'm mainly a developer. But here are a couple of good maintenance queries, it'll help you get going faster.
Saving the Grid ScrollBar position
It's useful to be able to pop up another form and then rebind the grid on the main form; and not have the grid scroll bar reset to the top again. This code sample demos a technique to do that
Needing to update company address name

We are renaming our company, changing the company name was pretty easy, however we have over 500 location names. (Company Setup/ Company Address/ lookup) I need to change the name from X to Y so to speak.

I am looking for a way to change location name X to location name Y ( new location name) using SQL , would anyone be able to assist me?

 

Even if thats just the correct table name/ names

 

Thank you

 

Mark
Enter/Match Invoice

Hello All,

I am having trouble driving in an enter match invoice using web services.  Long story short, I have a PO with two lines for the same item number.  Both lines are received on the same receipt document.  When attempting to create the object, the exception returned is "Divide by zero error encountered."  Has anyone ever seen this?  

What makes it more interesting is that I can invoice the two lines separately on two invoices, created back to back.  

Any help is appreciated, even if someone can share some insight as to what division may be taking place when a PO is being invoiced.  Code snippets can be provided if anyone cares to see what I am doing.

Thank you in advance,

Mike
send in report

Al cambiar de office 2010 a office 365 ocurre que en los reportes se bloquea el campo de enviar a tal como se muestra en la imagen.

Pueden ayudarme con esto?
Drill thru from SSRS to Dynamics GP

I am working on an SSRS report for inventory transactions.  I have been requested to add to a Drill through on the Document number to the Dynamics GP Inventory Transaction inquiry window for the transaction selected.  I am aware that this functionality exists and have been working on the statement to insert into the URL field related to the Document number.  However I am not sure what the function should be to access the inventory transaction inquiry window nor am I sure of all of the parameters that need to be passed.  Any help would be greatly appreciated. 

Thank you,

Terri
Problem moving PO's to History

I recently took over management of a GP environment, and previously PO's were never moved.  I tried to go to Tools -> Routines -> Purchasing -> Remove Completed PO's.  I am getting an error: A REMOVE OPERATION ON TABLE 'POP_POLINE' CANNOT FIND THE TABLE.  I have tried check links and reconcile, but still getting the error.   Any advice?

 

Thanks
Creating a PO in Request Resolution

When clicking the OK button to create a PO in Request Resolution I am getting an error message that says...

Unhandled script exception:
Object has no reference.



EXCEPTION_CLASS_SCRIPT_BAD_PARAM
SCRIPT_CMD_DEREF_TABLE

 

And PO will not create...
Purchase Receipt Number (POPRCTNM) already exists in the POP10300 or POP30300 Tables

Error: Purchase Receipt Number (POPRCTNM) already exists in the POP10300 or POP30300 Tables

Revertir anulación

Hola, cuando un documento de de ventas (factura o Pedido) es anulado, existe algún proceso para revertir la anulación?

When a sales document (invoice or order ) is voided, there is a process to reverse the void?

 
Send Key in VB
Is it possible to send a CTRL+T in VBA and add to a button on a GP form?
eConnect Sales Order Schema Changes

Hello Everyone,

  I am currently looking for a list of schema changes to eConnect throughout each version(2010,2013,2015, ect).  I am unable to find a dedicated list regarding this information.

I created a C# application that successfully pushes Sales Orders to Great Plains 2013.  I am searching to see if this same application would work for a GP 2010 integration.

Any inquiries would be greatly appreciated.

Thanks,

-Falk0r
SQL update to fix historical information

I want to fix an account number in historical years (several year ago); what is the easier way to do that?  

Is it possible to update the Account Number via SQL?

Thanks,

Wen

 
SmartConnect: Getting errors 62 Stored Procedure= taSopHdrIvcInsert Error Description = Tax table detail does not equal the tax amount

Also getting errors:  795 Stored Procedure= taSopLineIvcTaxInsert Error Description = Tax detail already exists  Have tried all the online suggestions keep bouncing between these 2 errors.  I have tried both XML and CSV

Here is the XML that SmartConnect created

<?xml version="1.0"?>
<eConnect>
    <SOPTransactionType>
        <eConnectProcessInfo>
            <ConnectionString>data source=gpsql;initial catalog=TUSBC;integrated security=SSPI;persist security info=False;packet size=4096;Connect Timeout=10</ConnectionString>
        </eConnectProcessInfo>
        <taSopLineIvcInsert>
            <PHONE2> </PHONE2>
            <STATE>TX</STATE>
            <ITEMDESC>The Core Kit</ITEMDESC>
            <ShipToName>Terry Richards</ShipToName>
            <PHONE1>2142223333</PHONE1>
            <SOPNUMBE>TC160912101251717</SOPNUMBE>
            <ZIPCODE>75422</ZIPCODE>
            <ADDRESS1>100 Main Street</ADDRESS1>
            <CITY>Dallas</CITY>
            <SOPTYPE>3</SOPTYPE>
            <ADDRESS2> </ADDRESS2>
            <UOFM>EA</UOFM>
            <ITEMNMBR>TC0001</ITEMNMBR>
            <UpdateIfExists>0</UpdateIfExists>
            <QUANTITY>1</QUANTITY>
            <DOCDATE>2016-9-22</DOCDATE>
            <CURNCYID>Z-US$</CURNCYID>
            <DOCID>TCINV</DOCID>
            <UNITPRCE>134.99</UNITPRCE>
            <XTNDPRCE>134.99</XTNDPRCE>
            <LOCNCODE>USBC01</LOCNCODE>
            <COUNTRY>US</COUNTRY>
            <CUSTNMBR>TR2016I12J12517</CUSTNMBR>
            <TAXAMNT>10.8</TAXAMNT>
            <TAXSCHID>TEXAS</TAXSCHID>
            <LNITMSEQ>1</LNITMSEQ>
        </taSopLineIvcInsert>
        <taSopLineIvcTaxInsert_Items>
            <taSopLineIvcTaxInsert>
                <CUSTNMBR>TR2016I12J12517</CUSTNMBR>
                <LNITMSEQ>1</LNITMSEQ>
                <SOPTYPE>3</SOPTYPE>
                <SOPNUMBE>TC160912101251717</SOPNUMBE>
                <STAXAMNT>10.8</STAXAMNT>
                <SALESAMT>145.79</SALESAMT>
                <TAXDTLID>TEXAS</TAXDTLID>
                <TAXTYPE>0</TAXTYPE>
            </taSopLineIvcTaxInsert>
            <taSopLineIvcTaxInsert>
                <CUSTNMBR>TR2016I12J12517</CUSTNMBR>
                <LNITMSEQ>1</LNITMSEQ>
                <SOPTYPE>3</SOPTYPE>
                <SOPNUMBE>TC160912101251717</SOPNUMBE>
                <STAXAMNT>10.8</STAXAMNT>
                <SALESAMT>145.79</SALESAMT>
                <TAXDTLID>TEXAS</TAXDTLID>
                <TAXTYPE>0</TAXTYPE>
            </taSopLineIvcTaxInsert>
            <taSopLineIvcTaxInsert>
                <CUSTNMBR>TR2016I12J12517</CUSTNMBR>
                <LNITMSEQ>1</LNITMSEQ>
                <SOPTYPE>3</SOPTYPE>
                <SOPNUMBE>TC160912101251717</SOPNUMBE>
                <STAXAMNT>10.8</STAXAMNT>
                <SALESAMT>145.79</SALESAMT>
                <TAXDTLID>TEXAS</TAXDTLID>
                <TAXTYPE>0</TAXTYPE>
            </taSopLineIvcTaxInsert>
        </taSopLineIvcTaxInsert_Items>
        <taCreateSopPaymentInsertRecord>
            <DOCAMNT>145.79</DOCAMNT>
            <PYMTTYPE>6</PYMTTYPE>
            <SOPNUMBE>TC160912101251717</SOPNUMBE>
            <CUSTNAME>Terry Richards</CUSTNAME>
            <CARDNAME>VISA</CARDNAME>
            <EXPNDATE>2017-12-1</EXPNDATE>
            <SOPTYPE>3</SOPTYPE>
            <RCTNCCRD>4242</RCTNCCRD>
            <CUSTNMBR>TR2016I12J12517</CUSTNMBR>
            <AUTHCODE>ch_18sw7AE1245jPojbHADCWMWL</AUTHCODE>
            <DOCDATE>2016-9-22</DOCDATE>
            <DOCNUMBR>0000027</DOCNUMBR>
        </taCreateSopPaymentInsertRecord>
        <taSopDistribution>
            <CRDTAMNT>134.99</CRDTAMNT>
            <ACTINDX>3022</ACTINDX>
            <CUSTNMBR>TR2016I12J12517</CUSTNMBR>
            <SOPTYPE>3</SOPTYPE>
            <DistRef>The Core Purchase</DistRef>
            <SOPNUMBE>TC160912101251717</SOPNUMBE>
            <DISTTYPE>1</DISTTYPE>
            <DEBITAMT> </DEBITAMT>
        </taSopDistribution>
        <taSopHdrIvcInsert>
            <SOPNUMBE>TC160912101251717</SOPNUMBE>
            <CUSTNAME>Terry Richards</CUSTNAME>
            <ReqShipDate>2016-9-22</ReqShipDate>
            <DOCAMNT>145.79</DOCAMNT>
            <LOCNCODE>USBC01</LOCNCODE>
            <CITY>Dallas</CITY>
            <PHNUMBR2> </PHNUMBR2>
            <PRSTADCD>SHIPPING</PRSTADCD>
            <PRBTADCD>BILLING</PRBTADCD>
            <DOCID>TCINV</DOCID>
            <ADDRESS2> </ADDRESS2>
            <DOCDATE>2016-9-22</DOCDATE>
            <COUNTRY>US</COUNTRY>
            <REFRENCE>The Core Purchase</REFRENCE>
            <CUSTNMBR>TR2016I12J12517</CUSTNMBR>
            <BACHNUMB>TC2016092</BACHNUMB>
            <UpdateExisting>0</UpdateExisting>
            <ShipToName>Terry Richards</ShipToName>
            <ADDRESS1>100 Main Street</ADDRESS1>
            <PYMTRMID>NET00</PYMTRMID>
            <ZIPCODE>75422</ZIPCODE>
            <PHNUMBR1>2142223333</PHNUMBR1>
            <INVODATE>2016-9-22</INVODATE>
            <PYMTRCVD>145.79</PYMTRCVD>
            <BatchCHEKBKID>GFFROST</BatchCHEKBKID>
            <CURNCYID>Z-US$</CURNCYID>
            <SOPTYPE>3</SOPTYPE>
            <STATE>TX</STATE>
            <TAXSCHID>TEXAS - SALES</TAXSCHID>
            <FRTTXAMT>0.00</FRTTXAMT>
            <MISCAMNT>0.00</MISCAMNT>
            <MISCTBLE>3</MISCTBLE>
            <MSCTXAMT>0.00</MSCTXAMT>
            <FREIGTBLE>3</FREIGTBLE>
            <FREIGHT>0.00</FREIGHT>
            <DEFTAXSCHDS>0</DEFTAXSCHDS>
            <TAXAMNT>10.8</TAXAMNT>
            <SUBTOTAL>134.99</SUBTOTAL>
        </taSopHdrIvcInsert>
    </SOPTransactionType>
</eConnect>

 

 

Any assistance will be greatly appreciated.   Sandy
Rounding

Hi,

I am working with a company that is looking for solution for investor allocation using Dynamic GP 2013.  The one solution I found is variable allocation using unit accounts.  However, the system rounds the calculation to the 5th decimal place and then adjust any difference in the last account.  Is there a way to remove the 5 decimal place set in the variable allocation? 

Thanks,
Wen

 
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