Top Categories:
Dynamics GP
ClassicPress

Microsoft Dynamics GP Support from ISC Software

ISC Software SolutionsI’ve worked with Microsoft Dynamics GP for over 17 years and, like most people, do not work for the company with whom I first started. In fact, I am now on my third company working with Microsoft Dynamics GP (and starting to do some work with Microsoft Dynamics Business Central). 18 months after I left the first company they bowed out of supporting Dynamics GP; I now hear from a former client whom ISC Software were already on-boarding for support, that two years after I left the second company, Perfect Image Ltd., that they are also now bowing out of supporting Microsoft Dynamics GP.

A few of the clients I worked with at Perfect Image have already decided that they needed to change their support partner and moved across to ISC Software where they can continue to work with me and report being happy with the support, and other services they receive from the team and I.

With a new year about to dawn (and hopefully a better one that 2020), it might be time to review your support contract and consider moving to ISC Software.


Direct phone line to our support team

Online support portal

Dedicated account manager

Non-support related assistance, reports and modifications

Free GP Elementz Add-ons

Tailored Support Plans including 24/7/365 and unlimited

Value for money, plans start at £/€2,000

Accredited consultants with real world experience

All versions supported including older releases

3rd party products including Jet Reports & SmartConnect

Continue reading “Microsoft Dynamics GP Support from ISC Software”

Implementing Post Master Enterprise: Series Index

Envisage Software SolutionsIn this series of posts I am going to look at implementing Post Master Enterprise for Microsoft Dynamics GP from Envisage Software, an ISV based in Australia.

Post Master is one of the auto-posting tools available for Microsoft Dynamics GP and is one which I have sold a number of times. I’ll take a look at the capabilities of the application itself, before moving onto the installation and configuration.

This series index will automatically update as posts go-live, but if you’re reading via a syndication, you’ll need to go to the original page for the updates: Implementing Post Master Enterprise

Implementing Post Master Enterprise

SQL View for to Extract Accruals from Microsoft Dynamics GP Payables Management Module

Microsoft Dynamics GPI recently did a webinar for my employer, ISC Software, on Prepayments and Acruals and as part of the accruals section used a SQL View to extract the accruals to be created; as mentioned in thw webinar, the extract cna be done either using a SmartList or a direct query in SmartConnect.

The SQL view has been created using the EOMONTH function which si available only in later versions of SQL Server; you may need to tweak the script a little to handle getting dates in different ways, if you are running an older version of SQL Server.

IF OBJECT_ID (N'uv_ISC_PayablesAccruals', N'V') IS NOT NULL
    DROP VIEW uv_ISC_PayablesAccruals
GO

