DynDeveloper.com

DynDeveloper.com

Sign Up Now!Log In

Archives

 

This very simple, yet very useful pivot table in Excel puts all the paycodes from Dynamics GP in a spreadsheet for a payroll expert to review.

We will take data directly from the UPR30300 paycheck detail table like this...

And turn it into this, one line per employee, showing all their aggregated payroll code data. Note that the pay codes are grouped, in the screen shot below you can see Benefits and Deductions

 

Start with copying this select statement into a SQL query window and run it.

Select
      d.EMPLOYID as 'Employee_ID',
      PAYROLCD as Payroll_Code,
      case when PYRLRTYP = '1' then 'RegularPay'
              when PYRLRTYP = '2' then 'Deductions'
              when PYRLRTYP = '3' then 'Benefits'
              when PYRLRTYP = '4' then 'Taxes'
              when PYRLRTYP = '5' then 'LocalTax'
              else '' end as Payroll_Type,
      UPRTRXAM as Amount,
      CHEKDATE as Check_Date
              from upr30300 d
                  where year(d.CHEKDATE) = year(GETDATE())

We had an overseas client call for help with a failing eConnect integration, it was failing with essentially no error at all. 

The last time this happened, it was because of an error in an Multi Currency trigger in the Dynamics database, today's error turned out to be a missing currency line in DYNAMICS..MC00200. The trick was finding the error, because it wasn't being reported. 

We had the XML, and it had three call: PO Line, PO Header, and PO tax. We already have 'wrapper' code on this site for the first two so I wrote the wrapper below for the tax call and sent it to them. 

We copied the XML into the three stored procedure wrapper calls and we got the error on the header: error on the MCValidate stored procedure. And... an error number. I looked up the number and it was 'missing MC00200 line'. Grrr. 

Hope this helps. 

This seems pretty simple, but on the server from IE it was pretty hard. I ended up loading Chrome and found it. IE has JavaScript restricted on servers. Screen shots below

I need to be able to return a JSON string containing the results of an SQL query.  I know there are 3rd party tools that can simplify this, but I wanted to not have to add more to my project.

 

I built a small WinForm project to determine how to convert text into a JSON-formatted string.  Even though my source data would be from an SQL results set, for my test I just generated some data.  You only have to change the Vendor.GetVendorInfo() to accommodate whatever means you use to supply your data.

It is important to note the decorations used in the Vendor and Vendors classes.  This is needed for the JSON serialization.

The Form1 class is just a WinForm with a Start and Exit button and a text box for displaying the resulting JSON string.  I first tried with a single Vendor instance, the v1 variable.  Once that was working, I moved on to an array of Vendor types (vendors).

The GetVendorInfo() just creates the data to be serialized.  The BuildJSON() method does the actual serialization.

NOTE: Make sure you add a reference to System.Runtime.Serialization to the project!

In a previous blog, I showed how to create a JSON string from data.  Many thanks to AscendLearning, who pointed out that SQL 2016 supports JSON.  I gave it a try.

This is a step by step walkthrough for a Sales Invoice integration into Dynamics 365/Business Central using SmartConnect

If you don't have any existing SmartConnect integrations, read this post first, it contains some setup items not covered here.

Please comment below if we can make this any clearer or helpful

Seems like a silly thing, hiding the tabs on a .NET TabControl. but it's a little bit of a trick

Here's an interesting piece of code that adds a line to the 'Additional Items' menu in most any Dynamics GP form. Try it!

This article contains two stored procedures that will insert/update data in Extender tables. The code is not fully flushed out for all of the data types, but it supports text and numbers. 

If you use it and update it, email your code to support@ and we'll update it here. 

Edit 11/16/2018

In the comments below, gerispapajani brings up an issue that I ran into at a client using this, there were duplicate entries in EXT20010 that break this code, I ended up renaming the duplicates.

There is no other field in that table that I can use to filter. Any ideas how to fix this issue? Why are there duplicates? I was thinking bad data…

Maybe a query to delete the entries there are never actually used in EXT01101/2/3/4?

EDIT 11/16/2018 

In response to customer feedback, new proc below updated to handle dates and dropdown values

This piece of code is ALL the code behind for a standard .net 1 form application that generates a license key that can be used by your applications. 

The form looks like this:

And, here is the code that consumes the key:

    Private Sub ProcessLicenseKey()
 
        ' This subroutine reads the distribution key, decrypts it, extracts the
        ' expiration date, and warns as expiration is within 2 months, and exits
        ' if expiration is past.
        Dim baseDir = Path.GetDirectoryName(Application.ExecutablePath)
        Dim expirationDate As DateTime
        Dim keyValid As Boolean = True
 
        Try
            Dim encryptedText As String = File.ReadAllText(Path.Combine(baseDir, "KEY.DAT"))
            Dim licenseKey As LicenseKey = New LicenseKey()
            expirationDate = licenseKey.GetExpirationDate(encryptedText)
            If Not (ValidKey(expirationDate)) Then
                Application.Exit()
            End If
 
        Catch ex As FileNotFoundException
            Dim msg As String = "Unable to read distribution key." + vbCrLf
            msg += String.Format("Error: {0}", ex.Message) + vbCrLf
            msg += "Contact your Run-Biz representative." + vbCrLf
            msg += "Program will be terminated."
            MessageBox.Show(msg)
            keyValid = False
 
        Catch ex As Exception
            Dim msg As String = "Unexpected error occurred." + vbCrLf
            msg += String.Format("Error: {0}", ex.Message) + vbCrLf
            msg += "Contact your Run-Biz representative." + vbCrLf
            msg += "Program will be terminated."
            MessageBox.Show(msg)
            keyValid = False
 
        End Try
 
        If Not keyValid Then
            Application.Exit()
        End If
 
    End Sub
End Class

 

So, here's a poser. A question. A mys-ter-y. 

I took a support call yesterday from a client, their IM integration was locking up with no warning. Now, before I lose those that hate IM, the solution had nothing to do with IM, so stay with me. 

Here's the deal. I'm going to describe the symptoms and then the steps that I took in troubleshooting. 

*** I'm going to number the steps.

If you get the solution at a step before I did, please enter a comment below with your step. The winner gets absolutely nothing, save fleeting fame and admiration from your peers.

So, here we go. 

Step #1 The client logs me into IM, and runs the RM Integration, and the document type is Finance Charge. It logs no ATTEMPTS, just locks up immediately. We have to end the task (both IM and Dynamics) to get control again. 

Step #2 The integration has Before Integration, Before Document, After Document, and After Integration scripts. Suspecting the code, I put MSGBOX statements in the first three and determined that the scripts were fully executing (not locking) and that we never even started the After Document. This appeared to be a Dynamics issue, not Integration Manager. 

Step #3...

 

Second error like this in a week. I blogged the last one too, but this one is attached to eConnect so I'll blog it just to record the error number. 

If you remember the post, you should be able to guess the problem just from reading the error...

Sql procedure error codes returned:

Error Number = 38  Stored Procedure= taCreateSopPaymentInsertRecord  Error Description = Unable to get payment number
Node Identifier Parameters: taCreateSopPaymentInsertRecord
SOPNUMBE = BR-285706           
SOPTYPE = 2
PYMTTYPE = 3


<taCreateSopPaymentInsertRecord>
  <SOPTYPE>2</SOPTYPE>
  <SOPNUMBE>BR-285706            </SOPNUMBE>
  <CUSTNMBR>B999999        </CUSTNMBR>
  <DOCDATE>11/27/2018</DOCDATE>
  <DOCAMNT>13.40000</DOCAMNT>
  <CARDNAME>WEBSITE        </CARDNAME>
  <PYMTTYPE>3</PYMTTYPE>
  <Action>1</Action>
  <SEQNUMBR>16384</SEQNUMBR>
  <USRDEFND1>SALE                                              </USRDEFND1>
</taCreateSopPaymentInsertRecord>


I'm just going to copy all the code from this project into an article. It was simple to code, and DAYS to configure for SSL. The issue was all with the web.config

 

https://docs.microsoft.com/en-us/dotnet/framework/wcf/feature-details/how-to-configure-an-iis-hosted-wcf-service-with-ssl

In the end, this article helped the most.

The is in response to a question on the forum, a user asked how to restrict data in a GP scrolling window using VBA. 

I this thread http://dyndeveloper.com/thread.aspx?threadid=630

RedSoxFred details how to do it, I'm copying it to an article so that I can put it on the VBA menu

http://dyndeveloper.com/Menus/VBA.aspx

 

