DynDeveloper.com

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.  

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?

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,

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

Thanks

Paul

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.