eConnect uses a Document Requester system to allow database activity in GP to be tracked, say for the creation of sales orders. It also allows the database to be queried via the eConnect API, without any logging to output tables.
Steve Endow has written up here on his blog, 8 year old bug in eConnect Requester SOP Transaction trigger an issue. Essentially the eConnect_Out table was not receiving any records when new sales orders were created, even though the SO_Trans Insert trigger was enabled.
The issue boiled down to the auto-generated code for the trigger generated by the default requester that is “supplied with” eConnect, inappropriately making CUSTNMR a required conditional field on order record insert. Steve correctly identified that as the CUSTNMBR field is initially empty in the SQL table and is actually updated after the record is inserted. As the CUSTNMR is updated rather than inserted this is why the requester fails to insert a record in the output table. See his post for full details and a better explanation.
I’ve worked with eConnect requesters a for a long time and can offer a suggested way I would solve this issue.
The utility is actually merely manipulating rows in [dbo].[eConnect_Out_Setup] table. SQL triggers on this table do the clever task of creating other triggers against the relevant tables that we seek to monitor. Looking at the row for the SO_Trans record, we see the following in the setup record;
To solve the problem we simply create a new output definition record that does not has a that "CUSTNMBR" valued in REQURIED1 the field. The design of eConnect is such that integrating applications can access the setup table directly to subscribe to what they need in GP.
To achieve this, script out the data from the original row setup row, give it a new alias name and remove the field in the REQUIRED1 value.
Insert this record into the [dbo].[eConnect_Out_Setup] table (in production the application can do this on install or start of the application). The insert causes a new trigger to be created against the sales table SOP10100, this time without that required field. Do a test, now creating a new sales order will correctly create a record in the output table.
INSERT [dbo].[eConnect_Out_Setup] (
[QUEUEPATH]
,[QUEUE_ENABLED]
,[DOCTYPE]
,[INSERT_ENABLED]
,[UPDATE_ENABLED]
,[DELETE_ENABLED]
,[TABLENAME]
,[ALIAS]
,[MAIN]
,[PARENTLEVEL]
,[ORDERBY]
,[USERDEF1]
,[USERDEF2]
,[USERDEF3]
,[USERDEF4]
,[USERDEF5]
,[REQUIRED1]
,[INDEX1]
,[INDEX2]
,[INDEX3]
,[INDEX4]
,[INDEX5]
,[INDEX6]
,[INDEX7]
,[INDEX8]
,[INDEX9]
,[INDEX10]
,[INDEX11]
,[INDEX12]
,[INDEX13]
,[INDEX14]
,[INDEX15]
,[INDEXCNT]
,[TRIGGER1]
,[TRIGGER2]
,[TRIGGER3]
,[TRIGGER4]
,[TRIGGER5]
,[TRIGGER6]
,[TRIGGER7]
,[TRIGGER8]
,[TRIGGER9]
,[TRIGGER10]
,[TRIGGER11]
,[TRIGGER12]
,[TRIGGER13]
,[TRIGGER14]
,[TRIGGER15]
,[TRIGGERCNT]
,[JOINTABLE]
,[JOIN1]
,[JOIN2]
,[JOIN3]
,[JOIN4]
,[JOIN5]
,[JOIN6]
,[JOIN7]
,[JOIN8]
,[JOIN9]
,[JOIN10]
,[JOINTO1]
,[JOINTO2]
,[JOINTO3]
,[JOINTO4]
,[JOINTO5]
,[JOINTO6]
,[JOINTO7]
,[JOINTO8]
,[JOINTO9]
,[JOINTO10]
,[DATACNT]
,[DATA1]
,[DATA2]
,[DATA3]
,[DATA4]
,[DATA5]
,[DATA6]
,[DATA7]
,[DATA8]
,[DATA9]
,[DATA10]
,[DATA11]
,[DATA12]
,[DATA13]
,[DATA14]
,[DATA15]
,[DATA16]
,[DATA17]
,[DATA18]
,[DATA19]
,[DATA20]
,[DATA21]
,[DATA22]
,[DATA23]
,[DATA24]
,[DATA25]
,[DATA26]
,[DATA27]
,[DATA28]
,[DATA29]
,[DATA30]
,[DATA31]
,[DATA32]
,[DATA33]
,[DATA34]
,[DATA35]
,[DATA36]
,[DATA37]
,[DATA38]
,[DATA39]
,[DATA40]
,[DATA41]
,[DATA42]
,[DATA43]
,[DATA44]
,[DATA45]
,[DATA46]
,[DATA47]
,[DATA48]
,[DATA49]
,[DATA50]
,[DATA51]
,[DATA52]
,[DATA53]
,[DATA54]
,[DATA55]
,[DATA56]
,[DATA57]
,[DATA58]
,[DATA59]
,[DATA60]
,[DATA61]
,[DATA62]
,[DATA63]
,[DATA64]
,[DATA65]
,[DATA66]
,[DATA67]
,[DATA68]
,[DATA69]
,[DATA70]
,[DATA71]
,[DATA72]
,[DATA73]
,[DATA74]
,[DATA75]
,[DATA76]
,[DATA77]
,[DATA78]
,[DATA79]
,[DATA80]
,[DATA81]
,[DATA82]
,[DATA83]
,[DATA84]
,[DATA85]
,[DATA86]
,[DATA87]
,[DATA88]
,[DATA89]
,[DATA90]
,[DATA91]
,[DATA92]
,[DATA93]
,[DATA94]
,[DATA95]
,[DATA96]
,[DATA97]
,[DATA98]
,[DATA99]
,[DATA100]
,[DATA101]
,[DATA102]
,[DATA103]
,[DATA104]
,[DATA105]
,[DATA106]
,[DATA107]
,[DATA108]
,[DATA109]
,[DATA110]
,[DATA111]
,[DATA112]
,[DATA113]
,[DATA114]
,[DATA115]
,[DATA116]
,[DATA117]
,[DATA118]
,[DATA119]
,[DATA120]
,[DATA121]
,[DATA122]
,[DATA123]
,[DATA124]
,[DATA125]
,[DATA126]
,[DATA127]
,[DATA128]
,[DATA129]
,[DATA130]
,[DATA131]
,[DATA132]
,[DATA133]
,[DATA134]
,[DATA135]
,[DATA136]
,[DATA137]
,[DATA138]
,[DATA139]
,[DATA140]
,[DATA141]
,[DATA142]
,[DATA143]
,[DATA144]
,[DATA145]
,[DATA146]
,[DATA147]
,[DATA148]
,[DATA149]
,[DATA150]
,[DATA151]
,[DATA152]
,[DATA153]
,[DATA154]
,[DATA155]
,[DATA156]
,[DATA157]
,[DATA158]
,[DATA159]
,[DATA160]
,[DATA161]
,[DATA162]
,[DATA163]
,[DATA164]
,[DATA165]
,[DATA166]
,[DATA167]
,[DATA168]
,[DATA169]
,[DATA170]
,[DATA171]
,[DATA172]
,[DATA173]
,[DATA174]
,[DATA175]
,[DATA176]
,[DATA177]
,[DATA178]
,[DATA179]
,[DATA180]
)
VALUES (
N' '
,0
,N'Sales_Transaction_All'
,1
,0
,0
,N'SOP10100'
,N'SO_Trans_All'
,1
,0
,1
,N' '
,N' '
,N' '
,N' '
,N' '
,N''
,N'SOPNUMBE'
,N'SOPTYPE'
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,2
,N'SOPNUMBE'
,N'SOPTYPE'
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,2
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,170
,N'ORIGTYPE'
,N'ORIGNUMB'
,N'DOCID'
,N'DOCDATE'
,N'GLPOSTDT'
,N'QUOTEDAT'
,N'QUOEXPDA'
,N'ORDRDATE'
,N'INVODATE'
,N'BACKDATE'
,N'RETUDATE'
,N'ReqShipDate'
,N'FUFILDAT'
,N'ACTLSHIP'
,N'DISCDATE'
,N'DUEDATE'
,N'REPTING'
,N'TRXFREQU'
,N'TIMEREPD'
,N'TIMETREP'
,N'DYSTINCR'
,N'DTLSTREP'
,N'DSTBTCH1'
,N'DSTBTCH2'
,N'USDOCID1'
,N'USDOCID2'
,N'DISCFRGT'
,N'ORDAVFRT'
,N'DISCMISC'
,N'ORDAVMSC'
,N'DISAVAMT'
,N'ORDAVAMT'
,N'DISCRTND'
,N'ORDISRTD'
,N'DISTKNAM'
,N'ORDISTKN'
,N'DSCPCTAM'
,N'DSCDLRAM'
,N'ORDDLRAT'
,N'DISAVTKN'
,N'ORDATKN'
,N'PYMTRMID'
,N'PRCLEVEL'
,N'LOCNCODE'
,N'BCHSOURC'
,N'BACHNUMB'
,N'CUSTNMBR'
,N'CUSTNAME'
,N'CSTPONBR'
,N'PROSPECT'
,N'MSTRNUMB'
,N'PCKSLPNO'
,N'PICTICNU'
,N'MRKDNAMT'
,N'ORMRKDAM'
,N'PRBTADCD'
,N'PRSTADCD'
,N'CNTCPRSN'
,N'ShipToName'
,N'ADDRESS1'
,N'ADDRESS2'
,N'ADDRESS3'
,N'CITY'
,N'STATE'
,N'ZIPCODE'
,N'COUNTRY'
,N'PHNUMBR1'
,N'PHNUMBR2'
,N'PHONE3'
,N'FAXNUMBR'
,N'COMAPPTO'
,N'COMMAMNT'
,N'OCOMMAMT'
,N'CMMSLAMT'
,N'ORCOSAMT'
,N'NCOMAMNT'
,N'ORNCMAMT'
,N'SHIPMTHD'
,N'TRDISAMT'
,N'ORTDISAM'
,N'TRDISPCT'
,N'SUBTOTAL'
,N'ORSUBTOT'
,N'REMSUBTO'
,N'OREMSUBT'
,N'EXTDCOST'
,N'OREXTCST'
,N'FRTAMNT'
,N'ORFRTAMT'
,N'MISCAMNT'
,N'ORMISCAMT'
,N'TXENGCLD'
,N'TAXEXMT1'
,N'TAXEXMT2'
,N'TXRGNNUM'
,N'TAXSCHID'
,N'TXSCHSRC'
,N'BSIVCTTL'
,N'FRTSCHID'
,N'FRTTXAMT'
,N'ORFRTTAX'
,N'FRGTTXBL'
,N'MSCSCHID'
,N'MSCTXAMT'
,N'ORMSCTAX'
,N'MISCTXBL'
,N'BKTFRTAM'
,N'ORBKTFRT'
,N'BKTMSCAM'
,N'ORBKTMSC'
,N'BCKTXAMT'
,N'OBTAXAMT'
,N'TXBTXAMT'
,N'OTAXTAMT'
,N'TAXAMNT'
,N'ORTAXAMT'
,N'ECTRX'
,N'DOCAMNT'
,N'ORDOCAMT'
,N'PYMTRCVD'
,N'ORPMTRVD'
,N'DEPRECVD'
,N'ORDEPRVD'
,N'CODAMNT'
,N'ORCODAMT'
,N'ACCTAMNT'
,N'ORACTAMT'
,N'SALSTERR'
,N'SLPRSNID'
,N'UPSZONE'
,N'TIMESPRT'
,N'PSTGSTUS'
,N'VOIDSTTS'
,N'ALLOCABY'
,N'NOTEINDX'
,N'CURNCYID'
,N'CURRNIDX'
,N'RATETPID'
,N'EXGTBLID'
,N'XCHGRATE'
,N'DENXRATE'
,N'EXCHDATE'
,N'TIME1'
,N'RTCLCMTD'
,N'MCTRXSTT'
,N'TRXSORCE'
,N'COMMNTID'
,N'REFRENCE'
,N'POSTEDDT'
,N'PTDUSRID'
,N'USER2ENT'
,N'CREATDDT'
,N'MODIFDT'
,N'Tax_Date'
,N'APLYWITH'
,N'WITHHAMT'
,N'SHPPGDOC'
,N'CORRCTN'
,N'SIMPLIFD'
,N'CORRNXST'
,N'DOCNCORR'
,N'SEQNCORR'
,N'SALEDATE'
,N'EXCEPTIONALDEMAND'
,N'Flags'
,N'BackoutTradeDisc'
,N'OrigBackoutTradeDisc'
,N'GPSFOINTEGRATIONID'
,N'INTEGRATIONSOURCE'
,N'INTEGRATIONID'
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
)
The new requester row creates the associated insert trigger against SOP10100 as shown below.
The new requester type also now shows up in the setup tool as expected for future editing (might need to reconnect to refresh).
In reality if it is merely a flag for created orders that is required, for the setup record, only SOPNUMBE and SOPTYPE are worth capturing as data fields, the rest are mostly blank anyway.
Also note that the user could click the [x] and close the order, and the SOPNUMBE will be returned to the stack and could be used again with a future order. The integrating application has to handle that too.
Although eConnect installs some example requester documents to get users going, additional requesters can be added to meet specific requirements. eConnect requesters are configurable and quite flexible, however this requires direct editing of the setup table.