CREATE VIEW uv_ISC_PayablesAccruals AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT * FROM (SELECT 'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number' ,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date' ,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy') AS 'Reverse Date' ,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()), 'MM/yyyy') AS 'Reference' ,ISNULL(GL00105_DAIM.ACTNUMST,GL00105.ACTNUMST) AS 'Account'[/sqlred] ,CASE WHEN DOCTYPE = 1 THEN 0 ELSE CASE WHEN PP000100.PP_Module IS NOT NULL THEN SUM(PP000101.TRXAMNT) ELSE SUM(PM10100.CRDTAMNT) END END AS 'Debit' ,CASE WHEN DOCTYPE = 1 THEN CASE WHEN PP000100.PP_Module IS NOT NULL THEN SUM(PP000101.TRXAMNT) ELSE SUM(PM10100.DEBITAMT) END ELSE 0 END AS 'Credit' ,LEFT(RTRIM(CAST(PM10100.VCHRNMBR AS VARCHAR(15))) + ' ' + PM10100.DistRef, 30) AS 'Description' ,PM10100.VCHRNMBR AS 'Voucher Number' ,PM10000.BACHNUMB AS 'Batch Number' ,PM10000.TRXDSCRN AS 'Document Description' ,PM10000.DOCNUMBR AS 'Document Number' ,PM10000.PORDNMBR AS 'PO Number' ,PM10100.DistRef AS 'Distribution Reference' FROM PM10000 AS PM10000 --PM Transaction WORK File (PM10000) INNER JOIN PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100) on PM10100.CNTRLTYP = PM10000.CNTRLTYP AND PM10100.VCHRNMBR = PM10000.VCHRNMBR INNER JOIN GL00105 AS GL00105 --Account Index Master (GL00105) on GL00105.ACTINDX = PM10100.DSTINDX LEFT JOIN -- Join to RED PP000100 AS PP000100 --Deferral Header Work (PP000100) on PP000100.CNTRLTYP = PM10100.CNTRLTYP AND PP000100.PP_Document_Number = PM10100.VCHRNMBR AND PP000100.PP_Sequencer = PM10100.DSTSQNUM LEFT JOIN GL00105 AS GL00105_DAIM --Account Index Master (GL00105) on GL00105_DAIM.ACTINDX = PP000100.ACTINDX LEFT JOIN PP000101 AS PP000101 --Deferral Line Work (PP000101) on PP000101.PP_Module = PP000100.PP_Module AND PP000101.PP_Record_Type = PP000100.PP_Record_Type AND PP000101.PP_Document_Number = PP000100.PP_Document_Number AND PP000101.PP_Sequencer = PP000100.PP_Sequencer AND PP000101.PPOFFSEQ = PP000100.PPOFFSEQ AND PP000101.CNTRLTYP = PP000100.CNTRLTYP AND PP000101.VCHRNMBR = PP000100.VCHRNMBR AND PP000101.DSTSQNUM = PP000100.DSTSQNUM AND PP000101.GLPOSTDT <= EOMONTH(DATEADD(month,-1,GETDATE())) WHERE PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions AND PM10000.DOCTYPE = 1 --Include only Invoices AND PM10100.DISTTYPE = 6 --Include only Purchases Distribution GROUP BY GL00105.ACTNUMST ,GL00105_DAIM.ACTNUMST ,PM10000.DOCTYPE ,PP000100.PP_Module ,PM10100.DistRef ,PM10100.VCHRNMBR ,PM10000.BACHNUMB ,PM10000.TRXDSCRN ,PM10000.DOCNUMBR ,PM10000.PORDNMBR HAVING SUM(PP000101.TRXAMNT) > 0 --partial deferral OR PP000100.PP_Module IS NULL --no deferral UNION ALL SELECT 'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number' ,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date' ,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy'[/sqlred]) AS 'Reverse Date' ,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()),'MM/yyyy') AS 'Reference' ,GL00105.ACTNUMST AS 'Account' ,CASE WHEN PP000100.PP_Module IS NOT NULL THEN SUM(PP000101.TRXAMNT) ELSE SUM(PM10100.DEBITAMT) END AS 'Debit' ,CASE WHEN PP000100.PP_Module IS NOT NULL THEN SUM(PP000101.TRXAMNT) ELSE SUM(PM10100.CRDTAMNT) END AS 'Credit' ,'Accrued Purchases' AS 'Description' ,'' AS 'Voucher Number' ,'' AS 'Batch Number' ,'' AS 'Document Description' ,'' AS 'Document Number' ,'' AS 'PO Number' ,'' AS 'Distribution Reference' FROM PM10000 AS PM10000 --PM Transaction WORK File (PM10000) INNER JOIN PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100) on PM10100.CNTRLTYP = PM10000.CNTRLTYP AND PM10100.VCHRNMBR = PM10000.VCHRNMBR INNER JOIN GL00100 AS GL00100 --Breakdown Account Master (GL00100) on GL00100.ACTDESCR = 'Accrued Purchases' INNER JOIN GL00105 AS GL00105 --Account Index Master (GL00105) on GL00105.ACTINDX = GL00100.ACTINDX LEFT JOIN -- Join to RED PP000100 AS PP000100 --Deferral Header Work (PP000100) on PP000100.CNTRLTYP = PM10100.CNTRLTYP AND PP000100.PP_Document_Number = PM10100.VCHRNMBR AND PP000100.PP_Sequencer = PM10100.DSTSQNUM LEFT JOIN GL00105 AS GL00105_DAIM --Account Index Master (GL00105) on GL00105_DAIM.ACTINDX = PP000100.ACTINDX LEFT JOIN PP000101 AS PP000101 --Deferral Line Work (PP000101) on PP000101.PP_Module = PP000100.PP_Module AND PP000101.PP_Record_Type = PP000100.PP_Record_Type AND PP000101.PP_Document_Number = PP000100.PP_Document_Number AND PP000101.PP_Sequencer = PP000100.PP_Sequencer AND PP000101.PPOFFSEQ = PP000100.PPOFFSEQ AND PP000101.CNTRLTYP = PP000100.CNTRLTYP AND PP000101.VCHRNMBR = PP000100.VCHRNMBR AND PP000101.DSTSQNUM = PP000100.DSTSQNUM AND PP000101.GLPOSTDT <= EOMONTH(DATEADD(month,-1,GETDATE())) WHERE PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions AND PM10000.DOCTYPE = 1 --Include only Invoices AND PM10100.DISTTYPE = 6 --Include only Purchases Distribution GROUP BY GL00105.ACTNUMST ,GL00105_DAIM.ACTNUMST ,PM10000.DOCTYPE ,PP000100.PP_Module HAVING SUM(PP000101.TRXAMNT) > 0 --partial deferral OR PP000100.PP_Module IS NULL --no deferral ) AS Accruals WHERE Accruals.Debit >[/sqlgrey] 0 OR Accruals.Credit > 0 GO GRANT SELECT ON uv_ISC_PayablesAccruals TO DYNGRP GO

