Workflows Galore in GP2018 and What Makes Them Great!

Curious as to what Workflow options are available in Dynamics GP? Check-out the full post HERE! Microsoft has done a fantastic job with the workflow system and our post touches on the pros and cons as well as what features and workflow options you should expect if/when you decide to jump on board with workflows.

Read More

Dynamics GP Training/Tutorial 101 - Youtube Video Series!

For all of the new/un-guided Dynamics GP users out there, this one’s for you!

Introducing the “Learn Dynamics GP - 101 Series” now up on YouTube! A lot of time and effort has gone into these tutorials but I’m happy to finally roll them out and make them official.

The 101 series is what you would think but is broken down core module by core module and includes some bonus 101 series training/tutorials (Fixed Assets, Navigation, etc.).

The hope is that these videos can serve as an introduction to Microsoft Dynamics GP and get you or your team up to speed very quickly. If you ever have questions or are looking for more in-depth & hands-on trainings, let our team help you out.

The 101 series is just the beginning and more videos are to come! Let us know what you think in the comments below or on the YouTube channel and do us a favor and SUBSCRIBE to stay up to date with our future releases.

Enjoy!

Youtube - GP Training/Tutorial 101 Series

https://www.youtube.com/playlist?list=PLYaFvNBJI6Dq8HQP3gr9tr2c3F9Sa6seP

GP-Training-Tutorial-101-Series.png

SmartConnect - Top 5 Quick Tips & Integration Improvements

SmartConnect is the integration tool of choice for the ALaCarteGP team.  There are a few different options out there for integrating data into GP, an important piece for an ERP system and part of which makes GP so great, however, the eOne Solutions team has found the perfect balance of creating a solution that's both end-user friendly and powerful at the same time.

I could go on and on discussing the great benefits of adding SmartConnect to your company's toolkit but today, let's focus on a few of the quick and easy to implement tips & tricks to improve your integrations.

1. Pre-Map Validation Check

Much of developing integrations is getting clean data in and reducing the number of things that can go wrong during import.  With "Pre-Map Validation" tasks in SmartConnect, you can accomplish both.

Under Tasks>>Map Pre Tasks>>SQL Validation, you can add checks to the data import to ensure data exists or doesn't exist at the time of import.  If either condition is true, you can halt the entire map, display a report w/ your matches/mis-matches & clean things up before importing again.

In the example below, we've added data checks on a typical vendor import to 1.) Ensure the vendors that we're importing DO NOT already exist & 2.) Ensure that the payment terms that we're importing DO exist.

SmartConnect-Data-Checks.png
SmartConnect-Data-Check-Payment-Terms.png

In the setup, we've essentially said, ensure the field 'Payment Terms' from our source matches an option from the payment terms setup table in GP.

2. ODBC Connection instead of a direct text or Excel connection

This is more of a integration principles tip but SmartConnect provides a beautiful interface for this option.

When setting up integrations from a source-file, say Excel, you want to go w/ the obvious of selecting 'Excel' right?  Well... Not really.

When you select the Excel option, that's it, you're done.  It's quick, simple & straight-forward but it's NEVER going to get any better or provide you with any flexibility past that point.  What we'd suggest is setting up an ODBC connection w/ the Excel as the source.

SmartConnect-ODBC-Excel.png

The ODBC option takes just a moment to figure out but once you do, it's very straight-forward.  With it, you can add things like...

  • Omit blanks lines from file

  • Select only from file that are between a certain range

  • Omit lines from file where debits <> credits

  • Sum fields from file

To set one up, select 'ODBC Connection' from the Data Source drop-down list, select Excel as your ODBC option & ultimately select your Excel file. 

SmartConnect-ODBC-Setup.png

