Integration Module 0.2.0.10 released

A small update to the MoveItNow Suite’s Integration Module has been released.

This version adds the menu: Export | Dynamics | Customers (all fields)

This function duplicates the Export | Dynamics | Customers item but uses a different query and output format. It still writes to the file “customers.txt”. It still uses a TAB separator. It reads from the view CUSTOMER_EXPORT4 rather than CUSTOMER_EXPORT3.

It writes out all fields found in the view. It uses the view’s field names in the column header (the first line of the file). Fields are written in the same sequence as found in the view.

In addition to these fields it also adds five (5) fields that are known to have issues when importing into Dynamics. These fields are:

  • TEL1(GP)
  • TEL2(GP)
  • TEL3(GP)
  • FAX(GP)
  • BLANKETPO(GP)

The three TEL fields are pulled from the matching TEL field in the view but have any character not a digit removed as Dynamics only allows digits for a phone number. This means the TEL fields must be in the view.

The FAX(GP) field is generated by the system with the same restrictions as the TEL fields (digits only). The system looks at all three TEL fields to see if any of them were flagged as a FAX number, it will use the first field so flagged.

The BLANKETPO(GP) field is an UPPERCASE version of the BLANKETPO field as Dynamics rejects lowercase letters in a PO field.

The export file now contains the following fields:

  • ACCOUNTID
  • ADDRESSID
  • NAME
  • ADDR1
  • ADDR2
  • CITY
  • PROV
  • PCODE
  • COUNTRY
  • TEL1
  • TEL2
  • TEL3
  • TEL1_TYPE
  • TEL2_TYPE
  • TEL3_TYPE
  • CONTACT
  • EMAIL
  • ENV_DOCK
  • ENV_STAGING
  • ENV_ELEVATOR
  • ENV_STAIRCARRY
  • ENV_FLOORPROTREQ
  • ENV_LOADINGFLOOR
  • ENV_DOCLOC
  • ENV_WALKLEN
  • ENV_ELEVATORCAP
  • ENV_FLOORTYPE
  • CONTACT2
  • CONTACT3
  • DUNS
  • CISCO
  • ENV_DOCKHEIGHT
  • ADDR_CCRA_BN
  • ADDR_CBSA_AN
  • ADDR_CUSTOMS_FAST
  • ADDR_SPINSO
  • ADDR_SPINSD
  • ADDR_SPINSB
  • ADDR_NO
  • REGION_ID
  • REGION_NAME
  • ADDR_ACEID
  • TZ
  • GPS_LAT
  • GPS_LON
  • GPS_CHECKED
  • REQUIRESAPPT
  • REQUIRESPHOTOID
  • REQUIRESSECURITYCLEARANCE
  • ENV_GROUNDLEVEL
  • ENV_STAIRSFLIGHTS
  • ENV_TAILGATE
  • ENV_TTACCESS
  • ENV_CONSTRUCTION
  • ENV_RESIDENTIAL
  • ENV_DOCKHEIGHTRESTRICTION
  • ENV_DOCKHEIGHTRESTRICTIONU
  • ENV_DOCKACCESSLENRESTRICT
  • ENV_DOCKACCESSLENRESTRICTU
  • GEOFENCEID
  • STREETNUMBER
  • FLOORNUMBER
  • UNITNUMBER
  • UNITDESC
  • COUNTRYISO
  • ADDR_ISACTIVE
  • LINKTOACCT
  • LINKTOACCTADDR
  • USERTEXT1
  • USERTEXT2
  • USERDATE1
  • USERDATE2
  • USERFLAG1
  • USERFLAG2
  • ID_CUST
  • ACCOUNT
  • MSTR_NAME
  • MSTR_ISACTIVE
  • ISDEAD
  • ISNEVERAGAIN
  • ISBANKRUPT
  • ONHOLD
  • CODONLY
  • PROSPECT
  • SALESREPID
  • ACCOUNTREPID
  • COORDINATORID
  • NATIONALACCOUNTID
  • RATEFILEID
  • TARIFFID
  • INDUSTRYCODE
  • COMMODITY
  • TERMS
  • CURRENCYID
  • HOUSEACCOUNT
  • ISPREPAY
  • DEF_DESTID
  • DEF_DESTADDR
  • DEF_BILLTOID
  • DEF_BILLTOADDR
  • REQ_PO
  • BLANKETPO
  • REFID
  • REFIDNO
  • TAX1_EXEMPT_NO
  • TAX2_EXEMPT_NO
  • TAX3_EXEMPT_NO
  • CREDITLIMIT
  • MAXBILLTIME
  • LASTVISITDATE
  • LASTCALLDATE
  • TOTAL_YTD_SHIPMENTS
  • TOTAL_YTD_WEIGHT
  • TOTAL_YTD_MILES
  • TOTAL_YTD_AMOUNT
  • TOTAL_LY_SHIPMENTS
  • TOTAL_LY_WEIGHT
  • TOTAL_LY_MILES
  • TOTAL_LY_AMOUNT
  • TOTAL_LYTD_SHIPMENTS
  • TOTAL_LYTD_WEIGHT
  • TOTAL_LYTD_MILES
  • TOTAL_LYTD_AMOUNT
  • CREATED
  • CREATEDBY
  • WEBPAGE
  • SECUREBILLING
  • CUSTOMS_BROKER
  • CUSTOMS_BROKER2
  • HAS_EDI
  • HAS_XML
  • ISA13
  • EDI_ELEMENTSEPARATOR
  • EDI_SEGMENTSEPARATOR
  • EDI_ISA16
  • MILEAGEGUIDE
  • AMOUNT_OUTSTANDING
  • REQ_POD
  • CREDITAPPONFILE
  • LAST_ORDER_NO
  • LAST_INVOICE_DATE
  • REASON_ONHOLD
  • REASON_CODONLY
  • REASON_ISDEAD
  • REASON_ISACTIVE
  • REASON_ISNEVERAGAIN
  • REASON_ISPREPAY
  • MSTR_CCRA_BN
  • MSTR_CBSA_AN
  • MSTR_CUSTOMS_FAST
  • MSTR_ACEID
  • MSTR_SPINSO
  • MSTR_SPINSD
  • MSTR_SPINSB
  • DAYSNOACT2DEAD
  • NOISDEADCHECK
  • ID_ORG
  • BRANCHOWNER
  • WHSE_SCHEDID
  • WHSE_USEMEOH4STG
  • REQ3RDPTYPAPERWORK
  • NOINVBREAKDOWN
  • ISRATEDBYSALESPERSON
  • DONOTMAILINVOICE
  • LIMITEDCHARGECODES
  • REQCUSTOMSPAPERWORK
  • FIXEDRATEFUELSURCHARGE
  • POREQOVERAMOUNT
  • INVHANDLING_MAIL
  • INVHANDLING_EMAIL
  • INVHANDLING_FAX
  • INVHANDLING_COURIER
  • INVHANDLING_EMAILTO
  • INVHANDLING_FAXNO
  • INVHANDLING_FAXATTN
  • BILLINGREQ
  • ONHOLDIFEXCEEDCREDIT
  • DECLAREDVALUERATE
  • DECLAREDVALUEPER
  • ONLYSHIPTOSELF
  • FISCALQ1START
  • FISCALQ2START
  • FISCALQ3START
  • FISCALQ4START
  • ONHOLDIFOVER60
  • ONHOLDIFOVER90
  • ISADDRONLY
  • TERMS_DESC1
  • TERMS_MAPTO
  • SALPEOPL_CODE
  • SALPEOPL_MAPTO
  • SALPEOPL_NAME
  • TEL1(GP)
  • TEL2(GP)
  • TEL3(GP)
  • FAX(GP)
  • BLANKETPO(GP)

As the above view pulls from multiple tables some fields had to be renamed due to duplication. Where the same field is used in both MSTR_CUSTOMER and CUST_ADDR the field name had either “MSTR_” or “ADDR_” prepended.

The fields had the source table prepended to the field name where a table is used as a lookup. So any field pulled from the MSTR_SALPOPL table has “SALPEOPL_” prepended. The fields from MSTR_TERMS has “TERMS_” prepended.

Any fields identified as BOOLEAN in the view (stored as either a 0 or 1) will be exported as “Y” or “N”.

Where as the base export includes the following fields:

  • Customer ID
  • Customer Name
  • Contact
  • Address Code
  • Address One
  • Address Two
  • Country
  • City
  • State
  • Zip
  • Phone
  • Phone2
  • Phone3
  • SalesRepID
  • SalesRepCode
  • SalesRepMapTo
  • Currency
  • PaymentTerms
  • ReqPO
  • OnHold
  • CODOnly
  • IsDead
  • IsBankrupt
  • IsNeverAgain
  • CreditLimit
  • CustIDNo
  • BlanketPO
  • Tax1ExemptNo
  • Tax2ExemptNo
  • Tax3ExemptNo
  • Fax
  • EMail
  • WebPage

Bookmark the permalink.

Comments are closed.