Identify and Fix Corrupt SOP Transactions in Microsoft Dynamics GP

Microsoft Dynamics GPA client recently logged a support call whereby reports were showing incorrect information, including for transactions which had been deleted. I did some exploring mof data and found that the Sales Transaction Amounts Work (SOP10200) and Sales User-Defined Work History (SOP10106) tables contained rows for transactions which were not in the Sales Transaction Work (SOP10100) table.

From reviewing the data, deleted transactions which had an entry in Sales User-Defined Work History (SOP10106) would also have one in Sales Transaction Amounts Work (SOP10200) making the job of identifying the corrupt ones somewhat easier (Sales User-Defined Work History (SOP10106) contains both Work and History rows).

The first script identifies rows in Sales User-Defined Work History (SOP10106) which are orphaned:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT DISTINCT SOPL.SOPNUMBE ,SOPL.SOPTYPE FROM SOP10200 AS SOPL --Sales Transaction Amounts Work (SOP10200) INNER JOIN SOP10106 AS SOPU --Sales User-Defined Work History (SOP10106) ON SOPU.SOPNUMBE = SOPL.SOPNUMBE AND SOPU.SOPTYPE = SOPL.SOPTYPE LEFT JOIN SOP10100 AS SOPH --Sales Transaction Work (SOP10100) ON SOPH.SOPNUMBE = SOPL.SOPNUMBE AND SOPH.SOPTYPE = SOPL.SOPTYPE WHERE SOPH.SOPNUMBE IS NULL GO

Continue reading “Identify and Fix Corrupt SOP Transactions in Microsoft Dynamics GP”

SQL View on Sales Order Processing Backorder Transactions

Microsoft Dynamics GPI’ve written this view for a number of different clients over the years, so am posting it here for future reference. It returns only open backorders which have yet top be transferred to order or invoice; it includes PO information so users can easily see if a PO has been raised and if any of it has been receipted.

The SQL below includes the

