DynDeveloper.com

DynDeveloper.com

Sign Up Now! Log In

Archives

 

If you have anything to do with Service Based Architecture or installing the Web Client, you'll want to read this. You don't need to memorize it, just remember where it is because it took me days to figure this out. It'll be on the Dynamics menu and the SBA menu, above.

The issue is the SSL cert that you have to use to get the Web Client to work. The two published approaches are to use a self-signed cert internally, or run a hosted approach and have GP on the public internet. Then you'd get a public IP and a public SSL cert, just like an eCom site would.

Today I'll walk you through an approach that uses an internal site (an intranet) and a public domain name and SSL Cert

Some tasks you have to do often. I have to frequently write reports that have addresses on them (invoices, statements). This function is something that I carry from job to job to properly format the address.

I'm going to post these two functions, and then at the bottom post the sql statement where I call them... 'cause this is kinda hard to explain. Probably easier to show. So far I've come across two different tables that hold these lookups, one for text fields and one for note fields.

The issue is that the Field_ID is different for different companies, and I need a report script to work against all companies. So, for each company, I need to look up the field ID instead of my usual method of hard coding it. j

(sigh)

Steve says that I need to blog this article. I don't know why.

https://winthropdc.wordpress.com/2015/11/05/gpugsummit-day-2/


 

What? Call a web service from SQL?

That's crazy talk.

This code will do it. It makes use of sp_OACreate and that requires special permissions. You'll need to grant permissions for that.


 

This is an advanced troubleshooting issue in the Web Client.

The symptom is that when you log onto the Dynamics GP web client and you enter in your AD username/password, you're directed to a second SQL logon request, shown below. We shouldn't need to do this, the AD logon should let us in.

This article discusses the fix.

I'm asked occasionally for documentation on how to set up security in SSRS. Here's a primer on how to do this

 

You need to run Microsoft Dynamics GP Utilities before you can run Microsoft Dynamics GP.  Do you want to launch Microsoft Dynamics GP Utilities now?

We have all seen this screen, but what do you do if this screen keeps popping up every time you enter GP?

I'm going to start this post by saying... I don't know. I start new integrations all the time, and what I usually do is load Visual Studio directly on the SQL Server box at the client. But they could have any version of Dynamics GP, and I have to guess what version of VS is supported by that version of Dynamics GP. So, I'm going to start a list.

Please, if you know one of the versions would you email us or add a comment to the bottom of the article?

The issue is that if you load a version of Visual Studio that is too high, you don't get the templates needed to do the work.

I used an older code to try and migrate the logins from 2008 R2 to 2014  and it wasn't recognizing sysxlogins 

 

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name = @login_name

 

 

so I found an updated code...

This sample Dynamics GP Integration Manager script is placed in the 'Before Document' script area, and is used to cancel the document based on the date. This specific example will not allow dates greater than or equal to the first of the next month.

 

This is a very simple stored procedure, but I thought I'd blog it because it might be useful in the future. We sometimes need to get the checkbook ID attached to an account index, this sproc will provide that.

While I know that this is not technically possible this is the issue I am faced when developing a report.

Two views and a table all reference the same data which is what I need however when I go to join them I receive Invalid Joins, so I looked at the naming and I have

three different versions. I am creating this report within Any view, however I believe this would occur regardless of the UI I am working with.

 

I would love to hear of any possible solutions.

 

 

Thank you

 

Mark

 

Hi,

 I have to import 3 types of Invoices into GP and was wondering what was the best method to accommodate the following scenarios.

In one file, 3 invoices will be present,  

1. Regular AP Invoices unrelated to PO's or Receipts, but in the same format as the next 2

2. 2 Way match - Invoices related to noninventory PO items (received or unreceived).

3. 3 Way Match - Invoices related to inventory PO items (received or unreceived).

 Should I use the Receivings Transaction Entry eConnect objects or the Enter/Match Invoice eConnect Objects, or both?

Any feedback will be very helpful.  thanks all!

~Mark

I am needing to see about stopping the quarterly Great Plains data move to historical from triggering a complete resend of invoice data to one of our systems.  Here is the information of the existing query that was created around 10 years ago, long before I arrived here. This is the most information I can string together.

 

This section holds a list of keys. The attributes of these keys tells the Check Integration application the location of Company Database, where to export the check flat-to, whether the account is active and when the account is active.

