|
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_
|
|
|
Ok, get your thinking caps on. The customer requirement is this:
We have a JOB table and a JOBSTATUS table, display the Job and the latest Status
Here is our data:
declare @job as table (Job varchar (15))
insert into @job (Job) values ( 'Job1' )
insert into @job (Job) values ( 'Job2' )
declare @log as table (RowID int identity, Job varchar (15), Status varchar (10))
insert into @log(job, status) values ( 'Job1' , 'Starting' )
insert into @log(job, status) values ( 'Job1' , 'Step 1' )
insert into @log(job, status) values ( 'Job1' , 'Step 2' )
insert into @log(job, status) values ( 'Job2' , 'Starting' )
insert into @log(job, status) values ( 'Job2' , 'Step 1' )
insert into @log(job, status) values ( 'Job2' , 'Step 2' )
insert into @log(job, status) values ( 'Job2' , 'Step 3' )
insert into @log(job, status) values ( 'Job2' , 'Done' )
Given this, how would you write a query to return this?

See my answer below... but I'd like to hear yours |
|
|
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?
|
|
|
Error: Invalid Document Type (DOCTYPE) - 1=Invoice, 2=Fin. Chrg, 3=Misc. Chrg, 4=Return, & 5=Credit Memo are valid
|
|
|
Hi,
I'm looking for the available values for the State Tax filing status in the UPR00700. The KB article says to see attachment; but, I can't seem to find it.
Where do I find the attachment? Here is the linky to the KB.
http://dyndeveloper.com/dyncolumn.aspx?moduleid=upr&tablename=upr00700&dbname=company%20db
Thanks
Joe Guyton
|
|
|
|
Table Definition Quick Links
|
|
|