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

SOPTransactionType, Multiple lines with one tax line

Props to Steve Massey at DataMasons for sending this in, it shows a SOP transaction with multiple lines and one tax node. We show that the LNITMSEQ in the lines is not related to the LNITMSEQ in the taSOPLineIvcTaxInsert.

All Vendors that do not have a record in the transactional tables

I received a request yesterday to write a query that would find all vendors that did not have a record in any of the transactional tables; so I thought I'd talk for a little bit about that.

The first thing is that you should not be doing this if you don't understand SQL or the Dynamics GP table structure. Take a backup first.

Second, there is a little bit of selectivity in use here. I started with a query of all the tables that use the VENDORID field, in my install there are 188. Too many for me to want to include all of them in my searching. But, I know that I can eliminate the SVC tables and the JC tables because this client doesn't use them.

So, you would use the script below as a template and modify it to your needs.

DELETE into an OUTPUT table

Did you know that it is possible to OUTPUT a table from a DELETE statement? This OUTPUT table would contain all the rows that you deleted, and you can then use that table in other processes.

Sample code below.

dd_BonusCalc

This query is a data source for a report to calculate overtime pay on bonuses. I don't really understand the accounting, but the general idea is that if you pay a bonus and the employee has overtime in that period, the bonus needs to be increased because of the overtime.

This script handles that and produces the extra amount for the overtime bonus.

It may very well be company specific (it may only work for the company that I wrote it for) but I'm posting it in case it comes up.

If you use it, I'd be really interested in knowing that it worked or didn't. There is a comment area at the bottom of the article. Thanks!

POPTransactionType

Sample POPTransactionType document

FORMATMESSAGE (SQL equivalent to String.Format)

I really love the .NET String.Format function, and you can use the SQL FORMATMESSAGE function to achieve the same thing.

Copy the code below into SQL and run it so see all the possibilities

Is it possible to restore specific integrations from a IM backup

I am now receiving a few requests for integrations to be restored to their previous working order, I have a few backups.

Is it possible to selectively choose what to restore or is it only the entire DB? If only the entire DB is it possible for me to view the integrations in the database and try to reverse engineer what I am being asked about?

eConnect taPoLine CMMTTEXT field size

The eConnect taPoLine CMMTTEXTfield size is only 500 characters, however, the CMMTTEXTcolumn in the POP10550 table is a text datatype.

Is there a way to pass comments that are beyond the 500 character limit when the PO is created? If not, is there a work around to accomplish that?

eConnect Issue with Project Creation.

Hello,

I am having a hell of time trying to get projects inserted into GP 2015. We have a VB script that uses the eConnect nodes to insert our projects into GP. It has been working for 3 years until now. I have no idea what and where the issue could be.

The eConnect error is shown below.

I bolded the error that confuses me because THERE is no where in the script and all the tables where 'ADMIN' would exist.

I have debug the script and it fails at this point

e.CreateEntity(sConnectionString, scontractDocument)

I looked at the XML and I also do not see any 'ADMIN' in it. I have included our XML below.

Any insight would help. Thanks

-------------------------------------------------------------------

Event[0]:
Log Name: eConnect
Source: Microsoft.Dynamics.GP.eConnect
Date: 2016-11-03T12:55:22.000
Event ID: 0
Task: N/A
Level: Error
Opcode: Info
Keyword: Classic
User: N/A
User Name: N/A
Computer: ACC-GP01.accretivesolutions.com
Description:
Action:
Create

Current User Name:

Input parameters:

Exception type:
System.Data.SqlClient.SqlException

Exception message:
Conversion failed when converting the varchar value 'ADMIN ' to data type int.

