DynDeveloper.com - Your Dynamics GP Questions, Answered

	SOP Scripts
	eConnect
	taSopDeleteDocumentWrapper
	taSopLineDeleteWrapper
	taSOPLineIvcInsert Wrapper
	taSopLineIvcInsertComponent
	taSOPLotAuto - complete procedure
	Functions
	ddf_Address
	ddf_AllHoldsOnASOPDoc
	General
	Getting Email addresses from Dynamics GP
	Move a SOP transaction from open to history
	Posting Sequences
	SOP40200 'Allow' fields query
	sopGetIDNumber
	sopTrxDeleteWork
	Unvoid a SOP document
	Stored Procedures
	dd_SOP10102_UPD
	dd_SOPGetNextDocNumber/ sopGetIDNumber.
	dd_SOPPayment_INS
	dd_SOPRound - Round a SOP Multicurrency document to two decimal places
	dd_SOPTotal
	dd_SOPTotalTaxes
	dd_taSopVoidDocumentWrapper

Archives

Import a CSV file and loop through it, doing work

Today's task is to open a CSV file using the OpenFileDialog and to loop through it line by line, doing work.

This piece of code will show that technique

RMSalespersonMasterType example

This piece of code will demonstrate how to code a salesperson insert using eConnect. We demonstrate with the RMSalespersonMasterType and taCreateSalesperson schemas.

Report Writer - How to add custom data to a Report Writer report

Today's task is to add some custom data to a Report Writer report. The specific task is to modify the 'Check With Stub on Bottom - CM' report, but the principles will hold for most any report.

The article details how to add custom fields and populate them from data queried from the database.

Formatting Fields in VBA

With the exception of composite fields, VBA returns report field values without any formatting. For instance, VBA returns the phone number (701) 555-0100 Ext. 1772 as 70155501001772. Likewise, VBA returns the currency value $10.56 as 10.56000.

This article will show how to format a field in VBA

Google Map API - How to map an address on a map in a VB Win Forms application

This piece of code will show you how to create a form that has a Google map in it.

You'll need to drop a 'WebBrowserControl' onto your form

Telerik - RadConfirm - How to execute server side code when the dialog closes

This is something that I've strugged with for a while, I'm glad to have found a solution.

When you use the Telerik RadConfirm dialog, it pops up and when it closes it executes Javascript. But I most always need it to execute .NET code in the code behind.

This example shows how to do just that.

FileSeek from www.binaryFortress.com

The only thing that I can think of to say is “Wow”.

I am a fan of BinaryFortress anyway, but have always looked past the FileSeek utility because that functionality is built into Windows anyway, right?

I have the need to – regularly – do searches for files in directories, both for the file itself or for a string inside the file. Today’s task was to find an XML document in a folder that contains 14.5K documents, in many subfolders. Kind of like searching the Windows directory. Maybe worse. Finding this doc was going to be a pain.

I could use the windows search feature, but I knew that it would take a little bit and I was in the mood to test FileSeek anyway.

In the end, I downloaded, installed, and found my file faster than I could have with Windows Search Assistant. Across the network. Booya.

Very impressive. I also do work with SQL script files, and recently needed to find one of my scripts that had errant syntax (go figure, right?). I did a ‘find a string inside a file’ search against about 50 scripts and got back what I needed in less than a second. I was also able to double click on the result line and open the file in SSRS. What could be easier?

And, it does it’s work on the fly without indexing, so I don’t have to deal with ‘your folder is not indexed’…

www.binaryFortress.com. Go there now.

Telerik - RadListView Example

This short piece of code will show how to populate a RadListView control

Getting Environmental Values from .NET

There often arises a need to get to an environmental value - the machine name, the name of the appData or Temp folder, the directory where the app is launched from. Know how to do that? Read on...

Telerik - Win Forms RadListBox and RadDropDownList - change the styling of items in the list

I have an app that I'm creating that is targeted at a Tablet, I need to make the font in all the controls larger. The code to change the font size of the items in the dropdowns too me quite a while to find. I thought I'd save it here. It's not complicated or overly cool... just a little cryptic.

Dynamics - How to log the user that changed a field in the database

I have a solution that will 'audit' changes to the database... but it doesn't log who did it. Also, my existing solution is too heavy handed... it logs every change to every field. This short script will show you how to log the changes to just one field and who did it.

In our example we'll log the changes to the Customer PO Number field in the SOP Work Header table.

eConnect - taGetNextJournalEntry

This short piece of SQL code shows how to use the taGetNextJournalEntry stored procedure to get the next GL Journal number.

taGetNextJournalEntry is a stored procedure that is part of eConnect, and is used internally when you call the 'GetNextJournalNumber' endpoint in eConnect.

But if you know how, it can be called manually as well.

DD_Year