(Tip! With this interface, it's sometimes easier to copy & paste your windows explorer folder path into the 'Database Name' path & select 'Enter' to navigate.)

3. Email Notifications

Roll an integration out to end-users or want to monitor a scheduled integration and want to monitor those integrations if they fail?  The best way to do so is with emailing!  With emailing, you can cc youself anytime an integration fails (or completes) so that you're on top of when things are awry and require attention.  Nothing makes you look like you know what's going on more than if/when an integration fails that you're already on the phone with the end-user.

Example template that would include successes, failures, &amp; detailed explanation of what causes the failure.

Example template that would include successes, failures, & detailed explanation of what causes the failure.

4. 'Fix' Option in Maps

How often does your integration have dozens of successes and then one failure... "Uuuugh" you say as you close the integration, open the source-file or GP, make the changes, re-open the map, re-run the integration, see if it works, rinse & repeat.

Well... The 'Fix' button is your savior.

With the fix button, if things fail during an integration, you can open the fix window, make the corrections right on the spot & then re-run JUST THE FAILURES at that time and without leaving SmartConnect. Magic!

SmartConnect-Fix-Concur-1.png
SmartConnect-Fix-Concur-2.png
SmartConnect-Fix-Concur-3.png
SmartConnect-Fix-Concur-4.png
SmartConnect-Fix-Concur-5.png

5. Calculations

When developing integrations that end-users will be using on a routine basis, it goes back to the point made in our first listed tip... You want to setup the integration in a way that leaves the least amount of things that can go wrong as possible.

One way that you can do that is with 'Calculating' columns.

Example.) You create a journal entry integration and want the JE date to always be the date of the integration.  Instead of adding the field 'JE Date' to the Excel file that they populate, which they'll 100% put in the wrong date at some point in time, you can create a calculating column in SmartConnect & automatically pull the current date.

SmartConnect-Calculation-Data Format-VBScript.png

Bonus! Push to GP - File (.XML) to Troubleshoot

Oh don't we all love troubleshooting.  You’re on day 3 with developing an integration that should've only taken you an hour and you're about to explode as you keep receiving the error "Unexpected Error Occured".  To ease the woes of the unfortunate, SmartConnect provides us with the ability to push to a "File".  This option essentially surpasses the rules that eConnect would yield and pushes the data to an XML file.  We can use this XML file to view EXACTLY what SmartConnect is trying to push through.

With this, we can easily pinpoint what's going wrong when we see the value "Bill Gates Pension Accrual" being passed through the "JE Date" field.

SmartConnect-GP-File-XML.png

Conclusion

SmartConnect is fantastic and these tips only touch the surface of what the tool has to offer.  If you'd like to learn more about the eOne Solutions SmartConnect application, we'd be happy to discuss or you can take a look for yourself on eOne's site.

SmartConnect and integrations are one of our main focuses here @ ALaCarteGP so please be sure to stay tuned for future #Integration related posts & also take a look at a very small sample of some integrations projects that we've worked on in the past.

Happy integration building!

GPUG - All-Star Nominations!

With the User Group Summit conference right around the corner in October, there are lots of exciting things going on in the GPUG world at this time of year, one of which is the yearly "All-Star" awards.

All-Star nominees are put up by their peers based on their contributions to the GPUG community & this year, I've been fortunate enough to be considered!

If you're a part of the GPUG community and have benefited from all that GPUG offers, take a look @ the 2018 All-Star nominees that can be viewed @ https://www.surveymonkey.com/r/GPUG2018All-StarVoting.  There are quite a few recognizable names on the nominees list this year.

If you're not familiar with GPUG & you use GP, I'd HIGHLY recommend taking a look @ what the group has to offer.  There are benefits to non-members but for members, here are a few of the things that you can take advantage of:

  • Live & Recorded Webinars
  • Open GP Related Forum (Highly active!)
  • Conference discounts
  • Local chapter events
  • Other exclusive member discounts

If you ever find yourself around the New Orleans area and want to check-out a local chapter meeting, we'd love to have you!  Presenters and/or attendees are always welcome!

Hope to see some familiar faces in Phoenix for Summit!

Start Processing Credit Card Payments in GP TODAY!

