Top Categories:
Dynamics GP
ClassicPress

“Unable to obtain the next FAINDEX” Error When Integrating Data into Microsoft Dynamics GP

Microsoft Dynamics GPWhen doing some work with a client recently, they reported an error in SmartConnect when trying to import new assets into Microsoft Dynamics GP.

This integration had been in use for a while and was known to work; it was was only being encountered in a newly created company.

The error they were receiving was very brief:

SmartConnect error

Unable to obtain the next FAINDEX

A quick search revealed a Microsoft Docs article covering this issue. As no assets have yet been created, the next number hasn’t yet been initiated so integrations can’t get the next FAINDEX. The solution is to manually create the first asset in Dynamics GP, after which the integration should run successfully.

However, the first time we tried the integration after manually creating an asset, we received another error:

SmartConnect error

Unable to obtain the next Financial Index

The solution to this was also quite simple; on the manually created asset, manually add the Book information.

Once a single asset was created and had it’s book information added, the integration was then able to run successfully.

Import Custom Company Colours into Security Informer Module for Microsoft Dynamics GP

ISC Software SolutionsThis post is part of the GP Elementz from ISC Software series.

One of the available modules in the GP Elementz is the Color Changer. This allows you to change the colour of certain elements of the Microsoft Dynamics GP client such as the windows background. Out-of-the-box the available colours are quite strong and I was asked by a client if we could add some softer colours.

In order to get them the colours quickly, I had a look around online for some pastel colours and found a reasonable palette which I worked out the RGB values for. Those colours are embedded in the below script which, when run against the system database, will insert them into the ELZ_COLD table for use in the Color Changer tool.

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (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). */
INSERT INTO ELZ_COLD ( ELZ_Definition_ID ,DSCRIPTN ,ELZ_Red_Paint ,ELZ_Green_Paint ,ELZ_Blue_Paint ,ELZ_Colour_Value ) VALUES ('Azure*','Azure','0','124','255','31999') ,('Turquoise*','Turquoise','172','233','221','11332061') ,('Emerald*','Emerald','181','239','206','11923406') ,('Peter River*','Peter River','183','220','244','12049652') ,('Amethyst*','Amethyst','221','198','231','14534375') ,('Wet Asphalt*','Wet Asphalt','185','192','201','12173513') ,('Green Sea*','Green Sea','173','222','212','11394772') ,('Nephritis*','Nephritis','180','228','201','11855049') ,('Belize Hole*','Belize Hole','181','212','233','11916521') ,('Wisteria*','Wisteria','219','189','229','14400997') ,('Midnight Blue*','Midnight Blue','181','189','196','11910596') ,('Sun Flower*','Sun Flower','251','235','165','16509861') ,('Carrot*','Carrot','246','211','175','16176047') ,('Alizarin*','Alizarin','248','195','185','16303033') ,('Clouds*','Clouds','250','252','252','16448764') ,('Concrete*','Concrete','219','226','226','14410466') ,('Orange*','Orange','253','221','170','16637354') ,('Pumpkin*','Pumpkin','240','195','163','15778723') ,('Pomegranate*','Pomegranate','235','186','180','15448756') ,('Silver*','Silver','232','234','236','15264492') ,('Asbestos*','Asbestos','211','217','217','13883865') GO

GP Elementz from ISC Software

