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

 
Format a field with a currency amount

This is a quick little adaptation that shows how to add a currency symbol to an amount in Dynamics GP/SQL. It's really only useful in a multicurrency situation, but it's worth remembering that it's here.

Just look up, and remember what menu it's on so you can find it later.
Find table and field a particular value reside
There are times (when we create a report in Crystal or SSRS or run an update statement in SQL), we want to know in which table and field a particular value resides. It can be a tedious and boring process to sift through all the table & field suspects in the DB.

Let's take an example. I want to update all the CHECKBOOK ID fields for all the Customers in the DB. I want to run an UPDATE statement such as:

UPDATE [TABLE] SET [XXXXX] WHERE CHECKBOOKID = 'BANKOFNY' and CUSTNMBR ='ABCD001'

But I do not know what fields in the RM00100 needs to be updated.
Using Microsoft Query and a View
This article details step by step how to view a SQL View object in an Excel Refreshable report. 
Suppress the results of a stored procedure

Today I need to call an existing stored procedure that inserts a line into a table. This proc always returns the RowID of the inserted line. 

In the place where I'm calling it I can't have that result set, so I need to suppress it. 

Had to search to find the answer, I'll blog it here so I don't lose it again. 

 
GP Business Alert reports

Does anybody know how to modify the Business Alerts reports in GP for portrait/landscape, column sizes, data formatting?

It creates a .TXT file which looks great in Notepad, but when you print it, not so great - columns don't align, text wraps due to data length, etc.

 
GP10 SmartList shows ú for GBP & Ç for EURO when exported to Excel

Hello

We are on GP10. Recently, the users are complaining that the smartlist is running very slow, so is the export to excel process. So I added the SmartlistEnhancedExcelExport=TRUE to the dex.ini file.

Beautiful...smartlist is super fast now.

However, when exported to Excel, the currency symbol is showing as Ç1,000.00 for EURO and ú3,150.00 for GBP.

If I remove that clause from the Dex.ini, the smartlist will slow down again.

Any suggestions/views folks?

Thanks

Paul Chacko
eConnect Shipment Invoice not calculating Credit Distribution with Tax

I am sending a Shipment Invoice to Great Plains using eConnect.  This is completed successfully; however, in SOME cases, the Credit Distribution is not being summed with the subtotal.

My current guess is that this is a configuration issue within Great Plains.  I am able to successfully push Shipment Invoices on other companies, but in a particular company, it pushes but does not calculate the Credit Distribution.

Any thoughts on this matter would be greatly appreciated.  

eConnect Example

<?xml version="1.0" encoding="utf-8"?>
    <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 xsi:nil="true" />
        <taPopRcptSerialInsert_Items xsi:nil="true" />
        <taPopRcptLineInsert_Items>
            <taPopRcptLineInsert>
                <POPTYPE>3</POPTYPE>
                <POPRCTNM>RCT2204</POPRCTNM>
                <PONUMBER>PO2136</PONUMBER>
                <ITEMNMBR>TEST</ITEMNMBR>
                <VENDORID>ADVANCED0001</VENDORID>
                <VNDITNUM>TEST</VNDITNUM>
                <UNITCOST>1000</UNITCOST>
                <EXTDCOST>2000</EXTDCOST>
                <NONINVEN>1</NONINVEN>
                <QTYSHPPD>2</QTYSHPPD>
                <QTYINVCD>2</QTYINVCD>
            </taPopRcptLineInsert>
        </taPopRcptLineInsert_Items>
        <taPopRcptMultiBin_Items xsi:nil="true" />
        <taPopRcptLineTaxInsert_Items>
            <taPopRcptLineTaxInsert>
                <VENDORID>ADVANCED0001</VENDORID>
                <POPRCTNM>RCT2204</POPRCTNM>
                <TAXDTLID>USEXMT+PSONO</TAXDTLID>
                <TAXTYPE>0</TAXTYPE>
                <TAXAMNT>250</TAXAMNT>
                <TAXPURCH>2000</TAXPURCH>
                <TOTPURCH>2000</TOTPURCH>
            </taPopRcptLineTaxInsert>
            <taPopRcptLineTaxInsert>
                <VENDORID>ADVANCED0001</VENDORID>
                <POPRCTNM>RCT2204</POPRCTNM>
                <TAXDTLID>AUSSTE+PS0N0</TAXDTLID>
                <TAXTYPE>0</TAXTYPE>
                <TAXAMNT>850</TAXAMNT>
                <TAXPURCH>2000</TAXPURCH>
                <TOTPURCH>2000</TOTPURCH>
            </taPopRcptLineTaxInsert>
        </taPopRcptLineTaxInsert_Items>
        <taPopRctUserDefined xsi:nil="true" />
        <taPopDistribution_Items xsi:nil="true" />
        <taAnalyticsDistribution_Items xsi:nil="true" />
        <taPopRcptHdrInsert>
            <POPRCTNM>RCT2204</POPRCTNM>
            <POPTYPE>3</POPTYPE>
            <VNDDOCNM>INV1733</VNDDOCNM>
            <receiptdate>4/4/2017 12:00:00 AM</receiptdate>
            <BACHNUMB>Batch 1</BACHNUMB>
            <VENDORID>ADVANCED0001</VENDORID>
            <SUBTOTAL>2000</SUBTOTAL>
            <TRDISAMT>0</TRDISAMT>
            <TAXAMNT>1100</TAXAMNT>
            <DISAVAMT>0</DISAVAMT>
            <TAXSCHID>ALL DETAILS</TAXSCHID>
            <USINGHEADERLEVELTAXES>1</USINGHEADERLEVELTAXES>
            <CURNCYID>Z-UK</CURNCYID>
        </taPopRcptHdrInsert>
        <taMdaUpdate_Items xsi:nil="true" />
    </POPReceivingsType>