More articles like this can be found on our SQL Data Access menu.

It happens (especially in SSRS report writing) that there are quite a few 'parameter' datasets that I find myself writing over and over. We'll document them here and try to end that pain

This is not groundbreaking code, the idea is to develop a library of datasets like this to speed up development. You'll find this and the others on the SQL General menu

This one is the DD_Year stored procedure. As is, it is designed to bring back a data set that holds years from @Start_Year to the current year. If you run it as is it'll bring back three rows, like this:

DD_Month

More articles like this can be found on our SQL Data Access menu.

It happens (especially in SSRS report writing) that there are quite a few 'parameter' datasets that I find myself writing over and over. We'll document them here and try to end that pain

This is not groundbreaking code, the idea is to develop a library of datasets like this to speed up development. You'll find this and the others on the SQL General menu

This one is the DD_Month stored procedure. As is, it is designed to bring back a data set that holds 12 months. If you run it you'll see this:

Telerik - ASP.NET RadGrid inside of a UserControl example

This is a complete example of a ASP.NET RadGrid placed inside of a user control

The example shows 'InPlace' editing.

The only thing that I didn't think was straight forward was that when you click 'edit' in the grid, the EditCommand fires, then the NeedDataSource event, then the grid displays the edit fields. The UpdateCommand event fires after you click the Update button

How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008

You shouldn’t be running this command except during extreme emergencies. When you truncate transaction logs, you lose the ability to recover to a specific point in time.

Microsoft recommends that instead of truncating logs, you switch to simple recovery mode instead. That way you don’t generate logs you won’t be using, and you won’t incur performance impacts from repeatedly filling and truncating the logs. You also remove the need to regularly back up the transaction log. This has plenty of drawbacks – if something goes wrong with your database, your only option will be to restore the previous full backup. You could lose hours – maybe even days – of data.

Microsoft recognized the problems with TRUNCATE_ONLY and removed this capability completely from SQL Server 2008.

However, I'm often called in an emergency, the server has crashed because the drive filled up. So...

SQL - Write to a text file

I've long used this method to write files to the file system using SQL.

This method employs the SQL sp_OACreate and sp_OAMethod procs to do the writing. Now in SQL 2012 that method is not working for me. I switched to the XP_CommandShell method and that works fine.

So, here is the documentation of how to write a text file using XP_CommandShell

How can I add more comment in the Miscellanous Check in BANK Reconciliation?

In the check with stub on Bottom CM, How can I add more comments to this form and add gl account to it also?

after business portal install, views on database failing

hi all,

after installing the business portal, i am noticing that some of the view in our gp database no longer run. specifically, the purchasing views...

for the sake of this post, let's just use the view called "PurchaseOrders". this is a union query with the working and history tables in it. In the pm00200 table, there is a field called "ACNMVNDR". after the business portal installs, it added some fields for "drillback"....one of which plays off this field.

'Account Number With Vendor For Drillback' = 'dgpp://DynamicsGPDrillBack/?DatabaseInstance=&ServerName=HOU1-SVRDBS1&CompanyID=RNGR&ProductID=0' + dbo.dgppAccountIndex(1, ['PM Vendor Master File'].[ACNMVNDR])

as you can see, it is using a function called dbo.dgppAccountIndex(), which takes two parameters which are both integer datatypes.

When i run the view, it gives me this: "Conversion failed when converting the varchar value 'Customer #905 ' to data type int"

So i realized it was this function, since the data in this field has alpha-numeric values, it is a string...the function wants two integers..

Why is this? What are these drillback columns for? Why would the GP database define the field as a char(21), and this installation wants it to be a number?

Any ideas what's going on here?

thanks!

Access eConnect across a network?

Sirs:

I'm setting up an eConnect 2010 project. Do I have to have eConnect installed on my workstation, or can I just connect to the eConnect 2010 web service on the SQL Server?

I'm trying to do that and not having much luck.

Can't figure out how to transfer inventory with serial numbers

Hi, I'm using the XML below to try to transfer two instances of an inventory item specifying the serial numbers I want transfered. I get the error "The Trx Qty <> to sum of serial / lots in the IV10002"

Here's the XML, I'm sure I'm doing it wrong but I can't figure out how to do it. Thanks for any help!

- Art

