Archives

 

Of all the things that I blog, the ones that have to do with VB Script are the most important to me because they're so painful. This one is no different. It took me hours to get this simple script working.

This article shows a piece of VB Script that will launch an executable file. This is useful to put inside an Integration Manager script window

 

When coding a .NET transactional form, we end up putting the same piece of code in those forms over and over - the form state code.

Every form is different, but generally we use three form states = CLEAN, POPULATED, and DIRTY. We'll use an 'Item' form for discussions sake

  1. Clean is for when the form is newly opened. Generally every field on the form is disabled except the main lookup field and maybe a lookup button.
  2. Populated is when the user has selected an item, but has not edited anything. Generally the item number field would be disabled, but the lookup button would not be, and all the form fields would be enabled
  3. Dirty is when the form has been edited an it needs to be saved. Both the item number field and the item lookup button are disabled, all remaining fields are enabled.

We also have code to clear every field on the form.

Last, we have code to add event handlers to each field, so that when the field is edited the form will become DIRTY.

 

As much as possible, I use the 'GetNext' eConnect procedures and methods to get the next document number in Dynamics. But it's not always available, and they're not available for all modules.

This article is a script that takes an alphanumber document number as a parameter and increments it.

For example, the current document number might be 'TSFR002345', this proc would increment that to 'TSFR002346'

 

This article shows a sample Telerik RadGrid Hierarchical example

 This article contains template code to display Grid Footer Template and Detail Templates using a Telerik ASP.NET RadGrid. Sure, you can go to the documentation and get it... but that took me hours. Now I'll have it when I need it.

 

 

I received a requirement today to write a SQL View for a smartlist that would show tax detail history and the line items. We wanted to use TX30000 for the base table so that we'd be sure that we weren't missing any lines. But TX30000 has one line for each tax detail in the order and we really wanted one line for the order, so in the view you'll see that the first line in the where clause is a sub query that returns one line for the order.

Then we join in the SOP30300 for the item info and the SOP10105 tax table for tax detail info, per the customer's request.

I always put a select statement below the 'create view' statement to be able to easily test the code.

There are two techniques that we use to save grid data to the database. One involves trapping the UserAddingRow, UserDeletingRow, and CellEndEdit events and committing the data to the database immediately.

That technique doesn't work or is not appropriate on all forms, like on a form that would have multiple changes going on at one time. An order entry form would have a total field that changed as the quantity in the grid was updated. In that case we hold all the changes and commit them with the Save button.

This article is a code example of that technique.

You're going to ask why a website dedicated to Dynamics GP development needs an article on Visual Studio Tools for Office (VSTO), right?

Give me a minute to sell you.

We do integrations. We recently did an application where the integration files arrived via email. The user either has to save the files manually, or... we write an Outlook addin that does it automatically. So, we write a VSTO add-in

This article contains a short code example of how to save files in Outlook automatically.

Sometimes I'm in the Telerik Winform RadGrid AddingNewRow event and I need to completely cancel the new row.

e.Cancel only cancels the add, the new row is still populated.

The code sample below will do the trick.

A large part of our work here is integration. We read in data from various sources and send it into Dynamics. On a recent job we were asked to read an Excel spreadsheet and integrate it; this article is a code example of how to do that for Excel 2010 and later formats. In other words, it needs to be an .XLSX file.

The code is heavily commented so it is self explanatory.

How would I write a sql script to find it the vendor id is different between 2 po tables?  Need to find this – POP10110 shows a different Vendorid for one PO than the vendor id in POP10100.  I know there is one, but want to find if there are any more…..I think it is easy, but my brain won’t go there. 

 

Hi All

I need to create an integration (with Scribe Insight) for Voided checks. I trigger on insertions to CM20200 for the original check and join to PM30300 for the applied to docs.

