DynDeveloper.com

DynDeveloper.com

Sign Up Now!Log In

Archives

 
A customer called and wanted to run a SmartConnect map with a user-entered parameter.
I tried to open a map in SmartConnect, but got an error message saying it was currently being edited by another user.  That user was me as I had to stop and restart SmartConnect as it hung when I clicked on the Modify button for a map.

Need to know how to turn logging on and off for DEX SQL.  The logging information records SQL activity during a Dynamics GP session.  This information can be very useful in debugging problems.

In these blogs, I am recording what I am learning as I get up to speed on SQL, particularly the T-SQL version.  I am assuming that you have basic computer skills and know the basic data types (string, Boolean, numeric, etc.), and that you have SQL already installed on your PC.

Received a requirement today from a client that adds an AA Dimension for each SOP transaction entered. The requirement was to automatically insert it when a SOP document is keyed. The code below is the result of that work.

In doing the work, we also discovered that we needed to add a line to AAG00602 at the same time, in order to populate the 'linked to node' field (see below)

Today, we will learn how to use a simple SELECT statement to look at the data that is returned from a query.
 

If you call up a Payment in Dynamics Payables Transaction Inquiry, you'll get the Payables Payments Zoom window. 

On that window is a button that says 'Re-create check stub'. A client recently asked me to recreate this for an integration

The task was MUCH harder than I thought it would be. It turns out that you have to recursively add in credits, then related invoices, and loop until you've got all the data. In testing we had to loop as many as three times. 

Finally, we needed to bring in related invoices at the end. 

Hope this is useful to you. 

Here's the output that we were trying to mimic:

 

 


This script wraps the eConnect taCreateCustomerAddress stored procedure, giving you an easy way to call it. 

If there is an error on creation, we return the full error string, not just the cryptic error ID

This stored procedure wraps the eConnect taItemSite stored procedure, making it easier to call. 

It also returns the full error text in case of an error, and not just the cryptic error ID

Today, we will learn a simple way to filter, or select, the amount of data returned from a SELECT statement.  This will just be an introduction, I will go into more detail next time.
 

I need to get a copy of the contents of an SQL result set.  I want to either put it in a spreadsheet or save it to a file.

 

https://dynamicsgpland.blogspot.com/2018/06/opening-dynamics-gp-windows-using-net.html

This is just brilliant. Every word of it, start to finish. 

You'd do well to follow Steve Endow's blogs. 

 

The complete text of the taCreateCustomerItems stored procedure. First 50 lines or so shown below.

CREATE PROCEDURE dbo.taCreateCustomerItems
    @I_vITEMNMBR CHAR(30),
    @I_vCUSTNMBR CHAR(15),
    @I_vCUSTITEMNMBR CHAR(30) = '',
    @I_vCUSTITEMDESC CHAR(50) = '',
    @I_vCUSTITEMSHORNAME CHAR(15) = '',
    @I_vCUSTITEMGENERICDESC CHAR(10) = '',
    @I_vUSERDEF1 CHAR(20) = '',
    @I_vUSERDEF2 CHAR(20) = '',
    @I_vUSRDEF03 CHAR(20) = '',
    @I_vUSRDEF04 CHAR(20) = '',
    @I_vUSRDEF05 CHAR(20) = '',
    @I_vSUBITEMNMBR CHAR(30) = '',
    @I_vSTRTDATE DATETIME = '',
    @I_vENDDATE DATETIME = '',
    @I_vRequesterTrx SMALLINT = 0,
    @I_vUSRDEFND1 CHAR(50) = '',
    @I_vUSRDEFND2 CHAR(50) = '',
    @I_vUSRDEFND3 CHAR(50) = '',
    @I_vUSRDEFND4 VARCHAR(8000) = '',
    @I_vUSRDEFND5 VARCHAR(8000) = '',
    @O_iErrorState INT OUTPUT,
    @oErrString VARCHAR(255) OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
DECLARE @SUBITEMDESC CHAR(50),
        @NOTEINDX DECIMAL(19, 5),
        @CMPANYID SMALLINT,
        @iGetNextNoteIdxErrState INT,
        @iAddCodeErrState INT,
        @iStatus INT,
        @iCustomState INT,
        @iCustomErrString VARCHAR(255),
        @O_oErrorState INT,
        @iError INT;
SELECT @SUBITEMDESC = '',
       @NOTEINDX = 0,
       @iStatus = 0,
       @O_iErrorState = 0;
IF (@oErrString IS NULL)
BEGIN
    SELECT @oErrString = '';
END;
EXEC @iStatus = taCreateCustomerItemsPre @I_vITEMNMBR OUTPUT,
                                         @I_vCUSTNMBR OUTPUT,
                                         @I_vCUSTITEMNMBR OUTPUT,
                                         @I_vCUSTITEMDESC OUTPUT,
                                         @I_vCUSTITEMSHORNAME OUTPUT,
                                         @I_vCUSTITEMGENERICDESC OUTPUT,
                                         @I_vUSERDEF1 OUTPUT,
                                         @I_vUSERDEF2 OUTPUT,
                                         @I_vUSRDEF03 OUTPUT,
                                         @I_vUSRDEF04 OUTPUT,
                                         @I_vUSRDEF05 OUTPUT,
                                         @I_vSUBITEMNMBR OUTPUT,
                                         @I_vSTRTDATE OUTPUT,
                                         @I_vENDDATE OUTPUT,
                                         @I_vRequesterTrx OUTPUT,
                                         @I_vUSRDEFND1 OUTPUT,
                                         @I_vUSRDEFND2 OUTPUT,
                                         @I_vUSRDEFND3 OUTPUT,
                                         @I_vUSRDEFND4 OUTPUT,
                                         @I_vUSRDEFND5 OUTPUT,
                                         @O_iErrorState = @iCustomState OUTPUT,
                                         @oErrString = @iCustomErrString OUTPUT;