<?xml version="1.0"?>
<eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <IVInventoryTransactionType>
        <eConnectProcessInfo xsi:nil="true" />
        <taRequesterTrxDisabler_Items xsi:nil="true" />
        <taUpdateCreateItemRcd xsi:nil="true" />
        <taIVTransactionSerialInsert_Items xsi:nil="true" />
        <taIVTransactionLotInsert_Items xsi:nil="true" />
        <taIVTransactionLineInsert_Items />
        <taAnalyticsDistribution_Items xsi:nil="true" />
        <taIVTransactionMultiBinInsert_Items xsi:nil="true" />
        <taIVTransactionHeaderInsert>
            <BACHNUMB>0</BACHNUMB>
            <IVDOCNBR>00000000000000470</IVDOCNBR>
            <IVDOCTYP>1</IVDOCTYP>
            <DOCDATE>7/26/2012</DOCDATE>
        </taIVTransactionHeaderInsert>
    </IVInventoryTransactionType>
    <IVInventoryTransferType>
        <eConnectProcessInfo xsi:nil="true" />
        <taRequesterTrxDisabler_Items xsi:nil="true" />
        <taUpdateCreateItemRcd xsi:nil="true" />
        <taIVTransferSerialInsert_Items>
            <taIVTransferSerialInsert>
                <IVDOCNBR>00000000000000481</IVDOCNBR>
                <ITEMNMBR>2-A3284A</ITEMNMBR>
                <SERLNMBR>123456</SERLNMBR>
                <LNSEQNBR>1</LNSEQNBR>
                <LOCNCODE>WAREHOUSE</LOCNCODE>
                <AUTOCREATESERIAL>1</AUTOCREATESERIAL>
                <TOLOCNCODE>000002</TOLOCNCODE>
            </taIVTransferSerialInsert>
            <taIVTransferSerialInsert>
                <IVDOCNBR>00000000000000481</IVDOCNBR>
                <ITEMNMBR>2-A3284A</ITEMNMBR>
                <SERLNMBR>654321</SERLNMBR>
                <LNSEQNBR>2</LNSEQNBR>
                <LOCNCODE>WAREHOUSE</LOCNCODE>
                <AUTOCREATESERIAL>1</AUTOCREATESERIAL>
                <TOLOCNCODE>000002</TOLOCNCODE>
            </taIVTransferSerialInsert>
        </taIVTransferSerialInsert_Items>
        <taIVTransferLotInsert_Items xsi:nil="true" />
        <taIVTransferLineInsert_Items>
            <taIVTransferLineInsert>
                <IVDOCNBR>00000000000000481</IVDOCNBR>
                <ITEMNMBR>2-A3284A</ITEMNMBR>
                <TRXQTY>2</TRXQTY>
                <TRXLOCTN>WAREHOUSE</TRXLOCTN>
                <TRNSTLOC>000002</TRNSTLOC>
            </taIVTransferLineInsert>
        </taIVTransferLineInsert_Items>
        <taAnalyticsDistribution_Items xsi:nil="true" />
        <taIVTransferMultiBinInsert_Items xsi:nil="true" />
        <taIVTransferHeaderInsert>
            <BACHNUMB>0</BACHNUMB>
            <IVDOCNBR>00000000000000481</IVDOCNBR>
            <DOCDATE>7/26/2012</DOCDATE>
        </taIVTransferHeaderInsert>
    </IVInventoryTransferType>
</eConnect>

Errors trying to receive inventory to a PO

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

Hello,

I'm trying to receive inventory and I'm getting a slew of errors. The PO exists, and I can receieve into if from the GP UI. The information I'm passing to the XML comes directly from the PO tables, so I know it's correct. The errors seem to indicate that it can't find the line and/or item number in the PO. XML follows the error messages below. Thanks in advance for any help!

- Art

Sql procedure error codes returned:

Error Number = 1276 Stored Procedure= taPopRcptLineInsert Error Description = POP Setup does not allow the site to be changed in Receiving
Node Identifier Parameters: taPopRcptLineInsert
POPTYPE = 1
POPRCTNM = RCT1208
PONUMBER = PO2084
ITEMNMBR = KPA100
Related Error Code Parameters for Node : taPopRcptLineInsert
PONUMBER = PO2084
LOCNCODE = Myers

Error Number = 1277 Stored Procedure= taPopRcptLineInsert Error Description = Location code does not exist in inventory
Node Identifier Parameters: taPopRcptLineInsert
POPTYPE = 1
POPRCTNM = RCT1208
PONUMBER = PO2084
ITEMNMBR = KPA100
Related Error Code Parameters for Node : taPopRcptLineInsert
LOCNCODE = Myers

Error Number = 2053 Stored Procedure= taPopRcptLineInsert Error Description = Purchase Order Line Item does not exist
Node Identifier Parameters: taPopRcptLineInsert
POPTYPE = 1
POPRCTNM = RCT1208
PONUMBER = PO2084
ITEMNMBR = KPA100
Related Error Code Parameters for Node : taPopRcptLineInsert
PONUMBER = PO2084

Error Number = 3799 Stored Procedure= taPopRcptLineInsert Error Description = VENDORID does not match the Purchase Order
Node Identifier Parameters: taPopRcptLineInsert
POPTYPE = 1
POPRCTNM = RCT1208
PONUMBER = PO2084
ITEMNMBR = KPA100
Related Error Code Parameters for Node : taPopRcptLineInsert
PONUMBER = PO2084
VENDORID = ACETRAVE0001

