Intended Audience:
The document has been designed to explain the VISCO API Integration opportunities and procedures that are currently available. It is intended to be read by project managers and developers looking to integrate their software with the VISCO Software Application.
Overview:
The VISCO Application Programming Interface (API) has been designed with industry standard RESTful techniques to receive and deliver information using formatted JavaScript Object Notation (JSON) in both the push and pull of data. By passing a URL to the browser within your application, you can make a direct call to our Web Service using syntax described further in the “Technical Details” section.
Where our API differs from many others however, is that the “POST, GET, PUT, DELETE” verbiage is replaced with TSQL stored procedure calls that are passed within the URL itself, including parameters for each of the procedures as needed. The explanation for this is two-fold. First, the TSQL language is shared heavily among our development staff and is used as a common form of direct database manipulation leaving the bare minimum of resources required by the web server or client machines to perform the appropriate database functions. Second, the ability to design and develop custom stored procedures for each API integration with VISCO gives both you, the developer, and the VISCO team a chance to work together on previously unimagined data interactions and touchpoints with each new VISCO integration.
Our approach to developing this API was to ensure that no option was left unaccounted for. With the ability to “create your own integration” using this methodology, the possibilities are ultimately endless within the very simple guidelines as highlighted below.
Part 1: How to get started
Establishing an integration with the VISCO API will require certain things be in place before you can start coding and testing. A list of things to do before you build you first VISCO API project includes
- Read through this documentation and gather any questions you may have about the integration.
- Contact VISCO Support (support@viscosoftware.com) notifying them of your plans to integrate and a rough outline, perhaps a few sentences, of what that integration will entail. This is also a good chance to pass along any questions coming from this documentation.
- Confirm with VISCO Support that the VISCO customer you are looking to integrate with has an externally accessible database so that procedures can be called directly from our web service on through to the VISCO customer’s database(s). Since some of our customers run in the traditional Client/Server environment, an externally accessible database in not a guarantee without some customer IT involvement in those cases.
- Confirm with VISCO Support the Base URL as described in Technical Details section 1 of Part 2 below.
- Request and keep the SQL credentials provided by VISCO, specific to your integration testing. SQL Credentials provided will NOT enable you to update real-time VISCO data until the integration code is live at which point the credentials will be configured according to the specific needs of the integration.
- Request and keep the Customer Specific API Key. Each VISCO Customer has a unique key assigned to them. Before you start accessing and updating information in that customer’s database, you’ll need the API Key. That API Key will need to be passed using the Authorization Header technique within your code. Please contact the VISCO support team and we will provide the API Key for your specific customer integration. If you intend to build an integration that will support many customers, you are going to want to store the API Key for each customer in a flat file or similar for easy access with your code. For more information on how to apply key information into an Authorization Header, review the article below
https://en.wikipedia.org/wiki/List_of_HTTP_header_fields
Part 2: Technical Details
VISCO runs a web service that accepts JSON and converts that JSON into XML based on a procedure call that is specified by the developer calling the web service. Since the web service can and will accept a number of parameters for each procedure, the intelligence of the code it is going to accept is actually built into the stored procedures themselves.
Each VISCO API setup means building a unique URL. Once that URL is passed into the browser, the web service will respond accordingly. The response can include (1) Success/Fail confirmation of an insert/update call in the procedure or (2) will return results from a SQL query. There are then, several questions to ask when deciding how and in what form the URL your API call will entail:
Section 1: What is the base URL?
The first part of any VISCO API call is the base URL. The base URL for the VISCO API is
https://viscowebservice.com/TheVISCOCustomerApplicationName/viscomobile.aspx
Every API call will include this base URL, so make sure you have the right application name before you start testing.
Section 2: What actions can I perform through the API?
The API is divided into “actions.” In version 1.0 of the API, only one action is available. This action should appear in your URL as “V_RunProcedure_Javascript.” Therefore, the new URL we are building will look like this so far
https://viscowebservice.com/TheVISCOCustomerApplicationName/viscomobile.aspxx?action=V_RunProcedure_Javascript
Section 3: What SQL Procedure should I run?
This is where things get quite interesting. VISCO has developed “canned” procedures, each requiring specific parameters be passed in to have a successful SQL procedure call. The canned procedures will now be defined further. Keep in mind, this list is always growing so check back with VISCO to see if new procedures (and hence, new “ready-made” chances to integrate) have become available recently.
(1) Procedure Name: V_GetKeyFromID
Purpose: This procedure is used to identify which numeric key corresponds to the primary ID available for a given Data Target in VISCO. The numeric key is used for any and all additional API calls. For example: Product Code ‘ABCXYZ’ returns ProductKey = 1000.
Available Parameters:
@XMLInput [Any JSON passed into this API call will be converted to XML and used in the procedure]
@DataTarget [Text values up to 50 characters can be passed into this parameter]
Data Targets Available:
Product, Customer, Vendor, Container, Delivery
URL and Associated JSON
https://viscowebservice.com/chem/viscomobile.aspx?action=V_RunProcedure_Javascript&procedurename=V_GetKeyFromID&DataTarget=Product
[{“ID”:”ABCXYZ”}]
Returns JSON
[{“1000”}]
(2) Procedure Name: V_GetDataTargetDetails
Purpose: This procedure is designed to query the VISCO database for all VISCO entities using a “get/select” call specifically to retrieve information.
Available Parameters:
@Key1 [Only Numeric values can be passed into this parameter]
@Key2 [Only Numeric values can be passed into this parameter]
@Key3 [Only Numeric values can be passed into this parameter]
@DataTarget [Text values up to 50 characters can be passed into this parameter]
Data Targets Available:
Product: Returns all available information about a specific product where Product Key is passed into @Key1. @Key2 and @Key3 can be ignored
Customer: Returns all available information about a specific customer where Customer Key is passed into @Key1. @Key2 and @Key3 can be ignored
Vendor: Returns all available information about a specific vendor where Vendor Key is passed into @Key1. @Key2 and @Key3 can be ignored
SalesOrder: Returns all available information about a specific Sales Order where Sales Order Number is passed into @Key1. @Key2 and @Key3 can be ignored
SalesOrderLine: Returns all available information about a specific Sales Order Line where Sales Order Number is passed into @Key1, Sales Order Line Number is passed into @Key2 and @Key3 can be ignored
SalesOrderShipment: Returns all available information about a specific Sales Order Shipment where Sales Order Number is passed into @Key1, Sales Order Line Number is passed into @Key2 and Sales Order Shipment Number is passed into @Key3.
PurchaseOrder: Returns all available information about a specific Purchase Order where Purchase Order Number is passed into @Key1. @Key2 and @Key3 can be ignored
PurchaseOrderLine: Returns all available information about a specific Purchase Order Line where Purchase Order Number is passed into @Key1, Purchase Order Line Number is passed into @Key2 and @Key3 can be ignored
PurchaseOrderShipment: Returns all available information about a specific Purchase Order Shipment where Purchase Order Number is passed into @Key1, Purchase Order Line Number is passed into @Key2 and Purchase Order Shipment Number is passed into @Key3.
Venture: Returns all available information about a specific Venture, where PO Number = @Key1, PO Line Number = @Key2 and PO Shipment Number = @Key3.
Container: Returns all available information about a specific Container where Container Key is passed into @Key1. @Key2 and @Key3 can be ignored
Delivery: Returns all available information about a specific Delivery where Delivery Key is passed into @Key1. @Key2 and @Key3 can be ignored
Quote: Returns all available information about a specific Quote where Quote Number is passed into @Key1. @Key2 and @Key3 can be ignored
QuoteLine: Returns all available information about a specific Quote Line where Quote Number is passed into @Key1, Quote Line Number is passed into @Key2 and @Key3 can be ignored
QuoteShipment: Returns all available information about a specific Quote Shipment where Quote Number is passed into @Key1, Quote Line Number is passed into @Key2 and Quote Shipment Number is passed into @Key3.
With Procedure and Parameters defined, our URL will look like this.
https://viscowebservice.com/chem/viscomobile.aspx?action=V_RunProcedure_Javascript&procedurename=V_GetDataTargetDetails&Key1=ProductID&Key2=0&Key3=0&DataTarget=Product
Part 3: Examples
Below are a couple of examples of what is returned when the above procedure is called.
When you pass this URL to the browser (where “CHEM” is a test install we use for such purposes)
https://viscowebservice.com/chem/viscomobile.aspx?action=V_RunProcedure_Javascript&procedurename=V_GetTabs&DataTarget=SalesOrder&Key1=44&UserName=VISCO&Key2=0&Key3=0
The following JSON is returned
[{“CategoryKey”:”50050″,”Description”:”Documents and Images”,”DisplayOrder”:”1″,”URLString”:”SOPrepare.html#?Key1=44&Key2=0&Key3=0&DataTarget=SalesOrder&CategoryKey=50050&UserName=VISCO&img=x&page=items”},{“CategoryKey”:”50040″,”Description”:”Line Detail”,”DisplayOrder”:”2″,”URLString”:”SOPrepare.html#?Key1=44&Key2=0&Key3=0&DataTarget=SalesOrder&CategoryKey=50040&UserName=VISCO&img=x&page=items”},{“CategoryKey”:”50020″,”Description”:”Terms & Remarks”,”DisplayOrder”:”4″,”URLString”:”SOPrepare.html#?Key1=44&Key2=0&Key3=0&DataTarget=SalesOrder&CategoryKey=50020&UserName=VISCO&img=x&page=items”},{“CategoryKey”:”50010″,”Description”:”General Information”,”DisplayOrder”:”5″,”URLString”:”SOPrepare.html#?Key1=44&Key2=0&Key3=0&DataTarget=SalesOrder&CategoryKey=50010&UserName=VISCO&img=x&page=items”}]
When you pass this URL to the browser (where “CHEM” is a test install we use for such purposes)
https://viscowebservice.com/chem/viscomobile.aspx?action=V_RunProcedure_Javascript&procedurename=V_GetDataTargetDetails&DataTarget=Customer&Key1=6553&UserName=VISCO&Key2=0&Key3=0
The following JSON is returned
[{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”90″,”CustomFieldNumber”:”10″,”DataTarget”:”Customer”,”FieldName”:”Credit Information”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”90″,”CustomFieldNumber”:”20″,”DataTarget”:”Customer”,”FieldName”:”Company Information”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”90″,”CustomFieldNumber”:”30″,”DataTarget”:”Customer”,”FieldName”:”Financial Information”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provi
ded”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3010″,”DataTarget”:”Customer”,”FieldName”:”Customer Address”,”Value”:[{“Name”:”3 Brothers”,”AddressLine1″:”PO Box 1201″,”AddressLine2″:”24100 Bergamo”,”AddressLine3″:”ITALY”,”AddressLine4″:””,”City”:””,”State”:””,”Zip”:””,”Country”:”ITALY”}]},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3020″,”DataTarget”:”Customer”,”FieldName”:”Bill to Address same as Customer Address”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3030″,”DataTarget”:”Customer”,”FieldName”:”Bill To Address”,”Value”:[{“Name”:”3BROS”,”AddressLine1″:”PO Box 1201″,”AddressLine2″:”24100 BERGAMO”,”AddressLine3″:”ITALY”,”AddressLine4″:””,”City”:””,”State”:””,”Zip”:””,”Country”:”ITALY”}]},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3040″,”DataTarget”:”Customer”,”FieldName”:”Company Phone”,”Value”:”phone there”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3050″,”DataTarget”:”Customer”,”FieldName”:”Company Fax”,”Value”:”fax there”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3060″,”DataTarget”:”Customer”,”FieldName”:”Company Email”,”Value”:”emailthere@test.com”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3070″,”DataTarget”:”Customer”,”FieldName”:”Company WebSite”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3080″,”DataTarget”:”Customer”,”FieldName”:”Discount Percent”,”Value”:”0.00000″},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3090″,”DataTarget”:”Customer”,”FieldName”:”Payment Terms”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3110″,”DataTarget”:”Customer”,”FieldName”:”Credit Limit”,”Value”:”0.00000″},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3120″,”DataTarget”:”Customer”,”FieldName”:”Tax State”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30000″,”CustomFieldNumber”:”3130″,”DataTarget”:”Customer”,”FieldName”:”Tax Zip”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30010″,”CustomFieldNumber”:”10″,”DataTarget”:”Customer”,”FieldName”:”Include all other product Specifications”,”Value”:”No”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30010″,”CustomFieldNumber”:”20″,”DataTarget”:”Customer”,”FieldName”:”Include Producer Name”,”Value”:”No”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30010″,”CustomFieldNumber”:”30″,”DataTarget”:”Customer”,”FieldName”:”Include Customer Specs”,”Value”:”No”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30020″,”CustomFieldNumber”:”10″,”DataTarget”:”Customer”,”FieldName”:”Comments”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30020″,”CustomFieldNumber”:”20″,”DataTarget”:”Customer”,”FieldName”:”Customer Level”,”Value”:”Not Applicable”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30020″,”CustomFieldNumber”:”30″,”DataTarget”:”Customer”,”FieldName”:”Region”,”Value”:”West
Coast”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30020″,”CustomFieldNumber”:”40″,”DataTarget”:”Customer”,”FieldName”:”Type”,”Value”:”Distributor”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”30020″,”CustomFieldNumber”:”50″,”DataTarget”:”Customer”,”FieldName”:”Industry”,”Value”:”Seafood”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”0″,”CustomFieldNumber”:”2055″,”DataTarget”:”Customer”,”FieldName”:”Accounting”,”Value”:”3BROS”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”0″,”CustomFieldNumber”:”2060″,”DataTarget”:”Customer”,”FieldName”:”Active?”,”Value”:”Yes”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”0″,”CustomFieldNumber”:”2070″,”DataTarget”:”Customer”,”FieldName”:”Last Changed On”,”Value”:”Dec 4 2014 12:21PM”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”0″,”CustomFieldNumber”:”2080″,”DataTarget”:”Customer”,”FieldName”:”By”,”Value”:”VISCO”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”0″,”CustomFieldNumber”:”2090″,”DataTarget”:”Customer”,”FieldName”:”Customer to Copy”,”Value”:””},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”0″,”CustomFieldNumber”:”2000″,”DataTarget”:”Customer”,”FieldName”:”Customer Id”,”Value”:”3BROS”},{“Key1Provided”:”6553″,”Key2Provided”:”0″,”Key3Provided”:”0″,”CustomCategoryKey”:”0″,”CustomFieldNumber”:”3100″,”DataTarget”:”Customer”,”FieldName”:”Default Trader”,”Value”:”TPeck”}]
Angular 2 Code Sample for SQL Call and Authorization Header:
postdata_returnarray() { let headers = new http_1.Headers({ ‘Content-Type’: ‘text/html; charset=utf-8’ }); this.createAuthorizationHeader(headers); let options = new http_1.RequestOptions({ headers: headers }); let body = ‘[‘ + JSON.stringify(this.data) + ‘]’; return this._http.post(this.baseUrl + ‘/VISCOMobile.aspx?token=yes&UserName=VISCO&action=V_RunProcedure_Javascript&ProcedureName=’ + this.iprocedurename + ‘&DataTarget=’ + this.iDataTarget + ‘&Key1=’ + this.iKey1 + ‘&Key2=’ + this.iKey2 + ‘&Key3=’ + this.iKey3 + ‘&CategoryKey=’ + this.iCategoryKey, body, headers) .map(this.extractArray) .catch((res) => this.handleError(res)); } createAuthorizationHeader(headers) { headers.append(‘Authorization’, ‘Basic ‘ + btoa(‘username:password’)); }
Feeling Creative?
Contact the staff at VISCO Support directly (845-383-3800 or spport@viscosoftware.com) to discuss some of our integrations in more detail. Below is a subset of integrations we’ve worked on and we’d love to describe the unique ways we used either the VISCO API or other APIs our developers have worked with, to accomplish a data exchange with another cloud based software.
Examples of how our API has already been used
VISCO Mobile v.2: The VISCO API was originally designed and developed for the VISCO Mobile App to interact directly with the database. When it became clear that the API could be written in such a manner that it allowed any application to interact with the database using the web service, we expanded the possibilities by adding the procedure call method we now use.
TradeLanes: (http://tradelanes.co/) For this integration, we created a custom procedure that accepts real time data pushes from Freight Forwarding companies with up to the minute status information about inbound containers. We log each entry coming, update our own ETA and ETD information for the associated containers and create a report highlighting any status change we’ve received. TradeLanes built out their API calls and continues to work with us on improving those calls and building out our API.
