Top Categories:
Dynamics GP
ClassicPress

SQL script to populate the Payables Distribution Type SETP (PM40103) table

Microsoft Dynamics GPI recently wrote a report for a client which included payables distributions; in order to output the distribution type, I joined to the Payables Distribution Type SETP (PM40103) table. Unfortunately, when the report was deployed, it didn’t work: all of the rows had a blank distribution type.

When I investigated I found that the Payables Distribution Type SETP (PM40103) table was empty. My demo environment is running Fabrikam and had fifteen rows of data, so I decided to migrate my table content to the clients system using a simple SQL script to insert the extracted rows.

The script is a simple one which doesn’t do any checking so confirm the table is empty before running.

/*
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). */
INSERT INTO PM40103 (DISTTYPE, DSNMSHRT, DSNMLONG) VALUES (1, 'CASH', 'CASH') ,(2, 'PAY', 'Accounts Payable') ,(3, 'AVAIL', 'Discounts Available') ,(4, 'TAKEN', 'Discounts Taken') ,(5, 'FNCHG', 'Finance Charge') ,(6, 'PURCH', 'Purchases') ,(7, 'TRADE', 'Trade Discounts') ,(8, 'MISC', 'Miscellaneous') ,(9, 'FREIGHT', 'FREIGHT') ,(10, 'TAXES', 'TAXES') ,(11, 'WRITE', 'Write Offs') ,(12, 'OTHER', 'OTHER') ,(13, 'GST', 'GST') ,(14, 'PPS', 'PPS') ,(15, 'UNIT', 'UNIT')

SmartList searching with wildcards

Microsoft Dynamics GPI was recently showing some clients how to use wildcard searching in SmartListand looked up my old blog post to refresh mym memory. When I was searching for my page, I found one from Shawn Dorward which he posted a couple of years ago and which covered some additional searching which was available.

Shawn’s blog post contains a some really good example of searching and also covers a couple of items, such as all fields of which I was not aware.

Windows rotation lock missing

WindowsI use a small convertible laptop (the screen rotates through 360o to turn into a tablet) for a lot of web browsing; occasionally I convert it into a tablet and switch allow the screen to rotate from landscape to portrait mode. However, when I tried to do this by switching off the Rotation Lock option the other day, the option in the action centre was missing; it is usually between Tablet Mode and Network:

Rotation Lock missing from the action centre

Continue reading “Windows rotation lock missing”

Bad Parameter in refreshable Excel report query

Microsoft Dynamics GPOver the last few months, I’ve been doing work with clients on creating some refreshable Excel reports. Working on one of them, we had parameters for dates and added one for Vendor ID, which is when I encountered an error:

Excel error message

Microsoft Excel

Bad parameter type. Microsoft Excel is expecting a different ind of value than what was provided.

Continue reading “Bad Parameter in refreshable Excel report query”

Workflows available in Microsoft Dynamics GP

Microsoft Dynamics GPSince Microsoft Dynamics GP 2013 R2 saw the introduction of a new Workflow module for approval of documents, almost every version of Dynamics GP since then has introduced new functionality to workflow; sometimes in the form of new approval types or integration with other modules such as Document Attachment.

This diagram shows the available workflow types, colour-coded by version introduced:

Available workflow approval types

If you are looking to implement workflow, I have a book which can guide you though configuring the Microsoft Dynamics GP Workfow module from the basics of designing your workflow process, through building it and the ways that users can interact with the workflow for approvals.

The Workflow module is quite popular and becoming much more so with so many clients working in a distributed manner. There is one problem on the horizon and that is the retirement of TLS 1.0 and 1.1 which the Web Services for Microsoft Dynamics GP rely on. We are waiting for confirmation as to when a fix for this will be available, but I am expecting the fix to require an upgrade of Microsoft Dynamics GP which isn’t always quickly possible.

Fortunately, the company for which I work, ISC Software, are both a VAR and an ISV with a number of products and services available.

One of those products is PurchaseHQ which provides an Azure hosted web portal through which users can log on to do their purchasing series approvals. It fully integrates with the Workflow module in Microsoft Dynamics GP (blog and video) for document approvals (blog and video). The portal is quite a popular approach for organisations with a lot of approvals which need to be done as approvers have a web page they can logon to to see all of their approvals for all companies in one list, rather than receiving one email per approval.

Record short training or demo videos

Useful ApplicationsAfter posting about a couple of useful websites, I thought I’d do a useful application. Flashback Express can be sued to record short training or demo videos. While it can be used to capture the webcam, I have been using it to record the screen and sounds; for one video I needed to record both myself and someone else speaking and Flashback Express easily allowed me to capture sound from both the microphone and speakers.