GP Elementz from ISC Software
What add-ons are available?
GP Elementz Portals
Features of PurchaseHQ from ISC Software: Introduction to PurchaseHQ
Features of PurchaseHQ from ISC Software: Introduction to PurchaseHQ [Video]
Features of PurchaseHQ from ISC Software: Secure access
Features of PurchaseHQ from ISC Software: Secure access [Video]
Features of PurchaseHQ from ISC Software: Configuration
Features of PurchaseHQ from ISC Software: Configuration [Video]
Features of PurchaseHQ from ISC Software: Setup images
Features of PurchaseHQ from ISC Software: Setup images [Video]
Features of PurchaseHQ from ISC Software: Granting users access
Features of PurchaseHQ from ISC Software: Granting users access [Video]
Features of PurchaseHQ from ISC Software: Enter a Purchase Requisition
Features of PurchaseHQ from ISC Software: Enter a Purchase Requisition [Video]
Features of PurchaseHQ from ISC Software: Enter a Purchase Order
Features of PurchaseHQ from ISC Software: Enter a Purchase Order [Video]
Features of PurchaseHQ from ISC Software: View my orders
Features of PurchaseHQ from ISC Software: View my orders [Video]
Features of PurchaseHQ from ISC Software: Workflow Integration
Features of PurchaseHQ from ISC Software: Workflow Integration [Video]
Features of PurchaseHQ from ISC Software: Document Approval
Features of PurchaseHQ from ISC Software: Document Approval [Video]
Features of PurchaseHQ from ISC Software: Receive Goods
Features of PurchaseHQ from ISC Software: Receive Goods [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Overview
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Overview [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) configuration
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) configuration
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) configuration [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Create Approvers
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Create Approvers [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Configure Delegation
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Configure Delegation [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) submit transaction for approval
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) submit transaction for approval [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) in portal
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) in portal [Video]
Features of PurchaseHQ from ISC Software: Free Trial Available
Features of PurchaseHQ from ISC Software: Free Trial Available [Video]
Features of CustomerHQ from ISC Software: Introduction to CustomerHQ
Features of CustomerHQ from ISC Software: Introduction to CustomerHQ [Video]
Features of CustomerHQ from ISC Software: Secure access
Features of CustomerHQ from ISC Software: Secure access [Video]
Features of CustomerHQ from ISC Software: Configuration
Features of CustomerHQ from ISC Software: Configuration [Video]
Features of CustomerHQ from ISC Software: Change images
Features of CustomerHQ from ISC Software: Change images [Video]
Features of CustomerHQ from ISC Software: Manage customer accounts
Features of CustomerHQ from ISC Software: Manage customer accounts [Video]
Features of CustomerHQ from ISC Software: Manage salesperson accounts
Features of CustomerHQ from ISC Software: Manage salesperson accounts [Video]
Features of CustomerHQ from ISC Software: Account Summary
Features of CustomerHQ from ISC Software: Account Summary [Video]
Features of CustomerHQ from ISC Software: View transactions
Features of CustomerHQ from ISC Software: View transactions [Video]
Features of CustomerHQ from ISC Software: View Statements
Features of CustomerHQ from ISC Software: View Statements [Video]
Features of CustomerHQ from ISC Software: Free Trial available
Features of CustomerHQ from ISC Software: Free Trial available [Video]
Features of SalesHQ from ISC Software: Introduction to SalesHQ
Features of SalesHQ from ISC Software: Introduction to SalesHQ [Video]
Features of SalesHQ from ISC Software: Secure access
Features of SalesHQ from ISC Software: Secure access [Video]
Features of SalesHQ from ISC Software: Configuration
Features of SalesHQ from ISC Software: Configuration [Video]
Features of SalesHQ from ISC Software: Change images
Features of SalesHQ from ISC Software: Change images [Video]
Features of SalesHQ from ISC Software: Manage customer accounts
Features of SalesHQ from ISC Software: Manage customer accounts [Video]
Features of SalesHQ from ISC Software: Manage salesperson accounts
Features of SalesHQ from ISC Software: Manage salesperson accounts [Video]
Features of SalesHQ from ISC Software: Account Summary
Features of SalesHQ from ISC Software: Account Summary [Video]
Features of SalesHQ from ISC Software: View transactions
Features of SalesHQ from ISC Software: View transactions [Video]
Features of SalesHQ from ISC Software: View statement
Features of SalesHQ from ISC Software: View statement [Video]
Features of SalesHQ from ISC Software: Inventory Lookup
Features of SalesHQ from ISC Software: Inventory Lookup [Video]
Features of SalesHQ from ISC Software: Price Lookup
Features of SalesHQ from ISC Software: Price Lookup [Video]
Features of SalesHQ from ISC Software: Add Prospect
Features of SalesHQ from ISC Software: Add Prospect [Video]
Features of SalesHQ from ISC Software: Create quote
Features of SalesHQ from ISC Software: Create quote [Video]
Features of SalesHQ from ISC Software: Create order
Features of SalesHQ from ISC Software: Create order [Video]
Features of SalesHQ from ISC Software: Create invoice
Features of SalesHQ from ISC Software: Create invoice [Video]
Features of SalesHQ from ISC Software: Free Trial
Features of SalesHQ from ISC Software: Free Trial [Video]
GP Elementz Add-ins
Features of Enhanced Notes from ISC Software: Introduction to Enhanced Notes
Features of Enhanced Notes from ISC Software: Introduction to Enhanced Notes [Video]
Features of Enhanced Notes from ISC Software: Individual notes
Features of Enhanced Notes from ISC Software: Individual notes [Video]
Features of Enhanced Notes from ISC Software: Categories/Subcategories
Features of Enhanced Notes from ISC Software: Categories/Subcategories [Video]
Features of Enhanced Notes from ISC Software: Assign tasks
Features of Enhanced Notes from ISC Software: Assign tasks [Video]
Features of Enhanced Notes from ISC Software: Available for reporting
Features of Enhanced Notes from ISC Software: Available for reporting [Video]
Features of Enhanced Notes from ISC Software: Free Trial
Features of Enhanced Notes from ISC Software: Free Trial [Video]
Features of Financial Elementz from ISC Software: Introduction to Financial Elementz
Features of Financial Elementz from ISC Software: Introduction to Financial Elementz [Video]
Features of Financial Elementz from ISC Software: Available imports
Features of Financial Elementz from ISC Software: Available imports [Video]
Features of Financial Elementz from ISC Software: General Ledger Code Import
Features of Financial Elementz from ISC Software: General Ledger Code Import [Video]
Features of Financial Elementz from ISC Software: Create General Ledger import
Features of Financial Elementz from ISC Software: Create General Ledger import [Video]
Features of Financial Elementz from ISC Software: Run General Ledger import
Features of Financial Elementz from ISC Software: Run General Ledger import [Video]
Features of Financial Elementz from ISC Software: Create Payables Transaction import
Features of Financial Elementz from ISC Software: Create Payables Transaction import [Video]
Features of Financial Elementz from ISC Software: Run Payables Transaction import
Features of Financial Elementz from ISC Software: Run Payables Transaction import [Video]
Features of Financial Elementz from ISC Software: Create Manual Payments import
Features of Financial Elementz from ISC Software: Create Manual Payments import [Video]
Features of Financial Elementz from ISC Software: Run Manual Payments import
Features of Financial Elementz from ISC Software: Run Manual Payments import [Video]
Features of Financial Elementz from ISC Software: Create Receivables Transaction import
Features of Financial Elementz from ISC Software: Create Receivables Transaction Import [Video]
Features of Financial Elementz from ISC Software: Run Receivables Transaction import
Features of Financial Elementz from ISC Software: Run Receivables Transaction Import [Video]
Import Custom Company Colours into Security Informer Module for Microsoft Dynamics GP