I need to view a users password in GP. I am using SSMS 12.0.5207.0 basic version. What query can I run, to decrypt the users password and view it? We use a third party application, that uses GP credentials to login. Without resetting the user's password, how can I view it from the SY01400 table?

Good morning,

  After install Manufacturing and Human Resources Features from control panel and run GP Utilities is necessary to install the feature like Manufacturing or Human Resources in each pc client even if it is not used?

Any suggestion is welcome,

Mr. García


I'm writing a Web Services integration for GP. I'm utilizing the UpdateSalesOrder method, pulling the existing Sales Order object using the Sales Order ID, and supplying a new set of SalesOrderLines, with Lots filled out.

The goal is that our system controls order/lot information, and we're exporting information to update the GP Sales Order and its lines. For an order we export an Item key, its lot keys, lot quantities, and an item quantity (total of lot quantities) and want to make these allocations in GP.

I have a basic test: Updating Sales Order with 1 Line Item and 1 Lot on the item. In my C# code I've set the SalesOrderLine Quantity, QuantityAllocated, QuantityFulfilled to 5, and SalesOrderLine's only Lot Quantity to 5 as well.

 

I'm getting Lot Quantity Fulfilled does not match the Sales Line Fulfilled. AllocateLotBehavior = 1.”  

UpdateSalesOrder Allocate Lots Behavior is set to Manual.

I checked a SQL Trace of taSopLotAuto and saw @I_vQUANTITY=5.00000; @I_vQTYFULFI=default (It looks like this might be the cause)

- If @v_QTYFULFI being sent needs to be the value on Line Item (5), how do I do that? (SalesLineLot object from web services only exposes Quantity)

XML from exception console:

 

<Lines>
    <SalesOrderLine>
        <Extensions/>
        <Key>
            <SalesDocumentKey>
                <CompanyKey>
                    <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
                </CompanyKey>
                <Id>DR1005</Id>
            </SalesDocumentKey>
            <LineSequenceNumber>0</LineSequenceNumber>
        </Key>
        <Quantity>
            <Value>5.00000</Value>
            <DecimalDigits>0</DecimalDigits>
        </Quantity>
        <IsNonInventory xsi:nil="true"/>
        <IsDropShip xsi:nil="true"/>
        <RequestedShipDate>2017-04-12T00:00:00</RequestedShipDate>
        <DeleteOnUpdate xsi:nil="true"/>
        <TaxBasis xsi:nil="true"/>
        <IntegrationSource xsi:nil="true"/>
        <WarehouseKey>
            <Id>WAREHOUSE</Id>
        </WarehouseKey>
        <ItemKey>
            <Id>TESTLOT</Id>
        </ItemKey>
        <ShippingMethodKey>
            <CompanyKey>
                <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
            </CompanyKey>
            <Id>LOCAL DELIVERY</Id>
        </ShippingMethodKey>
        <SalesTerritoryKey>
            <CompanyKey>
                <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
            </CompanyKey>
            <Id>TERRITORY 1</Id>
        </SalesTerritoryKey>
        <SalespersonKey>
            <CompanyKey>
                <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
            </CompanyKey>
            <Id>PAUL W.</Id>
        </SalespersonKey>
        <PriceLevelKey>
            <CompanyKey>
                <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
            </CompanyKey>
            <Id>RETAIL</Id>
        </PriceLevelKey>
        <ShipToAddressKey>
            <CompanyKey>
                <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
            </CompanyKey>
            <CustomerKey>
                <CompanyKey>
                    <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
                </CompanyKey>
                <Id>AARONFIT0001</Id>
            </CustomerKey>
            <Id>WAREHOUSE</Id>
        </ShipToAddressKey>
        <ShipToAddress>
            <Extensions/>
            <Line1>11403 45 St. South</Line1>
            <Line2/>
            <Line3/>
            <City>Chicago</City>
            <State>IL</State>
            <PostalCode>60603-0776</PostalCode>
            <CountryRegion>USA</CountryRegion>
            <Phone1>
                <Value>31255501020000</Value>
            </Phone1>
            <Phone2>
                <Value>31255501020000</Value>
            </Phone2>
            <Phone3>
                <Value/>
            </Phone3>
            <Fax>
                <Value>31255501020000</Value>
            </Fax>
            <Name>Aaron Fitz Electrical</Name>
            <ContactPerson>Bob Fitz</ContactPerson>
        </ShipToAddress>
        <Taxes/>
        <QuantityToBackorder>
            <Value>0</Value>
            <DecimalDigits>0</DecimalDigits>
        </QuantityToBackorder>
        <QuantityToInvoice>
            <Value>5.00000</Value>
            <DecimalDigits>0</DecimalDigits>
        </QuantityToInvoice>
        <QuantityCanceled>
            <Value>0</Value>
            <DecimalDigits>0</DecimalDigits>
        </QuantityCanceled>
        <QuantityFulfilled>
            <Value>5.00000</Value>
            <DecimalDigits>0</DecimalDigits>
        </QuantityFulfilled>
        <QuantityAllocated>
            <Value>5.00000</Value>
            <DecimalDigits>0</DecimalDigits>
        </QuantityAllocated>
        <FulfillDate xsi:nil="true"/>
        <ActualShipDate>2018-11-07T00:00:00</ActualShipDate>
        <Components/>
        <Serials/>
        <Lots>
            <SalesLineLot>
                <Extensions/>
                <DateReceived xsi:nil="true"/>
                <DateSequenceNumber xsi:nil="true"/>
                <IsPosted xsi:nil="true"/>
                <DeleteOnUpdate xsi:nil="true"/>
                <LotNumber>TESTLOTDRAla1</LotNumber>
                <Quantity>
                    <Value>5.00000</Value>
                    <DecimalDigits>0</DecimalDigits>
                </Quantity>
                <ManufacturedDate xsi:nil="true"/>
                <ExpirationDate xsi:nil="true"/>
                <UserDate>2018-11-07T00:00:00</UserDate>
                <Key>
                    <CompanyKey>
                        <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
                    </CompanyKey>
                    <SalesLineKey>
                        <CompanyKey>
                            <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
                        </CompanyKey>
                        <SalesDocumentKey>
                            <CompanyKey>
                                <Id xmlns="http://schemas.microsoft.com/dynamics/2006/01">-1</Id>
                            </CompanyKey>
                            <Id>DR1005</Id>
                        </SalesDocumentKey>
                        <LineSequenceNumber>0</LineSequenceNumber>
                    </SalesLineKey>
                    <QuantityType>On Hand</QuantityType>
                    <SequenceNumber>0</SequenceNumber>
                </Key>
            </SalesLineLot>
        </Lots>
        <Bins/>
    </SalesOrderLine>
