This is a short piece of code that shows how to do data access in Dynamics GP

In this code we use Pass Through SQL (a short query statement) and there are no parameters

It turns out that when you delete data in Dynamics GP that is tied to Extender data, the Extender data does not automatically delete. In order to get it to delete, you need to link Extender to the underlying table. 

Not horrible, but you need to be aware of that in case you're reporting off the Extender data.

This was just a little bit tricky, I didn't think the table naming was all that obvious... so here's the process

I got a support call from a friend yesterday, he wanted to create a report that could be run against any company using a 'company' parameter. 

I didn't know how to do it, but he emailed this morning with the technique that he used, and I'm sharing. 


This is a working POPTransactionType with Taxes, note that we're 'USINGHEADERLEVELTAXES'

We've never dealt with the SQL Server compression feature professionally, but we got a call yesterday from a consultant that noted that a customer had accidentally turned on compression in a SQL Express server while installing a service pack. At least that's what the customer stated.


We didn't even know that compression in SQL Express was possible, or that a service pack would behave like that. 

None the less, the consult found the script below and ran it against the server, and it corrected the problem. 

Use this with the greatest of care. Back everything up. Test, test, test. 

User btamulis sent in this query and we thought it worth keeping. It provides a list of user/companies, and the roles that they're assigned to.


This is T-SQL code that will allow the Dynamics GP eConnect taIVTransactionType. We provide a set of staging tables and provide the calls and error handling for eConnect. 

I see from Googling that you can change the due date for a receivables document by using the "Edit Transaction Information"  under the Sales tab.  However, it won't let you change the assigned terms and I'm wondering if there is an issue if I do it via SQL update?

I changed some due dates in a test company and then ran the aging update, and the due date seems to remain constant.  However,  I'd rather update the payment terms for the transactions so that when people look at the records the doc date and due date match what would be calculated based on the terms.

Is there any reason I should not update the terms along with the due date for receivables?


Hola, buen día

Estoy teniendo problemas con master number duplicados en las tablas SOP1010 Y SOP30200.

Una pagina que encontré en internet me sugiere revisar la tabal MSTRNMBRWORK, pero la misma no responde a un SELECT por este nombre.

Alguien conocerá el nombre Físico de esta Tabla?



EDITOR 10/7 

I'm having problems with duplicate master numbers in the SOP1010 and SOP30200 tables.
A page I found on the internet suggests me to check the MSTRNMBRWORK tab, but it does not respond to a SELECT by this name.
Will anyone know the Physical name of this Table?

Buen dia a todos,

Tengo una ventana en GP 10.0 que se llama CM_Checkbook_Balance sin embargo en GP 2018 no encontramos esta ventana en el modulo finanzas.

¿ Alguien podria darme orientacion porque no existe la ventana en GP 2018?  Adjunto imagen.

Gracias de antemano.

Editor 10/8

I have a window in GP 10.0 called CM_Checkbook_Balance however in GP 2018 we did not find this window in the finance module.
Could someone give me guidance because there is no window in GP 2018?  Attached image.


Greetings again, the question about the checkbook balance window, arises because it is not in GP 2018 and I must assume that it was created by a development that was in GP 10.0 that we had before, but I need to confirm it. In true case, if anyone knows a way to check the checkbook balance, I appreciate your suggestion. Thanks

Necesito hacer  INSERT AND UPDATE en las tablas de EXTENDER desde codigo vba, pero me rechaza todos los intentos.

Existiran procedimientos almacenados nativos para estas operaciones? si los conocen les agradesco la información.


Editor 10/8

I need to do INSERT AND UPDATE in the EXTENDER tables from vba code, but it rejects all attempts.
Will there be native stored procedures for these operations? if you know them I like the information.




El url siguiente debería ser el correcto para llamar a una vista desde GO TO SmartList.

sin embargo la que se genera es así:

Que sugerencia me dan para resolver y poder llamar el reporte desde un SMartList?


Edit 10/12/2021

The following url should be the correct one to call a view from GO TO SmartList.

however, the one that is generated is as follows: UDKEY=PWEB-21000456

What suggestion do you give me to solve and be able to call the report from a SMartList?


Okay - I would like a simple 'report' which shows one record for every GP user/Company combination - however I need to include a longer stringer of the first 7 or 8 Security Roles.

This SQL script has one row for every user/company/role.

['User Master'].USERID AS 'UserID'
,['User Master'].USERNAME AS 'Username'
----,['User Master'].USRCLASS AS 'UserClass'
----,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
,ISNULL(['Company Master'].INTERID, '') AS 'CompanyID'
,ISNULL(['Company Master'].CMPNYNAM, '') AS 'CompanyName'
,left(ISNULL(['Security Assignment User Role'].SECURITYROLEID,  ''), 11) AS 'SecurityRoleID'
,case when ['Security Assignment User Role'].SECURITYROLEID='POWERUSER' then 'Yes' else 'No' end as PowerUser
-----,left(ISNULL(['Security Roles Master'].SECURITYROLENAME, ''), 11) AS 'SecurityRoleName'
SY01400 AS ['User Master']
SY40400 AS ['Class Master']
ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
SY60100 AS ['User-Company Access']
ON ['User-Company Access'].USERID = ['User Master'].USERID
SY10500 AS ['Security Assignment User Role']
ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
SY09100 AS ['Security Roles Master']
ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
SY01500 AS ['Company Master']
ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID

It returns something like this: (desired output would be 4 rows - one for each user/company with one additional column 'PrimaryUse')