INTEGRATOR Cost Update is an INTEGRATOR utility that lets you update FORMULATOR raw material costs from an external ERP system or database.
Cost Update uses the INTEGRATOR.INI file to provide configuration parameters, to connect directly with ERP data. Interfaces to most ERP systems may be accomplished via manipulation of the INTEGRATOR.INI file.
The Cost Update may be run in batch mode, by adding the parameter “/auto” to the end of the command line.
Cost Update will update FORMULATOR’s raw material costs using the machine’s default FORMULATOR database. Be certain you select your preferred database in FORMULATOR prior to running Cost Update. This step needs to run only once to establish the desired FORMULATOR target for the cost update.
The Cost Update configuration within the INTEGRATOR.INI file uses [COST IN] as the paragraph identifier. A sample file is listed below:
[COST IN]
; Connection method 1 – uses DSN
ODBC=ABC
UserName=
Password=
; Connection method 2 – named host
HOST=MyNTHost
Database=MyERPDatabase
NTAUTH=Y
; Two authentication methods are available
NTAUTH=Y
; or
UserName=
Password=
; Where is the cost data located?
; SQL table name
CostTable=INLOC
; Column with the item code number or primary key
CostCodeColumn=Itemkey
; Additional parameters to append to the WHERE clause
CostKey Location = 'PLANT'
; Column with the actual cost data
CostColumn=Lstcost
; If the first column is null or zero, consider up to 2 more columns
CostColumn2=Avgcost
CostColumn3=Stdcost
; How are the units identified?
;UnitTable=
;UnitCodeColumn=
;UnitColumn=
; UnitMap Lb=W
;UnitMap Gal=V
; In this case all units are FORMULATOR unit ‘Lb’
CostUnit=Lb
NoBrackets=True
StripLeadingZeroes=True
The settings are as follows:
ODBC= Set up an ODBC connection to the database providing the costs, and enter it here. Or, set up a connection using
HOST=MyWindowsHostName
DATABASE=MyERPDatabaseName
UserName=,
Password= are only required if your database requires user names and passwords; otherwise, use
NTAUTH=Y
For Windows authentication.
CostTable= Name of the database table where cost is located.
CostCodeColumn= Name of the column in the cost table where the item code (to match FORMULATOR’s item code) is held. Cost is updated via the use of matching item codes.
CostKey Col=Val Many cost tables hold more than one instance of a raw material. To uniquely identify the desired record, add CostKey directives of the form “DatabaseColumnName = Value to match”. You may use an unlimited number of CostKey directives. “Value to match” must be enclosed in single parentheses if it is not held as a number in your ERP system (see example above).
CostColumn= Name of the column where the cost is located.
Additional cost columns may be considered, if the first column is zero or null. Use CostColumn2=
and CostColumn3= for up to two additional columns of cost data.
Cost units of measure may be a constant, or may be drawn from another table in your ERP
system.
CostUnit= If all costs in the cost table are in a single unit of measure, then enter FORMULATOR’s matching unit here. All costs will be assigned this unit in FORMULATOR. It must match FORMULATOR’s “Usual Description”.
If you are unable to use CostUnit (because your ERP units are mixed) then you must specify the
table and column for the unit of measure, plus any mapping.
UnitTable= Name of the database table where cost unit is located.
UnitCodeColumn= Name of the column in the table where the item code is held (to match FORMULATOR’s item code).
UnitColumn= Name of the column where the cost unit is located.
UnitMap FomulatorUnit=ERPUnit Equate FORMULATOR units with ERP units of measure.
ODBC Drivers lacking delimiter support (Pervasive, others):
You must use the NoBrackets=True option to prevent the interface from placing your table and column names in brackets.
NoBrackets=True
Customizing the Main Menu
At the end of the INTEGRATOR.INI file, add the following section:
[ERP]
ERPMenu=ERP system name Integration
ERPCostUpdateMenu=Update Costs from ERP system name
ERPOutputMenu=Send Formulas to ERP system name
Instead of “ERP system name”, insert the name of the target ERP system.
Importing Zero Costs
ImportZeroCosts=1
Command line parameter options
/auto Batch mode
FORMULATOR Connection
FMDSN=”DSN” SQL or MySQL DSN
FMHOST=”FM Host” SQL or MySQL Host Name
FMDBTYPE=”MySQL” Indicates MySQL (as opposed to default of Microsoft)
FMUID=”Me” (optional) User ID
FMPWD=”12345” (optional) Password
Accounting System Host Connection
ERPHOST=”My Host” ERP database host name
ERPDBNAME=”My Database” ERP database name
ERPDBTYPE={“MSSQL” | “AS400” | “DSN” } Type of connection
ERPSECNTAUTH=”Y” “Y” ONLY – indicates NT security on MS-SQL database
ERPUID=”Me” (optional) Database user ID
ERPPWD=”12345” (optional) Database password