New Book on Microsoft Dynamics GP Workflow Now Available

Microsoft Dynamics GPMy tenth book is now available to buy in eBook format from azurecurve Publishing, paperback and Kindle versions from Amazon US and UK.

This is a new update to the Workflow book I’ve written three prior editions over the last few years. This edition has had some chapters and sections have been rewritten to either expand or make clearer the topics being covered. It also covers the creation and use of an AD authorisation store for security settings as required for securing the web services with TLS 1.0 and 1.1 disabled.

eBook Paperback
azurecurve Amazon US Amazon UK

Continue reading “New Book on Microsoft Dynamics GP Workflow Now Available”

Microsoft Dynamics GP VBA Showing as Locked

Microsoft Dynamics GPI was asked to help a customer with a new Microsoft Dynamics GP client installation as they were encountering some issues with some old VBA customisations. Hearing it was a new server, I wondered if the problem was that they had used Windows Server 2019, on which VBA customisations will not work and will actually cause Dynamics GP to crash immediately.

However, when I delved into the issue with them we found that the VBA customisations were showing as locked in the Customisation Maintenance window ((Microsoft Dynamics GP menu » Tools » Customise » Customisation Maintenance)):

Customisation Maintenance windows showing locked VBA

Continue reading “Microsoft Dynamics GP VBA Showing as Locked”

Microsoft Dynamics GP Table Reference Wesbite Updated to Fall 2021 Release

Microsoft Dynamics GPI’ve just updated the Microsoft Dynamics GP Table Reference site to the Fall 2021 Release. Any of the new tables or columns introduced from the version it was (2018 RTM) to 2021 will now be present. If you do notice any omissions or issues, please do get in touch with details and I’ll see about a resolution.

As always, if you need version specific tables for Dynamics GP this is available in the GP Power Tools module available from Winthrop DC.

Enable the Windows Distributed Transaction Coordinator