Videos are recorded in a native format, but can be both exported to WMV or MP4 as well as exported directly to a YouTube channel.

Flachback Express

Continue reading “Record short training or demo videos”

Update Posting Setup in all companies to use Posting Date from Transaction

Microsoft Dynamics GPI was recently doing some work for a client and an issue which came up was the posting date for transactions posted to the GL was not always correct. After some investigation we determined that they had their Posting Setup (Administration area page » Setup » Posting » Posting) was set to use the posting date from the batch instead of the transaction.

As they had a substantial number of companies, and a lot of settings to change, they did not want to make this change manually. I’ve previously written a script to update some settings in Posting Setup in all companies so I was quickly able to amend the script for this change:

/*
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). */
DECLARE @SQL_Statement VARCHAR(1000) DECLARE cursor_InterID Cursor FOR SELECT INTERID FROM DYNAMICS..SY01500 Open cursor_InterID DECLAR @INTERID VARCHAR(100) FETCH NEXT FROM cursor_InterID INTO @INTERID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQL_Statement + 'UPDATE ' + RTRIM(@INTERID) + '.dbo.SY02300 SET UPSTDTFR = 1 WHERE UPSTDTFR = 0' exec (@SQL_Statement) FETCH NEXT FROM cursor_InterID INTO @INTERID END CLOSE cursor_InterID DEALLOCATE cursor_InterID

This will update all companies so I would recommend testing on a standalone test system before running on live and making sure you have a good backup before using the script.

Use SQL to generate a macro to create Vendor Items

Microsoft Dynamics GPOn a recent project with a client to implement the Purchase Order Processing module, we needed to create a lot of vendor item numbers. I’m a big advocate of doing updates in the front-end where possible or using integration tools where they support standard functionality. This particular client didn’t have a suitable integration tool available for inserting vendor items, so instead I created a SQL script which would generate a Microsoft Dynamics GP macro which can be played back to insert the records.

The script below will link all active vendors with every active non-discontinued item; it can easily be amended to limit the selected vendors or items (which is how we used it by running it several times with different restrictions):

/*
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 '# DEXVERSION=18.00.0021.000 2 2 CheckActiveWin dictionary ''default'' form ''IV_Item_Vendors'' window ''IV_Item_Vendors'' TypeTo field ''Item Number'' , ''' + RTRIM(REPLACE(ITEMNMBR, '''', '~"')) + ''' MoveTo field ''Vendor ID'' TypeTo field ''Vendor ID'' , ''' + RTRIM(REPLACE(VENDORID, '''', '~"')) + ''' MoveTo field ''Vendor Item Number'' CommandExec dictionary ''default'' form ''IV_Item_Vendors'' command ''Save Button_w_IV_Item_Vendors_f_IV_Item_Vendors'' ' FROM PM00200 AS ['PM Vendor Master'] CROSS JOIN IV00101 AS ['Item Master'] WHERE VENDSTTS = 1 AND ITEMTYPE <> 2 -- discontinued AND INACTIVE = 0 AND ( SELECT COUNT(*) FROM IV00103 WHERE VENDORID = ['PM Vendor Master'].VENDORID AND ITEMNMBR = ['Item Master'].ITEMNMBR ) = 0

Features of SalesHQ from ISC Software: Free Trial [Video]

SalesHQ PortalThis post is part of the Features of SalesHQ from ISC Software, as well as part of the over-arching GP Elementz from ISC Software series.

Earlier today I published a Free Trial post, but I am experimenting with making video alternatives to the standard blog posts.

You can watch the GP Elementz – SalesHQ – Free Trial video:

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]

Have an enquiry for ISC Software Solutions?

If you’re interested in speaking to ISC Software about consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

Features of SalesHQ from ISC Software: Free Trial

SalesHQ PortalThis post is part of the Features of SalesHQ from ISC Software, as well as part of the over-arching GP Elementz from ISC Software series.

There is a 28 day free trial available for SalesHQ which allows you to try it with no cost and no obligation to buy and connected to a system of your choice. The 28 day free trial can be quickly installed and connected to the system of you choice; if you have a standalone test system, it can be connected to this for you to do do testing of all scenarios and then when you purchase it will be connected with your live system.

Alternatively, you might choose to connect it to your live system straight away so you can give key members of your sales team or key customers access to SalesHQ against live data so they can operate using their normal daily processes.

If you’d like to speak to someone about SalesHQ or to organise a free trial, please use the contact form below or give ISC a call on either +44 (0)1480 802280 or +353 (0)1 514 3686:

Have an enquiry for ISC Software Solutions?

If you’re interested in speaking to ISC Software about consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

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]