GP Elementz from ISC Software: GP Elementz Portals

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

There are three GP Elementz portals available from ISC Software for Microsoft Dynamics GP which all share several key features:

  • Access data from anywhere and on any device.​
  • Safe and secure portals using Microsoft Azure.​​​
  • Fast and simple set up​​.
  • Ease of use​.
  • Portal branded with your own logo and background image.​

All portals are easy to setup and requires no technical skills or additional software or hardware. It is safe and secure on Microsoft Azure and all data remains in your Dynamics GP system with none stored on Azure. Responsive design means it can be used on any device, including cellphones and tablets.

Continue reading “GP Elementz from ISC Software: GP Elementz Portals”

GP Elementz from ISC Software: What add-ons are available?

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

ISC Software is both a VAR (Value Added Reseller) and an ISV (Independent Software Vendor) who have developed a number of add-ons for Microsoft Dynamics GP.

These add-ons come in two forms:

  1. GP Elementz portals which are fully response HTML5 portals which securely connect with Microsoft Dynamics GP.
  2. GP Elementz are Dexterity add-ins for Microsoft Dynamics GP which provide additional functionality in the client software. They are compatible with both the desktop and web client versions of Microsoft Dynamics GP.

GP Elementz Portals

There are currently three web portals available for Microsoft Dynamics GP in the GP Elementz portals range:

  1. ​​CustomerHQProvide your customers secure access to their Dynamics GP receivables account data such as invoices, statements and transaction reports.
  2. SalesHQProvide sales teams with the ability to generate quotes, enter orders and invoices and view transactions, from any location via the internet.
  3. PurchaseHQExtend your Dynamics GP system and allow purchasing users to manage purchasing from anywhere, via any device all in real-time. Includes integration with Microsoft Dynamics GP’s Document Attachment and Workflow module for approvals.

Key features of all of the web portals include:

  • Access data from anywhere and on any device.​
  • Safe and secure portals using Microsoft Azure.​​​
  • Fast and simple set up​​.
  • The user interface make the portals easy to use​.
  • Your portal can be branded with your own logo and background image.​

GP Elementz

There are five main add-ons available as GP Elementz:

  • Enhanced Notes​ – Extends and secures standard notes by allowing multiple separate notes to be recorded against records. Includes user alerts, categorization and audit functionality.
  • Distribution ​Elementz – ​A collection of windows & reports that extend Dynamics GP SOP, POP and inventory functions.
  • Financial Elementz – A collection of windows and reports that enhance standard financial Dynamics GP function.
  • Invoice Transformer – Consolidate or ‘mirror’ SOP invoices. Transfer item lines between SOP invoices.
  • PO Management – Consolidate or ‘mirror’ SOP invoices. Transfer item lines between SOP invoices.
  • ​Security ​Informer – Provides on-screen messages stating details of which window or report access is denied.

You’ll notice I said there were five main add-ons, but I have listed six; PO Management is a new one which was split off from another larger product and is now being made available to clients. Over the coming series, I will be taking a look at all six of the listed add-ins.

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: Series Index

ISC Software SolutionsI have been working for ISC Software since January 2019. I have been working in the Microsoft Dynamics GP space since 2003 and ISC are now the third partner I have worked for. However, they are somewhat different to the previous companies for whom I have worked in that as well as being a VAR (Value Added Reseller), ISC are also an ISV (Independent Software Vendor) who have a number of add-ons available for Microsoft Dynamics GP.

While ISC are based in the UK and have an additional office in Dublin, Ireland, the add-ons we sell are available to anyone around the world using Microsoft Dynamics GP. As I have with products from other vendors of add-ons (such as eOne, Fastpath and Jet Reports), I am going to a series of posts on the products available from ISC; in particular I am going to look at the functionality they contain and will at a later date cover the installation and configuration.

I will be interspersing the posts of this series, and sub-series, with other posts as things of interest come up.

Continue reading “GP Elementz from ISC Software: Series Index”

SQL View to return most recent five purchase orders by vendor

Microsoft Dynamics GPI don’t recall when I wrote this SQL view, but I stumbled across it when I was looking for something else and thought it worth posting.

It uses a common table expression to return the most recent five purchase orders for each vendor.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor ', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor
GO
-- create view
CREATE VIEW uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor 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). */
WITH PurchaseOrders ( PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS ,RowNumber ) AS ( SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS ,ROW_NUMBER() OVER (PARTITION BY VENDORID ORDER BY DOCDATE DESC) AS RowNumber FROM ( SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS FROM POP10100 UNION ALL SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS FROM POP30100 ) AS PurchaseOrders ) SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,CASE POSTATUS WHEN 1 THEN 'New' WHEN 2 THEN 'Released' WHEN 3 THEN 'Change Order' WHEN 4 THEN 'Received' WHEN 5 THEN 'Closed' WHEN 6 THEN 'Cancelled' END AS POSTATUS FROM PurchaseOrders WHERE RowNumber <= 5 GO GRANT SELECT ON uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor TO DYNGRP GO

As it is written as a view, it can easily be used in SmartList Designer, SmartList Builder, Refreshable Excel Reports, SSRS or other reporting tools.

Update on 6/4/2020: Correct corrupt SQL

