Create SmartConnect Journal – Standard Map: Prerequisites

eOne SolutionsThis post is part of a series on creating a Journal – Standard integration using SmartConnect from eOne Solutions; I recently posted a series on Implementing SmartConnect.

The prerequisites for creating a new integration map are quite straight forward:

    Decide on the type of integration to be created.
  • Decide how the integration should work.
  • Create the source data template.

The integration I am creating as part of this series is one of the easiest which can be created; it is a standard General journal integration. I always create this map after implementing SmartConnect (and before starting to use SmartConnect, Integration Manager) as it is a quick and easy one to create and which will be useful to all clients.

The most useful way of creating this integration for clients, is to create an integration which polls a folder every few minutes on a schedule and which reads data from an Excel spreadsheet. This approach allows one integration to be set up and usuable by all users; as it is a folder being polled, the files dropped into the folder can have any name and, as long as the format matches the template, SmartConnect will attempt to integrate the data.

In the past when using Integration Manager, the source file would be a CSV or TXT file as Integration Manager couldn’t easily handle Excel spreadsheets; SmartConnect has no such limitation, so I am going to use the Excel version of the Integration Manager template:

Excel spreadsheet source with BatchID, TrxDate, Reference, Account, Debit, Credit and DistRef columns

With the above decided and in place, the next post, in this series, will start to cover the creation of the integration map in SmartConnect.

Create SmartConnect Journal – Standard Map: Series Index

eOne SolutionsI recently did a series on Implementing SmartConnect, from eOne Solutions; in this series I am going to create a simple integration map to show how SmartConnect can be used to integrate standard journals into Microsoft Dynamics GP from a predefined folder.

The series index below will update as each post in the series goes live (unless you’re reading a syndicated version of this post in which case check back to the original post).

Create SmartConnect Journal – Standard Map

Delete Corrupt Fixed Allocation Master (GL00103) Data

Microsoft Dynamics GPI was recently putting together a report for a client on Fixed Allocation Accounts and came across some corrupt data in the Fabrikam, Sample Company, Database. To avoid encountering this issue again, next time I am working in this area, I put together a simple script to remove the corrupt records by joining Fixed Allocation Master (GL00103) with Breakdown Account Master (GL00100) to identify the data which should not be there and delete it; this makes the script generic enough that I can use it on a live company should the need ever arise.

/*
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).
*/
DELETE FROM
	['Fixed Allocation Master']
FROM
	GL00103 AS ['Fixed Allocation Master']
