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 3rd 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 3rd 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.
We're Here to Help
Need Help Figuring It All Out? Contact Us with any questions about upgrading, updating, integrating, and everything in between!
