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!

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!

Professional Services Tools Library (PSTL) - Practical Uses

Microsoft now offers Professional Tools Services Library as part of the base Microsoft Dynamics package.  The set of tools offer many under-utilized tools that are easy selling points from an administrative side and simple to implement and manage going forward.

Installation

Before we get too far along, let's be sure that everyone knows how to install PSTL within your Dynamics GP environment.

Once Dynamics has been installed, select 'Change' from the machine's Add or Remove Applications page for Microsoft Dynamics GP.  After the installation window appears, select the Professional Services Tools Library & 'Enable'.

After the installation is complete, PSTL must be setup in EACH GP company that you wish to be able to use the tools within.  Simply navigate to the PSTL 'Gear' Icon that should now be available in the list of available modules & open the hub.

Tip:  For frequent users of PSTL, we typically recommend adding PSTL to the Home navigation pane.  This can be done by right-clicking the pane & selecting Add>>Window>>Technical Service Tools>>Project>>Professional Services Tools Library

One-Time Tools

There are a handful of tools that we recommend making use of in almost all circumstances.

Doc Date Verify

First & foremost is doc date verify under the Purchasing module.  This tool does as imagined and checks the document date of transactions being saved and posted in the posting module.  The days of posting transactions with document dates from 2216 are now behind us.  Thank you PSTL!

During transaction entry, if entering a document date for a close fiscal period, the user is warned that the fiscal period is closed & allows them the ability to override.  If the user tries to enter a document date for a fiscal period that does not exist, entry is prevented until the document date is corrected or the fiscal period is created.

Master Triggers

Ever have the problem of vendors, customers or accounts existing in 10 different GP companies with 10 different values?  You certainly wouldn't be alone as this is a very typical problem.  The master triggers within PSTL aim to ease these woes by 'triggering' creation of master records in all companies that you select.  That means when you add ABC Company as vendor ID 0001 in company A, that vendor ID will automatically be created with the full information in all other GP companies that you've setup.  Pair this feature with field-level or window security in subsidiary companies and instruct your users to create records in only one company and you're one step closer

to standardization.

SOP Customer Item Lookup

We all love quick & beneficial reports right?  Enabling the SOP Customer Item Lookup provides you with an additional inquiry window that quickly lists all items that a customer has purchased as well as their current prices.  After enabling the tool, you can populate the report with historical data from Sales Order Processing Setup>>Additional.  After the data has been populated, the inquiry window becomes available under the SOP Entry & SOP Item Detail windows.

Maintenance Tools

Maintenance tools are the tools that make all of our lives easier as being those responsible for keeping our Dynamics environments up and running smoothly at all times.

Toolkit

Toolkit under the Systems module provides us with the capability to quickly and efficiently rebuild the GL00105 table using the GL00100 table.  Too many times have we seen the GL00105 table become corrupt and the archaic methods required a semi-advanced working SQL knowledge or using 'other' tools within GP that gave us little to no confidence in the efficiency of the task being performed.  The rebuild tool is a great tool.

Update User Date

This tool will automatically update the system date for all users in the system @ midnight.  For environments where users are typically logged in around the clock, this is a nifty little tool that avoids confusion for end-users and prevents the accidental "Posting an entire day's worth of activity in the wrong period".

RM Transaction Unapply

How many times have you or your users questioned 'Removing' paid transactions from the sales module?  One of the biggest headaches with this process is that once a transaction is in the historical tables, it became an overly complicated burden to make changes to that transaction (like unapply a payment).  This tool allows you to do just that and will move the transaction(s) back to the open tables automatically.

Everyday Tools

Our final grouping of tools are the tools that can be used to solve common business/data problems that are typically encountered

Master Record Modifiers/Combiners