The SQL used to query the Great Plains database was originally provided by Anthony Hill:

   SELECT

       dbo.CM20200.CMTrxNum,

       dbo.CM20200.CMTrxType,

       dbo.CM20200.TRXDATE,

       dbo.CM20200.TRXAMNT,

       dbo.CM20200.CMLinkID,

       dbo.CM20200.paidtorcvdfrom,

       dbo.PM30300.VENDORID,

       dbo.PM30300.APTODCNM,

       dbo.PM30200.TRXDSCRN,

       dbo.PM30200.DOCDATE,

       dbo.PM30200.DOCAMNT,

       dbo.PM30200.DOCNUMBR,

       dbo.PM30200.BACHNUMB,

       dbo.PM30200.PORDNMBR,D.DistRef

   FROM

       dbo.CM20200 // Transaction Table

       INNER JOIN dbo.PM30300 ON

           (dbo.CM20200.CMTrxNum = dbo.PM30300.APFRDCNM) AND (dbo.CM20200.CMLinkID = dbo.PM30300.VENDORID)

       INNER JOIN dbo.PM30200 ON

           (dbo.PM30300.APTODCNM = dbo.PM30200.DOCNUMBR) AND

           (dbo.PM30300.VENDORID = dbo.PM30200.VENDORID) AND

           (dbo.PM30200.DOCTYPE = 1)  // 6 - Checks, 1 - Invoices - But we need the invoice data

       LEFT OUTER JOIN

           (SELECT DISTINCT VENDORID, VCHRNMBR, DistRef FROM PM30600 WHERE CNTRLTYP = 0 AND DISTTYPE = 6) D

           ON dbo.PM30300.VENDORID = D.VENDORID

           AND dbo.PM30300.APTVCHNM = D.VCHRNMBR

   WHERE

       (dbo.PM30200.TRXDSCRN LIKE @SystemID + '%') AND

       (dbo.CM20200.TRXDATE = @TRXDATE);

The SystemID from the Custom Config Key is used for the @SystemID parameter.

Check Reconciliation Query

   SELECT

       T.CUSTNMBR,

       CM.CUSTNAME,

       T.DOCDATE,

       T.GLPOSTDT,

       T.CHEKNMBR,

       sum(A.APPTOAMT) APPTOAMT,

       A.APTODCNM,

       T.BACHNUMB,

       T.RMDTYPAL,

       T.DOCNUMBR,

       DistWorkFile.DistRef

   FROM

   -- T

   (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,

       DOCNUMBR, DEX_ROW_TS, BACHNUMB, CHEKNMBR

   FROM RM20101 -- RM Apply Open File

   WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0) and (AGNGBUKT <= 1) -- Only completed items

   UNION

   SELECT

       CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,

       DOCNUMBR, DEX_ROW_TS, BACHNUMB, CHEKNMBR

   FROM RM30101 -- RM History File

   WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0) --  Only completed items

   ) T

   INNER JOIN RM00101 CM -- Customer Master

       ON T.CUSTNMBR = CM.CUSTNMBR

   INNER JOIN

   -- A

   (SELECT tO1.CUSTNMBR, APTODCTY, APTODCNM,

       APFRDCTY, APFRDCNM,         APPTOAMT

   FROM RM20201 tO2 -- RM Apply Open File

   INNER JOIN RM20101 tO1 -- RM Open File Debit Copy

       ON tO2.APTODCTY = tO1.RMDTYPAL

           AND tO2.APTODCNM = tO1.DOCNUMBR

   WHERE (APTODCNM LIKE @SystemID + '%')

   UNION

   SELECT tH1.CUSTNMBR, APTODCTY, APTODCNM,

       APFRDCTY, APFRDCNM,         APPTOAMT

   FROM RM30201 tH2 -- RM Apply History File

   INNER JOIN RM30101 tH1 -- RM History File

       ON tH2.APTODCTY = tH1.RMDTYPAL

           AND tH2.APTODCNM = tH1.DOCNUMBR

   WHERE (APTODCNM LIKE @SystemID + '%')

   ) A

       ON A.APFRDCTY = T.RMDTYPAL and A.APFRDCNM = T.DOCNUMBR

   INNER JOIN

   -- D

   (SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF

   FROM RM20101 -- RM Open File Debit Copy

   UNION

   SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF

   FROM RM30101 -- RM History File

   ) D

       ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR        

   LEFT OUTER JOIN

   -- Distribution Work File

   (SELECT DISTINCT DOCNUMBR, RMDTYPAL, DistRef

   FROM dbo.RM10101

   WHERE DISTTYPE = 9

   ) DistWorkFile ON

       D.DOCNUMBR = DistWorkFile.DOCNUMBR and

       D.RMDTYPAL = DistWorkFile.RMDTYPAL

   WHERE

       T.DEX_ROW_TS >= @DEX_ROW_TS and T.DEX_ROW_TS <= DATEADD(D,1,@DEX_ROW_TS)

   GROUP BY

       T.CUSTNMBR,

       CM.CUSTNAME,

       T.DOCDATE,

       T.GLPOSTDT,

       T.CHEKNMBR,

       A.APTODCNM,

       T.BACHNUMB,

       T.RMDTYPAL,

       T.DOCNUMBR,

       DistWorkFile.DistRef

   ORDER BY

       A.APTODCNM;

 

 