Windows ServerOne of the line of business applications used by a few clients performs updates to both it’s own database and also the Microsoft Dynamics GP database. It does this using code in SQL stored procedures which use transaction boundaries.

As the updates are between SQL servers, it does this using BEGIN DISTRIBUTED TRANSACTION rather than the usual BEGIN TRANSACTION; updates of this sort are, as stated in the name, distributed and required the Distributed Transaction Coordinator (DTC) Windows component to be enabled, which it is not by default, on both servers.

However, enabling DTC is straightforward and quick to do. Launch Component Services and expand the nodes (Computers » My Computer » Distributed Transaction Coordinator) and right click on Local DTC; select Properties:

Component Services window

Continue reading “Enable the Windows Distributed Transaction Coordinator”

Management Reporter Multicurrency Error

Microsoft Dynamics GPI meant to post this a while ago, but apparently didn’t and have just found the screen shots again. This was reported to me by a client who was trying to run a multicurrency report in Management Reporter.

When columns were flagged with currency translations, this error was produced and shown in the Report Queue Status window:

Report Queue Status

An internal error occurred in the provider. Additional information is available in the event viewer on the Management Reporter server. Please contact the administrator.

I checked in the event viewer and found this error:

Event Viewer

Invalid column name 'TranslationDebitAmount'.
Invalid column name 'TranslationCreditAmount'.

After doing some investigation, I realised that this error is one I have seen before. The issue is that there is a longstanding bug in the legacy connection of Management reporter; if you need to use currency translations, then you need to be using the Management Reporter Data Mart.

Replace Expired SSL Certificate Binding with the Web Services for Microsoft Dynamics GP

Microsoft Dynamics GPA few years ago I posted about securing the Web Services for Microsoft Dynamics GP which is something I would recommend that everyone does, most especially if they are exposed externally.

However, when the certificate expires and needs to be replaced, there are a few steps you need to follow to do this and they are mainly done at the command line as it is the certificate binding which needs to be updated.

First, you need to delete the certificate (the highlighted section is the port used when the certificate was originally bound):

netsh http delete sslcert ipport=0.0.0.0:48666

Second, you need to add the new certificate; the certhash is the certificate thumbprint:

netsh http add sslcert ipport=0.0.0.0:48666 certhash=7c69255774b41dd0f4edc3a34514e5f08a526443 appid={8EFC5047-33C1-438D-B76B-3634DDCF585F}

Once the above steps have been completed, the Microsoft Dynamics GP Service Host service should be restarted.

Historical Inventory Trial Balance VS the Historical Stock Status Report in Microsoft Dynamics GP

Microsoft Dynamics GPI’m a little late, but there was a good article fromAngela Eckman Ebensteiner on the Dynamics GP Support and Services Blog on the differences between the Historical Inventory Trial Balance and the Historical Stock Status Report and why the HITB (Historical Inventory Trial Balance) is the one you should be using to determine the value of your stock on a given day and to reconcile with the GL.

If you’re a user of the Inventory Control module, then the article is definitely worth a few minutes of your time.

Clear Pending Batches from Microsoft Dynamics GP Generate EFT File Window

Microsoft Dynamics GPBack in Microsoft Dynamics GP 10 and before, the ability to produce EFT files was not available in UK language installs. This meant a lot of clients used other methods for producing the EFT files and many of them are still using those alternative methods. That said, I’ve done work with a few recently where they had changed banks or otherwise needed to mke changes to the EFT format, so we hve switched them to using the standard functionality so the format can be maintained esily through the EFT File Format window ((Financial area page » Cards » EFT File Format)).

This largely works, but as the clients had been running EFT payment runs, there was a multi-year backlog of entries in the Generate EFT File window ((Financial area page » Transactions » Generate EFT File)) which needed to be cleared. While it is possible to hit the generte button and have them cleared by creating the files, for some cients we were looking at hundreds of files which would need to be created.

Instead I explored the database and found two tables holding entries which would need to be cleared down to remove the pending batches:

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (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). */
DELETE FROM CM20202 --Checkbook Transaction Electronic Funds Transfer (CM20202) DELETE FROM CM20203 --Checkbook EFT Transaction Batch (CM20203)

This script removes all entries from the Generate EFT File window; before running the script make sure you don’t need any of the entries, test the script on a test company or system and make sure you have a good backup before running the script on live.