Archives

 

It's that time of the year...year end closing.  A prerequisite in year end closing is the Payroll Tax Update.  Have you received this error message?

 

The solution is quite simple - only 3 simple steps.

I love my job. Really.

I received this question via email today and it was so interesting that I decided to blog it.

One of our clients used to use pdk for timesheet entry but stopped several years ago. We now have over 100 users listed in sql under multiple databases that we’d like to remove. They all end with “_pdk”. Can you come up with an efficient way (more efficient than one at a time) to delete these SQL users and any related security records in each database?

 

Does anyone have an idea? Try and answer it before you look at my answer, see if yours is better.

In this article we show how to craft a SQL script that will run against all the company databases in a Dynamics GP installation. Very cool stuff, read on.

How to push the save button on the POP Purchase Order Entry form

I came across an interesting question and decided to write an article about it.  Create a calculated field, in report writer, to shorten the company name.  I kept getting errors with the following: Function_Script (RW_Left Company Name 3).  My goal was to shorten the company name to the left 3 characters. 

Recently a client asked us to round their sop documents to two decimals. Dynamics is set up in 5 decimal places because they sell widgets (small items). Also, the client uses Multicurrency so we have to deal with that.

This article includes the script that does the job in both the SOP document and the distributions

I'm getting the error

The security object does not exist.  Key = 25cc1a21-2cc4-4b13-a1c8-eea186fb688a

when upgrading an instance of Web Services for GP2013

This article discusses the fix

This query is a basic query that displays all the security items in Dynamics - users, security roles, security tasks

Today I had to solve the mystery of the following error:

 

 

I need to add fields from my Project card and the Project timesheets to my Payroll check. Is the best way to do this with VB?

Examples - I need to add the Class ID field (PAprjclsid) from PA01201 to the payroll check and I also need to add the Cost Category field (PACOSTCATID)from PA10001.

I am attempting to create a purchase receipt as a precursor to creating an invoice matched to a PO. I.E. "2 way matching".

