Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In

Archives

 

We recently had a requirement to report on the running sum of a certain GL account (it was the cash account)

We needed the BBF entry, and then a running sum of all the periods after that. Problem is, you don't know where to look for the BBF. In January it might be in history or open. Also, the periods that we need to report from might also be in history or open. 

The code below handles all of that. It can easily be expanded to return more accounts, and restricted to fewer months. 

We're doing a manual apply (inserting into PM10200) for two documents, and I was asked to write code to move the two documents and the apply line to history. 

We're assuming that the documents are 'fully applied', normally this code should make that check but in the interests of time we've omitted that step. 

 

Code sample for using the CellFormatting event in the Telerik Winforms RadGrid events
Code sample for coding the Telerik Winforms RadGrid CommandCellClick

SSRS does not handle the 'divide by zero' issue as gracefully as other environments. Assuming

a / b

you can say "iif(b=0,0,a/b)" and that'll do it. But SSRS returns an error for this, anyway. 

Solution below.

Sample code to easily call the eConnect taSopLineDelete stored procedure for Dynamics GP

Recently, I had the opportunity to help with a query that was looking at a CRM and ERP database, trying to compare customers and addresses to try and make them the same. 

I've taken that work and created a simple lesson on JOINs and LEFT JOINs

Enjoy

Microsoft has announced that VBA will not be available for Server 2019 and later. 

If you have any VBA running... you'll need to port it so something else. I'd use VS Tools for most applications. 

Microsoft Dynamics GP and VBA: Future Considerations - Microsoft Dynamics GP Community

Edit 3/16/2021 GP Guru David Musgrave adds his thoughts:

Using GP Power Tools is a simple and powerful solution. Much easier than VSTools. See:

https://winthropdc.wordpress.com/2019/08/12/gppt-replacing-vba-with-gp-power-tools-developer-tools/ 

This is a starter query to be use in creating an Olympic (Rockton) Project Cost Budget vs Actual report

 

Using Dynamics GP (any version) on Server 2019, you get this error: 

Attempted to read or write protected memory, This is often an indication that other memory is corrupt

BTW, I think that this is likely to happen with Office products, too. The solution is below. 

Edit 2/25/2021 when you open a form in Dynamics GP that has VBA behind it, GP unceremoniously shuts down. Blink. 

 

 

***never use any script found on this site without testing. All scripts are provided as-is, and without warranty of any kind.

 

I currently manage 54 production GP databases, and if I have to do something manually more than once or twice, I figure there has to be a way to automate it.

Our fiscal periods are plain vanilla - 12 monthly periods beginning on the first day of the month, however our GP users are all over the globe, so new periods must be opened automatically to 'reduce the noise'.

Attached are 3 scripts:

  1. usp_OpenCurrentPeriod - opens all 6 standard GP modules (modify as needed - notes are in the script) for the current calendar month. I created a SQL job that runs a few minutes after midnight UTC time on the first of the month to handle this for many of our companies;
  2. usp_OpenCurrentPeriodGLOnly - opens only the GP GL module (again - modify as needed - notes are in the script) for the current calendar month. This script is used for companies that only handle consolidation or other GL entries - this is also part of the SQL job noted above;
  3. usp_ClosePreviousPeriod - CLOSES all 6 standard GP modules (modify as needed - notes are in the script) for the previous calendar month. I do NOT use a SQL job for this as not all GP companies close at the same time. As written the script looks for the start date of the last calendar month and closes that period - so if I ran it today, it would close all modules for the period that started 1 Jan 2021.

A few other comments:

In my WHERE clause I am converting GETDATE() to a date format otherwise GP won't recognize it (you can also use a varchar(10)). 

The table I'm updating is [companydb].dbo.SY40100 - this contains a combination of 'header' records (one for each period) WHERE SERIES = 0. I'm setting the PSERIES_1 through PSERIES_6 to 0 to open the period for each module, and to close the period I set it to a 1.

Because I'm manually setting the header record, I also set the underlying 'detail' records, which represent the items you see in the 'Mass Close' window from the Fiscal Periods window. In 25 years working with GP I've never seen anyone use this window, but we should still clean up after ourselves... These series have a different value that the PSERIES!

-- Header records (These are columns)
PSERIES_1 = Financial

PSERIES_2 = Sales
PSERIES_3 = Purchasing
PSERIES_4 = Inventory
PSERIES_5 = Payroll
PSERIES_6 = Project

-- SERIES values - for open/close by window
(There is no series 1)
Series - 2 = Financial
Series - 3 = Sales
Series - 4 = Purchasing
Series - 5 = Inventory
Series - 6 = Payroll
No Project windows available in this column

I am using this in two different environments (GP 2016 R2 and GP 2013) as we still have one company to migrate to GP 2016. In a couple of months we'll start testing the current version of GP for a late summer upgrade...

Lastly, there are plenty of ways to calculate dates in SQL - hopefully your period begin dates are either static like a constant day of the month.

Please please please test this this for your environment!

Greetings -

I need to add the Current Promise Date from PO line Item Entry to the GP Standard Report - PO Blank

I was surprised that the report in report writer does not have that field that I can simply drag onto the report. Looks like since the PO is typically printed during the PO entry that report is using temp tables not the POP10110 Table.

Any suggestions on how to maybe create a calculated field or use VBA perhaps?

Thanks in advance.......

Hi

One of my associates were having issues entering the username, email ID & password while processing and emailing the EFT remittances to vendors so I stepped up and used mine, which was successful but the email went out with my name on it.

Can someone tell me how to fix it so that the future emails go out with her name on it? GP 2013 SP2 and SMTP server.

Much appreciated

Paul

selected 'use source field'  in integration mapping for posting date

selected the appropriate field from the source file

verified the data had a post date

run integration and new batch is created with post date being the system date, not the date in the source file

Could this be a system setup item that I am missing?

*Using GP 2016

Saludos, adjunto una imagen en que muestro la transacción 300989 en negro, por un monto de 199.02 y a la misma se le aplico un pago por 192.51 el cual fue OK. También se hizo una nota de crédito por 6.51 que genero un error por lo fue necesario borrarla por query sql.

El problema radica en que la factura aparece cancelada cuando debe mostrar un saldo pendiente de 6.51 y la pregunta es ¿Dónde encuentro la factura para modificar su monto pendiente u otra recomendación?







Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
site