My initial thought was to use an update trigger on CM20200 and look for void being 0 in deleted and 1 in inserted. That part seems Ok, but I need to figure out which applied payments have been reversed. After the void, payments are no longer in PM30300. (I'm not sure if they are still there when the trigger fires, but even if they are, I would prefer to join to some persistent data that exists after the void is complete, as it simplifies debugging.)

I'm assuming that there is a record somewhere of which payments the original check was applied to.

Can anyone point me in the right direction?

Regards

Mark

 

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

Hi,

I'm trying to use the stored procedure taSopSerial to create and update returns.

The first time I save the document the procedure works fine - because the serial number has been selected yet. But when I try to update an existing return, I get several errors from taSopSerial:

1526: The Serial Number has already been sold - please choose another Serial Number

2905: Qty fulfilled can not exceed qty allocated

1532: The Serial Number (SERLNMBR) has already been selected for the entered item - Edit 9/16/15 View the main article for this error here

 

 This is the XML:

<?xml version="1.0"?>
    <SOPTransactionType>
        <eConnectProcessInfo>
            <ProductName>SalesPad GP</ProductName>
        </eConnectProcessInfo>
        <taRequesterTrxDisabler_Items xsi:nil="true" />
        <taUpdateCreateItemRcd xsi:nil="true" />
        <taUpdateCreateCustomerRcd xsi:nil="true" />
        <taCreateCustomerAddress_Items xsi:nil="true" />
        <taSopSerial_Items>
            <taSopSerial>
                <SOPTYPE>4</SOPTYPE>
                <SOPNUMBE>RTN1223</SOPNUMBE>
                <LNITMSEQ>16384</LNITMSEQ>
                <ITEMNMBR>100XLG</ITEMNMBR>
                <BIN>RETN</BIN>
                <SERLNMBR>00969</SERLNMBR>
                <DOCID>RTN</DOCID>
                <UpdateIfExists>1</UpdateIfExists>
            </taSopSerial>
        </taSopSerial_Items>
        <taSopLotAuto_Items />
        <taSopLineIvcInsert_Items>
            <taSopLineIvcInsert>
                <SOPTYPE>4</SOPTYPE>
                <SOPNUMBE>RTN1223</SOPNUMBE>
                <CUSTNMBR>AARONFIT0001</CUSTNMBR>
                <DOCDATE>3/4/2013</DOCDATE>
                <LOCNCODE>WAREHOUSE</LOCNCODE>
                <ITEMNMBR>100XLG</ITEMNMBR>
                <AutoAssignBin>0</AutoAssignBin>
                <UNITPRCE>59.95000</UNITPRCE>
                <XTNDPRCE>59.95000000</XTNDPRCE>
                <QUANTITY>1.00000</QUANTITY>
                <MRKDNPCT>0</MRKDNPCT>
                <UNITCOST>55.50000</UNITCOST>
                <PRCLEVEL>RETAIL</PRCLEVEL>
                <ITEMDESC>Green Phone 6</ITEMDESC>
                <QTYONHND>1</QTYONHND>
                <LNITMSEQ>16384</LNITMSEQ>
                <DOCID>RTN</DOCID>
                <SALSTERR>TERRITORY 1</SALSTERR>
                <SLPRSNID>PAUL W.</SLPRSNID>
                <ITMTSHID>USASTCITY-6*</ITMTSHID>
                <TAXSCHID>USASTCITY-6*</TAXSCHID>
                <PRSTADCD>WAREHOUSE</PRSTADCD>
                <ShipToName>Aaron Fitz Electrical</ShipToName>
                <CNTCPRSN>Bob Fitz</CNTCPRSN>
                <ADDRESS1>11403 45 St. South</ADDRESS1>
                <CITY>Chicago</CITY>
                <STATE>IL</STATE>
                <ZIPCODE>60603-0776</ZIPCODE>
                <COUNTRY>USA</COUNTRY>
                <PHONE1>3125550102</PHONE1>
                <PHONE2>3125550102</PHONE2>
                <FAXNUMBR>3125550102</FAXNUMBR>
                <ReqShipDate>3/4/2013</ReqShipDate>
                <ACTLSHIP>1/1/1900</ACTLSHIP>
                <SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
                <UpdateIfExists>1</UpdateIfExists>
                <CURNCYID>Z-US$</CURNCYID>
                <UOFM>EACH</UOFM>
            </taSopLineIvcInsert>
        </taSopLineIvcInsert_Items>
        <taSopLineIvcInsertComponent_Items />
        <taSopTrackingNum_Items xsi:nil="true" />
        <taSopCommissions_Items xsi:nil="true" />
        <taSopLineIvcTaxInsert_Items xsi:nil="true" />
        <taCreateSopPaymentInsertRecord_Items xsi:nil="true" />
        <taSopUserDefined>
            <SOPTYPE>4</SOPTYPE>
            <SOPNUMBE>RTN1223</SOPNUMBE>
            <USRTAB01>TEST12</USRTAB01>
        </taSopUserDefined>
        <taSopDistribution_Items xsi:nil="true" />
        <taAnalyticsDistribution_Items xsi:nil="true" />
        <taSopMultiBin_Items />
        <taSopHdrIvcInsert>
            <SOPTYPE>4</SOPTYPE>
            <DOCID>RTN</DOCID>
            <SOPNUMBE>RTN1223</SOPNUMBE>
            <TAXSCHID>USASTCITY-6*</TAXSCHID>
            <SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
            <LOCNCODE>WAREHOUSE</LOCNCODE>
            <DOCDATE>3/4/2013</DOCDATE>
            <TRDISAMT>0.00000</TRDISAMT>
            <CUSTNMBR>AARONFIT0001</CUSTNMBR>
            <CUSTNAME>Aaron Fitz Electrical</CUSTNAME>
            <ShipToName>Aaron Fitz Electrical</ShipToName>
            <ADDRESS1>11403 45 St. South</ADDRESS1>
            <CNTCPRSN>Bob Fitz</CNTCPRSN>
            <FAXNUMBR>3125550102</FAXNUMBR>
            <CITY>Chicago</CITY>
            <STATE>IL</STATE>
            <ZIPCODE>60603-0776</ZIPCODE>
            <COUNTRY>USA</COUNTRY>
            <PHNUMBR1>3125550102</PHNUMBR1>
            <PHNUMBR2>3125550102</PHNUMBR2>
            <SUBTOTAL>59.95000</SUBTOTAL>
            <DOCAMNT>59.95000000</DOCAMNT>
            <SALSTERR>TERRITORY 1</SALSTERR>
            <SLPRSNID>PAUL W.</SLPRSNID>
            <USER2ENT>fredericp</USER2ENT>
            <BACHNUMB>RETURN</BACHNUMB>
            <PRBTADCD>PRIMARY</PRBTADCD>
            <PRSTADCD>WAREHOUSE</PRSTADCD>
            <CREATECOMM>1</CREATECOMM>
            <CREATETAXES>1</CREATETAXES>
            <DEFTAXSCHDS>1</DEFTAXSCHDS>
            <CURNCYID>Z-US$</CURNCYID>
            <ReqShipDate>3/4/2013</ReqShipDate>
            <UpdateExisting>1</UpdateExisting>
            <PRCLEVEL>RETAIL</PRCLEVEL>
            <DEFPRICING>1</DEFPRICING>
        </taSopHdrIvcInsert>
        <taSopToPopLink xsi:nil="true" />
        <taSopUpdateCreateProcessHold xsi:nil="true" />
        <taCreateSOPTrackingInfo xsi:nil="true" />
        <taMdaUpdate_Items xsi:nil="true" />
    </SOPTransactionType>
</eConnect>

 

 

I thought setting UpdateIfExists = 1 would be enough but apparently it's not. I tried adding other fields (for example AUTOCREATESERIAL, SERLNMBRToReplace), but I still get errors.

Please can anyone help me?

Thanks.

Hi,

I was wondering if there is a way to configure different COST LEVELS in GP, kind of the same idea of having PRICE Levels.  Has anyone heard or done this?

Thanks for your help

George

I'm trying to utilize some UDF fields in SOP through eConnect. When I send in the values - I don't see where them in the GP tables or UI anywhere. I'm assuming some setup is needed in Extender to utilize these elements. If so what needs to be done to set this up?

 

<eConnect>
<SOPTransactionType>
<taSopLineIvcInsert_Items>
<taSopLineIvcInsert>
<SOPTYPE>1</SOPTYPE>
<SOPNUMBE>MB_AmitOC7</SOPNUMBE>
<CUSTNMBR>MONKEYBOY</CUSTNMBR>
<DOCDATE>2013-03-04</DOCDATE>
<ITEMNMBR>Computers</ITEMNMBR>
<UNITPRCE>250.00</UNITPRCE>
<XTNDPRCE>5000.000</XTNDPRCE>
<QUANTITY>20.0</QUANTITY>
<COMMENT_1>PO_ln-1</COMMENT_1>
<PRCLEVEL>RETAIL</PRCLEVEL>
<ITEMDESC>CHEMICAL, CATALYST,TYPE 3 WAY ELEMENT,UNIT OF 

MEASURE LITER</ITEMDESC>
<USRDEFND1>UDF1</USRDEFND1>
<USRDEFND2>UDF2</USRDEFND2>
<USRDEFND3>UDF3</USRDEFND3>
<USRDEFND4>UDF4</USRDEFND4>
<USRDEFND5>UDF5</USRDEFND5>
</taSopLineIvcInsert>
<taSopLineIvcInsert>
<SOPTYPE>1</SOPTYPE>
<SOPNUMBE>MB_AmitOC7</SOPNUMBE>
<CUSTNMBR>MONKEYBOY</CUSTNMBR>
<DOCDATE>2013-03-04</DOCDATE>
<ITEMNMBR>Travel</ITEMNMBR>
<UNITPRCE>100.00</UNITPRCE>
<XTNDPRCE>3000.000</XTNDPRCE>
<QUANTITY>30.0</QUANTITY>
<COMMENT_1>PO_ln-2</COMMENT_1>
<PRCLEVEL>RETAIL</PRCLEVEL>
<ITEMDESC>CHEMICAL, CATALYST,TYPE 3 WAY ELEMENT,UNIT OF 

MEASURE LITER</ITEMDESC>
<USRDEFND1>UDF1</USRDEFND1>
<USRDEFND2>UDF2</USRDEFND2>
<USRDEFND3>UDF3</USRDEFND3>
<USRDEFND4>UDF4</USRDEFND4>
<USRDEFND5>UDF5</USRDEFND5>
</taSopLineIvcInsert>
<taSopLineIvcInsert>
<SOPTYPE>1</SOPTYPE>
<SOPNUMBE>MB_AmitOC7</SOPNUMBE>
<CUSTNMBR>MONKEYBOY</CUSTNMBR>
<DOCDATE>2013-03-04</DOCDATE>
<ITEMNMBR>Servers</ITEMNMBR>
<UNITPRCE>1000.00</UNITPRCE>
<XTNDPRCE>15000.000</XTNDPRCE>
<QUANTITY>15.0</QUANTITY>
<COMMENT_1>PO_ln-3</COMMENT_1>
<PRCLEVEL>RETAIL</PRCLEVEL>
<ITEMDESC>CHEMICAL, CATALYST,TYPE 3 WAY ELEMENT,UNIT OF 

MEASURE LITER</ITEMDESC>
<USRDEFND1>UDF1</USRDEFND1>
<USRDEFND2>UDF2</USRDEFND2>
<USRDEFND3>UDF3</USRDEFND3>
<USRDEFND4>UDF4</USRDEFND4>
<USRDEFND5>UDF5</USRDEFND5>
</taSopLineIvcInsert>
</taSopLineIvcInsert_Items>
<taSopHdrIvcInsert>
<SOPTYPE>1</SOPTYPE>
<DOCID>STDQTE</DOCID>
<SOPNUMBE>MB_AmitOC7</SOPNUMBE>
<DOCDATE>2013-03-04</DOCDATE>
<CUSTNMBR>MONKEYBOY</CUSTNMBR>
<CSTPONBR>AmitOC7</CSTPONBR>
<SUBTOTAL>23000.000</SUBTOTAL>
<DOCAMNT>23000.000</DOCAMNT>
<BACHNUMB>2013-03-11</BACHNUMB>
<USRDEFND1>UDF1</USRDEFND1>
<USRDEFND2>UDF2</USRDEFND2>
<USRDEFND3>UDF3</USRDEFND3>
<USRDEFND4>UDF4</USRDEFND4>
<USRDEFND5>UDF5</USRDEFND5>
</taSopHdrIvcInsert>
</SOPTransactionType>
</eConnect>

Sirs:

I'm getting 'Service cannot be started. The handle is invalid' when trying to install eConnect on a new machine. Has anyone seen this?

hi all,

this may be outside the scope of this forum, but i have a timesheet import routine (actually two).  one runs via sharepiont workflow (on our sharepoint server), and runs fine.  another timesheet import system works from an application page on the sharepoint server.  the later does not work for some reason.  why would eConnect work from a workflow and not from the application page?  the web.config should be setup correctly with the bindings and endpoints, or the workflow-based econnect functions wouldn't work.

There was no endpoint listening at net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/EntityOperations that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details. --- There was no endpoint listening at net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/EntityOperations that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details.

any ideas?

Have a Modified form VatCountryCode, Generate Dll with DAG, cannot capture AfterModalDialog event from Visual Studio tools

 

Public Shared Country_CodeF As DynamicsModifiedDictionary.VatCountryCodeMaintenanceForm = DynamicsModified.Forms.VatCountryCodeMaintenance

Public Shared Country_CodeW As DynamicsModifiedDictionary.VatCountryCodeMaintenanceForm.VatCountryCodeMaintenanceWindow = Country_CodeF.VatCountryCodeMaintenance

Sub Initialize() Implements IDexterityAddIn.Initialize

AddHandler Country_CodeW.AfterModalDialog, AddressOf VatCountryCodeMaintenance_AfterModalDialog

End Sub

Need help to create a script that will give me the results for commission for each salesperson.

Thanks

Hello

I am trying to write a post integration script to email an error log file after an integration has completed using SQL mail.  I know that my SQL mail script works and sends the file that I designate.  I know that the script will find the correct file; but, the error log changes names each time I run the Integration, which means I have to pass a VBScript Variable to a SQL stored procedure.  Here is my code:

 

'****************GET THE LOG FILE INFO******

'*******************************************

 'Set the path to the log files. Edit this line as appropriate

sLogPath="D:\DYNSHARE\Log\"

 Set sFileName = CreateObject("ADODB.Parameter")

 Set pFSO = CreateObject("Scripting.FileSystemObject")

Set pFolder = pFSO.Get Folder(sLogPath)

 dtDateCreated=CDate("1/1/1900")

 For Each File In pFolder.Files

 If File.DateCreated > dtDateCreated then

 dtDateCreated = File.DateCreated

 sFileName=File.Name

 End If

Next

MsgBox (sFileName)

'*******************************************

'************CREATE AND SEND THE E-MAIL*****

 '*******************************************

Set pConnection = CreateObject("ADODB.Connection")

Set xFileName = CreateObject("ADODB.Parameter")

Set xFileName=sFileName

 'Open the connection to the database

 Call pConnection.Open("msdb","sa","Cashmere_3")

'Execute the stored procedure Call

 Call pConnection.Execute("DECLARE @filename varchar(8000) exec INSCIO_SendInegrationLog set @filename=xFileName")

 'Close the connection

Call pConnection.Close

My Error is occurring on the bottom half of the code when it is trying to pass the variable value I created xFileName to the SQL stored procedure.  Here is the exact error I am getting:

Integration Manager Exception Detail Log
Date: 3/19/2013 3:01:00 PM

Message Values:
 Primary Message: Cannot write to a closed TextWriter.
 Exception Message: Cannot write to a closed TextWriter.

Stack Informaiton:
 Source: mscorlib
 System.IO.__Error.WriterClosed()
 System.IO.StreamWriter.Flush(Boolean flushStream, Boolean flushEncoder)
 System.IO.StreamWriter.Write(Char[] buffer, Int32 index, Int32 count)
 System.IO.TextWriter.WriteLine(String value)
 Microsoft.Dynamics.GP.IntegrationManager.IMProvider.LogManager.AddActivity(Log Log, DateTime ActivityTime, String Source, String LogText, Int32 StatusCode)
 Microsoft.Dynamics.GP.IntegrationManager.IMProvider.Log.AddActivity(DateTime ActivityTime, String Source, String LogText, Int32 StatusCode)
 Microsoft.Dynamics.GP.IntegrationManager.IMProvider.IntegrationContext.WriteLogActivityEntry(String Source, String LogText, LogClassEnum LogClass, Int32 StatusCode)
 Microsoft.Dynamics.GP.IntegrationManager.IMProvider.IntegrationContext.IIntegrationEngine_Run()
 Microsoft.Dynamics.GP.IntegrationManager.IMProvider.IntegrationContext.Microsoft.Dynamics.GP.IntegrationManager.IMProvider.IIntegrationEngine.Run()
 Microsoft.Dynamics.GP.IntegrationManager.IMProvider.Integration.IIntegration_RunIntegration(IntegrationContext IntegrationContext)
 Microsoft.Dynamics.GP.IntegrationManager.IMProvider.Integration.Microsoft.Dynamics.GP.IntegrationManager.IMProvider.IIntegration.RunIntegration(IntegrationContext IntegrationContext)
 Microsoft.Dynamics.GP.IntegrationManager.frmProgress.frmProgress_Activated(Object eventSender, EventArgs eventArgs)

Data:

End Log

 

Any ideas what is wrong with my code?

 

Thanks - Joseph Guyton

 

Problem: Tax Schedule does not exist.

I'm getting getting the following error when i'm trying to add a part #  to a sales order.

I tried to add a different part # and received the same error.

 

Any suggestions on how to fix.

When coding a .NET transactional form, we end up putting the same piece of code in those forms over and over - the form state code.

Every form is different, but generally we use three form states = CLEAN, POPULATED, and DIRTY. We'll use an 'Item' form for discussions sake

  1. Clean is for when the form is newly opened. Generally every field on the form is disabled except the main lookup field and maybe a lookup button.
  2. Populated is when the user has selected an item, but has not edited anything. Generally the item number field would be disabled, but the lookup button would not be, and all the form fields would be enabled
  3. Dirty is when the form has been edited an it needs to be saved. Both the item number field and the item lookup button are disabled, all remaining fields are enabled.

We also have code to clear every field on the form.

Last, we have code to add event handlers to each field, so that when the field is edited the form will become DIRTY.

 

This article shows a Telerik RadGrid Hierarchical example

 This article contains template code to display Grid Footer Template and Detail Templates using a Telerik ASP.NET RadGrid. Sure, you can go to the documentation and get it... but that took me hours. Now I'll have it when I need it.

 

 

There are two techniques that we use to save grid data to the database. One involves trapping the UserAddingRow, UserDeletingRow, and CellEndEdit events and committing the data to the database immediately.

That technique doesn't work or is not appropriate on all forms, like on a form that would have multiple changes going on at one time. An order entry form would have a total field that changed as the quantity in the grid was updated. In that case we hold all the changes and commit them with the Save button.

This article is a code example of that technique.

Sometimes I'm in the Telerik Winform RadGrid AddingNewRow event and I need to completely cancel the new row.

e.Cancel only cancels the add, the new row is still populated.

The code sample below will do the trick.

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3