Custom links example in Dynamics GP

A post on the Dynamics GP community forum reminded me of something I set up way back, custom links for linking to an item on a website from GP. Dynamics Community Forum for GP

Custom Links

Custom links are supported for a limited number of prompts within GP;

  • Chequebook
  • Credit Card
  • Debtor
  • Employee
  • Exchange Rate
  • Item
  • Salesperson
  • Tracking Number (SOP user defined fields)
  • Creditor

lets choose item as an example, here we see the custom link by clicking on Item Number in item enquiry or other screens that contain the hyperlink style Item Number prompt.

image

A drop down button list is displayed with a default entry of Item Maintenance and the custom link we have previously created that opens a web page for the item.

Creating a custom link

The Custom Link Setup window is found by navigating to:

Dynamics GP>>Tools>>Setup>>Company>>Custom Links

image

To create a new link click the new button.

image

For this example we want the custom link to open up the web page URL that has been saved into the Internet Information window for the item, under the “Home Page” field. So select Item for the prompt we want to create our link inside and then type “View web page” into the custom link label field. This is the text that will be displayed in the drop down button list. Finally select the source for the navigation, select home page and then save.

Before this will work you must edit the item to put the URL into the internet information window. In Item Maintenance  put the URL to open in the Home Page field and save. The internet information window is opened from the globe icon at the end of item description field.

image

Clicking on the button in item enquiry will now open our anteater page in the web browser.

image

Finally put some SQL in a SQL job to build the links every night…

INSERT INTO SY01200 (Master_type, Master_ID, INET2, INETINFO, EmailToAddress, EmailccAddress, EmailBccAddress)
SELECT 'ITM',
ITEMNMBR,
'http://www.mysite.com/products/' + rtrim(ITEMNMBR),
'',
'',
'',
''
FROM IV00101
WHERE ITEMNMBR NOT IN (SELECT Master_ID
FROM SY01200
WHERE Master_Type = 'ITM');
UPDATE SY01200
SET INET2 = 'http://www.mysite.com/products/' + rtrim(ITEMNMBR)
FROM IV00101
WHERE IV00101.ITEMNMBR = SY01200.Master_ID
AND SY01200.Master_Type = 'ITM' AND
INET2 != 'http://www.mysite.com/products/' + rtrim(ITEMNMBR) ;

This is very old SQL, these days I’d use a SQL MERGE statement to do this, but you get the idea.

Here is a good example of the technique used on tracking numbers to carrier websites for parcel tracking;

Dynamics GP Custom Links - Shipment Tracking

Dynamics GP Fix “Amount Remaining” incorrect on receivables transaction payment apply

For the following Debtor, the GP client server connection was interrupted whilst payments were being applied. This left the apply in an inconstant state. Subsequently the payment was unapplied, leaving the “amount remaining” for the payment double what it should be. This is because removing the apply has “credited” the amount remaining with the value of the document however it was never “debited” in the first instance, due to the interruption to the client server connection. This results in it now being twice the value it should be.

image

There are currently no apply records in Open Transactions Apply table RM20201.

SELECT * FROM RM20201 where CUSTNMBR='xxx'
(0 row(s) affected)
 
Looking at the transaction table RM20101, the two transaction values look good, many forum posts suggest that the CURTRXAM would be wrong in this table, for this situation, but everything is fine here:
SELECT * FROM RM20101 where CUSTNMBR='xxx'
image
So what is wrong? Note that this is a multicurrency transaction, checking in Multicurrency Receivables Transactions table MC020102 we find the following records:
SELECT * FROM MC020102 where CUSTNMBR='xxx'

image

Notice our offending 109.24 is here. Now updating this value to 54.62 from 109.24 brings us back to where we should be!

image

Now reapplying the transactions to each other works, with nothing remaining, as indeed there should be.

image

NOTE: Always back up your SQL data before attempting any data changes. If unsure consult your GP partner.

Custom eConnect XML Node–Procedure or function expects parameter, which was not supplied

Custom XML nodes error with parameters

It is possible to run your own custom stored procedures from eConnect, by passing in parameters via a custom XML nodes. I have explained this technique in a previous post, there is more information on MSDN too:

When developing a new Custom XML node it may be that the following error is encountered

Procedure or function expects parameter,  which was not supplied

This may be due to misconfiguration of the stored procedure parameters or XML node names.
Remember:

  • Prefix the stored procedure parameter names with I_v (Input Variable), so a parameter named “cardnumber” would become @I_Vcardnumber in the stored procedure
  • Pass in the eConnect XML document node like this (no prefix):
    <cardnumber>4929000000006</cardnumber>

Dynamics GP Integration Service

The Dynamics GP Integration Service, a windows service that provides an econnect end point service, if this is how the documents are being pushed through to GP, if using this then read on.

eConnect for Microsoft Dynamics GP 2013 Integration Service

eConnect integration Service for Dynamics GP

Cause

I have seen this behaviour a few times now. The error stated above occurs, using SQL profiler to view the SQL conversation between eConnect and the stored procedure, it looks like the parameters are being passed with the value of “default” rather than the expected values from XML.

Putting a break point on the application and inspecting the source eConnect XML document, it seems the XML looks ok, with all the data in the relevant nodes of the document. 

-So the nodes are not being extracted by eConnect and passed as the SQL parameters – why?

Solution

If this is being experienced, I expect that the stored procedure has been edited and/or the fields in the document have been edited, perhaps adding an extra field, changing a parameter name? What I have found is that the Integration Service seems to cache the profile of the SQL stored procedure parameters. I can guess this would be a good idea for performance reasons, but it means if a change is made to custom XML nodes or procedures there of, then the service must be restarted.

Restarting the service resets the cache and suddenly with no further changes the custom nodes behave correctly again! This cache behaviour can cause some serious head scratching until it is revealed what is happening.

not your issue?

There is another solution from another cause for this error, the post on Steve Endow’s blog explains:
Mysterious eConnect Error: The stored procedure does not exist. Watch your schemas!

SalesPad Mobile Installer

I love the “View Update SQL” button on the SalesPad mobile server installer. This button generates the SQL that is about to be executed against your GP database into a notepad window for inspection. It was reassuring to see and understand what was about to be done to the database.

SalesPad installer

If you look above you will see the way that SalesPad embrace namespaces for the objects that form their systems. They are up front and in in front of your eyes.

So I started reading the SQL, sadly enjoying it – as you do, knowing that you tick all the boxes of the Facebook nerd test. Here is what the resulting script looked like…

SalesPad Install script

Looking through I recognised much SQL analogous to that I’ve written before, myself for auto fulfilment and handling multiple bins. I did especially like the way they create a virtual table as a view for resolving SOPTYPE. It is a SQL technique I’ve seen elsewhere and I should adopt it more in my coding. I have also seen it used inside stored procedures too, in a similar way to create a virtual table to join against, as a common table expression.

View for resolving soptypes in Dynamics GP

Also note that they put all the db objects into the [spgpm] database schema, so I’m not the only one using schemas for what the creator intended! See my other post: Care naming database objects in Dynamics GP for custom GP Addins.