</Lines>
How do a create a sql query (not using a join or union) and search multiple companies in gp

Need some SQL help here....

I have a table with the following columns: 

ItemNumber,   BinLocation, TransactionDateandTime

There are multiple entries in this table, based on what happens with Inventory.

Example:

Item         Bin          Date and Time stamp

123456    LOC A     2018-10-01 07:23:13.397

123456    LOC B     2018-11-06  10:19:01.540

123456    LOC C    2018-10-30  08:22:00.770

111222    LOC O   2018-11-07  06:54:45.373

111222    LOC P   2018-11-03   06:12:59.067

 

 

I want the record for each item with the most recent date to be extracted into another table I'm using for a Smartlist.

Desired result:

123456    LOC B     2018-11-06  10:19:01.540

111222    LOC O    2018-11-07  06:54:45.373

 

 

I am creating a calculated column to pull the GP PA Project ID from a segment of the GL string to build an PO PA Receipt import

I need to extract the Project ID if greater than 000 from the 6th segment of this account string XXX-15-XXXX-XXX-300-XXX where segment 2 equals 15 and segment 5 equals 300.

Note: Smartconnect requires that I write the calculated column code using .NET -  I believe I need to use Substring, but cannot locate any documentation that shows me the .Net Syntax for Substring.

here's is what I wrote:

Dim mySegtwo as String = _Account.ToString.Trim

Dim mySegFive as String = _Account.ToString.Trim

Dim mySegSix as String = _Account.ToString.Trim

If String.IsNullOrEmpty(mySegtwo) Then

     Return ""

Else If mySegtwo.Substring(5,2) = “15” and mySegFive.Substring(17,3) = “300” and mySegSix.Substring(21,3) > “000”  Then

  Return mySegSix.Substring(21,3)

End If

When I ran the code above - I got the message shown below? 

Just seeking assistance with this or if someone could possibly direct me to a site with some example .Net syntax - that would be ideal and greatly appreciated