SELECT @iError = @@error;
I created a new map in SmartConnect that only calls a stored procedure.  I also have 2 parameters for the stored procedure.  When I double-click on the map name, I get the following error:
 

 

I click OK and then get the following error:

 

 

I click OK and run the map, but would like to not have these error messages pop up every time I run it.

 

 

 

This is a video that will walk you through your first VS Tools for Dynamics GP Project, from FILE > NEW all the way to the end. 

We end the video with some 'Hello World' code, then next article includes some template code that you'll need to do some real work

Finally...

This article shows us how to put all the previous articles to use, and actually respond to a button press in Dynamics GP using VS Tools

Please review all the previous articles if you haven't already

Hi, my client doesn't own Intgration Manager, what are the best options to importing budgets.

the Excel Based Budgeting option doesn't allow for multiple excel sheets to update a single budget ID.  the client has a ton of department budgets.

If they have to combine to import in EBB then I guess that is the only option.   Smartconnect doesn't have a Budget node.  

Anyone have any good ideas? I know there is an econnect proc for it too.

 

Thanks!

Mark

i need a help as the i couldn't able to make MO receipt for specific MF order which happened while non-completed entry for Same MF order , when i try to make a new receipt , i got message lot number is not selected and not available , is there any solution ? 

This should not be as difficult as it seems, but I am trying to find GP's file specifications for a Bank Reconciliation file in the standard BAI format.

What is GP looking for?   Field names, length, etc.   GP2016

 

 

Get the following message: quantities of the item are insufficient for this transaction

 

Upon examining the MOP1020, MOP1025, MOP1026, MOP1400 and MOP1900 tables, I am seeing records remaining on those tables for the manufacturing order.

 

What would cause this?  How does one "clean" this up?

Thank you,

Will

hi,

is there a setup table (lookup table) for "relationship" on the UPR00111 table?

I'm re-writing my employee self-service portal in MVC and need to know where that number comes from.

The weird thing is, that on the Contacts table (UPR00113), the relationship field (named "emergencyrelation") has text in it (like "wife", "self").  so not sure why the field lookup would change from contacts to dependents.

However, the dependents table currently only has a value of "7", which is "self" on every record, which I suppose is for employee benefit purposes. 

But, If I want to allow the employee to add new dependents, then go to our insurance provider to auto-update new kids or whatever, it would be nice to know the lookup.

thanks,

george

hello everybody 

i need to make a bank transfer with smartconnect, However, there is no node to do it, I know I have to do the stored procedure, does anyone know how to do it?

 

thanks!

Hi everyone,

It sounds simple but I am not getting the results I need...I need a report that shows the following

Item #, serial number, current location, date received, unit cost, quantity and UOM. Victoria Yudin does not seem to have a report that covers all.

Can someone help? End of day Thursday would be great.

Thanks

Paul

Where'd the search field disappear to on the Knowledge Base page? (Or anywhere else for that matter?) 

Need to reverse lookup the table containing COMMNTID for the line level detail. 

I keep running into a brick wall on this topic.

We have several Smartlists that were created in Smartlist Builder.

This one in particular is looking at a View of our Inventory Items in GP.     It is a custom SQL table view.

A user can see this Smartlist but when he runs it, no records are found.

I have checked that the Smartlist Object is showing in the Security tasks list, and the user has permissions to view Inventory items.

Why is he blocked from seeing the contents of the View unless I make him 'sa' ?   (Not the answer, obviously.)

 

 

So what is the deal with MS not putting these somewhere we can find them?  Any way, does anyone know if they have published VS SDK for GP2018.

And what is the likelyhood they are not just the GP2013 republished?

Hello everyone,

I want to know if there are ways to shut down or lock down an AP batch so that no one else would add transactions to that batch anymore?

We run the edit list, approve all transactions [mostly receiving & invoice matching], then when we are about to post, we see more transactions in it. It's like a moving target.

What are your thoughts?

Thanks

Paul Chacko

What is the exact backend SQL process to change an Item Type from Sales Inventory to Services after no work or open transactions exist for the item?

I am trying to track down where the "Damaged" quantity, on the Item Quantities Maintenance screen, is coming from.  Presently, for reporting, I am aggregating the transactions in the SEE30303 table to get inventory levels.  The users think this number may include items that GP otherwise identifies as damaged.

Thank you for any help you can give!

Hello,

I am developing a custom web-based integration for Dynamics GP 2010 using .NET/MVC/C# and had a particular question about eConnect. I am creating an eConnectOut object like so...

eConnectOut = new eConnectOut
{
    DOCTYPE = "Purchase_Order_Transaction",
    INDEX1FROM = purchaseOrderId,
    INDEX1TO = purchaseOrderId,
    OUTPUTTYPE = 2,
    FORLIST = 1
}

Then using the 'eConnectMethods.GetEntity()' method to retrieve the 'Purchase_Order_Transaction' XML Document. I am then deserializing the retrieved XML into a 'taPoHdr' C# model via reflection. That is all working fine but I have noticed that the 'Purchase_Order_Transaction' XML Document that is returned does not completely matchup with the `taPoHdr` model.

My question is this:  the 'taPoHdr' object has properties for the Purchase Address information ('PURCHCITY', 'PURCHSTATE', etc.) that the 'Purchase_Order_Transaction' XML Document does not. Is there another 'DOCTYPE' I can use with the 'eConnectOut' object that will include the Purchase Address information?

Thanks