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!
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