Delete a Sales order processing document after being printed

You cant see the delete button anymore after printing the batch or the transaction even though it is just saved and not yet posted, so this is a simple query to switch the printing status of the batch or the document to initial status before printing.

update SOP10100
set TIMESPRT = 0
 where SOPNUMBE='<insert the SOP document>'

After executing the query the delete button will appear in the action drop down list.

Excel Refreshable reports

Setting up and Deploying Excel Refreshable reports

1- deploy the excel reports from  Administration> Setup> System> Reporting Tools Setup.

- press on the excel reports tab

- Choose Network share ( if on folder in shared network ) or choose SharePoint (online or on-premise)

- if Network share GP will need a location for the extracted or published excel sheet ( that's supposed to be the shared folder)

for example as a Report Server URL:

\\<servername>\Reports

- Another path will be needed  In the User Level area, enter the location to where Excel reports are stored on each user’s computer.

You must use the “%” character as the variable for the Windows user ID in the path.

For example: C:\Documents and Settings\%\My Documents\My Data Sources\GP Connections

- in the white box below you will find the companies you have in the Dynamics GP,  all items that are not fully deployed are automatically marked.

To exclude an item, unmark the check box. For example, if you don’t want to deploy Excel reports in Fabrikam, Inc., expand the Company level in the tree view and unmark Excel Reports.

- check the last two tabs and then click on Deploy Reports

2- go to smart list and then click on your report and click on Publish.

if you go to the Location you entered in the setup you will find the smartlist report published. just wait for it to load and refresh as it may take time regarding how big the report is.

Best Regards,

Account Transactions Report with Manual Payments applied to Purchase Invoices

In this Post i'm sharing a view regarding the Smartlist report of Account Transaction as i linked this view with another tables to get the Manual Payments that are applied to purchase invoices.
this view contains the work and history tables.
you will find the invoice number, date and the applied amount.



USE [Company_NAME]
GO

/****** Object:  View [dbo].[_AccountTransactionsVSPayementsAndInvoices_AK]    Script Date: 5/21/2018 10:45:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO




CREATE VIEW [dbo].[_AccountTransactionsVSPayementsAndInvoices_AK] AS

select AccountTransactions.* ,  PM20100.APTVCHNM , PM20100.DOCDATE , PM20100.APTODCNM , PM20100.APPLDAMT , PM20100.APTODCDT
from AccountTransactions as AccountTransactions left join PM20100 as PM20100
on AccountTransactions.[Originating Control Number] = PM20100.VCHRNMBR
--where AccountTransactions.[Originating Control Number]='00000000000001094'
union all
select AccountTransactions.* ,  PM30300.APTVCHNM , PM30300.DOCDATE , PM30300.APTODCNM , PM30300.APPLDAMT , PM30300.APTODCDT
from AccountTransactions as AccountTransactions left join PM30300 as PM30300
on AccountTransactions.[Originating Control Number] = PM30300.VCHRNMBR
--where AccountTransactions.[Originating Control Number]='00000000000002380'




GO

Best Regards,

Upgrade Dynamics GP 2013 R2 to Dynamics GP 2016 R2

Upgrading the Dynamics GP 2013 R2 has been a challenge for most people so in this post i'm going to set a clear path for upgrading the Dynamics GP 2013 R2 to the 2016 R2

• If you got Dynamics GP 2013 R2 with a version like 12.00.1218 you will need to upgrade it to the last version of the GP 2013 R2 which is Version 12.00.2230 ( a hot fix released on January) you can download it from here.
• Install GP 2016 R2 version 16.00.0675
• Open utilities and upgrade the database to the GP 2016 R2 

This simple post will walk you through the upgrade with no problem related to the GP versions.

If you want to know more about the upgrade and why anyone should upgrade please follow this Blog post by Sara Prudy  stating everything with the upgrade.

Regards,

Purchasing Fixed Assets through Purchase order processing

Purchasing Fixed Assets through Purchase order processing

Use purchase order processing to purchase fixed assets

To enter a standard purchase order:

1.    In the navigation pane, choose the Purchasing button, and then choose the Purchase Order Transactions list.

2.    In the New group or its overflow menu, choose Standard Purchase Order to open the Purchase Order Entry window.

3.    Enter a purchase order number or accept the default entry.

4.    Enter or select a buyer ID.

5.    Accept the default date or enter a date that will be used to update your purchasing records.

6.    In multicurrency transactions, the exchange rate used is based on the document date, the currency ID, and associated rate type that’s entered for the transaction.

7.    Choose the Date expansion button to open the Purchasing Date Entry window, where you can enter a contract expiration date for the purchase order. Choose OK to return to the Purchase Order Entry window.

8.    Enter or select the vendor that you’re ordering the item from.

9.    Enter or select a currency ID, or change the default currency ID.

10.    If the currency ID is not the company’s functional currency, a rate type and associated exchange rate table is assigned to the transaction.

11.    Enter or select the number of the item you’re purchasing.

1. Enter the item quantity.
2. If you’ve entered a non-inventoried item, enter the unit cost. If you’ve entered an inventoried item, you can modify the default unit cost.
3. Enter a site ID, or accept the default site.

Sites are required for line items. You must enter a site ID before continuing to the next line. To mark the item as a capital item select the arrow highlighted to the right of the line item column labeled “item.” This will open the Receiving Item Detail Entry window.

    6. Mark the capital Item checkbox

7. Continue to enter all the line items for the purchase order.

8. Continue entering all the Purchase order data

9. Save the purchase order or Transfer to be received or receive and invoice.

If you’re buying multiple assets and need each one with a different serial number in the Fixed assets module 

select the arrow highlighted to the right of the line item column labeled “item.” This will open the Receiving Item Detail Entry window.

.    Click on the blue arrow of the capital item as shown below

.    mark on create Multiple fixed assets 

Note: you can define all the asset info from the below part "additional fixed assets information" and if you are using Dynamics GP 2013 R2 and later you can also add the class ID next serial number to the asset.

After posting the purchase order go to the fixed assets

Use the fixed assets general card to add new purchased assets to the fixed assets module

Financial >> Cards >> Fixed Assets >> General

A new drop down list will appear after purchasing a capital item called purchases as shown below

. From the drop down list in the purchases select the asset that you want to place in the fixed assets module

Note: if a mistake is done while choosing the item from the drop down list click on Clear to clear all entered data and return the chosen asset to the drop down list again

 . Fill all data of the asset and save

Regards,

Creating Macro in Dynamics GP

Creating Macro in Microsoft Dynamics GP

1. Open which screen you would to insert data and don’t insert any char [Example: HR-

--> Position]

2. Start Recording the Macro

3. Name your Macro

4. Start insertion of the data

5- Stop Macro Recording

2- Fill the Macro file with all required data for upload.

a- Open Macro created file with Microsoft Word

b- From Mailing Tab --- Select Recipients --> Use Existing List

c-Select Data Source [Pre- prepared File .XLS contains all required Data for each record]

d- if the excel sheet contains multiple sheets, select which tab contains the data

e- the Macro file code will be like below:

f- Highlight each screen filed and map it with data file sheet column form

g- Make the previous step for each filed of the screen.

h- At the End choose merge all form

Note: - the main Macro file pages will increased by number of XLs file data records.

i- Finally save that file as .TXT file the replace file extinction from .txt ---> .mac

3- Play the prepared Macro file to insert all data required.

a- Open Dynamics GP and close all windows

b- Open which screen you would to insert data and don’t insert any char [Example: HR-

--> Position]

d- Start Playing the Macro

The Macro will run with all the data inserted in the excel sheet till it finishes 

Kindly Regards,