Sage X3 Web Services Integration with Formulator

Written by RKL Team | Dec 17, 2013 2:11:40 PM

We continue to hear about the need for Sage X3 web services integration with Formulator™. As we like to say here at RKL eSolutions, "Don't be part of the problem be part of the solution". So our software development team created this functionality to have it in our bag of tricks to meet the various industry requirements and regulatory needs that some of our clients face. These include Chemical, Nutrient, Cosmetics and Pharmaceutical.

What is Formulater for Sage X3?

Formulator™ is a 3^rd party product from Formulator Software, LLC.

[feature_box_creator style="1"]Note: We are not a part of this company. You should use your own due diligence to determine if this product is right for you and your company. We mention it here due to the amount of questions we continue to receive.[/feature_box_creator]

We did this process in 3 distinct steps to make sure we understood the information and the files that Formulator exchanged with Sage ERP X3.

Manual Import/Export with flat files
Batch Process to run the Manual process created in step 1, automation of the manual process, so to speak
Sage ERP X3 Web Services

We are going to focus on the 3^rd option in this blog post. We always stress that we can automate anything if you can outline for us how you do this manually, that was the real purpose of the first 2 steps.

Create an Import Sequence for Formulator Information

Create Import Sequence to process the Products, Sites, Standard Costs and Bill of Materials.

Setup > Usage > Imports / Exports > Sequences

Create Sub Program to call Import Sequence

Development > Processes > Processes > Process editor

Create the Sub Program Code

Create Sub Program EXECSEQ to call an Import Sequence. The Import Sequence Code will be passed to the Sub Program as a parameter.

############################################################################################ ### Subprog principal ############################################################################################ Subprog EXECSEQ(SEQID) Value Char SEQID

Gosub INITWS
Gosub OUVRE From GIMPENCH

If !clalev([M:DIA]) : Local Mask IMPENCH [DIA] : Endif

Raz [DIA]
[DIA]CODE = SEQID
Gosub EXEC From GIMPENCH

Call ECR_TRACE ("Name of the sequence code:"-[DIA]CODE,0) From GESECRAN

#Call AERROR(AERROR,2,"$HEAD") From ASTRSUB : GOK =0

End

#######################################################
$INITWS
Global Integer I , J, NBFIC, IMPSTA
Global Char MESSAGE(80)
Global Char NOMFIC(GDIMFIC) , FICBAS(GDIMFIC)
Global Char REPERT(GDIMFIC)
Global Char MACHINE(GDIMFIC)
Global Char TIT(50)
TIT = ""
Return

Define the Subprogram

Development > Processes > Processes > Subprograms

Publish as a Web Service

From the Subprograms screen above, click "Publication".

Name the Web Service

This is the Publication name. We are naming this one ZIMPORT. We use the leading "Z" to follow best practices with Sage ERP X3 development.

The Web Service is now available with consumption by an external application

Create the .Net program to Call the Import Process

The code below will call the Web Service created above and process the Import Sequence. It can be added as an ".exe" call from the Formulator application to import files generated from formulator into Sage ERP X3. Formulator Software has added a button to their application to call this .Net program. You must have the proper license from them in order for this button to be activated. This requires that you purchase the Formulator Integrator module.

.Net Program Code

private void SetContext() { // Initialize connection values callContext = new X3WebService.CAdxCallContext(); webService = new X3WebService.CAdxWebServiceXmlCCService(); paramKey = new X3WebService.CAdxParamKeyValue[1]; resultXML = new X3WebService.CAdxResultXml();

callContext.codeLang = Properties.Settings.Default.X3Language; // Connection language
callContext.codeUser = Properties.Settings.Default.X3User; // X3 user
callContext.password = Properties.Settings.Default.X3Pwd; // X3 password
callContext.poolAlias = Properties.Settings.Default.X3WSPool; // Connection pool name
}

private void DoX3Import(string importName)
{
_success = true;
_result = "";

try
{
// Initialize Connection Values
SetContext();

// Complete call context
callContext.requestConfig = "adxwss.trace.on=off&adxwss.trace.size=16384&adonix.trace.on=off&adonix.trace.level=3&adonix.trace.size=8";

// Build Input XML with Parameters
StringBuilder xmlInput = new StringBuilder("");
xmlInput.Append("");
xmlInput.AppendFormat("{1}", "SEQID", importName);
xmlInput.Append("");

// Call web service
resultXML = webService.run(callContext, WEB_SERVICE_NAME, xmlInput.ToString());

foreach (X3WebService.CAdxMessage msg in resultXML.messages)
{
_result += msg.message.ToString() + "^";
}

if (_result.Length > 0) { _success = false; }
}
catch (Exception ex)
{
_success = false;
_result = ex.ToString();
}
}

.Net Program Settings

The settings below are used by the .Net program calling the interface.

X3WSPool – This is the Sage ERP X3 Web Service Pool as defined during setup
X3Language – This is the Sage ERP X3 Language to use
X3User – This is the Sage ERP X3 User ID to use for authentication
X3Pwd – This is the Sage ERP X3 User Password to use for authentication
FileDirIn – This is the directory for the files to be processed. This should match the path defined in the Import Sequence in Sage ERP X3.
FileDirOut – This is the directory where processed files will be moved to
SequenceName – This is the Name of the Import Sequence defined in Sage ERP X3.
LogFileDir – This is the directory where log files will be written
FileNames – These are the file names that will be processed. These should match the files defined in the Sage ERP X3 Import Sequence. They are used here to verify that the files exist before attempting to do the import, and to move the files to the processed directory.

FormulatorImport_X3WebService_CAdxWebServiceXmlCCService - This is the url for the Sage ERP X3 Web Services based on the above contrived example.