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

 

Or, how to solve the error The year you've selected for an open-year column is invalid for Profit and Loss

<smiles>

This article will detail how to change the year on the Dynamics GP Profit and Loss report. The report hasn't changed in years, I imagine this technique will work on most any GP version. 

Details below.  

https://www.purestorage.com/ 

The company that I spend most of my time at just installed a SAN made up of the Pure storage solutions. Flash drives. 

7tb of flash drives

Here's the deal. Pay attention: 200gb of SQL databases that take an 1.5 hours to back up went to

..

..

..

7 minutes. 

Dude. 

 

This is going to be the longest post on this site... but it's a really great piece of code. 

This is SQL code that reads a JSON document and imports an order into Dynamics GP. All of this is done in SQL, no .NET code involved. 

This iteration of the code handles the order and lines, as well as taxes and payments. I'd call this a 'typical web order'. The taxes are provided by the web site at the header level only, and the orders are always completely paid. 

Below you'll find a series of scripts needed to make this work. Here's the overview:

A script that contains the sample JSON (remember that each implementation will have a different JSON format, you'll have to adjust for that. If you need help, just ask)

The main script, called Shopify_SOPOrder_Integration (because this happens to be a Shopify integration)

4 'wrapper' scripts that handle the calls to eConnect

4 scripts that define the 4 tables that we use as staging

Too much code to read through, really, but just remember where it is when you need it. 

Leave a comment?

Grrr. 

I've used the SonicWall Global VPN client for years, it's always worked well. After moving and setting up blinding fast new Spectrum cable service, it won't connect on either computer. 

But my AT&T mobile hot spot connects fine. 

I'm not technical, and this is not a networking site, but I wanted to let anyone that is searching on this error that I got it working by right clicking on the client and choosing 'run as administrator'. 

Note that it has to be completely shut down. Go to the system try, right click and 'exit', then do it. 

 

This is a line of code that you can use to hide duplicate fields in a report. 

Put another way: If you have a report that repeats a field (like a name or address) several times in a row, this formula will hide all but the first

 

Hi All,

I am trying to integrate Bank Transaction Increase and Decrease adjustments using eConnect. I couldn't find any procedure to get next transaction number. How to get GP next number and pass in column CMTrxNum for eConnect procedure taBRBankTransactionHeader.

Thanks,

Shyam.

Hi,

Someone changed the server role to sysadmin for an regular employee. Is there a way to figure out who made this change?

Thanks

Paul

I've built a stored procedure to create and update inventory items using eConnect, for use in another application. It's working pretty well for the most part, but the total price in the price list is not populating. When I execute taIVCreateItemPriceListLine, the margin percentage shows up on the item price list card, but the total price for the line is still zero. Normally, this field auto-populates after you enter a percentage on the card or import a price list. I'm using option 5, which is %Margin. Is there another field I'm supposed to be populating, or some other way to trigger this?

We use GP Sales Order Fulfilment.

The SOP10112 table is pretty simple - it stores a record for the progression of order fulfilment status.

SOPNUMBE SOPTYPE ORD Effective_Date TIME1 SOPSTATUS USERID DEX_ROW_ID
FULFIL100148          3 16384 2020-08-10 00:00:00.000 1900-01-01 17:56:16.000 2 CHALLENSTEIN    976
FULFIL100148          3 32768 2020-08-12 00:00:00.000 1900-01-01 15:27:22.000 3 CWALKINGTON    1127
FULFIL100148          3 49152 2020-08-12 00:00:00.000 1900-01-01 17:07:00.000 4 ABECERRA        1143
FULFIL100148          3 65536 2020-08-13 00:00:00.000 1900-01-01 13:55:47.000 6 CWALKINGTON    1231
FULFIL100148          3 81920 2020-08-29 00:00:00.000 1900-01-01 11:50:02.000 4 BTAMULIS        3501
FULFIL100148          3 98304 2020-09-02 00:00:00.000 1900-01-01 08:16:52.000 6 JANDERSON      3709
FULFIL100148          3 114688 2020-09-08 00:00:00.000 1900-01-01 16:59:31.000 7 ACARDONA        4069

I have a SQL query that I am struggling with to assure I return only one record with the earliest date for a particular status.

If you notice this record (FULFIL100148) has two records with Status = '6'. 

I need to grab the earliest dated record. 

My query doesn't return MIN as I expected........

(SELECT SOPNUMBE, MIN(CAST(Effective_Date as Date)) as PackedDate from FIRE..SOP10112
where SOPSTATUS ='6'
GROUP BY SOPNUMBE, Effective_Date
)

I thought the MIN would return earliest date but it doesn't......in other words it does nothing......

It returns:

SOPNUMBE PackedDate
FULFIL100148          2020-08-13
FULFIL100148          2020-09-02

Any guidance or suggestions would be appreciated. 







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