CREATE VIEW uv_AZRCRV_SOPOpenBackorders AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT ['Sales Transaction Work'].SOPNUMBE AS 'SOP Number' ,['Sales Transaction Work'].SOPTYPE AS 'SOP Type' ,['Sales Transaction Work'].DOCDATE AS 'Document Date' ,['Sales Transaction Work'].CSTPONBR AS 'Customer PO Number' ,['Sales Transaction Work'].CURNCYID AS 'Currency' ,['Sales Transaction Work'].CUSTNMBR AS 'Customer Number' ,['Sales Transaction Work'].CUSTNAME AS 'Customer Name' ,['Sales Transaction Amounts Work'].ITEMNMBR AS 'Item Number' ,['Sales Transaction Amounts Work'].ITEMDESC AS 'Item Description' ,['Sales Transaction Amounts Work'].QUANTITY AS 'Original Backorder Quantity' ,['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV AS 'Current Backorder Quantity' ,['Sales Transaction Amounts Work'].QUANTITY - (['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) AS 'Quantity Shipped' ,['Sales Transaction Amounts Work'].UNITPRCE AS 'Unit Price' ,['Sales Transaction Amounts Work'].XTNDPRCE AS 'Extended Price' ,(['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) * ['Sales Transaction Amounts Work'].UNITPRCE AS 'Current Extended Price' ,['Sales Transaction Amounts Work'].ORUNTPRC AS 'Originating Unit Price' ,['Sales Transaction Amounts Work'].OXTNDPRC AS 'Originating Extended Price' ,(['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) * ['Sales Transaction Amounts Work'].ORUNTPRC AS 'Current Originating Extended Price' ,['Sales Transaction Amounts Work'].LOCNCODE AS 'Site ID' ,['Sales Transaction Amounts Work'].ReqShipDate AS 'Requested Ship Date' ,['SOP_POP Link'].PONUMBER AS 'PO Number' ,['Purchase Order Lines'].VENDORID AS 'Vendor ID' ,['SOP_POP Link'].QTYRECVD AS 'Quantity Received' ,['Purchase Order Lines'].PRMSHPDTE AS 'Promised Ship Date' ,['Sales Line Comment Work and History'].CMMTTEXT AS 'Comment Text' FROM SOP10100 AS ['Sales Transaction Work'] --Sales Transaction Work (SOP10100) INNER JOIN SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200) ON ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE INNER JOIN RM00101 AS ['RM Customer Master'] --RM Customer MSTR (RM00101) ON ['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR LEFT JOIN SOP60100 AS ['SOP_POP Link'] --SOP_POPLink (SOP60100) ON ['SOP_POP Link'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE AND ['SOP_POP Link'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE AND ['SOP_POP Link'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ AND ['SOP_POP Link'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ LEFT JOIN SOP10202 AS ['Sales Line Comment Work and History'] --Sales Distribution Work and History (SOP10102) ON ['Sales Line Comment Work and History'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE AND ['Sales Line Comment Work and History'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE AND ['Sales Line Comment Work and History'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ AND ['Sales Line Comment Work and History'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ LEFT JOIN POP10110 AS ['Purchase Order Lines'] --Purchase Order Line (POP10110) ON ['Purchase Order Lines'].PONUMBER = ['SOP_POP Link'].PONUMBER AND ['Purchase Order Lines'].ORD = ['SOP_POP Link'].ORD WHERE ['Sales Transaction Work'].SOPTYPE = 5 GO GRANT SELECT ON uv_AZRCRV_SOPOpenBackorders TO DYNGRP GO

SQL Query to Get Login Password Expiry

Microsoft SQL ServerI don’t recall exactly why this script was required, but the below SQL query can be used to get the expiration date for logins in Microsoft SQL Server. Some of the data is available directly from the sys.sql_logins table, but other pieces had to be retrieved using the LOGINPROPERTY function:

SELECT
[‘SQL Logins’].name AS ‘LoginName’
,LOGINPROPERTY([‘SQL Logins’].name, ‘PasswordLastSetTime’) AS ‘PasswordLastSetTime’
,LOGINPROPERTY([‘SQL Logins’].name, ‘DaysUntilExpiration’) AS ‘DaysUntilExpiration’
,DATEADD(dd,CONVERT(int, LOGINPROPERTY ([‘SQL Logins’].name, ‘DaysUntilExpiration’)),CONVERT(datetime,LOGINPROPERTY([‘SQL Logins’].name,‘PasswordLastSetTime’))) AS ‘PasswordExpiration’
,[‘SQL Logins’].is_policy_checked AS ‘IsPolicyChecked’
,LOGINPROPERTY([‘SQL Logins’].name, ‘IsExpired”) AS ‘IsExpired’
,LOGINPROPERTY([‘SQL Logins’].name, ‘IsMustChange”) AS ‘IsMustChange’
,LOGINPROPERTY([‘SQL Logins’].name, ‘IsLocked”) AS ‘IsLocked’
,LOGINPROPERTY([‘SQL Logins’].name, ‘LockoutTime”) AS ‘LockoutTime’
,LOGINPROPERTY([‘SQL Logins’].name, ‘BadPasswordCount”) AS ‘BadPasswordCount’
,LOGINPROPERTY([‘SQL Logins’].name, ‘BadPasswordTime”) AS ‘BadPasswordTime’
,LOGINPROPERTY([‘SQL Logins’].name, ‘HistoryLength’) AS ‘HistoryLength’
FROM
sys.sql_logins AS [‘SQL Logins’]
WHERE
is_expiration_checked = 1
ORDER BY
[‘SQL Logins’].name

What Data Can SmartConnect Integrate into Microsoft Dynamics GP?

Microsoft Dynamics GPI’ve worked with a number of clients on SmartConnect from eOne Solutions this year as well as discussing with a few prospects. SmartConnect is a very flexible tool which can be used to integrate many different types of data. In this respect, it competes with the Microsoft Integration Manage product, but SmartConnect has many other features over Integration Manager.

Discussions on SmartConnect usually involve the question as to what data can be integrated by it into Dynamics GP; the short answer is if eConnect can do it then so can SmartConnect, but few people (including me) remember all of the nodes available within eConnect. As would be expected, this is also a common question direct to eOne, and one which Lorren Zemke addressed on the eOne blog: Tech Tuesday : What Data Can SmartConnect Integrate in Dynamics GP (eConnect).

Excluding Folders From a Cron Backup

Cron JobsA while ago, I posted an article on using cron to make backups of folders on Linux web hosting. One issue I’ve become aware of since then is that there are some folders I want to exclude from the backup process. I did some exploring and found the --exclude switch can be used to exclude a single folder; to exclude multiple folders, you can use the switch multiple times.

The example below, excludes the backup folder and anything which has a name starting with a .:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
/bin/tar -czvf /home/{username]/backups/backup_{foldername}_$(date +\%Y\%m\%d\%H\%M\%S).tar.gz --exclude=backups --exclude=".*" /home/{username}/{foldername}

The highlighted section shows the two exclude switches used to exclude the folders and files we don’t want to backup.

Error Posting Journals in One Microsoft Dynamics GP Company

Microsoft Dynamics GPA client reported an urgent support call a short time ago that they could not post any journals and were receiving this error when they tried:

Error message
Insert failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on comp

This error was produced for all users in only one of their 10+ companies; the other companies could have a journal posted by anyone with no issues. My initial question was to ask what had been changed or deployed in that database, but was told nothing. This meant some investigation.

I fairly quickly found a post from Tim Wappat covering how SQL indexed views are incompatible with Dynamics GP and will result in the exact error the client was seeing. this would make sense as the error referenced indexed views.

I used my find all SQL objects script to look for any object containing the SCHEMABINDING keyword, and found two SQL views in that one database.

After passing this information back to the client they again checked around and found a developer had deployed two SQL views for a report to the database first thing that morning; they hadn’t mentioned them as they didn’t expect them to have caused the problem. Once the views were updated to remove the index, the error was no longer produced when journals were posted.

Insert Segments from Text File/CSV into Microsoft Dynamics GP

Microsoft Dynamics GPIt seems that while I’ve posted about how to update segment descriptions from CSV in Microsoft Dynamics GP and how to copy them to a new company or even all companies, I’ve never actually posted the script I use to insert them.

I needed this script the other day and ended up using the update descriptions one as the basis for a new script to insert segments into Dynamics GP; this script will pick the text file (or CSV file if you change the second highlighted section to a comma), update any existing segments and insert new segments into the Segment Description Master (GL40200) table.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE TABLE #Segments ( SGMTNUMB VARCHAR(100) ,SGMNTID VARCHAR(100) ,DSCRIPTN VARCHAR(100) ) GO BULK INSERT #Segments FROM 'C:\Integrations\COA\Segments.txt' WITH ( FIELDTERMINATOR = '\t' ,ROWTERMINATOR = '\n' ,FIRSTROW = 2 ) GO UPDATE Segments SET Segments.SGMTNUMB = NewSegments.SGMTNUMB ,Segments.SGMNTID = NewSegments.SGMNTID ,Segments.DSCRIPTN = Left(NewSegments.DSCRIPTN, 31) FROM GL40200 AS Segments INNER JOIN #Segments AS NewSegments ON NewSegments.SGMTNUMB = Segments.SGMTNUMB AND NewSegments.SGMNTID = Segments.SGMNTID GO INSERT INTO GL40200 ( SGMTNUMB ,SGMNTID ,DSCRIPTN ) --VALUES ( SELECT SGMTNUMB ,SGMNTID ,LEFT(DSCRIPTN, 31) FROM #Segments AS NewSegments WHERE ( SELECT COUNT(*) FROM GL40200 AS Segments WHERE Segments.SGMTNUMB = NewSegments.SGMTNUMB AND Segments.SGMNTID = NewSegments.SGMNTID ) = 0 ) GO DROP TABLE #Segments GO

As always with a SQL script which makes changes, I’d make sure you have a good backup and test the script before running it in case you encounter problems.

Management Reporter Currency Does Not Exist Error

Microsoft Dynamics GPI was doing some training on Management Reporter for a client the other week and had an issue to follow up on. The issue was around multicurrency conversion which wasn’t working correctly. When doing some testing around the issue, I was able to reproduce the same currency error the client had encountered:

Currency could not be found error message
Currency XXXX does not exist for company XXXX. No values will be returned.

The currency did exist and on most reports was working fine. It took me a little investigation and research to find that this was an issue with the Data Mart connector; the reports using the legacy connector were working correctly.

This issue is covered in Microsoft KB Article 3058400; when using the Data Mart connector, currency lookups are done using the ISO Code of the Currency rather than the Currency Code itself.