Stack Trace:
   at Microsoft.Dynamics.GP.eConnect.ServiceProxy.HandleSqlException(FaultException`1 ex)
   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.CreateEntity(String connectionString, String sXML)

------------------------------------------------------------------

XML

<eConnect>

<PAContractsType>

<eConnectProcessInfo xsi:nil="true"/>

<taRequesterTrxDisabler_Items xsi:nil="true"/>

<taPAContracts>

<CUSTNMBR>FF40559 </CUSTNMBR>

<PAcontid>FF94981</PAcontid>

<PAcontname>ERA</PAcontname>

<PACONTNUMBER>FF94981</PACONTNUMBER>

<PAcontclassid>8010</PAcontclassid>

<PAProjectType>1</PAProjectType>

<PAAcctgMethod>1</PAAcctgMethod>

<PAMCCURNCYID>Z-US$</PAMCCURNCYID>

<PAContMgrID>080159</PAContMgrID>

<PABusMgrID>080159</PABusMgrID>

<ADRSCODE>C144032</ADRSCODE>

<SLPRSNID> </SLPRSNID>

<PABILLFORMAT>T&M MULTIWK SUMMARY</PABILLFORMAT>

<PRBTADCD>C144032</PRBTADCD>

<PATRKCHGORDS>1</PATRKCHGORDS>

<PATRKCOBDGADDFLY>1</PATRKCOBDGADDFLY>

<USRDEFND1>12/12/2016</USRDEFND1>

<USRDEFND2>1/27/2017</USRDEFND2>

</taPAContracts>

<taPAContractBillingCycle_Items xsi:nil="true"/>

<taPAAccountsSetup_Items xsi:nil="true"/>

<taPAProjects_Items>

<taPAProjects>

<CUSTNMBR>FF40559 </CUSTNMBR>

<PACONTNUMBER>FF94981</PACONTNUMBER>

<PAprojid>FF94981</PAprojid>

<PAprojname>ERA</PAprojname>

<PAPROJNUMBER>FF94981</PAPROJNUMBER>

<PAProjectType>1</PAProjectType>

<PABBeginDate>12/12/2016</PABBeginDate>

<PABEndDate>1/27/2017</PABEndDate>

<PAprjclsid>8010</PAprjclsid>

<PADepartment>08 </PADepartment>

<PALabor_Rate_Table_ID>Finan94981</PALabor_Rate_Table_ID>

<PALabor_RateTable_Type>1</PALabor_RateTable_Type>

<PABILLFORMAT>T&M MULTIWK SUMMARY</PABILLFORMAT>

<PA_RestrictCustomerList>0</PA_RestrictCustomerList>

<PATRKCHGORDS>1</PATRKCHGORDS>

<PATRKCOBDGADDFLY>1</PATRKCOBDGADDFLY>

</taPAProjects>

</taPAProjects_Items>

<taPAProjectBillingCycle_Items>

<taPAProjectBillingCycle>

<PAPROJNUMBER>FF94981</PAPROJNUMBER>

<PABILLCYCLEID>BIWEEKLY</PABILLCYCLEID>

<PABILLFORMAT>T&M MULTIWK SUMMARY</PABILLFORMAT>

</taPAProjectBillingCycle>

</taPAProjectBillingCycle_Items>

<taPAProjectAccess_Items xsi:nil="true"/>

<taPAProjectEquip_Items xsi:nil="true"/>

<taPAProjectFeeSchedule_Items xsi:nil="true"/>

<taPAProjectFee_Items xsi:nil="true"/>

<taPAProjectBudgetMaster_Items>

<taPAProjectBudgetMaster>

<PAPROJNUMBER>FF94981</PAPROJNUMBER>

<PACOSTCATID>SERVICES</PACOSTCATID>

<PAProfitType>1</PAProfitType>

<PAbllngtype>1</PAbllngtype>

<PABBeginDate>12/12/2016</PABBeginDate>

<PABEndDate>1/31/2017</PABEndDate>

<PABQuantity>158.40</PABQuantity>

<PABUnitCost>80.290000</PABUnitCost>

<PABProfitAmt>185.000000</PABProfitAmt>

<PABProfitPcnt>35.000000</PABProfitPcnt>

<PABaselineOvrhdPct>35.000000</PABaselineOvrhdPct>

</taPAProjectBudgetMaster>

</taPAProjectBudgetMaster_Items>

</PAContractsType>

</eConnect>

GP Tasks

I would like to know more about GP tasks. What can initiate them, and how do you remove an entire recurring series of tasks?

We had over 5000 recurring task entries for one task that appeared one day last week on most of our GP users' home pages.

The only way we found to remove the task from view was to complete it, one by one - very time-consuming.

If I know what table(s) is involved, I could delete the entries easily.

Consulta

existe alguna tabla que contenga un registro de saldo pendiente de pago asociado al numero de cliente?

Is there a table that contains an outstanding balance record associated with the customer number

Dexterity not showing new fields assigned to a variable.

I have a form with variables on it. I have an array of 6 long types, the fields are on the form. At the end of a script, I have added these values to be modified. The values get modified but the new values do not show on the form. Is there any way to refresh the form to show these changed fields. Dex newbie here FYI. Bewlos is the code, as you can see from commented code, i check the values if they are assigned and the assignment works fine but new values do not show up on the form. Redraw field does not work either. I have Formula_Market on the form and need to show values from Formula_Market_Actual. Please help.

for i = 1 to 6 do
{warning str(Formula_Market[i]) + " BEFORE : " + str(Formula_Market_Actual[i]);}
Formula_Market[i] = Formula_Market_Actual[i];
[redraw field Formula_Market[i];]
{warning str(Formula_Market[i]) + " AFTER: " + str(Formula_Market_Actual[i]);}
end for;

Trying to create a Vendor import integration in IM

I am trying to create a Vendor import integration for new vendors in GP 2010 using IM in lieu of me importing using .csv and SQl for end users.

Is it possible to use a Source file that loads into SQL tables or is that frowned upon? So far I only see default integration sources, can these be created custom or is this a limitation of IM?

I was able to select Destination Vendor Under AP, looks to be okay to use for a basic vendor integration, but what If I wanted to setup a complete vendor, banking info, emails, ACH/ EFT details, etc. If this is a separate integration fro EFT banking information how would I go about creating a new EFT Vendor destination. I am not understanding how to create something this custom.

Any help would be great from the IM experts out there!

Thank you

Mark

Integration Manager IM

acabo de instalar IM pero me pide registrarlo, he puesto el site name y los key iguales a los de GP pero dice que son inválidos.

Como lo registro?

Puedo utilizarlo sin Registrar?

I just installed IM but it asks me to register it, I have put the site name and the same key as the GP but it says they are invalid.
How do I register it?
Can I use it without registering?

Integration Manager 14.0

I'm trying to use IM to move Project transfers(part number, qoh, Serial/lot) from one company into an Inventory Adjust transactions in another company. I'm getting an error that says:

DOC1 WARNING: The number of selected lot numbers does not equal the extended
quantity for item 'xxxxx-xxx'.

The part has been created in the second company and I can see the transfer but the Serial./Lot data does not import. I've verified that the qty being transferred matches the Lot and or Serial QOH. I'm using standard Integration destination because the eConnect version does not even have the serial/lot destination fields.

Thanks you for your help,

Cathy