On another installment of our video series, we covered a feature that's been around for quite awhile but was recently added to with Dynamics GP 2018.

Setting up credit cards, processing credit card transactions, and issuing payments for credit card bills all done within GP.

This short video will cover everything that you need to setup credit cards & begin processing them TODAY!

Enjoy, subscribe, and let us know what else you're interested in!

Provided by ALaCarteGP.com Don't forget to subscribe to our Youtube channel to stay in the loop w/ future posts & also check-out our blog @ ALaCarteGP.com/blog

Fixed Assets - Upgraded Projection Reports Using SSRS

Quick show-off of how to utilize SSRS to deploy improved Fixed Asset Projection reports in Dynamics GP 2018. Versions are a month over month report as well as a summary/detailed projection listing with G/L account.

Read More

Payables Payment Process Using Dynamics GP 2018!

The payables process within GP is tried & true within Dynamics GP.  It's one of the fundamental process that GP was built-on and it works very very well.  Below is a video that can be found on our YouTube channel that covers the payment process from beginning to end and highlights some of the additional features that Microsoft brought to the table with Dynamics GP 2018.

Feel free to take a look @ our full video library on our YouTube channel & let us know what you think!

If you like our videos, give us a like and a subscribe to be in the loop on future posts.

If there's a future topic that you'd like us to cover, let us know in the comments below!

SmartList Builder - Develop & Deploy Reports

eOne Solutions' SmartList Builder is the equivalent of the SmartList Designer product that Microsoft now offers with the out of the box Dynamics install.  SmartList Designer has been around a little longer thus was the focus of our post.  Everything that's done in this particular video can be accomplished using SmartList designed in almost a similar sequence.

Please check out our video post on Youtube and <SUBSCRIBE> to our channel for notifications on future posts.

Also, if you have feedback on our videos or have a topic in mind that you'd like for us to cover, please let us know in the comments below!

1099 - Simpler and More Powerful Reporting

This post covers 1099 reporting enhancements specifically.  If you're looking for overall information on the 1099 reporting process, check out our post and YouTube video on "1099 Processing in GP".

If you use Dynamics GP for payables, I hope that you're also utilizing the 1099 capabilities.  This post will focus on the out of the box reports, their short-comings, & then some alternative approaches to reporting.

Before we get started, there are a few basic setups that need to be complete before your company is ready to start with 1099s.

1.) Ensure your company's main address & Federal ID Number are setup in the system under Admin>>Setup>>Company

CompanySetup.png

2.) Ensure your vendors are setup correctly w/ a Tax ID number, 1099 Tax Type & 1099 Box Number.  These options are all viewable under Purchasing>>Cards>>Vendors>>Options.

1099VendorOptions.png

Once setup... Processing is easy.

Simply create your voucher for a 1099-enabled vendor, you'll notice the 1099 amount field is active & auto-populates w/ the document amount (you can adjust if needed) & then post the voucher just like you would any other payable transactions.

1099TransactionProcess.png

Setup & reporting is the easy part, now onto reporting....

Out of the box, you're provided with a few basic inquiries & reports.

Inquiries (Vendor by Vendor Reporting)

- Purchasing>>Inquiry>>1099 Details

- Purchasing>>Cards>>1099 Details

- Purchasing>>Transactions>>Edit 1099 Transaction Information

Reports (all vendors at once)

- SmartList>>Purchasing>>Payables Transactions (Include 1099 column)

- SmartList>>Purchasing>>Vendors

"Print 1099" under Purchasing>>Routines.  This is going to provide you with what the ultimate goal is, your printed 1099s.

Downfalls of Out of Box Reports

- Payables Transactions SmartList is based off of payables voucher dates and not payment date.  Thus if during either ones of your calendar year-ends you posted invoices in one period and paid them in another... Goodluck.

- Vendors SmartList like some other GP SmartList reports is out-of-date or just frankly doesn't have all of the information that it should.  For 1099 purposes, the 1099 Box field nor the '1099 Address' from the vendor setup is available.

