Posts

Top 10 Dynamics GP Tables

  Top 10 Dynamics GP Tables Every Consultant Should Know by Heart If you live in Dynamics GP long enough, you realize success often comes down to knowing where the data lives . Below are the ten tables I reach for constantly—what they store, how to join them, and a bite-size query you can use right away. All samples are read-only and use the Fabrikam database code TWO ; swap it for your company DB. Quick Reference (what & why) SY00500 — Batches Tracks batches across modules; crucial when something’s “stuck” or unposted. GL20000 — General Ledger Open Un-year-closed GL detail (current year). Your best friend for recon. GL30000 — General Ledger History Prior years’ GL detail. Same shape as GL20000, different period. PM20000 — Payables Open Vendor transactions not yet fully applied/closed. PM30200 — Payables History Closed/fully applied vendor transactions. RM20101 — Receivables Open Customer transactions in open status. RM30101 — Receivables History ...
Post a Comment
Read more

🗂 Understanding Dynamics GP Table Structures

  Company Data : Each company has its own database (e.g., TWO , Fabrikam , or your company code). Common Table Prefixes : GL → General Ledger PM → Payables Management RM → Receivables Management SOP → Sales Order Processing POP → Purchase Order Processing Series Breakdown : 10000 → Financial 20000 → Sales 30000 → Purchasing 40000 → Inventory Knowing the right prefix saves hours when searching for tables in SQL. 🛠️ Useful Queries You Can Use Today Here are some read-only queries (safe for reporting, won’t update data). Replace TWO with your company database. 1. Find Unposted Batches SELECT BACHNUMB, SERIES, BACHNAME, USERID, BCHSOURC FROM TWO..SY00500 WHERE BCHSTTUS <> 0 ORDER BY SERIES, BACHNUMB; 👉 Great for identifying stuck batches across modules. 2. Reconcile Payables Transactions to GL SELECT PM.VCHRNMBR, PM.DOCDATE, PM.DOCAMNT, GL.DEBITAMT, GL.CRDTAMNT FROM TWO..PM20000 PM LEFT JOIN TWO..GL20000 GL...
Post a Comment
Read more

Microsoft Dynamics GP 18.5 release and what's next?

  In  #Dynamics  GP general session at  #MySummitNA , Microsoft’s Terry Heley detailed these new features in October’s GP 18.5 release: + account category lookup options + warnings and tools to manage inactive vendor address records + ability to print 1099-NEC forms + new cash receipts option in transaction entries + workflow option for "no approvals needed" In addition,  #microsoft  VP Michael Morton said 18.5 will be the last release with major new features, but the software will continue to receive regulatory/tax and security updates and the company remains committed to GP. New features and investments will primarily target  #businesscentral .
Post a Comment
Read more

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.
1 comment
Read more

Excel Refreshable reports

Image
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,...
Post a Comment
Read more

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....
Post a Comment
Read more

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,
Post a Comment
Read more