DynDeveloper.com - Your Dynamics GP Questions, Answered

	SOP Scripts
	eConnect
	taSopDeleteDocumentWrapper
	taSopLineDeleteWrapper
	taSOPLineIvcInsert Wrapper
	taSopLineIvcInsertComponent
	taSOPLotAuto - complete procedure
	Functions
	ddf_Address
	ddf_AllHoldsOnASOPDoc
	General
	Getting Email addresses from Dynamics GP
	Move a SOP transaction from open to history
	Posting Sequences
	SOP40200 'Allow' fields query
	sopGetIDNumber
	sopTrxDeleteWork
	Unvoid a SOP document
	Stored Procedures
	dd_SOP10102_UPD
	dd_SOPGetNextDocNumber/ sopGetIDNumber.
	dd_SOPPayment_INS
	dd_SOPRound - Round a SOP Multicurrency document to two decimal places
	dd_SOPTotal
	dd_SOPTotalTaxes
	dd_taSopVoidDocumentWrapper

Archives

Single Click Behavior

I had an issue with the Telerik ASP.NET RadButton yesterday, I have a button on a web form and the process behind the button is several seconds, and users were clicking on it multiple times.

I searched the web for answers and mostly found JavaScript solutions, the best seemed to be putting

OnClientClicked="this.disabled=true;

in the markup. This worked, but for some odd reason it keeps the AJAX Autopost from working.

I submitted a support case, and Telerik reminded me that...

VS Tools for Dynamics GP and Visual Studio 2022 - Please Read

I just did my first install of Visual Studio 2022 while upgrading a VS Tools project on a new server

VS 2022 wouldn't load my code.

Allowing a series to resume posting

I'm a huge fan of Postmaster from Envisage software.

Post Master | Envisage (envisagesoftware.com)

I have a snippet that I use to fine tune the posting process, code below

Responsive CSS Site Framework using display:grid

TLDR: How to code a web site that looks like this

And will resize down to this.

Try the demo CSS code here.

I am, in my heart of hearts, a database developer. If you ask me what I do, I code SQL.

But I'm also insatiably curious, I want to know how the things around me work. So, I know some networking. I know web server management, SQL management.

And I'm learning CSS.