More Powerful Reports

Using SmartList Builder (could also use SmartList Designer)  & a little bit of SQL reporting knowledge, we can deploy improved reports. (For details on deploying SmartList Builder reports, take a look @ our earlier post or video "Building and Deploying Dynamics GP SmartList Builder Reports").

Report 1.) 1099 Summary

- This report simply matches what you see in the Purchasing>>Inquiry>>1099 Summary window except it can be used to view all vendors at once.

1099Summary.png

Report 2.) 1099 Detail

- This report as you can imagine is the detailed version of the above.  The largest benefit between this report and the out of the box payables transactions report is that the year-end cut-off is clean and you're not chasing down reconciling items

1099Details.png

Between the 2 reports above, you can easily reconcile and have confidence that your 1099 amounts are correct (again more detail on recommended year-end process in separate post)

Report 3.) Vendors with 1099 Information

1099Vendors.png

- This report is pretty similar to the existing vendors report but provides you with a couple key bits of information that the original does not (1099 Address information, 1099 Box, etc.).

Reports Designed in SmartList Builder

1099 Summary

SLB_1099Summary.png

1099 Detail

Vendors w/ 1099 Information

SLB_1099VendorsSetup.png

 

Check-out our other mentioned posts on SmartList Builder, Overall 1099 Process & our YouTube channel for more information.  If you have any questions or your own 1099 tips and tricks, let us know!

Note: All Screenshots from Dynamics GP 2018

Automate Unit Account Values Based on Sales Quantities

It is a typical KPI request to be able to report on unit account values.  Let's go ahead and say that we're looking at a profit and loss report, by product and the business wants to see units sold that are included in that margin.

In a perfect world (and using some tools that are not named Management Reporter), if you're capturing the Product A quantity in the Sales Order Processing (SOP) module in GP, you'd be able to reference the sum of the sales instead of using a unit account.  However... We don't live in a perfect world whilst using Management Reporter thus Unit Accounts are required.

So what we'd see is in GP, we'd setup a unit account for something like "Units Sold - Product A"

In Management Reporter, we'd then have a row definition that looked something like...

Sales Product A

- Expenses Product A

= Margin

/ Units Sold - Product A

= Margin/Unit

In a typical fashion, in order for this report to be up to date, the accounting team would need to make a routine journal entry to the unit account within GP.  This process leaves way for manual errors, timing issues & the potential for management to lose confidence in their reports.

Using the method below, we complete streamline this process by automating the unit account journal entry within GP.

Part 1: Associate a unit account with an inventory item in GP.

Note: For example deliverable, one item may be used at multiple locations (segment 1 of COA).  Therefore, we just assign the Natural account to the item & then let the integration define the remaining COA segments)

Approach: We accomplish this using

eOne Solution's Extender product

Part 2: Setup integration that captures posted activity in GP & creates journal entry in GP.

Note: For example deliverable, we're pulling the prior day's activity & creating a journal entry first thing in the morning.

Note: Integration script defines COA segments based on the SITE of the sale on SOP invoice & then the natural account that we associated to the item.

Approach:  We accomplish this using

eOne Solution's SmartConnect product

Map/Integration Setup

ODBC Connection to Company Database - Selecting Prior Day Sales

Part 3: Enable map/integration schedule.  This is an out of the box functionality of SmartConnect.

Part 4: Create reconciling reports using SmartList/SmartView.  These reports can be used at month-end to reconcile financials.

Note: This report is best suited to be built using a Sales Line Item report, filter on the posting date & ensures that returns are shown as negatives

Part 5: Notify accounting team that all they need to do moving forward is post their batches & reconcile once @ month-end!

Integrated transactions in GP would look similar to...

Note: All prior day activity are rolled into 1 line.  Option to separate for each sales transaction would be available if desired.

A few other notes to consider in this process...

- Returns must be accounted for

