Dynamics GP 2015 with 120+ enhancements!

As promised, Microsoft has released Dynamics GP 2015 on time! 120+ enhancements were introduced, see below the first look into the product:

Before you plan your upgrade, keep in mind that SQL 2008 is no longer supported, you must upgrade at least to SQL 2012 or later.

1. Icons has been Changed!

2. Login Page has been changed!

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

No Print for Purchase Requisitions!!

When you see the window print button you expect that the information of the window you are on is being printed on a report writer report, and when one of your customers inform you that no action is being taken upon pressing the report you just feel like the user is not aware whats going on and a privileges  issue should take place!

Well for Purchase Requisition Entry form this is not the case! the window print is not linked to any of the available reports, clicking the button will do nothing and you will feel a little bit confused about what to be done!

The button indeed does not have a report assigned, but you can design your report on SSRS and link it to this button! Just click the Small Arrow near the print button and hit “Assign Reports”, then select the report you need to link, see the below for clarification:

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Workflow 2.0 “OR” Operator not working

While working on the Dynamics GP 2013 R2 workflow, I realized that the “OR” operator is not working properly, take a look into the below example:

Step 1: Approval By User X --> Only when the Requester is "User 1" OR "User 2"
Step 2: Approval By User Y --> Only when the Requester is "User 3" OR "User 4"

When you submit the requisition it goes for both User X & User Y, this is for both the Requisition and the Purchase Order as well!

I reported this to the connect website, hope they will fix this in the upcoming 2015 release.

https://connect.microsoft.com/dynamicssuggestions/feedbackdetail/view/1026359/or-is-not-working-properly-in-dynamics-gp-2013-r2-workflows-po-requisition

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Dynamics GP 2013 - Web Client Only Users & Active Directory Integration (LDAP)

This morning I been thinking about the new security structure for Dynamics GP web-client user who does not have SQL Users, and how these users will interact with SQL Server, I had to go through this as I have a client who’s running a very strict policy in granting SQL permissions for domain users.

Getting through this, I have created few users with multiple scenarios as below:

1. The first user linked to domain account called that “TEST1”, but didn’t check “Web Client user only (no SQL Server Account)”
2. The second user linked to domain account called that “TEST2”, and checked the “Web Client user only (no SQL Server Account)” option.

I have noticed that the first case created an “SQL User Account” called “TEST1” and didn’t granted the domain user any access to the database, and the second didn’t create any users which been a mystery to me understanding which user will be used to access the SQL Server!

To test this I have modified the GL00100 table and added a new column called “UserName” –this is for testing only, you cannot do this on GP tables!- and set the default value for this column to be “SUSER_SNAME()” function which returns the user who’s currently logged in and started my testing by creating new accounts!

Logged in by TEST1 to the web client and created a new account, the result was somehow wired, the user who was recorded was “DYNSA”

Logged in by TEST2 to the web client and created a new account, the result was the same, the user who was recorded was “DYNSA” as well.

Then logged in but selected “SQL Authentication” option from the login page:

The result was as expected, the logged in user was “TEST1”!

Now it been clear, all windows authentication users will be using one account to access the SQL, the login is the one you specified during the installation of the web client, so if you been counting on the user who is currently logged in in your customizations you might need to review your code!

Hope that helps giving you a good understanding to the new security process.

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Consolidating multiple Dynamics GP Companies into single DYNAMICS database

Recently I had an interesting project with one of my customers, they had the following environment:

Server I:

• Dynamics GP 9 RTM with 14 active companies.
• SQL Server 2005 (Collation is Arabic_CI_AS).
• Windows Server 2003 & FRx

Server II:

• Dynamics GP 11 RTM with 3 active companies.
• SQL Server 2008 R2 (Collation is Latin)
• Windows Server 2008 & FRx

The customer simply wanted to upgrade to Dynamics GP 2013 SP2 and to SQL 2012, and also wanted to consolidate the databases into one single DYNAMIMCS database.

Challenges:

Now we have few challenges detailed below:

1. Dynamics GP 2013 does not support upgrading from GP 9.0
2. Dynamics GP 2013 does not support SQL 2005 and GP 9.0 does not support SQL 2012.
3. Database collation differences between companies.
4. Data stored in DYNAMICS database like currencies, users and BP objects.
5. Changes that will occur on columns like “Company ID” and “Currency Index” which will be changed due to DYNAMICS data consolidation.
6. Security migration from GP 9.0 to GP 2013.
7. FRx migration to Management Reporter.
8. Minimum downtime need to be maintained! 17 companies across multiple countries cannot stay down forever!

Resolutions:

Well we believe that nothing is impossible in the IT field, we have planned for everything and scheduled the upgrade process, below the steps we followed:

Server I:

• Took a backup for all databases.
• Upgraded from version (GP 9.00.0114) to 9.00.0371 that supports the upgrade to GP 2010 (we need GP2010 because it supports both SQL 2005 and SQL 2012!)
• Upgrade from (GP 9.00.0114) to GP 2010 (GP 11.00.2320) that supports direct upgrade to GP 2013.
• Moved upgraded databases from SQL 2005 to SQL 2012 with moving all users and configurations.
• Upgraded to GP 2013 SP2.
• Checked System stability.
• Updated Users permissions and passwords.
• Installed Dynamics GP 2013 with SP2 on all clients
• Installed Management Reporter that replaces the FRx on the server and on the clients.
• Migrated existing FRx Reports to Management Reporter
• Activated SQL Reporting Services
• Installed and activated Dynamics GP Web Client
• Started the data entry!