Good morning,

  I want to test Manufacturing in Fabrikam but when the user enter to other company a Manufacturing Series Registration Window appear, the user need select "Cancel" to continue. I don't have Manufacturing registration key still because I want to test the Manufacturing module before buy a license but the window is an inconvenience for the user. how to avoid this window? see below

Any suggestion is welcome,

Mr. García


Does anyone have code that will help me add the 5 char DB name to all of the results of a Query of GP tables. 

If I query the CMPANYID from  SY00100 I get 1, then I go to Dyanmics  SY01500 and I look up 1 and I can get the long Company Name. But then I have no common fields to join the name to any other selected items.  Even if it worked, all I really want is the 5 Digit Database Name. 

Any help would be appreciated. 

 

Thank you

 

 

 

how to give access to multi-level query wizard to a user who is not poweruser

 

Hi there is there a procedure or flag in GP to stop a sales order from being edited. right now  our sales people often modify the sales order after it's sent to warehouse for fulfillment, causing lots of discrepancy at the time of invoicing. thanks!

Hi, i'm testing an econnect 14 integration on SOP Modulu in which i have to send a lot of records (14K thousand orders total /18k thousand lines total).

In order to do this i'm sending one big econnect transaction, data is OK, however i'm getting this error:

"The operation is not valid for the state of the transaction."

Seems like some sort of timeout or disconnection, i just wonder if somebody had this in the past and how to it was solved, below you'll see the stacktrace.

  at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateTransactionEntity(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.CreateTransactionEntity(String connectionString, String sXML)
   at SopConsApp.Program.Main(String[] args) in c:\Console Apps\SopConsApp\SopConsApp\Program.cs:line 266

Hello,

I am developing a web-based integration for Dynamics GP 2018 using .NET/MVC/C# and have run into an issue with eConnect. After using the 'UpdateEntity()' eConnect method, some of our Purchase Orders have their 'Payment Terms' values stripped. I have examined the value in the POP10100 table just before and after calling that method so I can safely say that it is not caused by another piece of the integration. I am also certain that we are not somehow setting the Payment Terms to empty. This error occurs even when the <PYMTRMID> Xml node is not included in the Xml passed to the 'UpdateEntity()' method.

I have done some research and compiled a list of Payment Terms values that do get dropped after updating, and others that do not.

Payment Term values that DO get dropped:

0.5%10 Net 25     -     dropped
0.5%25 Net 30     -     dropped
0.5%Net 10        -     dropped
1.5%10 Net 30     -     dropped
1.5%net 20        -     dropped

Payment Term values that do NOT get dropped:

1.5% 10 Net 45    -     not dropped
1%10 Net 30      -     not dropped
1%10 NET 60     -     not dropped
33% down / net 30     -    not dropped
2% 35 ,Net 45      -     not dropped
2%10Net45      -      not dropped

I have not been able to identify a clear reason as to why some values do get removed and others do not. I have compared the 'SY03300' records and nothing stands out there either.

Anyone have any ideas?

Using eConnect to import AUS dollar transaction into a GP company db which uses USD as the functional currency, I receive an out-of-balance error message (and the transaction does not post). The transaction clearly balances in AUS dollars.

Error Number = 944  Stored Procedure= taGLTransactionHeaderInsert  Error Description = Unbalanced journal entry, the credits do not equal the debits

Node Identifier Parameters: taGLTransactionHeaderInsert

BACHNUMB = SSCASH

JRNENTRY = 215452

REFRENCE = SS Cash 10/31/2018

TRXDATE = 10/31/2018 12:00:00 AM

TRXTYPE = 0

When I hand key the transaction into GP, it goes in just fine.  The system adds in a rounding line to accommodate a rounding difference when it translates to functional USD currency.  In this transaction’s case, there is a penny rounding difference when it translates to USD.   How can we get eConnect to perform the same rounding function for functional currency translation rounding that GP performs systematically when the transaction is hand keyed?

 

Hi, can you help me In which table the routes for the articles are saved thanks

Multiple bins can't be enabled because there are unposted invoices transactions"

 

Is there a script to see if there any open batches. There are none unposted batches in the user interface.


Is there any way to modify the underlying query that populates a Dynamics GP lookup window? I want to restrict the query that populates the PA Return Item Lookup window that auto-opens and populates when the user exits the Item Number field in the Project Inventory Transfer Entry window (Project > Transactions > Inventory Transfer Entry). Thanks.