Error Number = 3808 Stored Procedure= taPopRcptLineInsert Error Description = Invalid PO Line number. This line number does not exists on the corresponding PO
Node Identifier Parameters: taPopRcptLineInsert
POPTYPE = 1
POPRCTNM = RCT1208
PONUMBER = PO2084
ITEMNMBR = KPA100
Related Error Code Parameters for Node : taPopRcptLineInsert
PONUMBER = PO2084
POLNENUM = 1

Error Number = 3809 Stored Procedure= taPopRcptLineInsert Error Description = Invalid Item number. This Item number does not match Item number on the corresponding PO
Node Identifier Parameters: taPopRcptLineInsert
POPTYPE = 1
POPRCTNM = RCT1208
PONUMBER = PO2084
ITEMNMBR = KPA100
Related Error Code Parameters for Node : taPopRcptLineInsert
ITEMNMBR = KPA100

Error Number = 4604 Stored Procedure= taPopRcptLineInsert Error Description = Item number/location code does not exist in inventory
Node Identifier Parameters: taPopRcptLineInsert
POPTYPE = 1
POPRCTNM = RCT1208
PONUMBER = PO2084
ITEMNMBR = KPA100
Related Error Code Parameters for Node : taPopRcptLineInsert
ITEMNMBR = KPA100
LOCNCODE = Myers

Error Number = 6662 Stored Procedure= taPopRcptLineInsert Error Description = VENDORID does not match the Purchase Order
Node Identifier Parameters: taPopRcptLineInsert
POPRCTNM = RCT1208
RCPTLNNM = 1
PONUMBER = PO2084
POLNENUM = 1
Related Error Code Parameters for Node : taPopRcptLineInsert
VENDORID = ACETRAVE0001

Error Number = 7922 Stored Procedure= taPopRcptLineInsert Error Description = Item Number/Vendor Item number does not match a line on the Purchase Order
Node Identifier Parameters: taPopRcptLineInsert
POPTYPE = 1
POPRCTNM = RCT1208
PONUMBER = PO2084
ITEMNMBR = KPA100
Related Error Code Parameters for Node : taPopRcptLineInsert
ITEMNMBR = KPA100
VNDITNUM = KPA100
VENDORID = ACETRAVE0001
PONUMBER = PO2084
POLNENUM = 1

<taPopRcptLineInsert>
<POPTYPE>1</POPTYPE>
<POPRCTNM>RCT1208</POPRCTNM>
<PONUMBER>PO2084</PONUMBER>
<ITEMNMBR>KPA100</ITEMNMBR>
<VENDORID>ACETRAVE0001</VENDORID>
<RCPTLNNM>1</RCPTLNNM>
<VNDITNUM>KPA100</VNDITNUM>
<QTYSHPPD>50</QTYSHPPD>
<AUTOCOST>1</AUTOCOST>
<LOCNCODE>Myers</LOCNCODE>
<POLNENUM>1</POLNENUM>
</taPopRcptLineInsert>

Here's the XML:

<?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 xsi:nil="true" />
        <taPopRcptSerialInsert_Items xsi:nil="true" />
        <taPopRcptLineInsert_Items>
            <taPopRcptLineInsert>
                <POPTYPE>1</POPTYPE>
                <POPRCTNM>RCT1208</POPRCTNM>
                <PONUMBER>PO2084</PONUMBER>
                <ITEMNMBR>KPA100</ITEMNMBR>
                <VENDORID>ACETRAVE0001</VENDORID>
                <RCPTLNNM>1</RCPTLNNM>
                <VNDITNUM>KPA100</VNDITNUM>
                <QTYSHPPD>50</QTYSHPPD>
                <AUTOCOST>1</AUTOCOST>
                <LOCNCODE>Myers</LOCNCODE>
                <POLNENUM>1</POLNENUM>
            </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>RCT1208</POPRCTNM>
            <POPTYPE>1</POPTYPE>
            <receiptdate>07/29/2012</receiptdate>
            <BACHNUMB>ARTSBATCH</BACHNUMB>
            <VENDORID>ACETRAVE0001</VENDORID>
        </taPopRcptHdrInsert>
        <taMdaUpdate_Items xsi:nil="true" />
    </POPReceivingsType>
</eConnect>

Determine Company_Options from DYNAMICS.dbo.SY01500

I am trying to figure out how to calculate the Company_Options from DYNAMICS.dbo.SY01500. It is a binary(4) field. I assume there is some bitwsie logic to determine the value based on an enum list. However, I do not know the values.

This table is not in the Knowledge Base.