- When deploying, it is recommended to have the accounting team reconcile more frequent.  As they gather an understanding of process and ensure that numbers are tying out, reconcile less frequent.

- Failed integrations should auto-notify an integration PowerUser.  PowerUser could re-run integration if/when needed

This solution is built in a manner that's easy to implement & maintain and quickly accomplishes the goals of time savings, accurate data & providing a more robust GP system.

Thinking about implementing for your business and looking for additional details?  Already implemented and have feedback?  Let us know in the comments below!

Copying Quick Links from One User to Another

Microsoft has come a decent ways to allow each user to customize their home login page.  For a long time there has been the feature for the first time that a user connects, they are prompted to select their "Role" that determines how their out of the box homepage will look & feel. 

With later versions of GP, there also came the ability to copy a user's homepage settings from 1 user to another (along with every other setting from the original user).

However, Microsoft still hasn't made it simple just to take an existing user's quick links and apply them to another user.  From using the interface, you'd have to copy ALL user settings from one user to another using the "Users Maintenance" window OR you'd have to login to a certain user's account and set their quick links OR provide them with instructions on setting them themselves. 

NO MORE! 

Using the script below, you can define a copy from user (TestUser1) & a copy to user (TestUser2) and either replace the existing quicklinks or add to.

--Declare Variables
DECLARE @CopyFromUserID CHAR(15), @CopyToUserID CHAR(15), @Count INT, @Delete CHAR(1)

 --Provide Values for Variables
--This is the section that MUST BE UPDATE
SELECT @CopyFromUserID = 'TestUser1', @CopyToUserID = 'TestUser2', @Delete = 'N' --Delete = Y means delete any existing quicklinks for copy to user, N = Append Only

--This script will delete existing favorites if @Delete = Y
DELETE FROM dbo.SY08140 WHERE userid = @CopyToUserID AND @Delete = 'Y'

--Start copy from one user to another
SELECT * INTO #TempQuickLinks FROM sy08140 WHERE userid = @CopyFromUserID AND dsplname NOT IN (SELECT DSPLNAME FROM dbo.SY08140 WHERE userid = @CopyToUserID) AND @CopyToUserID IN (SELECT userid FROM SY01400)

SET @Count = 1

WHILE @Count <= (SELECT COUNT(seqnumbr) FROM #TempQuickLinks)

begin

UPDATE #TempQuickLinks SET SEQNUMBR = ISNULL((SELECT MAX(seqnumbr) + 1 FROM sy08140 WHERE USERID = @CopyToUserID),1)

INSERT INTO SY08140 (USERID, SEQNUMBR, TYPEID, CmdID, CmdFormID, CmdDictID, DSPLNAME, ScbTargetStringOne, ScbTargetStringTwo, ScbTargetStringThree, ScbTargetLongOne

,ScbTargetLongTwo, ScbTargetLongThree, ScbTargetLongFour, ScbTargetLongFive)

SELECT TOP 1 @CopyToUserID, SEQNUMBR, TYPEID, CmdID, CmdFormID, CmdDictID, DSPLNAME, ScbTargetStringOne, ScbTargetStringTwo, ScbTargetStringThree, ScbTargetLongOne

,ScbTargetLongTwo, ScbTargetLongThree, ScbTargetLongFour, ScbTargetLongFive FROM #TempQuickLinks WHERE DSPLNAME NOT IN (SELECT dsplname FROM sy08140 WHERE USERID = @CopyToUserID)

SET @Count = @Count + 1

END

DROP TABLE #TempQuickLinks
--End copy from one user to another

--View ending results
SELECT * FROM dbo.SY08140 WHERE userid = @CopyToUserID

Note: The script has only been used in limited GP 2015 R2 environments.  As always, it is recommended to run the script in a development environment before deploying to production.

This script might be useful when...
- Setting up a new user and wanting to setup their quicklinks based off another user in a similar role
- Setting up a new module and wanting to add quicklinks to all users based on your model user (say when setting up Fixed Assets & wanting to add an external link to the new GAAP Financial Leases requirements)
- Setting up a new SmartView favorite for certain users but also wanting to add the quicklink to their homepage

Have feedback or have you expanded the user of this script?  Let us know!

Company Address IDs - Import

Did you know that within Purchase Order Processing that the list of available ship to addresses pulled from the Company Address ID setup within GP?  During a POP/Inventory/SOP implementation, it's no uncommon for companies to have 100s of potential ship to addresses for purchases.  Below we've covered our favorite way to get this list of address IDs into the system using .csv/.txt files and SQL.

Step 1.)  Create a spreadsheet that list all address lines.  Our example contains the following columns.