We are working on a larger Econnect Integration using .NET MVC.  We have come across an issue whereas the eConnect model is not binding to our form POST when working with eConnect objects but instead are returning an empty object.  All other objects are binding as expected in our bigger integration.  I have torn it down to its simplest form following Microsoft MVC standard below to show our issue. Is this expected behavior for eConnect objects?

The odd part about this is it works in WebServices using their objects.  Unfortunaly there are some missing peices from WebServices, like UpdateItem.

 

Test:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Microsoft.Dynamics.GP.eConnect.Serialization;
 
namespace Thruway_Intranet.Areas.Inventory.Controllers
{
    [OverrideAuthorization]
    [AllowAnonymous]
    public class TestController : Controller
    {
        // GET: Inventory/Test
        public ActionResult Index()
        {
            taUpdateCreateItemRcd item = new taUpdateCreateItemRcd();
            item.ITEMDESC = "dumby text";
 
            return View(item);
        }
 
        [HttpPost]
        public ActionResult Index(taUpdateCreateItemRcd newItem)
        {
             
            //returns ""
            var description1 = newItem.ITEMDESC;
 
            //returns value entered "dumby text"
            var description2 = Request.Form["ITEMDESC"];
 
            return View();
        }
    }
}
 
 
 
@model Microsoft.Dynamics.GP.eConnect.Serialization.taUpdateCreateItemRcd
 
@using (Html.BeginForm())
{
 
    <div class="form-horizontal">
 
        <div class="col-md-12">
 
            @* Displays "dumby text" *@
            <div class="form-group">
                @Html.LabelFor(model => model.ITEMDESC, "Item Description", htmlAttributes: new { @class = "control-label col-md-3" })
                <div class="col-md-9">
                    @Html.TextAreaFor(model => model.ITEMDESC, htmlAttributes: new { @class = "form-control", style = "height: inherit;", rows = "2", cols = "40" })
                    @Html.ValidationMessageFor(model => model.ITEMDESC, "", new { @class = "text-danger" })
                </div>
            </div>
 
            <div class="form-group">
                <div class="col-md-offset-2 col-md-10">
                    <input type="submit" value="Create" class="btn btn-default" />
                </div>
            </div>
 
        </div>
    </div>
}

I am trying to update an existing sales order that has 2 lines.  I'm basically sending the exact same XML I used to generate the order, but this time it includes the SOPNUMBE, LNITEMSEQs and the UpdateExisting / UpdateIfExists nodes.

I get this exact error message when I call UpdateTransactionEntity: If UpdateExisting is set to 1 than a transaction document number must be provided.

What am I doing wrong?