Ever wanted to combine checkbook activity, change an item number, update your chart of account format, etc?  More likely than not, PSTL is a good place to begin this process and the tools available can handle the execution of changing data on the back-end.  We recommend starting small & testing any changes in a development environment before attempting in production.  Also, factor in the 3rd party applications in your environment that may NOT be updated using the PSTL tools available.  No need to fear if PSTL doesn't reach far enough, most ISVs are prepared to handle any data changes that are needing to be made.

Company Copy

This tool isn't perfect and doesn't do ALL of the work for you (what good would we be if it did?) but it does handle a large majority of the simple company setup tasks.  When setting up a company and wanting to use another company as a template, try using the company copy tool.  You'll be glad that you did after you're hours into the new company setup checklist provided by Microsoft.

Years ago, this article might have never ended but with each new release of Dynamics GP, Microsoft has done a great job at providing some more robust tools directly within the normal interface or made improvements so that some of these cleanup tools are no longer needed to be relied on.

Want to know more about a tool or have another favorite in mind?  Let us know in the comments below!

GP Security: Identifying Security Roles, Tasks, & Operations

A common request we receive is: "We want to tie down security of our users but have no clue as to who has access to what".  To be frank, the reporting for this in GP is not exactly easy to come by.  While there are a number of ISVs available that will provide you with tools and analytics for this information, we have a quick fan-favorite solution via SQL to gather this data.

This information can also be found on the Microsoft Developer Blog here.  

First thing's first, in order to get the following script to populate data, the Clear Data Procedure on the Security Resource Descriptions table within GP.  This can be done from Microsoft Dynamics GP>>Maintenance>>Clear Data

Select 'Display' in the toolbar change to 'Physical', Series = 'System' & Table = Security Resource Descriptions.

After inserting the table and running the clear data procedure, the SQL query will now populate the users>>roles>>tasks>>operations which can be used as a starting point to identify and cleanup security/internal control concerns.

SELECT DISTINCT

    S.USERID UserID, S.CMPANYID CompanyID,

    C.CMPNYNAM CompanyName, S.SecurityRoleID,

    coalesce(T.SECURITYTASKID,'') SecurityTaskID,

    coalesce(TM.SECURITYTASKNAME,'') SecurityTaskName,

    coalesce(TM.SECURITYTASKDESC,'') SecurityTaskDescription,

    coalesce(R.DICTID,'') DictionaryID,

    coalesce(R.PRODNAME,'') ProductName,

    coalesce(R.TYPESTR,'') ResourceType,

    coalesce(R.DSPLNAME,'') ResourceDisplayName,

    coalesce(R.RESTECHNAME,'') ResourceTechnicalName,

    coalesce(R.Series_Name,'') ResourceSeries

FROM    SY10500 S   -- security assignment user role

LEFT OUTER JOIN

    SY01500 C   -- company master

    ON S.CMPANYID = C.CMPANYID

LEFT OUTER JOIN

    SY10600 T  -- tasks in roles

    ON S.SECURITYROLEID = T.SECURITYROLEID 

LEFT OUTER JOIN

    SY09000 TM  -- tasks master

    ON T.SECURITYTASKID = TM.SECURITYTASKID 

LEFT OUTER JOIN

    SY10700 O  -- operations in tasks

    ON T.SECURITYTASKID = O.SECURITYTASKID 

LEFT OUTER JOIN

    SY09400 R  -- resource descriptions 

    ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE

    AND O.SECURITYID = R.SECURITYID

Without the Clear Data procedure ahead of time, the resource descriptions table would be empty and provide no detail on the security operations.

Security is never fun and sometimes hard to grasp your arms around.  We hope this report helps.  Please leave us any questions or feedback below & be sure to our out website @ ALaCarteGP.com.

Until Next Time!

Customer/Vendor Relationship Setup and Processing

Too many times have we seen makeshift customer & vendor relationships and processes being used within GP.  With the latest versions of GP, even more features are available that make creating relationships and processing transactions between the purchasing and sales module a breeze.  Our video tutorial covers the setup and processing of the most useful features to help you bridge the gap between the purchasing and sales modules. 

Check out our full video library on our Youtube channel.