DynDeveloper.com

DynDeveloper.com

Sign Up Now! Log In

Archives

 

Today's task is to open a URL from a button in the SOP Inquiry form. The URL requires that we do some data access to look up one of the parameters. 

Here we go...

There are several fields that are hidden on Dynamics forms that are quite useful. Sometimes there is a LOAD field or a SAVERECORD field that you can trap changing and save yourself some trouble. 

Today, we want to show the SOPTYPE field on the SOP TRANSACTION INQUIRY form so that we can do a database requests. 

Enjoy!

Huh?

Yeah, I know. 

This is a test, see if you can code this actual client request before you look at my solution. 

The requirement is to return a list of orders where the order has ALL of the items supplied in a comma separated list of parameters. So, if the parameter is 'ITEM1,ITEM2', the code would return a list of orders where the order has BOTH ITEM1 and ITEM2.

Our code starts like this

declare @items varchar(max)
declare @count int
 
--declare a table that mimics the SOP LINE table in Dynamics GP
declare @SOP10200 table (SOPNUMBE VARCHAR(21), ITEMNMBR VARCHAR(31))
 
--insert a series of orders
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD001','ITEM01')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD001','ITEM02')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD001','ITEM03')
 
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD002','ITEM02')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD002','ITEM03')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD002','ITEM04')
 
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD003','ITEM03')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD003','ITEM04')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD003','ITEM05')
 
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD004','ITEM03')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD004','ITEM04')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD004','ITEM05')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD004','ITEM06')
 
 
-- the @items parameter holds a comma separated list of items
--To test, vary these items
select @items = 'ITEM03, ITEM04'

So, given items ITEM03, ITEM04', above, we need a query that returns the orders that have those items:

Order ORD001 would not return, because it does not have ITEM004

Code your own solution, then look at mine. 

One of the stars of our industry, Steve Endow, today announced a free SFTP that will help with integrations. If you've ever needed to FTP GP data, give this a look

 

Today's little slice of heaven involves workflow setup, we're getting this error: An error occurred in the Microsoft .NET framework while trying to load assembly id 65565. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_

 

 

How do you send a system-wide message to all users or just a message to one particular user?

Hi,

In the eConnect section there is a code snippet for taIVGetNumber that retrieves the next number from table IV10400 for two types of documents identified by the first parameter.  I am interested in the next number for Next_Bin_Xfer_Doc_Number for bin transfers.  Will this procedure work for that next number as well or is there another procedure or do I have to come up with my own?

Thanks much, appreciate the help.

Hi Steve, 

I installed the code below on our system a couple weeks ago but it caused the following issue:

"We can no longer login to GP in the lab or live using sa.  Every time we try it creates an activity row, then tells us we’re already logged in and won’t allow you to go further.  You have to close gp, reopen it, delete the activity row to even try again, but when you do … same result.  It creates a new activity row and then tells you that you are already logged in."

 

I must have not done something (missed a step??) or installed this incorrectly.   Just looking for guidance here and it was my fault for not confining this to our test system first.

 

Please advise, thank you.

Karen

 

 

 

Trigger name - ddt_ACTIVITY_INS

 

USE [DYNAMICS]

GO