<?xml version="1.0" encoding="utf-8"?>
<eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <SOPTransactionType>
        <eConnectProcessInfo xsi:nil="true"/>
        <taRequesterTrxDisabler_Items xsi:nil="true"/>
        <taUpdateCreateItemRcd xsi:nil="true"/>
        <taUpdateCreateCustomerRcd xsi:nil="true"/>
        <taCreateCustomerAddress_Items xsi:nil="true"/>
        <taSopSerial_Items xsi:nil="true"/>
        <taSopLotAuto_Items xsi:nil="true"/>
        <taSopLineIvcInsert_Items>
            <taSopLineIvcInsert>
                <SOPTYPE>2</SOPTYPE>
                <SOPNUMBE>ORD007535</SOPNUMBE>
                <CUSTNMBR>SKYBZ_0001979</CUSTNMBR>
                <DOCDATE>11/9/2015</DOCDATE>
                <ITEMNMBR>GXT3000</ITEMNMBR>
                <UNITPRCE>240.00</UNITPRCE>
                <QUANTITY>5.00</QUANTITY>
                <DOCID>ORDER</DOCID>
                <ReqShipDate>11/4/2015</ReqShipDate>
                <ShipToName>Bill Wade</ShipToName>
                <PRSTADCD>SKYBZ_0001979_PRIMARY</PRSTADCD>
                <SHIPMTHD>UPS GND PPA</SHIPMTHD>
                <DEFEXTPRICE>1</DEFEXTPRICE>
                <UpdateIfExists>1</UpdateIfExists>
            </taSopLineIvcInsert>
        </taSopLineIvcInsert_Items>
        <taSopLineIvcInsertComponent_Items xsi:nil="true"/>
        <taSopTrackingNum_Items xsi:nil="true"/>
        <taSopCommissions_Items xsi:nil="true"/>
        <taSopLineIvcTaxInsert_Items xsi:nil="true"/>
        <taCreateSopPaymentInsertRecord_Items xsi:nil="true"/>
        <taSopUserDefined>
            <SOPTYPE>2</SOPTYPE>
        </taSopUserDefined>
        <taSopDistribution_Items xsi:nil="true"/>
        <taAnalyticsDistribution_Items xsi:nil="true"/>
        <taSopMultiBin_Items xsi:nil="true"/>
        <taSopHdrIvcInsert>
            <BACHNUMB>SFDCORDINT</BACHNUMB>
            <SOPTYPE>2</SOPTYPE>
            <UpdateExisting>1</UpdateExisting>
            <SOPNUMBE>ORD007535</SOPNUMBE>
            <DOCID>ORDER</DOCID>
            <SHIPMTHD>UPS GND PPA</SHIPMTHD>
            <DOCDATE>11/9/2015</DOCDATE>
            <CUSTNMBR>SKYBZ_0001979</CUSTNMBR>
            <CSTPONBR></CSTPONBR>
            <ShipToName>Bill Wade</ShipToName>
            <PRBTADCD>SKYBZ_0001979_PRIMARY</PRBTADCD>
            <PRSTADCD>SKYBZ_0001979_PRIMARY</PRSTADCD>
            <CMMTTEXT></CMMTTEXT>
            <CREATECOMM>1</CREATECOMM>
            <CREATETAXES>1</CREATETAXES>
            <DEFTAXSCHDS>1</DEFTAXSCHDS>
            <DEFPRICING>1</DEFPRICING>
            <CREATEDIST>1</CREATEDIST>
        </taSopHdrIvcInsert>
        <taSopToPopLink xsi:nil="true"/>
        <taSopUpdateCreateProcessHold xsi:nil="true"/>
        <taCreateSOPTrackingInfo xsi:nil="true"/>
        <taMdaUpdate_Items xsi:nil="true"/>
    </SOPTransactionType>
</eConnect>

 
 

 

Hi,

 I have been given the task of integrating with a dynamics solution and creating Purchase Invoices.  This is all going according to plan, however there is one stumbling block that I cannot resolve.

 

For a purchase invoice, there is a TaxScheduleKey - this relates to the form within dynamics called Tax Schedule Maintenance, and each Tax Schedule has a configured Tax Detail.

 

What I need to do is pull a list of all of these Tax Schedules and their related Tax Details from dynamics and synchronise it with our own database to provide lookups so they can alter the tax rates of the invoice before posting to dynamics, but I cannot seem to find any method for retrieving either, I can only seem to set the Tax Schedule key.  

 

There are over 40 dynamics databases I need to synchronise with, so unfortunately I don't really see a manual import of the data as an option (except a last resort)

 

Thanks

I am having the following issue with the Fixed Asset Book Integration Manager.  Please review the error message below.

Opening source query...
Establishing source record count...
Beginning integration...
DOC 1 ERROR: Sql procedure error codes returned:

Error Number = 10363  Stored Procedure= taCreateAssetBook  Error Description = The Asset ID/Asset Suffix combination does not exist in the Fixed Assets Master Table (FA00100)
Node Identifier Parameters: taCreateAssetBook
ASSETID = 1500001
ASSETIDSUF = 1
BOOKID = Corporate
Related Error Code Parameters for Node : taCreateAssetBook
ASSETID = 1500001

Regards,

ZW

Dynamics CRM 2013-2015 Upgrade Questions

Regarding some clarifications to the 2013-2015 Migration, I have the following thoughts and questions... would greatly appreciate any input insofar as corrects to the thought process and answers to these questions. Many thanks in advance.

1. As a prerequisite to installing the CRM 2015 Server product,  installation of SQL Server 2014 is performed. During the SQL Server 2014 installation, a new instance is created, e.g. CRM2015.

Question:

During the creation of this new instance (using SQL Server setup) should the ReportServer database be included ? It would seem that we would want only one ReportServer database in all instances, not one in each instance.

 2. Another SQL Server Instance is created so that the CRM2013 database can be restored to it, and from there the Organization Can be imported (which will be imported into yet another New Instance). Note: Only one CRM Database can exist in each instance.

Questions:

During the SQL Server Installation creation of this new instance should the ReportServer database be included ?

