DynDeveloper.com - Your Dynamics GP Questions, Answered

	SOP Scripts
	eConnect
	taSopDeleteDocumentWrapper
	taSopLineDelete
	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

Remove unwanted characters from a field in SQL

Got a request in today to remove special characters from a field in a SQL table. The result was slightly more verbose than I thought it would be, but it's still 'article worthy'

Here we go...

Telerik ASP.NET Dropdownlist - Style each item in the dropdown separately

The task today is to style each item in a Telerik RadDropdownlist separately, like this:

Code below.

SQL Puzzler

Welcome to a new feature, SQL Puzzlers! Hope you enjoy them. Follow the link above to see all that we have.

Here is a puzzler for the SQL types in the crowd

Given this dataset:

How would you code a running total?

I've gotten you started, here is the code that produces the first window, all you have to do is code the RunningTotal column.

declare @t table (RowID int identity, Docdate date, Sales numeric(19,2))

insert into @t (docdate, Sales) values ('1/1/2019',100)

insert into @t (docdate, Sales) values ('1/2/2019',200)

insert into @t (docdate, Sales) values ('1/3/2019',300)

insert into @t (docdate, Sales) values ('1/4/2019',400)

insert into @t (docdate, Sales) values ('1/5/2019',500)

select *

from @t

order by docdate

The answer is below, peek at your own peril.

SmartConnect to Business Central integration error: xxx is related to a different company than customer xxxxx

SmartConnect to Business Central integration error: Contact xxx is related to a different company than customer xxxxx

I'm getting this error, even though the contact is valid and working.

SmartConnect/eConnect error: There was an error reading from the pope: The pipe has been ended

During a SmartConnect integration today (which uses eConnect) today we got the following error

There was an error reading from the pipe: The pipe has been ended

After some searching, I arrived at the solution below.

FORMAT date examples (SQL 2012 and later)

I'm all about speed. Seriously. It's a flaw.

I need to know how to (quickly) use the SQL FORMAT function to format dates.

Here's my cheat sheet.

I need to create a DevExpress report and populate it from a stored procedure.

How do I create a DevExpress report and populate it from a stored procedure? I want to use VB.Net

Hello World

We're creating a new section today, for DevExpress. Specifically, we were asked to create a few reports for an ISV and our methodology has always been to figure out how to do the work, then to thoroughly document the effort.

Today we wrote on creating DevExpress reports; and we'll need some place to file that code. Enjoy.

Is there a dev topic related to Dynamics that we can help you with?

Setting PURCHSTAT for DROPSHIP items.

When integrating SOP documents with eConnect, the PURCHSTAT field is not correctly set for document items with DROPSHIP = 1.

Telerik ASP.NET Radgrid group footer aggregation

I need to write a grid that will group and total in the footer. I do this often enough that I really should have a template but I don't... so here it is

Select query parameter from drop-down list

I need to select a value for a parameter to be used in a query for a DevExpress xtraReport

Adding a QTY Total field in the SOP Picking Ticket and SOP Blank Invoice Form

I need assistance in resolving a problem that maybe I’m missing something.

I need to add a QTY total in the SOP Picking Ticket and SOP Blank Invoice Form in the Report Footer.

My Problem is that the field should not include the components of the kits just only the kits item number when enter in the invoice.

Example:

If You add 6 different kits with 2 components each But one of them has 3 components,
the QTY total field should give me 6 items not 19. Should not include the components.

Also, is there’s normal items (No Kits) included it should give total of those items entered also.

Please review these form I’ve included and they are running Dynamics GP 2010R2.

Thank You

Kaspersky Password Manager

Create a strong password for your account

Do not show again

Integration Manager for update only

I need to run a fairly simple integration that inactivates a bunch of accounts. I cannot remember how to set the destination properties to update only. As it shows now, Insert Allowed, Update, Delete, Open are all set to TRUE. It does not give me the option to change it. Please advise.

Creation Date for a Manufacturing Order

I am trying to determine when a manufacturing order was created.

I cannot find any record showing me when it was first entered into GP. What am I missing?

I appreciate any help.

Will

GP cards

Hello,

I apologize if this has been covered before, but does GP "out of the box" allow the administrator to "lock down" certain fields on the item card, for example?

We have many users who all want to see the item card, but only a handful have the authority to change any field there and some are permitted to change one or two flags. The permissions I have seen so far look like it's all or none - you may view the item card or maintain it, but I don't see where specific fields are enabled or disabled unless we do that behind the scenes programmatically (which we have done).

Thank you.

sop return item no tax dynamics gp

I need to consult them, I have an invoice in sales with 3 items. I made a refund and 2 of the items did not calculate the tax. Make an inquiry and all the articles have tax configured in the inventory module.

In the original invoice, all the articles have the tax in the line.

Someone can guide me what to do to solve this.

Thank you

Need help creating a batch file

Hi,

I need to create a batch file that picks up a .txt file from source network location, remove the spaces, drop it into another network location, then move it to another folder.

What we are trying to do it:

1. User will create the ACH file [something like "Payment Chase 022819.txt"]

2. Pick it up and remove spaces [PaymentChase022819.txt]

3. Drop it into the bank's FTP folder in iPSwitch[sftp://transmissions.jpmorgan.com/DropBox/ACH]

4. Move it to another folder called 'Processed'

Any help will be highly appreciated.

Thanks

Paul

AR Trial Balance - Fully Applied Items

Hello,

When we run a HATB, there are fully applied items from years ago that are displaying on the report. . For example, invoice Cxxxxxx is showing on the aging as $46.67 outstanding but looking at Transaction by Document in GP this invoice is fully paid. Any ideas? The transaction is back from 2010.