INNER JOIN
	GL00100 ['Account Master']
		ON
			['Account Master'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
		AND
			['Account Master'].FXDORVAR = 2

As always, before you run any script, ensure you have a good backup of your database and verify the results after the script has been run.

Hands On With Microsoft Dynamics GP 2018 R2 New Features: Transaction Level Post Through General Ledger

Microsoft Dynamics GPThis post is part of the Hands On With Microsoft Dynamics GP 2018 R2 New Features series in which I am going hands on with the new features introduced in Microsoft Dynamics GP 2018 R2 (which was released on the 2nd October). I reblogged the new features as Microsoft announced them along with some commentary of how I thought they would be received by both my clients and I. In this series, I will be hands on with them giving feedback of how well they work in reality.

The seventh new feature is .When transaction posting is enabled, this feature allows users to perform posting through the General Ledger at a transaction level. The following is a list of supported transactions for this feature:

  • Invoice Entry
  • Receivables Apply Doc.
  • Receivables Cash Receipts
  • Receivables Sales Entry
  • Sales Deposits
  • Sales Trx Entry
  • Voided Trx Maintenance (Sales)
  • Payables Trx Entry
  • Payment Entry
  • Purchasing Invoice Entry
  • Purchasing Prepayments
  • Receivings Trx Entry
  • Returns Trx Entry
  • Assembly Entry
  • Transaction Entry (Inventory)
  • Transfer Entry
  • Manual Checks (Payroll)
  • Project Accounting Transaction windows except Project Closing

To enable post through for transactions, open the Posting Setup window (Administration area page » Setup » Posting » Posting), and mark the Post Through General Ledger checkbox below the Allow Transaction Posting one:

Posting Setup

In theory this is a great addition to Dynamics GP, as many clients want to do transaction level posting, although more these days are wanting to use workflow for approvals which still requires batches.

However, in my testing so far, all Sales transactions which I post using post through work fine, but nothing on Payables posts through at transaction level regardless of the settings.

I need to do additional testing, but so far this feature isn’t looking good.

Click to show/hide the Hands On With Microsoft Dynamics GP 2018 R2 New Features Series Index

Update Year on Budgets in Fabrikam Sample Company

Microsoft Dynamics GPI’ve written a few scripts to update data in the Fabrikam sample company (I will be posting a series index to link them all together in the near future).

The sample company ships with a few budgets preloaded, but they are not named very well. For example, the budget for 2027 (the year in which Fabrikam is operating) is named BUDGET 4 and the budget for 2025 is named BUDGET 2005.

Continue reading “Update Year on Budgets in Fabrikam Sample Company”

Hands On With MDGP 2018 RTM New Features: New Workflows

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

One of my favourite new features in Microsoft Dynamics GP 2018 RTM, are the new workflows which were introduced:

  • General Ledger Account Approval
  • Receivings Transaction Approval
  • Purchasing Invoice Approval

I’ve had a number of clients and prospects ask if it is possible to have approvals of goods receipt notes and invoices in POP, so it’s good that I can now say that there are. These workflows are likely to be fairly complicated, so, in this post, I am going to be giving a test to the General Ledger Account Approval workflow.

The workflow process is created and maintained just like any other via Workflow Maintenance (Administration area page >> Setup >> Company >> Workflow >> Workflow Maintenance):

Workflow Maintenance

Continue reading “Hands On With MDGP 2018 RTM New Features: New Workflows”

Hands On With MDGP 2018 RTM New Features: DocAttach Available on More Windows

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The first of the features announced by Microsoft was the introduction of DocAttach on more windows: the DocAttach button on the action pane makes the function more accessible to users. The windows updated for this are:

  • General Ledger Transaction Entry
  • Receivables Transaction Entry
  • Asset General Information
  • Employee Entry
  • I-9 Form (Payroll)
  • Journal Entry Enquiry
  • Payables Transaction Entry Zoom
  • Receivables Transaction Entry Zoom

I quite like the DocAttach function in Microsoft Dynamics GP, although I am careful to be clear with clients and prospects that is is document attachment and not document management.

While it’s good to see DocAttach getting wider exposure across Microsoft Dynamics GP on the action panes, it’s especially nice to see Fixed Asset Management getting some DocAttach improvements as many of my clients have images of their assets to store:

Fixed Asset General

Click to show/hide the Hands On With MDGP 2018 RTM New Features Series Index

MDGP 2018 RTM Feature of the Day: DocAttach Available on More Windows

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 RTM; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here.

The first Feature of the Day is Document Attachment available on more windows.

Five additional windows now have Doc Attach available via a button on the action pane:

It is nice to see Fixed Assets getting some Doc Attach functionality; quite a few clients have documentation or images relating to their assets, so this offers a good way to keep these with the asset.
Continue reading “MDGP 2018 RTM Feature of the Day: DocAttach Available on More Windows”

Dynamics GP Tech Conference 2017: Comprehensive Doc Attach

Microsoft Dynamics GPMicrosoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

The Document Attachment module has been extended into the action pane in quite a few new windows:

  • Document Attachment on Receivables Transaction Entry
  • Document Attachment on General Ledger Entry
  • Document Attachment on Employee Inquiry
  • Document Attachment in I-9 Form
  • Display Vendor notes on Vendor Inquiry
  • Display Customer notes on Customer Inquiry
  • Display Project Notes in Project Inquiry and PA Project Inquiry
  • Document Attachment in Asset General Information
  • Option to attach new documents in Inquiry windows – there is a setting in the Document Attachment Setup winodw to enable this (and to require a password to be entered).

SQL View to Return Budgets By Month

Microsoft Dynamics GPThe budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

Continue reading “SQL View to Return Budgets By Month”