Does the ReportServer database from the original 2013 CRM Database need to be restored to this instance? What about existing and custom reports that may have been created in 2013 and how are they migrated ?

 

 3. The 2013 CRM databases:

PRMCRM2013_MSCRM

MSCRM_CONFIG

Are to be restored to the instance created in Step 2 above. This restored database will be used as the restore source during the Import Organization Utility, and will create a new database with upgraded 2015 data.

Questions:

Where is the data migrated to? Is a new instance created, since it is understood that only one CRM Database can reside in one SQL Server instance? In addition, in which instance is the primary ReportServer Database going to reside ? Are configuration changes required to be made to location of the ReportServer database, because during the Import Organization routine the ReportServer URL address was pointed to the CRM2015 Instance (the new install of 2015 where the SSRS Data Connection Tools were pointed to). It would seem like there needs to be a reconfiguration of the ReportServer URL after the database has been successfully upgraded to the new instance, and a re-install of the SSRS Data Tools Extensions and Data Connector to the new, upgraded database--uncertain where this new database will reside (e.g. in which SQL Server Instance).

4. Received an error message during Import Organization, User Mapping. All users are not mapped.

Question:

How can this be addressed without errors so the process is more streamlined?

5. Receiving a defragment/rebuild warning on indexes. This can be resolved using the following TSQL Script to rebuild all indexes in a database

DECLARE @Database VARCHAR(255)  

DECLARE @Table VARCHAR(255) 

DECLARE @cmd NVARCHAR(500) 

DECLARE @fillfactor INT

 

SET @fillfactor = 90

 

DECLARE DatabaseCursor CURSOR FOR 

SELECT name FROM master.dbo.sysdatabases  

WHERE name NOT IN ('master','msdb','tempdb','model','distribution', 'MSCRM_CONFIG','ReportServer$CRM2013', 'ReportServer$CRM2013TempDB')  

ORDER BY

 

OPEN DatabaseCursor 

 

FETCH NEXT FROM DatabaseCursor INTO @Database 

WHILE @@FETCH_STATUS =

BEGIN 

 

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +

  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES

  WHERE table_type = ''BASE TABLE'''  

 

   -- create table cursor 

   EXEC (@cmd) 

   OPEN TableCursor  

 

   FETCH NEXT FROM TableCursor INTO @Table  

   WHILE @@FETCH_STATUS = 0  

   BEGIN  

 

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)

       BEGIN

           -- SQL 2005 or higher command

           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

           EXEC (@cmd)

       END

       ELSE

       BEGIN

          -- SQL 2000 command

          DBCC DBREINDEX(@Table,' ',@fillfactor) 

       END

 

       FETCH NEXT FROM TableCursor INTO @Table  

   END  

 

   CLOSE TableCursor  

   DEALLOCATE TableCursor 

 

   FETCH NEXT FROM DatabaseCursor INTO @Database 

END 

CLOSE DatabaseCursor  

DEALLOCATE DatabaseCursor

 

 6. Received a warning that indexes are not consistent with Microsoft Dynamics CRM Indexes:

The indexes on the Microsoft Dynamics CRM database are not consistent with Microsoft Dynamics CRM indexes.

The following indexes are not consistent with Microsoft Dynamics CRM indexes:

tablename:                              auditbase

indexname:                              ndx_primarykey_audit

remark:                                 deleted index

tablename:                              auditbase

indexname:                              ndx_primarykey_audit_primary

remark:                                 deleted index

 

Questions:

How should this be dealt with – does the Import Organization utility make necessary overrides ? Will this adversely affect the database ?

 7. Received a warning message THAT THE FOREIGN KEY CONSTRAINTS IN THE Microsoft Dynamics CRM Database are not consistent with Microsoft Dynamics CRM foreign key constraints:

The foreign key constraints in the Microsoft Dynamics CRM database are not consistent with Microsoft Dynamics CRM foreign key constraints.

 The following foreign key constraint(s) are not consistent with Microsoft Dynamics CRM foreign key constraints:

name:                                   activity_pointer_new_dispatchlog

remark:                                 added foreign key

name:                                   activity_pointer_po_surveyactivity

remark:                                 added foreign key

name:                                   new_appointment_opportunity_quailifier

remark:                                 added foreign key

name:                                   new_competitor_lead_currentservicecompany

remark:                                 added foreign key

name:                                   new_contact_account_billingmanager

remark:                                 added foreign key

name:                                   new_contact_account_secondarycontact

remark:                                 added foreign key

name:                                   new_contact_contact_reportsto

remark:                                 added foreign key

etc. …

Question:

It looks like the import utility make corrections to these foreign key constraints. Is this correct?