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

Excel - refresh the dataset and save from VBS

A client called me today and asked for something that I'd not considered before - he had Excel spreadsheets that are connected to a data source, he wants to refresh them and email them out nightly

*** the email recipients are outside the domain and will not have a connection to the data souce

The article below describes how I did this

Why do my button properties change

I set up a new window and set buttons to look like GP buttons. After I save my project and relaunch, the buttons revert their alignment to center center for both image and text and the size changes back to what they originally were when dragged out to the form. See screen shots below. This has happened in two different projects.

Formatted the way I want them to look

After I save, close Visual Studio then log back in and open the project

collateral damage

Hi, I have a problem that I could not solve in two days. My infrastructure is as follows: GP 10.0, window server 2008, SQL server 2008. We have just migrated office to office 365 including emails and now business alerts and other emails that use sql mail do not work.
I already tried what the following links indicate and it has not worked for me, maybe I omit something .. https://blogs.msdn.microsoft.com/momalek/2013/02/25/how-to-configure-sql-database- mail-so-send-emails-using-office-365-exchange-online-a-walkthrough /
https://docs.microsoft.com/en-us/exchange/mail-flow-best-practices/how-to-configure-iis-for-relay-with-office-365

Has anyone in this community had something similar and has solved it?

Linking GP Table Fields to GP Screens

We need to ask Business users if they use a certain field on a GP Table. Sometimes the field name is different than what is on a GP Screen. Business users know the field name based on the screen they are using, is there a way to identify the screen fields to the GP Table field name?

Assembly duplicate items

Good morning,

We've encountered an error where an assembly used the same item multiple times in excess of the bill of material, duplicating the lines that got posted with that assembly and generating a double decrease for the items in question. The value of the end product didn't change, but the items consumed and the duplicates all have been used up in the assembly.

To have a better picture of the issue, I've enquire IV30300 (Inventory transaction amounts history), and noticed that all the duplicate items have 2 different transaction source (one of those lines have a blank trx source) and the line sequence number don't look right (original item and duplicate item have a difference between line sequence number of 1 instead of the usual 16384). Please see the result of the inquiry below for the assembly.

To get a better picture I've enquired BM30200 (Assembly Transaction History) and BM30100(Assembly Batch History) with the filter on the two transaction sources and it looks like one of the transaction sources appear on one table, but not the other (BMASM00002207 appears in BM30200 and BMASM00002289 appears in BM30100). See image below.

BM30200 result:

BM30100 result:

Now, the only way I can see that we can solve this is by creating a positive variance against all the items that have been incorrectly written off.

Is there any other way we could proceed? Also, does anyone know why this would happen and how to prevent this in future?

Thanks for your help.

Rolling 52 Week Sales History Report

Hello!

Does anyone have a View, SmartList or Excel Builder file that would provide a weekly sales quantity, by Part Number report...

Part Number Week 1 Week 2 Week 3 Week 4 Week 5 ..... Week52

ABC123 10 6 0 5 15 5

DEF321 0 0 5 6 10 8

Etc.

Thanks

David C_IL

Item allocated but inventory count remains the same

Searched for days now and still can't find the solution to this issue.

What I am trying to do is to insert SOP records into GP using eConnect. GP has all the inventory information and the integration I am developing will use this to keep track of the items that are being sold, in stock, etc. I was able to create SOP records with the correct inventory items but the issue that I am facing is that even if there are not enough items in the inventory, GP will automatically allocate and fulfill the line item.

Is there a way for eConnect to consider the stock count before creating the SOP record?

Error when inserting line item with a different price

I am getting an error 299 when trying to insert a line item that has a different unit price than the default. Please see the error below. I tried several suggested solution like setting MRKDNAMT and MRKDNAMTSpecified values but I the error still persists. Extended amount calculation looks correct as well.