This operation took the first weekend!

Server II:

• Took a backup for all databases.
• Upgraded from your current version GP to the service pack that supports the upgrade to GP 2013.
• Upgraded to GP 2013 SP2.
• Checked System stability
• Created new companies on the new server
• Restored databases from old server to PROCCOSQL over newly created databases
• Update databases collation to match old server collation (I used the tool that updates the SQL database collation by dropping all functions, constraints and indexes that is collation dependence)
• Updated CURRENCY ID in all databases columns to match the newly added currencies, it was indexed as “100X” and changed to “10XX”
• Update Company ID in all databases columns to match the newly added companies.
• Add newly created companies to Management Reporter
• Activate SQL Reporting Services

This operation took the second weekend! Till now everything is working properly and this is how their GP looks like right now:

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Disable “Remember Company” checkbox

I got a request from one of my customers to disable the flag of “Remember Company” that exists on the company login form below:

I have made this using VBA, please download the code using the link below:

http://di.jo/Files/RememberCompany.zip

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Fell the Management Accounting power with ERP

Hello Guys,

I am joining Emad Al Faris from Microsoft in presenting a couple of sessions for JAMA (Jordanian Association for Management Accountants), under the subjects of:

1. Get to know ERP solutions – by Emad Al Faris

2. Fell the Management Accounting power with ERP

Everyone is welcome to join! Below the session brochure:

http://di.jo/files/erp.pdf

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Maps Reporting using SSRS

One of the challenges that I been trying to achieve is having my reports graphically designed to show data on “Maps” and that always been the toughest part customers might request.

Few days back I been researching with my colleagues on how the “Sales By States” KPI works in Dynamics GP that dynamically pulls the sales of each state on the US map and discovered the “Map” feature in SSRS!

We have been able to download the “.shp” file for Jordan map from one of the online free GIS providers and integrated this will SSRS, and then created the following report in SSRS in few minutes, saved this report in SSRS and been able to print this report from GP business analyzer:

This article is just to show that this is doable for users to give it a try.

Happy reporting!

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Purchase Order Commitment–Required Date from PO Required Date or Item Required Date?

I got an interesting question from of my customers who are willing to activate the purchase order commitments, the question was the documentation stated the following:

The Required Date must fall in a valid and open fiscal period.

The purchase order header has a required date field and the line items each has its own required date field, so which one will be used to commit the purchase order against the budget?

Logically each line item will be validated separately against the budget and therefore the date should be the line item date, after checking this it turned to be right and the system is looking into the line item “Required Date”.

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

How to Track unused Journal Numbers

I got an interesting question from one of the GP users requesting a list of unused Journal Number, therefore I decided to scratch my head and write this query, below will help:

SELECT DISTINCT NUMBER FROM MASTER..spt_values WHERE NUMBER BETWEEN
(SELECT MIN(JRNENTRY) AS FROMNUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
(SELECT MAX(JRNENTRY) AS TONUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
number NOT IN (SELECT DISTINCT JRNENTRY FROM GL20000)

Happy tracking…!

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Analytical Account Posting Setup - Breaking the Hidden Secrets!!

Yesterday one of my customers reported an issue that the analytical accounting report is not being printed after the “General Posting Journal” upon posting a GL Transaction.

First thing came to my mind that this report has been unmarked in “Posting Setup” then in moment I realized that the Posting Setup does not contains anything for AA!!

It has been said in the analytical accounting installation manual that YOU MUST CONFIGURE YOU POSTING SETUP BEFORE ACTIVATING THE PRODUCT:

Setting up posting options for Analytical Accounting
Once you’ve installed Analytical Accounting for your company, you must set up
the appropriate posting options for Analytical Accounting in the Microsoft
Dynamics GP Posting Setup window. Be sure that you’ve completed this task before
you activate Analytical Accounting.

In order to collect analytical information for an account, the account must be linked
to an account class. Analytical Accounting does not support posting in summary.

So how to adjust this? Where can I find the posting setup for AA and how to configure my reports?

An hour of profiler trace returned what does GP actually do to load report, specially after activating the “Allow Transactions Posting” option and getting the error below on the report generation:

“Unable to obtain posting destination record.”

I my trace, I noticed that GP is running the following query to get report printed to the appropriate destination:

exec TWO.dbo.zDP_SY02200SS_1 2,'General Entry','General Posting Journal'

This script returned the following results:

Then I saw the following command which returned the following result:

exec TWO.dbo.zDP_SY02200SS_1 2,'General Entry','3180rpt_8'

At this moment I realized that this “3180rpt_8” report is being called without being exist in posting setup table and that’s why I used to get the above message and being able to get the report printed.

Resolution:

I just added the report row into SY02200 table and rerun the posting! That worked like a charm!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Auto Assign Newly Added Analytical Accounting Code to All Users

I got a request from one of my customers, they had a need to auto assign newly created AA codes to all users and that how the idea started, I saw the scripts created by Sivakumar earlier to auto assign all codes to all users and used his cross join to create the below trigger.

The trigger is simply works on AAG00401 table upon record insert, it assigns the newly created codes to all GP users automatically, just run below script over your company database and enjoy.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

 

-- =============================================
-- Author     :    Mohammad R. Daoud
-- Create date: 09:56 AM ‎07/‎12/‎2012
-- Description:    This trigger as created to auto assign 
-- newly created analytical accounting codes to all users.
-- =============================================
CREATE TRIGGER DI_AutoAssignSecurity
   ON  AAG00401
   FOR INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
DECLARE @aaTrxDimID INT
DECLARE @aaTrxDimCodeID INT

SELECT TOP 1 @aaTrxDimID = aaTrxDimID, @aaTrxDimCodeID = aaTrxDimCodeID FROM INSERTED
DELETE FROM AAG02000 WHERE aaTrxDimID = @aaTrxDimID AND AAG02000.aaTrxDimCodeID = @aaTrxDimCodeID
INSERT INTO AAG02000 (USERID, aaTrxDimID, aaTrxDimCodeID, aaDistribute, aaAdjust) (SELECT B.USERID, A.aaTrxDimID, A.aaTrxDimCodeID, 1, 1 FROM AAG00401 A CROSS JOIN DYNAMICS..SY01400 B WHERE A.aaTrxDimID = @aaTrxDimID AND A.aaTrxDimCodeID = @aaTrxDimCodeID) 
END
GO

Selecting a New Microsoft Dynamics GP Partner

I got an interesting questions from one of the Dynamics GP users out of my region, on how to select the best partner to deliver my demands, for sure my immediate reply was “Select me!”

However, not all customers will select me! And this user been looking for local partner in their area, therefore I felt that this is the best time to publish how do I select a new partner if I been a customer and how not to be blamed if the partner been not as expected.

I been through this before, where I been selected to evaluate tendering process for selecting a partner for a customer out of my region who runs GP for a while and looking to re-implement the product to follow the standards and work on the product correctly.

For partner selection, I normally analyze all the aspects carefully before recommending any partners even if I worked with them closely, just to make sure that customers fully comfortable with the recommended partner before doing the selection, normally I recommend people I know abroad and looking for local partners to follow steps below before signing the agreement with the new partner:

Major Selection Criteria:

1.      Ask for the complete list of customers, make sure to visit 3 as per your selection not the partner and randomly talk to at least 10 on phone, select users from the accounting department who works on the product and have no interest in recommending the partner, ask for partner performance in delivering support, knowledge in business and knowledge in Dynamics GP modules, plus what modules do they really implements for customers? Do they deliver the core application modules to close the project or they really providing their customers with the max of Dynamics GP features.

2.      Ask the partner to deliver a presentation on Microsoft Dynamics GP features for you, and make sure to ignore normal product modules and focus your questions on the modules that requires a real experts in the product like (Payment Document Management, Cashbook Management, Electronic Funds Transfer, Electronic Bank Reconciliation, KPIs Management, Requisitions Management, and Workflow) which will enhance the product usability in the future and will benefit your organization.

3.      Ask to meet the resource who will be handling your support and make sure to include his name in the contract if you liked his experience.

4.      Make sure that the partner you selects runs a helpdesk system with monitored response time KPI to make sure that any delays in your call be monitored and accountable.

5.     Grant added value for partner who worked on a business near to yours with other customers, this is really make a big difference.

With the above points, all your partners will get low scores, but at least you will know who gets the highest!

Other Points to Consider from the blogsphere:

1. The partner you choose should be a "Managed Partner" for Dynamics GP by Microsoft. This assures access to local Microsoft resources and response to your needs by a team that is incented to work with you.
2. Years in business and years of Microsoft Dynamics Experience - more is better. The average life of a Microsoft Dynamics GP partner is less than 4 years. Many partners have come and gone over the years. Many have closed their doors, sold, merged or have been acquired. Many of these players have resurfaced with new company names and new faces, leaving clients with broken partnerships and promises.
3. Breadth of experience. Make sure partner has specialist and certified professionals for core competencies (Financial, Distribution/Manufacturing, Supply Chain, Payroll/Human Resources, CRM, etc.) around Dynamics.
4. Partner Advantage Support. This is the highest level of support a partner can get from Microsoft for Dynamics. Your partner should have a contract with Microsoft for this level of Dynamics GP support. This provides the partner 24×7, level II one hour response times. Otherwise, 3 hour estimated response times is the norm, 8-5, level one support (less than the level of support a client can contract for).
5. Microsoft Certified Sales Professionals. Partners with this position are allowed to provide clients with special offers on licenses and Microsoft services.
6. Dedicated Client Account Manger (often referred to as a Customer Sales Manager, CSM, within the Microsoft Dynamics community). Partners that have CSMs value long-term relationships with customers and hold events to ensure your organization stays on top of the latest features and functionality long after go-live.
7. Microsoft Sure Step Methodology for implementations. This methodology ensures confidence that implementations will go smoothly, be consistently replicated, and have predictable outcomes.
8. Reasonable travel time and travel expense charges. These charges can add as much as a 50% to 100% surcharge to the effective hourly rate.
9. Relationships with all of the Dynamics GP Independent Software Vendors (ISV) related to your implementation. Make sure your new partner has a positive previous working relationship with these vendors.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Dynamics GP Excel Paste – New Features has been Added!

Following to the requests we are getting from many users, we have added the below functionalities the the product:

Move rows up and down

You can now add your rows and then use arrows to move your record up and down:

Direct Grid Modifications

You can now perform modifications to the product by modifying the grid directly and then click on “Save Grid Modifications” so if you missed up with Excel Column Index for example you can simple readjust the sequence and save:

Download Templates

Few days back we have released the ability to paste the setup directly to the tool, but found this a little bit hard for end users to copy tasks one by one, therefore we have added a new functionality to download templates from the internet directly to your database:

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Dynamics GP Excel Paste Templates

A new version of Microsoft Dynamics GP Excel Paste has been released, but not the one that pastes with header and details, instead we have decided to release “templates support” version until getting the other version ready due to the high demand to get this feature and to reduce the support tasks that been assigned to the support team.

By releasing this version, end users will be able to download ready templates from the following page:

http://di.jo/GPExcelPasteTemplates.aspx

And simply paste the template into the Dynamics GP Excel Paste Setup form as shown in the video below:

Setup Excel Paste Templates

Experts can easily create templates, copy what they have created from the grid to Excel and just send it to me for publishing, this will make users life easier!

Enjoy!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Dynamics GP Excel Paste Updates

Since the first day I announced the release of the excel paste, I been getting questions and requests from customers who already purchased the tool to do few modifications.

Issues started with unique screens, since the product is totally dynamic and does not have screens statically defined, sometimes few screens might not be effective as required and we been working the last few weeks on catching these screens by following customers requests.

Honestly I didn’t expect this number of requests nor the number of interested customers, which added a big number of helpdesk incidents to cover customers demands and product gaps.

One of the big things we used to get was to include the ability of having “headers” and “details” as a part of the product functionality so end users can paste multiple invoices at once or one invoice with multiple lines and each line with multiple serials, and here the challenge was started!

We been able to dynamically deliver the first part, we have adjusted the product to accept multiple windows by having one to many relation between the header and details lines. Below video might clarify more:

Example on header and details lines in GP Excel Paste

This version will be published soon once it get released from the Quality Assurance team and fine tuning some features, and will keep trying hard to find a solution for items and serials.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

COPY Advanced Financials Reports Across Companies

 

Normally you will need to create your balance sheet and paste it over all your companies, script below will do the task for you:

 

Download Link:

http://www.di.jo/files/COPY Advanced Financials Reports Between Companies.zip

 

Script:

--REPLACE Source with your source Company ID

--REPLACE Destination with your destination Company ID

INSERT INTO [Destination].DBO.AF40100 ([RPRTNAME],[REPORTID],[RPRTTYPE],[CLCFRPRT],[LSTMODIF],[NOTEINDX])  SELECT [RPRTNAME],[REPORTID],[RPRTTYPE],[CLCFRPRT],[LSTMODIF],[NOTEINDX] FROM [Source].DBO.AF40100

INSERT INTO [Destination].DBO.AF40101 ([REPORTID],[MNHDRCNT],[MNFTRCNT],[SHDRCNT],[SFTRCNT],[ROWCNT1],[COLCNT],[SHDRPCNT],[SFTRPCNT],[MNHDRFLG],[MNFTRFLG],[SHDRFLAG],[SFTRFLAG],[MNHDRSIZ],[MNFTRSIZ],[SHDRSIZE_1],[SHDRSIZE_2],[SHDRSIZE_3],[SHDRSIZE_4],[SHDRSIZE_5],[SFTRSIZE_1],[SFTRSIZE_2],[SFTRSIZE_3],[SFTRSIZE_4],[SFTRSIZE_5],[SHDROPT_1],[SHDROPT_2],[SHDROPT_3],[SHDROPT_4],[SHDROPT_5],[SHDRPRT_1],[SHDRPRT_2],[SHDRPRT_3],[SHDRPRT_4],[SHDRPRT_5],[SFTROPT_1],[SFTROPT_2],[SFTROPT_3],[SFTROPT_4],[SFTROPT_5],[SFTRPRT_1],[SFTRPRT_2],[SFTRPRT_3],[SFTRPRT_4],[SFTRPRT_5],[COLHDCNT],[COLDHSIZ_1],[COLDHSIZ_2],[COLDHSIZ_3],[COLDHSIZ_4],[COLDHSIZ_5],[COLDHSIZ_6],[RTOTLSIZ],[COLTOSIZ],[COLOFSIZ],[LFTMARGN],[RTMARGIN],[TOPMARGN],[BOTMARGN]) SELECT [REPORTID],[MNHDRCNT],[MNFTRCNT],[SHDRCNT],[SFTRCNT],[ROWCNT1],[COLCNT],[SHDRPCNT],[SFTRPCNT],[MNHDRFLG],[MNFTRFLG],[SHDRFLAG],[SFTRFLAG],[MNHDRSIZ],[MNFTRSIZ],[SHDRSIZE_1],[SHDRSIZE_2],[SHDRSIZE_3],[SHDRSIZE_4],[SHDRSIZE_5],[SFTRSIZE_1],[SFTRSIZE_2],[SFTRSIZE_3],[SFTRSIZE_4],[SFTRSIZE_5],[SHDROPT_1],[SHDROPT_2],[SHDROPT_3],[SHDROPT_4],[SHDROPT_5],[SHDRPRT_1],[SHDRPRT_2],[SHDRPRT_3],[SHDRPRT_4],[SHDRPRT_5],[SFTROPT_1],[SFTROPT_2],[SFTROPT_3],[SFTROPT_4],[SFTROPT_5],[SFTRPRT_1],[SFTRPRT_2],[SFTRPRT_3],[SFTRPRT_4],[SFTRPRT_5],[COLHDCNT],[COLDHSIZ_1],[COLDHSIZ_2],[COLDHSIZ_3],[COLDHSIZ_4],[COLDHSIZ_5],[COLDHSIZ_6],[RTOTLSIZ],[COLTOSIZ],[COLOFSIZ],[LFTMARGN],[RTMARGIN],[TOPMARGN],[BOTMARGN] FROM [Source].DBO.AF40101

INSERT INTO [Destination].DBO.AF40102 ([REPORTID],[HDRFTRTY],[FLDNUM],[FLDPOSX1],[FLDPOSY1],[FLDPOSX2],[FLDPOSY2],[FLDTYPE],[FLDFRMAT],[SBHSBFIN],[FLDOPT],[FLDOPT2],[FLDALIGN],[FLDFTFML],[FLDFTSIZ],[FLDSTYLE_1],[FLDSTYLE_2],[FLDSTYLE_3],[FLDSTYLE_4],[FLDSTYLE_5],[FLDSTYLE_6],[FLDVALUE],[FLDVALU2],[FLDPRNAM]  ) SELECT [REPORTID],[HDRFTRTY],[FLDNUM],[FLDPOSX1],[FLDPOSY1],[FLDPOSX2],[FLDPOSY2],[FLDTYPE],[FLDFRMAT],[SBHSBFIN],[FLDOPT],[FLDOPT2],[FLDALIGN],[FLDFTFML],[FLDFTSIZ],[FLDSTYLE_1],[FLDSTYLE_2],[FLDSTYLE_3],[FLDSTYLE_4],[FLDSTYLE_5],[FLDSTYLE_6],[FLDVALUE],[FLDVALU2],[FLDPRNAM]   FROM [Source].DBO.AF40102

INSERT INTO [Destination].DBO.AF40103 ([REPORTID],[COLNUM],[CLTKNCNT],[COLTYPE],[COLSIZE],[COLOMCNT],[COLOFMRK_1],[COLOFMRK_2],[COLOFMRK_3],[COLOFMRK_4],[HIDEFLAG],[TEXTVALU],[STPERIOD],[ENDPEROD],[AMNTFROM],[HISTYEAR],[BUDID],[PRTSIGN],[PRTCOMMA],[PRTPCENT],[PRTTEXT],[ROUNDOPT],[HEADALIN],[HDFTFMLY],[HDFTSIZE],[HEDSTYLE_1],[HEDSTYLE_2],[HEDSTYLE_3],[HEDSTYLE_4],[HEDSTYLE_5],[HEDSTYLE_6],[HEADTYPE_1],[HEADTYPE_2],[HEADTYPE_3],[HEADTYPE_4],[HEADTYPE_5],[HEADTYPE_6],[HEDFRMAT_1],[HEDFRMAT_2],[HEDFRMAT_3],[HEDFRMAT_4],[HEDFRMAT_5],[HEDFRMAT_6],[HEADOPT_1],[HEADOPT_2],[HEADOPT_3],[HEADOPT_4],[HEADOPT_5],[HEADOPT_6],[HEADOPT2_1],[HEADOPT2_2],[HEADOPT2_3],[HEADOPT2_4],[HEADOPT2_5],[HEADOPT2_6],[COLHDNG_1],[COLHDNG_2],[COLHDNG_3],[COLHDNG_4],[COLHDNG_5],[COLHDNG_6],[COLHDNG2_1],[COLHDNG2_2],[COLHDNG2_3],[COLHDNG2_4],[COLHDNG2_5],[COLHDNG2_6],[ALGNOFST],[COLEXPER],[NOTEINDX],[SEGFROM_1],[SEGFROM_2],[SEGFROM_3],[SEGFROM_4],[SEGFROM_5],[SEGFROM_6],[SEGFROM_7],[SEGFROM_8],[SEGFROM_9],[SEGFROM_10],[SEGTO_1],[SEGTO_2],[SEGTO_3],[SEGTO_4],[SEGTO_5],[SEGTO_6],[SEGTO_7],[SEGTO_8],[SEGTO_9],[SEGTO_10]) SELECT [REPORTID],[COLNUM],[CLTKNCNT],[COLTYPE],[COLSIZE],[COLOMCNT],[COLOFMRK_1],[COLOFMRK_2],[COLOFMRK_3],[COLOFMRK_4],[HIDEFLAG],[TEXTVALU],[STPERIOD],[ENDPEROD],[AMNTFROM],[HISTYEAR],[BUDID],[PRTSIGN],[PRTCOMMA],[PRTPCENT],[PRTTEXT],[ROUNDOPT],[HEADALIN],[HDFTFMLY],[HDFTSIZE],[HEDSTYLE_1],[HEDSTYLE_2],[HEDSTYLE_3],[HEDSTYLE_4],[HEDSTYLE_5],[HEDSTYLE_6],[HEADTYPE_1],[HEADTYPE_2],[HEADTYPE_3],[HEADTYPE_4],[HEADTYPE_5],[HEADTYPE_6],[HEDFRMAT_1],[HEDFRMAT_2],[HEDFRMAT_3],[HEDFRMAT_4],[HEDFRMAT_5],[HEDFRMAT_6],[HEADOPT_1],[HEADOPT_2],[HEADOPT_3],[HEADOPT_4],[HEADOPT_5],[HEADOPT_6],[HEADOPT2_1],[HEADOPT2_2],[HEADOPT2_3],[HEADOPT2_4],[HEADOPT2_5],[HEADOPT2_6],[COLHDNG_1],[COLHDNG_2],[COLHDNG_3],[COLHDNG_4],[COLHDNG_5],[COLHDNG_6],[COLHDNG2_1],[COLHDNG2_2],[COLHDNG2_3],[COLHDNG2_4],[COLHDNG2_5],[COLHDNG2_6],[ALGNOFST],[COLEXPER],[NOTEINDX],[SEGFROM_1],[SEGFROM_2],[SEGFROM_3],[SEGFROM_4],[SEGFROM_5],[SEGFROM_6],[SEGFROM_7],[SEGFROM_8],[SEGFROM_9],[SEGFROM_10],[SEGTO_1],[SEGTO_2],[SEGTO_3],[SEGTO_4],[SEGTO_5],[SEGTO_6],[SEGTO_7],[SEGTO_8],[SEGTO_9],[SEGTO_10] FROM [Source].DBO.AF40103

INSERT INTO [Destination].DBO.AF40104 ([REPORTID],[CLCOLNUM],[TKNODNUM],[TKNTYPE],[TKNVALUE],[TKNDLVAL],[TKNUNACT_1],[TKNUNACT_2],[TKNUNACT_3],[TKNUNACT_4],[TKNUNACT_5],[TKNUNACT_6],[TKNUNACT_7],[TKNUNACT_8],[TKNUNACT_9],[TKNUNACT_10]) SELECT [REPORTID],[CLCOLNUM],[TKNODNUM],[TKNTYPE],[TKNVALUE],[TKNDLVAL],[TKNUNACT_1],[TKNUNACT_2],[TKNUNACT_3],[TKNUNACT_4],[TKNUNACT_5],[TKNUNACT_6],[TKNUNACT_7],[TKNUNACT_8],[TKNUNACT_9],[TKNUNACT_10] FROM [Source].DBO.AF40104

INSERT INTO [Destination].DBO.AF40105 ([REPORTID],[CLCOLNUM],[TKNODNUM],[TKNTYPE],[TKNVALUE],[TKNDLVAL],[TKNUNACT_1],[TKNUNACT_2],[TKNUNACT_3],[TKNUNACT_4],[TKNUNACT_5],[TKNUNACT_6],[TKNUNACT_7],[TKNUNACT_8],[TKNUNACT_9],[TKNUNACT_10]     ) SELECT [REPORTID],[CLCOLNUM],[TKNODNUM],[TKNTYPE],[TKNVALUE],[TKNDLVAL],[TKNUNACT_1],[TKNUNACT_2],[TKNUNACT_3],[TKNUNACT_4],[TKNUNACT_5],[TKNUNACT_6],[TKNUNACT_7],[TKNUNACT_8],[TKNUNACT_9],[TKNUNACT_10]FROM [Source].DBO.AF40105

INSERT INTO [Destination].DBO.AF40106 ([REPORTID],[ROWNUMBR],[TOTKNCNT],[ROWTYPE],[ROWSIZE],[ROLUPFLG],[ROWDESC],[SUBSUDID],[TYPCLBAL],[CATNUMBR],[PRTSIGN],[PRTHEDER],[CENTHEDR],[ROWFTFAM],[ROWFTSIZ],[ROWSTYLE_1],[ROWSTYLE_2],[ROWSTYLE_3],[ROWSTYLE_4],[ROWSTYLE_5],[ROWSTYLE_6],[ROFMRKIN_1],[ROFMRKIN_2],[ROFMRKIN_3],[ROFMRKIN_4],[ROFMRKIN_5],[ROFMRKIN_6],[ROFMRKIN_7],[ROFMRKIN_8],[ROFMRKIN_9],[ROFMRKIN_10],[ROFMRKIN_11],[ROFMRKIN_12],[ROFMRKIN_13],[ROFMRKIN_14],[ROFMRKIN_15],[ROFMRKIN_16],[ROFMRKIN_17],[ROFMRKIN_18],[ROFMRKIN_19],[ROFMRKIN_20],[ROFMRKIN_21],[ROFMRKIN_22],[ROFMRKIN_23],[ROFMRKIN_24],[ROFMRKIN_25],[ROFMRKIN_26],[ROFMRKIN_27],[ROFMRKIN_28],[ROFMRKIN_29],[ROFMRKIN_30],[ROFMRKIN_31],[ROFMRKIN_32],[ROFMRKIN_33],[ROFMRKIN_34],[ROFMRKIN_35],[ROFMRKIN_36],[ROFMRKIN_37],[ROFMRKIN_38],[ROFMRKIN_39],[ROFMRKIN_40],[CFLOSCTN],[RWEXPERR],[NOTEINDX],[STTACCT_1],[STTACCT_2],[STTACCT_3],[STTACCT_4],[STTACCT_5],[STTACCT_6],[STTACCT_7],[STTACCT_8],[STTACCT_9],[STTACCT_10],[ENDACCT_1],[ENDACCT_2],[ENDACCT_3],[ENDACCT_4],[ENDACCT_5],[ENDACCT_6],[ENDACCT_7],[ENDACCT_8],[ENDACCT_9],[ENDACCT_10]) SELECT [REPORTID],[ROWNUMBR],[TOTKNCNT],[ROWTYPE],[ROWSIZE],[ROLUPFLG],[ROWDESC],[SUBSUDID],[TYPCLBAL],[CATNUMBR],[PRTSIGN],[PRTHEDER],[CENTHEDR],[ROWFTFAM],[ROWFTSIZ],[ROWSTYLE_1],[ROWSTYLE_2],[ROWSTYLE_3],[ROWSTYLE_4],[ROWSTYLE_5],[ROWSTYLE_6],[ROFMRKIN_1],[ROFMRKIN_2],[ROFMRKIN_3],[ROFMRKIN_4],[ROFMRKIN_5],[ROFMRKIN_6],[ROFMRKIN_7],[ROFMRKIN_8],[ROFMRKIN_9],[ROFMRKIN_10],[ROFMRKIN_11],[ROFMRKIN_12],[ROFMRKIN_13],[ROFMRKIN_14],[ROFMRKIN_15],[ROFMRKIN_16],[ROFMRKIN_17],[ROFMRKIN_18],[ROFMRKIN_19],[ROFMRKIN_20],[ROFMRKIN_21],[ROFMRKIN_22],[ROFMRKIN_23],[ROFMRKIN_24],[ROFMRKIN_25],[ROFMRKIN_26],[ROFMRKIN_27],[ROFMRKIN_28],[ROFMRKIN_29],[ROFMRKIN_30],[ROFMRKIN_31],[ROFMRKIN_32],[ROFMRKIN_33],[ROFMRKIN_34],[ROFMRKIN_35],[ROFMRKIN_36],[ROFMRKIN_37],[ROFMRKIN_38],[ROFMRKIN_39],[ROFMRKIN_40],[CFLOSCTN],[RWEXPERR],[NOTEINDX],[STTACCT_1],[STTACCT_2],[STTACCT_3],[STTACCT_4],[STTACCT_5],[STTACCT_6],[STTACCT_7],[STTACCT_8],[STTACCT_9],[STTACCT_10],[ENDACCT_1],[ENDACCT_2],[ENDACCT_3],[ENDACCT_4],[ENDACCT_5],[ENDACCT_6],[ENDACCT_7],[ENDACCT_8],[ENDACCT_9],[ENDACCT_10] FROM [Source].DBO.AF40106

INSERT INTO [Destination].DBO.AF40107 ([REPORTID] ,[TOTRWNUM],[TKNODNUM],[STROWNUM],[ENDRWNUM]) SELECT [REPORTID] ,[TOTRWNUM],[TKNODNUM],[STROWNUM],[ENDRWNUM] FROM [Source].DBO.AF40107

INSERT INTO [Destination].DBO.AF40108 ([REPORTID],[TOTRWNUM],[MBRWNUM]) SELECT [REPORTID],[TOTRWNUM],[MBRWNUM] FROM [Source].DBO.AF40108

INSERT INTO [Destination].DBO.AF40109 ([FLDPRNAM],[FLDPCTUR]) SELECT [FLDPRNAM],[FLDPCTUR] FROM [Source].DBO.AF40109

--INSERT INTO [Destination].DBO.AF40110 ([USERNAME],[SHGRDFLG],[SHCGRFLG],[SHTBARFL],[SCDEFAFL],[SHRWARFL],[SHOFMKFL],[SNPTGRFL],[SHMARFLG],[SHPGBDFL],[SHRLRSFL] ) SELECT [USERNAME],[SHGRDFLG],[SHCGRFLG],[SHTBARFL],[SCDEFAFL],[SHRWARFL],[SHOFMKFL],[SNPTGRFL],[SHMARFLG],[SHPGBDFL],[SHRLRSFL]  FROM [Source].DBO.AF40110

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

What do you expect from Dynamics GP 2013??

 

I been looking around the blogosphere an noticed an awesome article by Frank Hamelly simply summarizing new features of Dynamics GP 2013, I am sure that you are very much excited to check what that could be therefore I have copied them all, sorry Frank for the infringe of your copyrights! but I wanted to bold features that is useful for customers in my region, blow is the list:

• Excel Export Using Open XML
• Enable printing of SSRS report from forms
• Navigation Lists for Web Client (BA Functionality)
• Re-architect home pages to WPF
• New SSRS reports to support Web Client templated forms
• Business Analyzer WPF upgrade
• Additional Word Templates for SOP
• Single Email with multiple attachments
• SRS Reports
• Print Customer Items option
• Select invoice on one-off Email window
• Notes on Credit Documents
• Document Attach
• Item Standard Cost adjustment post to GL
• Multi-S/N select
• Inactivate a Site and Item Site
• Reason Codes for stock movements/adjustments
• Inactive Items - Inventory Status enhancements
• Bin Transfer History
• Tolerance Handling
• Track Serial Numbers for Drop Ship items
• Allow prepayments on a PO
• Prevent PO close prior to Invoice Match
• Print Button on Pick List Shortage Inquiry
• Ship to Address has different Company Name
• Relate / Link Items for suggestions on Sales
• Integrate AA with Copier Series SOP/POP and GL Copy
• Add Field Service Allocations to Item Allocation Inquiry Window
• Update Back Order Quantity in Item Maintenance
• RMA Entry - Update Contact & Address ID
• Consolidate Revenue Recognition
• Item Description from RMA Credit
• Multi-User check in RMA Entry
• Equipment Super Session
• Hotline Update
• RMA Return Quantity and Type
• Ship to Address Integration to SOP
• Cross Ship RMA consolidation
• Honor Discontinued Item Status
• Auto Populate Customer ID
• Add a Contract Number search in Revenue recognition window
• Allow contract line to be moved to another contract even if on an open service call
• Option to have an equipment card created when manufacturing receives a serialized item into inventory
• Contract Workflow
• Add BAI2 format support to eReconcile
• FA Calendar Setup
• FA Intercompany Asset Transfer
• FA Historical Depreciation Report
• Lock Current FA Year and Mass Backout
• FA to GL posting process updates
• FA Updates for MACRS
• Integrate Fixed Assets with AA
• GL Journal Entry Inquiry for History
• Reconcile Bank Reconciliation with GL
• Year End Close Options
• Subledger Reconcile IV to GL
• Calculate Average Exchange Rate for Currency Translation
• Integrate GL with encumbrance
• Enable Enc to assign Multiple GL Budgets for Fiscal Year
• Encumbrance Cost Diff Notification
• Restrict Void of reconciled check, display reconcile status in Check Inquiry
• Void of check returns applied Credits
• Void credit card payment vendor invoice - void to original vendor
• EFT add settlement date option
• EFT Field Length and Delimitation Options
• Edit Payables Transactions: Remit to Address, 1099 information, Edit Description
• PM Reprint Checks/Remittance forms
• Need a 1096 Form
• Allow renaming of user-defined fields on Customer Address
• Multicurrency apply in cash entry
• Database multi-tenancy (named system database)
• Application multi-tenancy
• Report deployment and retrieval for multi-tenant
• Multi-tenant Applications - tenant service
• Multi-tenant Integration Manager
• Multi-tenant web service
• Life Insurance enhancements
• Check Build Exception Report
• Stop Duplicate Checks
• Paycode History Edit
• Paycode, Deduction, Benefit Modifier
• FICA TSA Deductions
• Customer Fields
• Advanced Warning Notice and Delete History Records
• Mandatory Arrears Template and Reminders
• DIA Reporting
• Voucher Description and Voucher Document Number
• Voucher Consolidation
• HR Calendar
• Negative Carryover
• PTO Detail and History
• PTO Manual Checks
• Year End PTO
• Office Presence in Advanced HR, Advanced Payroll, Payroll Extensions & PTO Manager
• HR Requisition List View
• Consolidated Invoicing
• Additional String Resources (Dex)
• Key Relations tracked in index file for source control(Dex)
• Update VBA Components
• Enhance the AltMod windows with a mark all/unmark all functionality
• 64 Bit Support for Email (Dex)
• Default sort for Customer and Vendor lookups

• Email Indicators in Transaction lists

• Save batch approval information

• Select Printer at time of print AND Support for http:// on the Report Destination window

• Adding support for the Business Analyzer to NLB

• Grouped Reports

• SmartList Builder - remove dependency on having an Admin login after new install

• Extender Window export/import to include Smartlist integrations

• Add Extender & SmartList Builder Default Security

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Microsoft Dynamics GP Excel Paste is now Online

 

Production version of Microsoft Dynamics GP Excel Paste is now released and the development team has updated the website to integrate PayPal!

Download Fabrikam copy now or purchase your company version now from the below link:

http://www.dynamicsinnovations.com/GPExcelPaste.aspx

Below is the pricing table for the tool:

Core Financial Modules (GL, CM, DTA)	350$
Accounts Payables and Receivables (AR, AP)	300$
Sales Order Processing (SOP)	400$
Purchase Order Processing (POP, POR, POE)	400$
Inventory Control (IV, IVC)	300$
Payroll (UPR)	300$
Full Package – All Modules	1,300 $

Enjoy!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Excel Paste for “Any Screen” in Microsoft Dynamics GP

 

Last few days, we have released a tool to paste data from Excel into Dynamics GP GL Transaction Entry form and published the tool for all users with no charge.

From that day, the development team in DI has decided to go further with this and tried to enhance the dynamicity of the tool to go beyond what we have developed and develop a tool for sale but worth payment, so end users can define the paste path and how to paste columns in Dynamics GP.

In addition they wanted to make this for all GP forms not only GL Transaction Entry, and going from there below what we have developed.

If you would like to watch this live on what we have done using excel paste for forms with scrolling windows, please take a look into the following video:

Purchase Order Excel Paste

So what if you do not have scrolling window and you want to paste from excel into “Cards”? We have included this we well! Check below video on how to paste customers:

Customer Card Excel Paste

Initially, users will need to analyze what fields need to be included in the paste operation, for example let us take the purchase order screen as an example:

I have picked the integration path that I need on the screen and been ready to setup my Excel Paste using the following setup screen:

End user will be able to define what they just analyzed using the above screen, they just select Form Name, Window Name and start selecting fields one by one.

Once finalizing the setup, copy data from an excel like the below:

And just paste on the purchase order screen:

Enjoy!!!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com