/****** Object:  Trigger [dbo].[ddt_ACTIVITY_INS]    Script Date: 11/3/2017 7:07:11 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[ddt_ACTIVITY_INS] 

    ON [dbo].[ACTIVITY]

    FOR INSERT

AS

--Written by Steve Gray 941-747-3669

DECLARE @hostname varchar(255)

declare @body varchar(200)

declare @cc as varchar(200)

set @cc = ''

DECLARE curName CURSOR LOCAL FAST_FORWARD FOR

    SELECT convert(varchar(255), SERVERPROPERTY('MACHINENAME') )

        FROM inserted ins

        where ins.USERID = 'sa'

OPEN curName

WHILE 1=1

BEGIN

    FETCH NEXT FROM curName INTO @hostname

    if @@fetch_status <> 0 begin

        break

    end

    select @Body = 'the sa use has just logged on from the ' + @hostname + ' workstation'

    EXEC msdb.dbo.sp_send_dbmail

        @profile_name = 'Default profile',

        @recipients = 'kkoehle@valuedrugco.com',

        @copy_recipients = @cc,

        @body = @Body,

        @body_format = 'HTML',

        @subject = 'SA access'

END

CLOSE curName

ALLOCATE curName

 

In GP10, when the sa logs in, System Reports are visible in the smartlist. How do I give a non-sa user access to these reports? I am looking for 'Exchange Rates' report in particular.

I need it ASAP please. Thanks in advance

Paul Chacko

  Good morning,

  I need enable Allow Repeating Documents for Sales Orders Processing-Orders but this field is disable (in gray), I try deleting all orders but theis field continue in gray (disable) and does not allow me mark the box. If you know the table and the field to make this change directly via SQL that fine for me.  I have GP 2016

Any suggestion is welcome,

Mr. García

Look below this field in yellow

Hello,

I am developing a .NET MVC C# Web Application that uses the Dynamics GP Web Service to help perform tasks like those found in GP. I am working on the Purchase Order feature and need the ability to Create, Read and Update Purchase Orders.

Using the Dynamics GP Web Service, I was able to fairly easily Create a Purchase Order. However, I cannot seem to edit any of its header information. I am using a web form to send the edited data back to the server in the form of a PurchaseOrder object as defined by the Web Service. At first, I thought the form structure may be the culprit but I have since tried simply creating a new PurchaseOrder object right on the Server after I send the request, eliminating any issues with the form. I have tried setting different properties, and leaving others null, but nothing seems to work.

The strangest part, is that I can edit Line item information perfectly fine. When using a web form, the Line data is within the same form as the header info and I have verified that all the expected header information is being passed to the server appropriately.

After my initial attempts failed, I decided to try using eConnect instead of the Dynamics GP Web Service. Because eConnect is actually used behind the scenes, I was expecting eConnect to also fail. However, using eConnect, I was able to edit the header information for an existing PO. This seems to conclude that there is some issue with the Web Service, although I have no idea what it could be.

Below is an example of a simpler test I have tried.

var purchaseOrder = new PurchaseOrder
{
    CompanyKey = new CompanyKey { Id = 2 },
    Key = new PurchaseTransactionKey { Id = "P0350607" },
    VendorKey = new VendorKey { Id = "00865" },
    BuyerKey = new BuyerKey { Id = "ROC-JENNIFERS" }
};
var policy = Client.GetPolicyByOperation("UpdatePurchaseOrder", Context);
Client.UpdatePurchaseOrder(purchaseOrder, Context, policy);

This code is within a service method that I have made and normally accepts a PurchaseOrder object. However, to have better control over the data I have chosen to temporarily create a new one within the method. Also, the `Client` object is an instance of the DynamicsGPClient object I have moved into a base class to make using the Web Service a little easier.

In the above example, the BuyerId of the Purchase Order does not change. I have tried similar tests with a variety of other header properties and none of them have ever changed.

Also, the last thing I did was using a SQL Trace. I looked at the RPC:Starting and RPC:Completed events and found that the `taPoHdr` eConnect procedure was being run (if I understand eConnect correctly, this is the correct node for updating a Purchase Order). However, when looking at the command, I could see that all the properties for the xml node were set to `default`. Below is an example. You can see that this command is updating essentially nothing.

I did this same trace while using eConnect directly and the fields I changed were NOT set to default. The changes I had made appeared correctly as they should.

declare @p104 int
set @p104=0
declare @p105 varchar(255)
set @p105=''
exec taPoHdr @I_vPOTYPE=default,
@I_vPONUMBER='P0350607         ',
@I_vVENDORID='00865          ',
@I_vVENDNAME=default,
@I_vDOCDATE=default,
@I_vBUYERID=default,
@I_vALLOWSOCMTS=default,
@I_vTRDISAMT=default,
@I_vFRTAMNT=default,
@I_vMSCCHAMT=default,
@I_vTAXAMNT=default,
@I_vSUBTOTAL=default,
@I_vCUSTNMBR=default,
@I_vPRSTADCD=default,
@I_vCMPNYNAM=default,
@I_vCONTACT=default,
@I_vADDRESS1=default,
@I_vADDRESS2=default,
@I_vADDRESS3=default,
@I_vCITY=default,
@I_vSTATE=default,
@I_vZIPCODE=default,
@I_vCCode=default,
@I_vCOUNTRY=default,
@I_vPHONE1=default,
@I_vPHONE2=default,
@I_vPHONE3=default,
@I_vFAX=default,
@I_vVADCDPAD=default,
@I_vPURCHCMPNYNAM=default,
@I_vPURCHCONTACT=default,
@I_vPURCHADDRESS1=default,
@I_vPURCHADDRESS2=default,
@I_vPURCHADDRESS3=default,
@I_vPURCHCITY=default,
@I_vPURCHSTATE=default,
@I_vPURCHZIPCODE=default,
@I_vPURCHCCode=default,
@I_vPURCHCOUNTRY=default,
@I_vPURCHPHONE1=default,
@I_vPURCHPHONE2=default,
@I_vPURCHPHONE3=default,
@I_vPURCHFAX=default,
@I_vPRBTADCD=default,
@I_vSHIPMTHD=default,
@I_vPYMTRMID=default,
@I_vDSCPCTAM=default,
@I_vDSCDLRAM=default,
@I_vDISAMTAV=default,
@I_vDUEDATE=default,
@I_vDISCDATE=default,
@I_vTXRGNNUM=default,
@I_vCONFIRM1=default,
@I_vCOMMNTID=default,
@I_vCOMMENT_1=default,
@I_vCOMMENT_2=default,
@I_vCOMMENT_3=default,
@I_vCOMMENT_4=default,
@I_vHOLD=default,
@I_vTAXSCHID=default,
@I_vPurchase_Freight_Taxable=default,
@I_vPurchase_Misc_Taxable=default,
@I_vFRTSCHID=default,
@I_vMSCSCHID=default,
@I_vFRTTXAMT=default,
@I_vMSCTXAMT=default,
@I_vBCKTXAMT=default,
@I_vBackoutFreightTaxAmt=default,
@I_vBackoutMiscTaxAmt=default,
@I_vBackoutTradeDiscTax=default,
@I_vUSINGHEADERLEVELTAXES=default,
@I_vCURNCYID=default,
@I_vXCHGRATE=default,
@I_vRATETPID=default,
@I_vEXPNDATE=default,
@I_vEXCHDATE=default,
@I_vEXGTBDSC=default,
@I_vEXTBLSRC=default,
@I_vRATEEXPR=default,
@I_vDYSTINCR=default,
@I_vRATEVARC=default,
@I_vTRXDTDEF=default,
@I_vRTCLCMTD=default,
@I_vPRVDSLMT=default,
@I_vDATELMTS=default,
@I_vTIME1=default,
@I_vUSERID=default,
@I_vPOSTATUS=default,
@I_vCMMTTEXT=default,
@I_vPRMDATE=default,
@I_vPRMSHPDTE=default,
@I_vREQDATE=default,
@I_vCONTENDDTE=default,
@I_vCNTRLBLKTBY=default,
@I_vREQTNDT=default,
@I_vUpdateIfExists=1,
@I_vNOTETEXT=default,
@I_vRequesterTrx=0,
@I_vUSRDEFND1=default,
@I_vUSRDEFND2=default,
@I_vUSRDEFND3=default,
@I_vUSRDEFND4=default,
@I_vUSRDEFND5=default,
@O_iErrorState=@p104 output,
@oErrString=@p105 output
select @p104,
 @p105

Anyone have any ideas on what I could try to resolve this issue? Anyone experienced anything similar? I could use eConnect if I had to, but I would prefer to use the Web Service.

Thanks.

I am currently passing a Purchase Invoice to Great Plains.  Within the taPopEnterMatchInvHdr Object I have CreateDist Enabled, which creates the distributions automatically from the information on the Purchase Order.  Would it be possible to pass the distRef while still having CreateDist enabled or would I have to recreate the entire distributions node to include the 1 field?