DynDeveloper.com

DynDeveloper.com
Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now! Log In
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

Archives

 
Calculate Business Days

Today I'm being asked to do a certain action if an order's RequestedShipDate is more than x business days out. 

Below is a simple, quick piece of logic that will do that for you
GET Invoices

The code below is a complete working example of an API call to Fullbay to query invoices. 

Notes:

It requires an SHA1 hash, we use the Kellerman suite to do that. 
STRING_AGG SQL 2017 (14) or later

New SQL features are hard to use because it's years before I get customers that have the new version of SQL

But this feature came out in SQL 2017, and it's becoming available.

 

STRING_AGG will turn this

Into this

 
Get DataKeyValues in the Group

Short code example that shows how to get DataKeyValues in group events

 
Retrieve JSON from disc and convert to a searchable Dictionary object

I'm a SQL guy, normally I would not be found more than 5 feet from a SQL Server. 

But Business Central (BC) and D365 is coming to power, and that has me coding ... without SQL. 

Today's task is part of an integration, I've downloaded a file that needs to get integrated into BC, but several of the fields need to be translated

This code will take this JSON:

{
  "status": "SUCCESS",
  "shop": [
    {
      "shopname": "Store #1 - 3330",
      "shopcode": "3330",
      "taxareacode": "WILSON, NC",
      "invoiceprefix": "WIL-"
    },
    {
      "shopname": "Store #2 - 3310",
      "shopcode": "3310",
      "taxareacode": "GOLDSBORO, NC",
      "invoiceprefix": "GOL-"
    }
  ]
}

and import it into a dictionary object so that it can be searched during the import. 

Restated: The raw data will have "Store #1 - 3330', we need to use the 'shopcode' during the integration so we'll look it up. 

 

 
SQL Row Number for Grouping

Okay - I have been playing with Row Number in SQL for a while and can't seem to get results I desire.

I have this query:

select 
Type,
TypeID,
InvoiceDate,
LeadDate,
Account,
CustomerID,
ProviderID,
LeadID,
Name,
Fee,
ROW_NUMBER() OVER (
                     PARTITION BY [Account],[CustomerID], [InvoiceDate],[TYPE],[TypeID] 
                     ORDER BY [Account],[CustomerID] DESC
             ) AS [ROWNUMBER]
from APFM_SOP_STAGING2

It returns this:

What I need is the first 13 records (1764/44527) combination to be Row Number = '1' then the last two records (1899/44528) combination to be Row number='2'

Basically I am trying to use row number partition to 'group' data - which I don't think it was designed to do.....

Any assistance or guidance would be appreciated. Thank you in advance.
The Tax Type parameter is invalid: 1=Not a 1099 Vendor; 2=Dividend; 3=Interest; 4=Misc.

Error: The Tax Type parameter is invalid: 1=Not a 1099 Vendor; 2=Dividend; 3=Interest; 4=Misc.

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
Archives | Table Listing | eConnect Error Database | eConnect Schema | Gold Star Locksmith
3