I am using eConnect 12 on GP2013. I will be attempting to use this same code on GP2010 and GP2015, so if there are any additional changes I need to do to make this work on those as well, that would be helpful.

 The POPRCTNM I am using here (I assume should be the number for the shipping receipt I am creating, which I obtained using the

GetNextDocNumbers.GetNextPOPReceiptNumber() function.

The eConnect XML I am sending is:

<?xml version="1.0"?>
    <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>RCT1181</POPRCTNM>
                <PONUMBER>PO0997</PONUMBER>
                <ITEMNMBR>PHON-ATT-53BK</ITEMNMBR>
                <VENDORID>ADVANCED0001</VENDORID>
                <RCPTLNNM>16384</RCPTLNNM>
                <VNDITNUM>PHON-ATT-53BK</VNDITNUM>
                <QTYSHPPD>1.00000</QTYSHPPD>
                <AUTOCOST>1</AUTOCOST>
                <POLNENUM>16384</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>RCT1181</POPRCTNM>
        <POPTYPE>1</POPTYPE>
        <receiptdate>1/8/2015</receiptdate>
        <BACHNUMB>PAYABLES BATCH </BACHNUMB>
        <VENDORID>ADVANCED0001</VENDORID>
        <AUTOCOST>1</AUTOCOST>
        <NOTETEXT>http://sp2010dev/tap/Invoices/Invoice1.pdf</NOTETEXT>
        <USRDEFND4>http://sp2010dev/tap/Invoices/Invoice1.pdf</USRDEFND4>
    </taPopRcptHdrInsert>
        <taMdaUpdate_Items xsi:nil="true"/>
    </POPReceivingsType>
</eConnect>

I am getting the following error:

ERROR: Error updating POPRcptHdr for PO - PO0997 - Invalid object name 'PA01303'.

I am new to both GP and eConnect, so bear with me as I try to understand whatever answer you may give.

Thanks.

 


I will preface this with the fact that I'm a novice with both GP and eConnect.

Our software already uses eConnect to upload both PO and NON-PO invoices into GP.

We have a new requirement to include both the VAT and VAT Rate with the invoice so that they go whereever the proper place is in GP for that invoice.

Any pointers on how to go about it or where to look for more detailed info would be appreciated.

OK, here we go.  This is a long one.  I've gotten past my original error in creating the shipping receipt.

That said, The shipping receipt, RCT1185, got successfully created.  HOWEVER, it seems like GP created TWO shipping receipts for some reason: The one I requested, RCT1185, and an identical one pointing to the same PO, RCT1187 (This is NOT an invoice receipt - it's a shipping receipt).  I'm not sure if this happened when I created the shipping receipt transaction, or when I later sent the invoice with the RCT1185 shipping receipt referenced. I know I didn't create two of them, because if I had, it would be in my application log.

The receipt was created successfully. However, when I tried to create the invoice, I got the following error:

ERROR: Error updating EnterMatchInvHdr for PO - PO2075 - Sql procedure error codes returned:

Error Number = 4852  Stored Procedure= taPopEnterMatchInvLine  Error Description = Shipment Line Item does not exist to match Invoice against
Node Identifier Parameters: taPopEnterMatchInvLine
POPRCTNM = RCT1186
PONUMBER = PO2075
POLNENUM = 16384
Related Error Code Parameters for Node : taPopEnterMatchInvLine
PONUMBER = PO2075
POPMtchShpRcpt = RCT1187
ShipRCPTLNNM = 16384
ITEMNMBR = 256 SDRAM
VENDORID = COMVEXIN0001

<taPopEnterMatchInvLine>
  <POPRCTNM>RCT1186</POPRCTNM>
  <POPMtchShpRcpt>RCT1187</POPMtchShpRcpt>
  <ShipRCPTLNNM>16384</ShipRCPTLNNM>
  <PONUMBER>PO2075</PONUMBER>
  <QTYINVCD>1.00000</QTYINVCD>
  <ITEMNMBR>256 SDRAM</ITEMNMBR>
  <VENDORID>COMVEXIN0001</VENDORID>
  <UNITCOST>0</UNITCOST>
  <EXTDCOST>0</EXTDCOST>
  <AUTOCOST>1</AUTOCOST>
  <POLNENUM>16384</POLNENUM>
< /taPopEnterMatchInvLine>

For reference, here is the shipping receipt transaction I sent:

<?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>RCT1185</POPRCTNM>
                    <PONUMBER>PO2075</PONUMBER>
                    <ITEMNMBR>256 SDRAM</ITEMNMBR>
                    <VENDORID>COMVEXIN0001</VENDORID>
                    <RCPTLNNM>16384</RCPTLNNM>
                    <VNDITNUM>256 SDRAM</VNDITNUM>
                    <QTYSHPPD>1.00000</QTYSHPPD>
                    <AUTOCOST>1</AUTOCOST>
                    <POLNENUM>16384</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>RCT1185</POPRCTNM>
               <POPTYPE>1</POPTYPE>
               <receiptdate>1/13/2015</receiptdate>
               <BACHNUMB>PAYABLES BATCH </BACHNUMB>
               <VENDORID>COMVEXIN0001</VENDORID>
               <AUTOCOST>1</AUTOCOST>
               <NOTETEXT>http://sp2010dev/tap/Invoices/Invoice2.pdf</NOTETEXT>
               <USRDEFND4>http://sp2010dev/tap/Invoices/Invoice2.pdf</USRDEFND4>
          </taPopRcptHdrInsert>
          <taMdaUpdate_Items xsi:nil="true" />
     </POPReceivingsType>
< /eConnect>

Here is the invoice transaction I sent:

<?xml version="1.0"?>
< eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <POPEnterMatchInvoiceType>
    <eConnectProcessInfo xsi:nil="true" />
    <taRequesterTrxDisabler_Items xsi:nil="true" />
    <taUpdateCreateItemRcd xsi:nil="true" />
    <taUpdateCreateVendorRcd xsi:nil="true" />
    <taCreateVendorAddress_Items xsi:nil="true" />
    <taPopRcptLineTaxInsert_Items xsi:nil="true" />
    <taPopDistribution_Items xsi:nil="true" />
    <taAnalyticsDistribution_Items xsi:nil="true" />
    <taPopEnterMatchLotInsert_Items xsi:nil="true" />
    <taPopEnterMatchSerialInsert_Items xsi:nil="true" />
    <taPopEnterMatchInvLine_Items>
      <taPopEnterMatchInvLine>
        <POPRCTNM>RCT1184</POPRCTNM>
        <POPMtchShpRcpt>RCT1185</POPMtchShpRcpt>
        <ShipRCPTLNNM>16384</ShipRCPTLNNM>
        <PONUMBER>PO2075</PONUMBER>
        <QTYINVCD>1.00000</QTYINVCD>
        <ITEMNMBR>256 SDRAM</ITEMNMBR>
        <VENDORID>COMVEXIN0001</VENDORID>
        <UNITCOST>0</UNITCOST>
        <EXTDCOST>0</EXTDCOST>
        <AUTOCOST>1</AUTOCOST>
        <POLNENUM>16384</POLNENUM>
      </taPopEnterMatchInvLine>
    </taPopEnterMatchInvLine_Items>
    <taPopEnterMatchInvToShpMultiLine_Items />
    <taPopEnterMatchInvHdr>
      <POPRCTNM>RCT1184</POPRCTNM>
      <VNDDOCNM>12345-2075</VNDDOCNM>
      <receiptdate>1/13/2015</receiptdate>
      <BACHNUMB>PAYABLES BATCH </BACHNUMB>
      <VENDORID>COMVEXIN0001</VENDORID>
      <SUBTOTAL>0</SUBTOTAL>
      <AUTOCOST>1</AUTOCOST>
      <NOTETEXT>http://sp2010dev/tap/Invoices/Invoice2.pdf</NOTETEXT>
      <USRDEFND4>http://sp2010dev/tap/Invoices/Invoice2.pdf</USRDEFND4>
    </taPopEnterMatchInvHdr>
    <taMdaUpdate_Items xsi:nil="true" />
  </POPEnterMatchInvoiceType>
< /eConnect>

Can anyone please help in creating a calculated field in report writer for a shorter Company Name to print automatic its too long.

I am trying to simply update a flag in a table to say that I have integrated the integration works but the "After Script" Fails Wrong number of arguments or invalid property assigment"MyCon"

The database is on the GP SQL server but not a GP Database.

'Create a new connection object

Set MyCon = CreateObject("ADODB.Connection")

'Open the connection to the database

MyCon.ConnectionString = "database=Concur"

GPConnection.Open(MyCon)

sSQL = "Update concur.Cash_Amt set IntegrationFlag = '1' where Entity = 'SUS'"

'Execute the stored procedure

Call MyCon(sSQL)

 

It maybe a permissions thing as the database isn't a GP but any ideas would be appreciated.

 

Hello,

 I am attempting to update an existing invoice with a payment.

 Here is my message:

<?xml version="1.0"?>
<eConnect>
 <SOPTransactionType>
  <taCreateSopPaymentInsertRecord_Items>
   <taCreateSopPaymentInsertRecord>
    <SOPTYPE>3</SOPTYPE>
    <SOPNUMBE>INV00123935</SOPNUMBE>
    <CUSTNMBR>500006</CUSTNMBR>
    <DOCAMNT>87.50</DOCAMNT>
    <PYMTTYPE>6</PYMTTYPE>
    <CARDNAME>VISA</CARDNAME>
    <DOCNUMBR>INV00123935</DOCNUMBR>
    <Action>1</Action>
    <UpdateExisting>1</UpdateExisting>
   </taCreateSopPaymentInsertRecord>
  </taCreateSopPaymentInsertRecord_Items>
  <taSopHdrIvcInsert>
   <SOPTYPE>3</SOPTYPE>
   <DOCID>INV</DOCID>
   <SOPNUMBE>INV00123935</SOPNUMBE>
   <DOCDATE>2015-01-15</DOCDATE>
   <CUSTNMBR>500006</CUSTNMBR>
   <PYMTRCVD>87.50</PYMTRCVD>
   <BACHNUMB>TEST_20150115_15</BACHNUMB>
   <UpdateExisting>1</UpdateExisting>
  </taSopHdrIvcInsert>
  <taSopHdrIvcInsert>
   <UpdateExisting>1</UpdateExisting>
  </taSopHdrIvcInsert>
 </SOPTransactionType>
</eConnect>

But gp2013 throws the following SQL error?

error attempting to insert into SOP010106 (INV00123935           ,                     )

the extra spaces are logged in event manager.

Is this a  SQL permissions issue, incorrect eConnect operation or message content?

Stephen

Can someone tell me the SQL tables associated with this inquiry window in GP 2013???

Thanks,

Our report catalog became corrupted and had to restored with a backup from previous day. During the import I received a message that the the Header is missing but the import was successful. After the report was tested the Centered header with Company name, report name and range of date was missing. At the footer the file name, row column and tree name was missing. Do you please have some simple documentation on how to put this information back on the report....thanks 

I am a beginner with Dynamics GP.  I have worked with somewhat similar systems before but they were still different.  I have learned quite a bit about the tables and can pull the information I need but am having some trouble editing an invoice.  As a staffing service, we bill hours but I cannot find a way to create a line that totals the hours and multiplies by a surcharge.  This would just be an additional line in the purchase order.  From what I understand, it can be done in Report Writer but I just have not gotten to the point where I can do this.  Any help would be greatly appreciated!!!

Hi All,

 Can anyone direct me to a good 'How To' document on using SQL Server Profiler with Dexterity. We use Dex to call eConnect stored procedures but the taPoLine sproc keeps throwing a -127 Sql Proc Error. We know it gets called, but beyond that we have no way of knowing what is causing the error by using just the Dex SQL or Dex Script logs. We don't typically use SQL Profiler due to it's overhead and complexity.

Thanks in advance... Jeff 

This is a code sample for the RadGrid showing Inserts, Updates, and Delete. This technique uses a separate form for the editing
Code samples for the RadNumericTextBox
Common code for the RadDatePicker ASP.NET Controls

This is a simple technique to get an 'Are you sure?' dialog to pop up when a button is pressed

 

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