Company ID, Address ID, Name, Contact, Address 1, City, State, Zip Code

Step 2.) Save spreadsheet to a tab-delimited text file

Step 3.) Open the text file & replace any " characters w/ {blanks}.  " characters will appear when a field contains a comma.

Step 4.) Import into SQL using SQL Server Management Studio

>>Right-Click Database

    >>Select Tasks

        >>Select Import Data

           >>Select Flat File as source

              >>Enter Staging Table Name in SQL Destination (Use name of CoAddressID_Template in our example below)

Step 5.) Create backup of table that we're about to insert into (select * into sy00600_backup{date} from sy00600.  This step is important in case we need to roll-back our insert and don't want to have to refresh the entire database.

Step 6.) Ensure that no address IDs in your staging table already exist within GP.

select * from sy00600 where locatnid in (select [Address ID] from coAddressid_template)

Step 7.) Run SQL Insert statement to move data from staging table (CoAddressID_template) to SY00600 table.

declare @cmpanyid numeric(2,0);

set @cmpanyid = (select cmpanyid from dynamics..sy01500 where cmpnynam = '{ENTER YOUR CO NAME}');

insert into gpent..sy00600 (CMPANYID,LOCATNID,LOCATNNM,ADRSCODE,ADRCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,COUNTY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAXNUMBR,CHANGEBY_I,CHANGEDATE_I,CCode)

select @cmpanyid,left([Address ID],15),left([Name],30),'',left([Contact],60),left([Address 1],60),'','',left([City],25),

'',left([State],30),left([ZIP Code],10),'','','','','','','1900-01-01 00:00:00.000','' from coAddressid_template

That should do it.  You can add additional columns to your sourcefile if you please.  Just be sure to update your insert script to account for the new columns & remember character limitations! left(Address1,60)

Our first preference is to always use the Microsoft eConnect procedures or integration tools like Integration Manager when available.  However, when all else fails, a carefully executed SQL script can achieve the same outcome.  At the end of the day, it's all data in a database.

Let us know if this works for you or if you'd like to see similar processes using SQL!

Submitting Microsoft Dynamics Feedback - Updated 2018!

"The only thing that is consistent is change"

Change in the GP world is always coming and in most cases should be welcomed with open arms.  Changes for the betterment of the system ensures that you're getting the most out of your investment and using a product that is competitive in the current marketplace.  

Did you know that you can factor into the upcoming changes for GP?  There's an actual direct line to the Microsoft Dynamics GP team that allows customers and partners to submit product improvements and bugs to be considered for future releases.  We urge everyone to use this avenue when you're looking for more from your system!

Update 2018:

The feedback/suggestion process is now a whole lot simpler and with the times.  Simply go to the new Microsoft Dynamics GP Ideas page, create an account, review existing suggestions or suggest away!

Give it a shot and keep us posted on what new features you'd like to see in GP.  We're happy to give your submission a vote as well!

Handling Unapplied Open Balance on Purchase Order Prepayment

The ability to create purchase order prepayments was introduced w/ GP 2013R2.  With any new feature, there is almost always rooms for improvement and POP prepayments is no exception.  One of the areas of improvement is such a cluster that we felt a blog describing the issue and the necessary workaround was warranted.

Issue:

When a PO prepayment has a remaining balance and the corresponding Purchase Order is moved to history, it becomes nearly impossible to apply the prepayment to any future vouchers.

When closing a Purchase Order under Purchasing>>Transactions>>Edit Purchase Orders, the following screen appears which prompts you that there is an unapplied balance on a prepayment.

At this time, we are still able to apply the prepayment balance to any offsetting invoice or debit memo without issue.

However, once the Remove Completed Purchase Orders routine is run (Purchasing>>Routines>>Removed Competed POs), the PO is moved to history and the prepayment is NO LONGER available to select and apply to existing vouchers.  It cannot be transferred to regular payables, there cannot be any transactions entered against the PO since it is now historical, it is not available under Apply Payables Documents, & cannot be applied to future check runs.

Quite the conundrum...

Workaround:

Although the balance cannot be transferred to regular payables, we do still have the ability to transfer the balance to A/R using customer/vendor relationships.  After transferring to A/R, we can turn around and transfer it back to A/P where it will now become a regular PM voucher (Credit Memo) that can be applied to existing invoices or debit memos for the vendor.  We covered the process to transfer balances between customers and vendors in an earlier video here.

Perfect World:

All is well up until the point that the Remove Completed Purchase Orders routine is run.  During that process, there are no warnings that you'll no longer be able to apply invoices & nothing that stops you from proceeding to create the scenario that we've highlighted above.  Here's to hoping that the developers at Microsoft prevents Purchase Orders from moving to history if there is an outstanding balance on a prepayment OR they provide a more direct route to move a POP prepayment to regular payables.

Until then, this will assist you in clearing up those A/P trial balances and remaining documents come month/year-end!

Recurring Sales Transaction in GP Using Sales Order Processing

For the environments that rely on the Sales Order Processing module for billing purposes, one of the downsides is that recurring batches that are available in other modules aren't so readily available within SOP.  While recurring batches may not be available by name, there is still the opportunity to create recurring transactions using sales orders.

Instead of a batch of recurring sales invoices, we'll create Sales Orders that can be placed in a batch and transferred to invoices over and over.

Setup Recurring Sales Orders

The first thing that we'll want to do is create a sales order document 'template' that will allow recurring transactions.

1. Open the Sales Order Processing Setup window and create or open a sales order document ID by selecting the 'Order ID' link.

2. Setup Sales Order

Note Required:

- Allocate by: None

- Allow repeating documents

Create Individual Recurring Sales Order

1. Open the Sales Transaction Entry window

2. Enter normal Order information

3. Drill into each lineitem (blue arrow) and set distribution accounts

4. Review transaction distributions to ensure they are valid for current and future transactions.

5. Drill into Document Number (blue arrow) once Sales Order has been filled out

Note Required:

- Enter times to repeat

- Enter frequency of recurrence.  (This will update order date, not posting or invoice dates)

- Ensure Invoice Type ID is valid

- Enter batch ID of recurring INVOICES - Create holding batch if necessary

- Save

Process: Batch Transfer from Order to Invoice

Transactions can be transferred from recurring sales orders to sales invoices in a batch or at the transaction-level

1, Open 'Recurring' Sales Order batch

2. Select Transfer

Note Required:

- Transfer Order to Invoice (bottom-left)

3. Preview items to be transferred

4. Transfer document(s)

5. Open sales batch window and ensure posting date is correct.

6. Open Transaction(s) and ensure expected invoices transferred to designated batch(es)

Note Required:

- Check document date 

Process: Transaction Transfer from Order to Invoice

1. Open sales order transaction

2. Select Actions>>Transfer>>Transfer to Invoice

Best Practice Notes

- Utilize separate recurring batches for orders and invoices.

Example is to create a recurring sales order batch for daily, weekly, monthly transactions and have the transferred transactions push to similar setups for invoice transactions.

- Always review posting and document dates of transferred invoices.

We hope that this process makes your billing process easier?  Have any billing tips of your own that you'd like to share?  Let us know in the comments below!