SQL Script to update vendor and customer email addresses

Microsoft Dynamics GPBack in August 2011 I posted a SQL script which transfers vendor email addresses from the INET1 to the Email Address field and today’s script is a variation of that one.

The original script I posted only did a transfer for vendors and would migrate the INET1 one field regardless of whether there was already something in the Email Address field.

This new script only does the transfer if INET1 is longer than 1 character and the Email Address field is empty.

It also sets the Email Bcc Field to a supplied email address.

/*
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). */
UPDATE SY01200 SET EmailToAddress = INET1 ,EmailBccAddress = 'purchase.archive@example.com' WHERE Master_Type = 'VEN' AND LEN(CAST(INET1 AS VARCHAR(100))) > 0 AND LEN(CAST(EmailToAddress AS VARCHAR(100))) = 0 GO UPDATE SY01200 SET EmailToAddress = INET1 ,EmailBccAddress = 'sales.archive@example.com' WHERE Master_Type = 'CUS' AND LEN(CAST(INET1 AS VARCHAR(100))) > 0 AND LEN(CAST(EmailToAddress AS VARCHAR(100))) = 0 GO

SQL Script to get all accounts set as available in Purchasing lookups

Microsoft Dynamics GPFollowing on from the last post, in which I posted a script to get accounts linked to a vendor, this post is a script which returns all accounts which are set as available in lookups in the Purchasing series.

The SQL is straightforward, but I had to work out the binary entries to check for by saving and checking each combination.

/*
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 ['Account Master'].ACTINDX ,['Account Index Master'].ACTNUMST ,['Account Master'].ACTDESCR FROM GL00100 AS ['Account Master'] INNER JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX WHERE DSPLKUPS IN ( 0x04000000 -- purchasing ,0x05000000 -- sales/purchasing ,0x06000000 -- inventory control/purchasing ,0x07000000 -- sales/inventory control/purchasing ,0x0C000000 -- purchasing/payroll ,0x0D000000 -- sales/purchasing/payroll ,0x0E000000 -- inventory control/purchasing/payroll ,0x0F000000 -- sales/inventory control/purchasing/payroll ,0xFFFFFF7F -- never set ) GO

SQL Script to get all vendor purchasing accounts

Microsoft Dynamics GPI recently needed to select all assigned purchasing accounts for a particular vendor; this would include both the normal purchasing one on the Vendor Account Maintenance window and the additional ones in the Additional Vendor Accounts window.

The SQL is fairly straightforward, but I had to make sure each account was only included once:

/*
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 ['Account Master'].ACTINDX ,['Account Index Master'].ACTNUMST ,['Account Master'].ACTDESCR FROM PM00200 AS ['PM Vendor Master'] LEFT JOIN PM00203 AS ['Vendor Accounts'] ON ['Vendor Accounts'].VENDORID = ['PM Vendor Master'].VENDORID LEFT JOIN GL00100 AS ['Account Master'] ON ['Account Master'].ACTINDX = ['Vendor Accounts'].ACTINDX OR ['Account Master'].ACTINDX = ['PM Vendor Master'].PMPRCHIX LEFT JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX WHERE ['PM Vendor Master'].VENDORID = 'ADVANCED0001' GO

SQL Script to find SQL Objects containing specific text

Microsoft SQL ServerI did some work for a client recently which involved some custom SQL objects created by a consultant at their previous VAR. It appeared that there was a consistent prefix on the created objects which meant we could use a script I’ve previously posted to identify them all.

However, we quickly realised there were other objects which did not adhere to the naming convention we’d identified. Fortunately, I remembered writing a script to update database names in SQL views after copying live to test which identified the views to update by using the sys.sql_modules object.

Using that oncept, I came up with the below script which will search for the text specified in the variable at the top in all objects:

/*
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 @SearchString VARCHAR(100) = 'string to find' SELECT DISTINCT ['sys objects'].name AS 'Object Name' ,['sys objects'].type_desc AS 'Object Type Description' FROM sys.sql_modules AS ['sys modules'] INNER JOIN sys.objects AS ['sys objects'] ON ['sys objects'].object_id = ['sys modules'].object_id WHERE ['sys modules'].definition like '%' + @SearchString + '%'

Implementing Fastpath’s Audit Trail: View reports

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

In yesterday’s post, I took a look at the Data Changes view available in the Fastpath Assure portal for Audit Trail. This is the way of looking at the raw data, but is not the friendliest method of reviewing the audited changes which is available, It is suitable for someone like me who understands the data structure of Microsoft Dynamics GP, but the ordinary end-user needs something more user friendly.

Fortunately, this is available from the reports which are included as standard. To access these reports, expand Audit Trails and select Reports; there is a long list of reports then shown in the navigation pane:

Audit Trail Available Reports

Continue reading “Implementing Fastpath’s Audit Trail: View reports”

Implementing Fastpath’s Audit Trail: Review data changes

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

With Audit Trails installed, configured with triggers created and a scheduled data collect in place, the audited changes are available for users to view in the Fastpath Assure portal.

The first palc you can see them, and the one covered in this post, is in Data Changes (accessible from (Audit Trail » Data changes):

Data Changes

Continue reading “Implementing Fastpath’s Audit Trail: Review data changes”