</eConnect>

 
default GL account for RTNSINDX in SOP
Hey guys was wondering as a common practice shall I default return inventory GL account ([RTNSINDX]) to "inventory" or "inventory offset"? Currently in our system it's default to "inventory offset" but seems to me it makes more sense to default it to "inventory"? Thanks a lot!
eConnect error suddenly appearing

We rn a third party Procurement system with our GP system and are suddenly receiving the following errors.

Seeing as this is eConnect I wanted to ask the experts their opinions of the root cause of this.

This latest error is "Unable to increment next document number from Purchasing Setup Table (POP40100) after 1000 attempts"

This error actually is being presented by the procurement application (Ariett)

 

Any ideas what the root cause could be?

The POP40100 table has one row listed

 

 

 
inventory turnover ratio or inventory stock days
I'm trying to create a sql script to help measure inventory performance. Some measures I'm thinking about is inventory turnover ratio or inventory stock days (average days one item sits in stock). Has anyone done similar things in GP before? I have scripts to calculate the inventory aging on a specific day, but has yet to figure out the ratios. Any thought or comment is highly appreciated. Many thanks!
Is it possible to mass change Customer Account Maintenance Field Checkbook ID

We have had to create a new checkbook id, however the majority of the customers in a particular company are setup as Cash account from Check book (button selected) and pointing to the old default checkbook and not the new checkbook I set as default.

I was looking for the table to try and update to the new default checkbook I setup and applied in Sales/ Receivables setup however I am not finding this.

Is it even possible or will this need to be manually cleaned up?

 

Thank you

 

Mark

 
Testing and Deploying a Changed GP form

I am working in the GP 2010 Field Service module, making changes to a stock form - the SVC_Return_Authorization form used by RMA Entry/Update.

I do not know how to make the print function use my version instead of the stock report form.

 

Any ideas out there?

 

Thank you.

 

 
RM00103 ONORDAMT

We have a customer that has an OnOrdAmt in RM00103 that is way out of whack - it's about 47K and outstanding orders only add up to 6K.

What can cause this balance to get out of whack and is there a GP function that will allow the user to reset this field?

Do not want to resort to updating the table manually - not best practice, obviously.

 

 

 

 
Ready to invoice

My client wants to create three separate invoices... one for goods purchased, one for loans, and one for a brand marketing fund.  They invoice multiple clients and have a custom reporting solution that is looking directly at the database.    

 

 The current report lists everything and they want to remove the loans and brand marketing and create separate invoices.  What is the best way to implement multiple invoice options from within Dynamics GP?   

Also... is there an easy way to flag an invoice as ready to print?

 

Thank you for any help you can offer!
Change Item Price and add new order line via SQL?
Is there a proper way to change the price on a specific line in the SOP10200 table and perhaps add a new line to an order via direct SQL code?
Error when trying to do Sales Transaction Entry in GP Sales

I have a user who gets a pop-up error "Your previous transaction-level posting session has not finished processing. Please allow time for it to finish..."  (Please see the screen shot below.)

This error appears when he attempts to use the Sales Transaction Entry function in GP  Sales.

I only see his username in the DYNAMICS  ACTIVITY table - one entry showing he is logged in, but he does not appear in SY00800 or SY05000 tables, where I usually have issues with users "stuck" in batches, etc.

Does anybody know which table he is stuck in?    He is not posting any transactions, has logged out of GP many times, rebooted his system a few times, but nothing helps.  My temporary solution was to assign him a new username so that he can at least view orders.

 

 

 

 

 
GL Recivable transctions
What determines when my receivables move from the work to open GL  tables?
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