Re-Enable New Account Segment Notification in Dynamics GP

If you've ever integrated new accounts via Integration Manager, you've probably found that the notification that prompts users that a Chart of Account segment does not exists and that prevents you being able to add accounts easily.  The easiest and quickest solution for this problem is to quickly select 'Yes, add segment' and 'Do not display this message again'.  No harm no foul.

The downside of this is that the notification window exists for a reason and that reason is to ensure that users are not accidentally adding unnecessary COA segments.  Once you've selected 'Do not notify me again', GP listens and will no longer notify that user.

However, via SQL, we can reverse these effects and force GP to forget what we told it earlier.

Step 1. View users that currently have disabled this notification window

- Select * from {CompID}..SY01401 where coDefaultType = 13

Step 2. Create database backup or table backup

- Select * into SY01401_backup_MMddYY from SY01401

Step 3. Run delete statement

- This delete statement removes the records that suppress the notification window from appearing

- Delete from (CompID}..SY01401 where coDefaultType = 13

- To filter for one specific user, add the filter "and UserID = {User}"

Step 4. Run select to confirm records have been removed

- Select * from {CompID}..SY01401 where coDefaultType = 13

Step 5. Add a non-existent COA segment in GP & ensure the prompt window appears

Seems minor but notifications like these are valuable in ensuring account structures remain clean in consistent in an environment.

Simple solution to a simple problem?  Let us know in the comments below.

Automatically Running the Receivables Aging Routine via SQL - Dynamics GP

The manual aging routine can be a controversial topic when dealing with Dynamics GP.  For some businesses, seeing aged values at a certain period in time (say end of week, end of month, end of year), is all that's needed for receivables management.  For other businesses, there may be a demand to have more up to date receivable balances thus requiring the aging routine to be run on a more frequent basis. 

With a system like Microsoft Dynamics GP where the customer base is so diverse, having this sort of flexibility is key and makes sense from a development perspective.  However... it does create a rather tedious process of having to run the aging process each time the receivables team deems they'd like to see up to date summary balances by period.

Until Microsoft releases the ability to auto-age on a pre-determined schedule (fingers crossed for GP 2016!), the following script can be used to run the aging process directly through SQL.  The beauty of having a script is that this routine can be scheduled to run on a nightly basis thus never having to manually run again!

Please note that this stored procedure is designed to run in

GP2015R2

.  Table/Stored procedures can change between versions thus requiring the script to be updated.  If you're looking for another version of the script, please contact us or let us know in the comments below.

/*
Automatic Running of Aging Routine
--Includes Aging of Finance Charges
--Deployed Date yyMMdd
--Runs every x hours, days, months, etc
*/

DECLARE @O_iErrorState int, @I_dAgingDate datetime

select @I_dAgingDate = convert (varchar(10),GetDate(),102)

EXEC dbo.rmAgeCustomer 0,'','þþþþþþþþþþþþþþþ',@I_dAgingDate,127,1,0,'',@O_iErrorState OUT

/*
Automatic Running of Aging Routine
--Excludes Aging of Finance Charges
--Deployed Date yyMMdd
--Runs every x hours, days, months, etc
*/

DECLARE @O_iErrorState int,@I_dAgingDate datetime

select @I_dAgingDate = convert(varchar(10),GetDate(),102)

EXEC dbo.rmAgeCustomer 0,'','þþþþþþþþþþþþþþþ',@I_dAgingDate,127,0,0,'', @O_iErrorState

OUT

After running the script, you can open the aging window in GP to view your last run and also view the customer summary window to see the "Aged as of date".

Quick, efficient, and manageable solutions that make everyone's live easier are our favorite.  Running the auto-aging routine via SQL when wanting to see up to date balances is a no-brainer.  Just be sure that this routine is documented so that it doesn't get forgotten in future upgrades or server changes.

Check back soon for information on more simple solutions that we'll be discussing!