Microsoft.Dynamics.GP.eConnect.eConnectException: Sql procedure error codes returned: Error Number = 299 Stored Procedure= taSopLineIvcInsert Error Description = Unit Price calculation does not match out to Extended priceNode Identifier Parameters: taSopLineIvcInsertSOPNUMBE = PARTS00061 SOPTYPE = 2Related Error Code Parameters for Node : taSopLineIvcInsertSOPTYPE = 2QUANTITY = 5.0000UNITPRCE = 30.765MRKDNAMT = 0XTNDPRCE = 153.825Error Number = 4646 Stored Procedure= taSopLineIvcInsert Error Description = Decimal Places passed in on UNITPRCE does not match setupNode Identifier Parameters: taSopLineIvcInsertSOPNUMBE = PARTS00061 SOPTYPE = 2Related Error Code Parameters for Node : taSopLineIvcInsertUNITPRCE = 30.765Error Number = 4647 Stored Procedure= taSopLineIvcInsert Error Description = Decimal Places passed in on XTNDPRCE does not match setupNode Identifier Parameters: taSopLineIvcInsertSOPNUMBE = PARTS00061 SOPTYPE = 2Related Error Code Parameters for Node : taSopLineIvcInsertXTNDPRCE = 153.825<taSopLineIvcInsert> <SOPTYPE>2</SOPTYPE> <SOPNUMBE>PARTS00061 </SOPNUMBE> <CUSTNMBR>403MB</CUSTNMBR> <DOCDATE>10/15/2019</DOCDATE> <ITEMNMBR>00.02016</ITEMNMBR> <UNITPRCE>30.765</UNITPRCE> <XTNDPRCE>153.825</XTNDPRCE> <QUANTITY>5.0000</QUANTITY> <MRKDNAMT>0</MRKDNAMT> <DOCID>STDORD</DOCID> <UpdateIfExists>1</UpdateIfExists> <UOFM>Each</UOFM></taSopLineIvcInsert> at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateEntity(String connectionString, String xml) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ProcessEntityImport(String connectionString, String sXML, Boolean isTransaction) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateEntity(String connectionString, String sXML) at MidwestJobs.Integrations.GreatPlains.Data.DataAdapter.SendDocument(eConnectType eConnect)

duplicate vendor name

Hi, anyone has a SQL script to identify duplicate vendor names in a GP database? Not the vendor transactions or combiner. I need to know if there are vendors with same or similar names.

Thanks

Paul

Change Menu Item Description

Hi,

I did this in the past and completely forgot how. How can I change the Menu display description? For example... in the Purchasing Transactions menu list... there is Receivings Transaction Entry - I want to change it to "Projects Payable Trx Entry"... I did this 2 other times many years ago.

Can anyone help me remember?

Thanks!
Mark

Management Reporter - Wont Multiply by Exchange Rate

I am implementing GP 2018 and the latest version of MR. I have configured two different sets of Exchange Rate tables, one that derives Reporting Currency (USD) from GBP by multiplying and one by dividing by the exchanges rate.

No matter which I setup using the Rate Types MR insists on calculating the wrong USD, always making it less than the original GBP instead of more. For example if the rate is set as a multiply type 1.25 GBP to each 1.00 USD and you have a value of 100 GBP it shows 80 USD, when it should be 125 USD.

If I change the Exchange tables the result is the same.

What step am I missing? I have loaded the Dec 2015 Balance Sheet in GBP and that is the first month being entered ever in the entity, and the year is not closed. I just want to know that I can translate this to the USD balance I was expected.

Do I need to close the year, and then enter historical data? But the balances were all GBP, its not that they were USD transactions converted to GBP. I just want MR to show me a translated amount.

CReate SOP Invoice with eConnect

New to Dynamics, very new to eConnect. I am trying to push a single header and line into Dynamics from an outside application. I am using sample code to do so...but when I enter tax, I now receive an error saying the tax table does not match the detail. I am thinking there is another stored procedure to call to create yet another table entry. Any ideas? Thanks in advance!