As a part of my quest, I wrote this site (www.dyndeveloper.net) and the business site (www.4penny.net), by hand (in other words, I didn't use WordPress, or some framework like that).

And I used TABLEs. The whole site was tables inside of tables inside of tables. Because 10 years ago, that's how it was done. Three major things have happened to cause us to move away from that: The death of IE, cell phones, and the rise of the CSS display:grid. Any site that you write in today's world MUST display on a cell phone. So, how can you do that? (stop for a second and resize this web site from full screen to as narrow as it will go, slowly)

This article is a simple(ish) exampe of how to code a web site using display:grid so that it will be RESPONSIVE. That means that it will work on the largest and smallest displays at the same time. Important word, remember that. There will be a buzz word test at the end.

Two: I need to know if you care about CSS content. Should I blog on it? Here, we're GP developers, but I can tell you that in 20 years, I've coded a number of internal sites that attach to GP. A major university, a large plant, a large distributer all use sites that I've built. It's a good skill to know. Want to learn more?

Please... if you've read this far, leave me a comment below.

All the code is in the article below.

Updating the DECPLCUR (Decimal Place Currency) in Dynamics GP

First off... this is highly unsupported. You should not be doing this. Stop, go back. Don't do this.

(sigh)

But sometimes clients ask me to update the decimal places on an item that they've had in production for some time.

The code below works for making a decimal place longer. BUT NOT SMALLER. You'd have to add code to round all the decimal places if you're going smaller.

18.4 update

It's tax season, those of us that write GP integrations are going to get asked to help with (or do) the GP 18.4 update. I've done two now... here are my 'things not to forget'

CHARINDEX/PATINDEX

I've used CHARINDEX for years, but came across a need to use a FILTER with a WILDCARD with CHARINDEX and found that it would not support it.

Enter: PATINDEX.

We'll use this article to put up tips on the uses of CHARINDEX and PATINDEX, for today this piece of code. The Task is to find 'INV12345678' in a notes field using wildcards and retrieve the invoice number for a report.

SQL Insert a record into a Table with a NOTEINDEX Column

The collection module in GP has a Table - CN00100. It has one record for every note entered.

I am systematically inserting a record into the table based on the value in an external table.

Problem - the table can't have duplicate values in the NOTEINDEX column. Here's a screen shot of the Table. My insert has to specify a NOTEINDEX = '15' or get next and then '16' or get next for next record etc.....

Any thoughts?

Here's what I have so far:

INSERT INTO [dbo].[CN00100]
([CUSTNMBR]
,[CPRCSTNM]
,[DATE1]
,[Contact_Date]
,[TIME1]
,[Contact_Time]
,[NOTEINDX]
,[RevisionNumber]
,[CN_Group_Note]
,[Caller_ID_String]
,[Action_Promised]
,[ActionType]
,[Action_Date]
,[Action_Assigned_To]
,[Action_Completed]
,[ACTCMDSP]
,[Action_Completed_Date]
,[Action_Completed_Time]
,[Amount_Promised]
,[Amount_Received]
,[USERID]
,[Note_Display_String]
,[CNTCPRSN]
,[ADRSCODE]
,[USERDEF1]
,[USERDEF2]
,[USRDAT01]
,[PRIORT]
,[NOTECAT]
,[NoteStatus]
,[Action_Cancelled_By]
,[Action_Cancelled_Date]
,[MODIFDT]
,[CN_Pinned])
VALUES
('QUEEN001'
,'QUEEN001'
,CAST(getdate() as Date)
,CAST(getdate() as Date)
,CAST(getdate() as Time)
,CAST(getdate() as Time)
,'1111' ------------this is where I need to get next index number is there an equivalent to getdate type functionality?
,'0'
,'0'
,'sa'
,'DUNFINAL'
,'1'
,CAST(getdate() as Date)
,'sa'
,'0'
,'0'
,'1900-01-01 00:00:00.000'
,'1900-01-01 00:00:00.000'
,'5800.00'
,'0'
,'BRON'
,'Dunning Letter Sent'
,''
,''
,''
,''
,'1900-01-01 00:00:00.000'
,'2'
,''
,'1'
,''
,'1900-01-01 00:00:00.000'
,CAST(getdate() as Date)
,'0')

GO

Thanks in advance.......

Applying AP credit Notes through SQL script

Hello,

Is there any database script or way available to apply AP credit notes in bulk? Applying manually in GP apply screen one by one is very time consuming. SmartConnect is also not having any node for AP applying.

Thanks,

Habeeb

AP applying node is not available in smartConnect

Hello SmartConnect Gurus,

AP applying node is not available in smartConnect. Not sure what's the reason but, it should be available like AR apply node below. Is there a way to add the AP apply node? Please confirm?

Thanks,

Habeeb

Is there a way to find the bin where a sales order line was picked from in GP?

Is there a way to find the bin where a sales order line was picked from in GP? I am specifically looking for the SQL to accomplish getting the bins the invoice lines were picked from based on an invoice number.

I have a manager that wants to know what bins invoices were picked from.

Thanks!

Tim

SQL Sub String question

I have a requirement to extrapolate from a SQL String Field our Invoice Number.

My Query is as follows:

SELECT CUSTNMBR,

CAST(DATE1 AS DATE) AS Date,

Action_Promised,

Amount_Promised,

SUBSTRING(

Note_Display_String,

PATINDEX('%REF%', Note_Display_String) + 0,

LEN(Note_Display_String) - PATINDEX('%;REF;%', Note_Display_String) + 0

) AS InvNumber,

Note_Display_String

FROM CN00100

WHERE DATE1 >= '2022-01-01'

AND Action_Promised LIKE '%Dun%';

I need to get the Invoice Number (REFXXXXXX) from the Note_Display_String Field. How can I change my Substring to only Grab REFXXXXXX no matter where in the string it falls? Thanks in advance......

Query returns this:

Automate a task in GP

Hi,

Client would like to have the project task "create combined history" run on a scheduled basis every night. Are there any GP Utilities that could automate that for us?

Thanks