API Developers Guide: Difference between revisions

From RangerMSP Wiki - PSA software for MSPs and IT services providers
Jump to navigation Jump to search
 
(140 intermediate revisions by 6 users not shown)
Line 1: Line 1:
CommitCRM provides various tools in order to read/add/update information in the Commit
{{usermanuals}}
database. These tools include: Programming API and API by Email for updating the
 
database, and an ODBC Link for pulling information from the database.
=Introduction=
The Commit API provides developers with a set of APIs which allow them to perform
RangerMSP provides various tools in order to read/add/update information in the RangerMSP database.  
database transactions in order to add and update records in the Commit database. The
 
API can be used as a Programming API by embedding code which uses the API functions,
These tools include:  
or as an API by Email which allows you to send XML formatted emails which will be
*'''Programming API (recommended)'''<br>The RangerMSP Programming API provides developers with a set of API functions which can be used in CSharp, VB.NET and C++. The Programming API libraries provide easy-to-use classes and objects which make the development very simple and friendly.
processed and executed.
* '''Low-level Programming API''' <br>Provides low-level functions for programmers using other languages, such as VB, Delphi or any other programming language that lets you use a standard Windows dll file. The Programming API includes two sets of functions: Data-Retrieval API and Data-Update API.  
*'''Programming API'''<br>The Commit API provides developers with a set of API functions which can be used in VB, C++, Delphi and any other programming language that lets you use a standard Windows dll file. It provides the ability to create a connection to Commit's database, add new records to the database and update them.
*'''API by Email'''<br>The API by Email allows you to send XML formatted emails which contain database transactions. RangerMSP Email Connector then pulls the incoming XML emails from your POP3 mail server, processes the email and performs the transactions in the XML transaction. XML formatted email gives you access to the Data-Update API, which allows you to perform many actions such as linking to external systems, receiving forms submitted from your web site, and more.
*'''API by Email'''<br>The API by Email allows you to send XML formatted emails which contain database transactions. Commit Email Connector then pulls the incoming XML emails from your POP3 mail server, processes the email and performs the transactions in the XML transaction. XML formatted email gives you an API for the Commit database, which allows you to perform many actions such as linking to external systems, receiving forms submitted from your web site, and more.
*'''Web API'''<br>The Web API allows you to interact with your RangerMSP server and data over the Web using HTTP.
*'''ODBC Link'''<br>While the API provides the means for adding and updating information in the database, you should use ODBC Link in order to read information from the database. Commit uses an open database structure and allows other applications to access the database using ODBC (Open Data Base Connectivity) for '''read-only''' purposes.<br><br>You can use ODBC Link in order to read data from the database and integrate it with an external system (i.e. Crystal reports, or any other application). You may also use the ODBC Link to read Database Record IDs and update existing records. This will be discussed later in this document.
*'''ODBC Link'''<br>In addition to the Data Retrieval API, there is an option of using ODBC to read data directly from the database. RangerMSP uses an open database structure and allows other applications to access the database using ODBC (Open Data Base Connectivity) for '''read-only''' purposes. The ODBC link should not be used from programs you develop (you should use the data retrieval API for this purpose). You can use ODBC Link in order to read data from the database and integrate it with an external system (i.e. Crystal reports, or any other application).




__TOC__
__TOC__
==Glossary==
*'''Commit API (Application Programming Interface)''' - CommitCRM provides an API which allows you to <u>add and update</u> records in the Commit database. The API includes a list of possible actions and parameters.


*'''ODBC Link (Open Data Base Connectivity)''' - ODBC is a function library which provides a common API for ODBC compliant databases. Commit provides an open database and allows other applications to access the database using ODBC for '''read-only''' purposes.
=Glossary=
*'''RangerMSP API (Application Programming Interface)''' - RangerMSP provides an API which allows you to <u>add and update</u> records in the RangerMSP database. The API includes a list of possible actions and parameters.
 
*'''ODBC Link (Open Data Base Connectivity)''' - ODBC is a function library which provides a common API for ODBC compliant databases. RangerMSP provides an open database and allows other applications to access the database using ODBC for '''read-only''' purposes.


*'''Record''' - Each entity in the Commit database (such as Ticket, Account, Asset, Item, Appointment, Task, etc.) is considered a "record".
*'''Record''' - Each entity in the RangerMSP database (such as Ticket, Account, Asset, Item, Appointment, Task, etc.) is considered a "record".


*'''Database fields''' - Each record contains a list of fields, each of which holds the record's data.
*'''Database fields''' - Each record contains a list of fields, each of which holds the record's data.
Line 26: Line 27:
*'''Transaction Parameters''' - Each transaction contains the parameters for performing the transaction: record type, list of database fields and a list of the values to be updated in these fields.
*'''Transaction Parameters''' - Each transaction contains the parameters for performing the transaction: record type, list of database fields and a list of the values to be updated in these fields.


*'''External Application''' - This refers to the external program which integrates with Commit in order to update the database. This name should be passed when performing updates to the database, and it will be saved in the Commit record to indicate who performed the changes.
*'''External Application''' - This refers to the external program which integrates with RangerMSP in order to update the database. This name should be passed when performing updates to the database, and it will be saved in the RangerMSP record to indicate who performed the changes.
 
*'''Record ID (REC ID)''' - Each record in Commit has a '''unique Record ID''' which is created when adding the record to the database. Every ID is 20 characters and should be transferred with the API transaction when you need to update an existing record.
 
==Using Commit API==
Commit API allows you to add and update records in the Commit database, such as
Accounts, Tickets, Assets, Items, Appointments, Tasks and more.
 
The updates are performed using transactions, where each transaction includes the
operation you wish to perform, the data type you wish to add/update, and any additional
parameters (i.e. record type, database fields and their values).
 
Every record added to the system is assigned a unique Record ID (REC ID) which is the
database identifier for this record.
 
The Commit API allows you to perform the following operations.
 
*'''Add new records''' - if no Record ID is passed in the transaction, the API assumes that this is a new record, adds it to the system and creates a new Record ID for it.


*'''Update existing records''' - if the record ID is passed in the transaction, and the record exists, the system will update the record with the information in the transaction.
*'''Record ID (REC ID)''' - Each record in RangerMSP has a '''unique Record ID''' which is created when adding the record to the database. Every ID is 20 characters and should be transferred with the API transaction when you need to update an existing record.


In both cases, the system will try to set default values for fields which do not exist in the
<br>
incoming transaction. If it fails, the action will fail with an error.


Note that in order to '''read data''' from the Commit database, you should use the ODBC
=Using API=
Link, which provides a read-only link for the Commit database. See more details in
[[Commit API Reference Manual#Retrieving data using ODBC|Retrieving data using ODBC]].


===Workflow===
==Workflow==
Following is a sample work-flow, which demonstrates the process of adding a new Account, and then adding a new Ticket to this Account, using the Commit API.
Following is a sample work-flow, which demonstrates the process of adding a new Account, and then adding a new Ticket to this Account, using the RangerMSP API.


Note that the API can return two parameters to the External Application:
Note that the API can return two parameters to the External Application:
#The Commit REC ID - when adding new records to the database, this will contains the new record ID.
#The RangerMSP REC ID - when adding new records to the database, this will contains the new record ID.
#The ExternalTransactionID (relevant for XML transaction only) - In case the external application passes a parameter to be returned with the response, the ExternalTransactionID will be returned as-is to in the response.  
#The ExternalTransactionID (relevant for XML transaction only) - In case the external application passes a parameter to be returned with the response, the ExternalTransactionID will be returned as-is to in the response.  


Line 66: Line 47:
[[File:flow.gif|center]]
[[File:flow.gif|center]]


===Updating existing records===
==Updating existing records==
In order to update an already existing record in the database (e.g. add a Ticket to an
In order to update an already existing record in the database (e.g. add a Ticket to an
Account, update an Account's phone number, add a new Charge to an existing Ticket,
Account, update an Account's phone number, add a new Charge to an existing Ticket,
Line 75: Line 56:
The Record ID can be obtained in the following ways:
The Record ID can be obtained in the following ways:
#'''API Response''' (as in the flow above) - You can extract the newly created Record ID from the Email Response when adding new records with the API. This requires the developer to process the API responses, and extract the returning REC ID for each newly added record.
#'''API Response''' (as in the flow above) - You can extract the newly created Record ID from the Email Response when adding new records with the API. This requires the developer to process the API responses, and extract the returning REC ID for each newly added record.
#'''ODBC Link''' - Use the ODBC Link to find relevant records (Accounts, Tickets, etc.), and read all information from the database, including the unique Record ID.
#'''Data Retrieval API''' - Use the Data Retrieval API functions to find relevant records (Accounts, Tickets, etc.), and read all information from the database, including the unique Record ID.
#'''Manually''' - from the Account's <u>Notes</u> tab, at the bottom, right-click the REC ID field and copy it.
#'''Manually''' - from the Account's <u>Notes</u> tab, at the bottom, right-click the REC ID field and copy it.


Line 81: Line 62:
API and update this record.
API and update this record.


===Programming API===
<br>
The Programming API allows you access the Commit database and perform transactions
 
from within a program (using various programming language). This means you can embed
=Programming API=
code which accesses the Commit database from within your own application, and add
The Programming API allows you access the RangerMSP database and perform transactions from within a program (using various programming language). This means you can embed code which accesses the RangerMSP database from within your own application, and add
your own functionality which updates the database.
your own functionality which updates the database.


The programming API executes database transactions. Each transaction uses API methods
The RangerMSP Programming API provides developers with a set of API functions which can be used in CSharp, VB.NET and C++. The Programming API libraries provide easy-to-use classes and objects which make the development very simple and friendly.
provides by the API dll file which is located in the Commit installation. Both the
 
Programming API and the API by Email use the same dll for performing the transactions.
See more details and samples about each programming environments in the following links:
* [[VB.NET_API|VB.Net Programming API]]
* [[CSharp_API|CSharp Programming API]]
* [[C%2B%2B_API|C++ Programming API]]
<br>
 
<br>
 
= Low Level Programming API=
The Low-level programming API provides a way to use the API from development languages which are not supported by the main Programming API.
 
The Low-level programming API executes database transactions. The low-evel API provides two separate dll programs, one for adding/updating data from the database, and one for retrieving data from the database.
 
<br>For the full list of API functions go to [[API_Reference_Manual#API_Functions|API Functions]].
<br>For samples go to [[API Code Samples]].
 
Note:
Retrieving data can be performed either by the API (recommended) or using ODBC when needed. You can read more about it in the  [[API_Developers_Guide#Data_Retrieval|Data Retrieval]] section.
 
==Adding and Updating Data==
RangerMSP API allows you to add and update records in the RangerMSP database, such as Accounts, Tickets, Assets, Items, Appointments, Tasks and more.
 
The updates are performed using transactions, where each transaction includes the operation you wish to perform, the data type you wish to add/update, and any additional parameters (i.e. record type, database fields and their values).
 
Every record added to the system is assigned a unique Record ID (REC ID) which is the database identifier for this record.
 
The RangerMSP API allows you to perform the following operations.
 
*'''Add new records''' - if no Record ID is passed in the transaction, the API assumes that this is a new record, adds it to the system and creates a new Record ID for it.
 
*'''Update existing records''' - if the record ID is passed in the transaction, and the record exists, the system will update the record with the information in the transaction.
 
*'''Retrieve records''' - The Data Retrieval API allows you to perform an SQL Query to get a list of record IDs and retrieve the details for a single record according to a REC ID.
 
Note that in order to '''retrieve data''' using ODBC (e.g. when linking to Crystal Reports), use the ODBC Link, which provides a read-only link for the RangerMSP database. See more details in [[#Data_Retrieval|Retrieving Data]].
 
==Data Retrieval==
RangerMSP uses an open database and the RangerMSP API provides the ability to retrieve data using standard API functions which let you define queries and retrieve data.
 
In addition to the Data Retrieval API, there is an option of using ODBC to read data directly from the database. The ODBC link should not be used from programs you develop (you should use the data retrieval API for this purpose). Use the ODBC link only when you need to access the database from an external program, such as external report application (i.e. Crystal reports, or any other application).
<br>
* Learn more about [[API_Developers_Guide#Data_Retrieval_API|Data Retrieval API]].
* Learn more about [[API_Developers_Guide#ODBC_Data_Retrieval|Data Retrieval using ODBC Link]].
<br>
 
=API by Email (XML transaction)=
The API by Email operates under the RangerMSP Email Connector module, which automatically processes incoming emails arriving at a defined public email address. The Email may contain XML formatted messages which contain API transactions, and provides
a full data update API to RangerMSP.


Note that the API provides the tools needed for adding/updating information in the
The Email Connector pulls incoming emails from your POP3 mail server, and processes the email. When an email containing XML formatted message is detected, the system analyzes the XML content of the email and performs the transactions written in the XML. You may set the Email Connector to send automatic replies in response to XML formatted email.
database. In order to retrieve data, you should use the ODBC link which allows you to
connect to the database and retrieve data directly from the tables. You can read more
about it in the [[Commit API Reference Manual#Retrieving data using ODBC|Retrieving data using ODBC]] section.


You can find more information and samples in the [[Commit API Reference Manual#Code samples|Code samples]] section.
The API by Email supported only the Data Update API (Data Retrieval API is not supported via emails). The API by Email executes database the transactions using the functions provided by the API dll file which is located in the RangerMSP installation. Both the Programming API and the API by Email use the same dll for performing the transactions.


===API by Email (XML transaction)===
Note that the API by Mail is supported only for the Data Update API, allowing you to add and update data. In order to retrieve data, you should use the Data Retrieval Programming API (recommended) or use ODBC, both methods do not support API by Email. You can read more about it in the [[API_Developers_Guide#Data_Retrieval|Data Retrieval]] section.
The API by Email operates under the Commit Email Connector module, which
<br><br>
automatically processes incoming emails arriving at a defined public email address. The
* Learn more about using [[API_by_Email_and_MSP_Integration|API by Email]].  
Email may contain XML formatted messages which contain API transactions, and provides
* Learn more about building the XML transactions for the API by Email in [[API Code Samples#XML samples|XML samples]].
a full API to Commit.
<br>


The Email Connector pulls incoming emails from your POP3 mail server, and processes the
=Web API=
email. When an email containing XML formatted message is detected, the system analyzes
the XML content of the email and performs the transactions written in the XML. You may
set the Email Connector to send automatic replies in response to XML formatted email.


The API by Email executes database transactions. Each transaction uses API methods
The Web API allows you to build applications or addons which interact with your RangerMSP server remotely over the Web. You get access to all the API functions and can query the data or add/edit data (or both) giving you unparallelled control over your data. To use the Web API you need to have the RangerMSP Server service, which is also used by the Email Connector and the Alerts Server modules, installed and running and be using the [[#Programming API|Programming API]].
provided by the API dll file which is located in the Commit installation. Both the
<br><br>
Programming API and the API by Email use the same dll for performing the transactions.


You can find details on connecting remotely using various programming environments here:
<br><br>


Note that the API provides the tools for adding/updating information in the database. In
*[[VB.NET_API#Remote_Server_.28Web_API.29|VB.NET]]
order to retrieve data, you should use the ODBC link which allows you to connect to the
*[[CSharp_API#Remote_Server_.28Web_API.29|CSharp]]
database and retrieve data directly from the tables. You can read more about it in the
*[[C%2B%2B_API#Remote_Server_.28Web_API.29|C++]]
[[Commit API Reference Manual#Retrieving data using ODBC|Retrieving data using ODBC]] section.


You can find more information and samples in [[Commit API Reference Manual#XML samples|XML samples]].
<br>


==Retrieving data using ODBC==
For instructions on how to activate the Web API, go to [[Web API Server Configuration]]
Commit uses an open database and allows other applications to access it using ODBC
<br><br>
 
=Data Retrieval API=
The Data Retrieval API provides API functions which help you execute database queries and receive the results. Each function uses API functions provided by the Retrieval API dll file which is located in the RangerMSP installation. The Data Retrieval API is available only via the Programming API; no API by Email is available for data retrieval.
 
The Data Retrieval API provides the following abilities:
* Execute a database query and receive a result records list.
* Read each record with its fields.
* Find out field properties.
 
See low-level programming API information in [[API_Developers_Guide#Data_Retrieval|Low Level API Data Retrieval]].<br>
See the [[API_Reference_Manual#Data_Retrieval_API_Functions|Data Retrieval API Reference Manual]] for detailed usage description.
<br><br>
 
==ODBC Data Retrieval==
RangerMSP uses an open database and allows other applications to access it using ODBC
(Open Data Base Connectivity) for '''read-only''' purposes. While the API provides the means
(Open Data Base Connectivity) for '''read-only''' purposes. While the API provides the means
for adding and updating information in the database, it is preferred to use ODBC to read
for adding and updating information in the database, it is preferred to use ODBC to read
Line 129: Line 166:
You can use ODBC Link to read data from the database and integrate it with an external
You can use ODBC Link to read data from the database and integrate it with an external
system (i.e. Crystal reports, or any other application). You may also use the ODBC Link in
system (i.e. Crystal reports, or any other application). You may also use the ODBC Link in
order to read the Database Record ID and update existing records (see [[Commit API Reference Manual#Updating existing records|Updating existing records]]).
order to read the Database Record ID and update existing records (see [[API_Developers_Guide#Updating_existing_records|Updating existing records]]).


<u>'''Activation Notes:'''</u>
<u>'''Activation Notes:'''</u>


*ODBC connection is not officially supported yet, and although ODBC itself is well tested, we do not provide "pure ODBC" support.
*The ODBC drivers are well tested and work well, however, please note that the RangerMSP Support team doesn't provide "pure ODBC" support.
 
*NEVER use ODBC to modify or delete data; use it for READ purposes only. Modifying data should be done using the Commit API.


*The ODBC Link can be used with your installed version of Commit, and only requires installing an external driver. Before working with the API we recommend that you '''backup the database'''.
*NEVER use ODBC to modify or delete data; use it for READ purposes only. Modifying data should be done using the RangerMSP API.


*We also suggest that the Commit application <u>not be in use</u> while data is being accessed with ODBC (this includes the CommitWebInterface and CommitServer services if they are in use). This may prevent data sharing issues.
*The ODBC Link can be used with your installed version of RangerMSP, and only requires installing an external driver. Before working with the API we recommend that you '''backup the database'''.




Line 145: Line 180:
{| class="wikitable"
{| class="wikitable"
|-
|-
! '''Commit Record'''
! '''RangerMSP Record'''
! '''Database Table Name'''
! '''Database Table Name'''
|-
|-
Line 179: Line 214:
|}
|}


For the database fields list, please refer to [[Commit API Reference Manual#API Reference Manual|API Reference Manual]] section.
For the database fields list, please refer to [[API_Developers_Guide#API_Reference_Manual|API Reference Manual]] section.
You can find detailed instructions for using the ODBC Link in the [[Commit API Reference Manual#Installing ODBC Driver|Installing ODBC Driver]] section.
You can find detailed instructions for using the ODBC Link in the [[API_Developers_Guide#Installing_ODBC_Driver|Installing ODBC Driver]] section.




Line 199: Line 234:


To install the Advantage ODBC Driver:
To install the Advantage ODBC Driver:
#[http://www.commitcrm.com/downloads/commit-odbc-8-1-0-26.exe Click here] to download the Advantage ODBC setup program.
#Download the ODBC driver version that corresponds with '''your''' Advantage SQL Database Server version from the following links: (The Advantage Version number can be found in the [[Media:ADS_Version.jpg|Advantage Configuration Utility]] located on the RangerMSP Server)<br> '''[http://{{SERVERNAME}}/downloads/crm-odbc-11-10-0-10-64bit.exe ODBC Driver Version 11.10 – 64 bit]<br> [http://{{SERVERNAME}}/downloads/crm-odbc-11-10-0-10.exe ODBC Driver Version 11.10 – 32 bit]<br>[http://{{SERVERNAME}}/downloads/crm-odbc-10-10-0-28.exe ODBC Driver Version 10.10 – 32 bit]<br> [http://{{SERVERNAME}}/downloads/crm-odbc-10-0-0-3.exe ODBC Driver Version 10.0 – 32 bit]<br> [http://{{SERVERNAME}}/downloads/crm-odbc-9-0-0-7.exe ODBC Driver Version 9.0 – 32 bit]<br> [http://{{SERVERNAME}}/downloads/crm-odbc-8-1-0-26.exe ODBC Driver Version 8.1 – 32 bit] (**Also for systems that do not use the RangerMSP SQL Database)<br> '''
#Run the Setup program.
#Run the Setup program.
#Proceed through the setup windows to complete installation.
#Proceed through the setup windows to complete installation.
'''**DISCLAIMER: Using the ODBC connector without the RangerMSP SQL Database is not recommended, since the chances for Database Corruption would be elevated.'''


<u>'''Data Source Setup for Windows'''</u><br>
<u>'''Data Source Setup for Windows'''</u><br>
Line 219: Line 255:
#From the ODBC Administrator, click Add.
#From the ODBC Administrator, click Add.
#Highlight the Advantage SQL ODBC line, and click OK.
#Highlight the Advantage SQL ODBC line, and click OK.
#Type a unique data source name. For example, type AdvData. This name is used by applications to reference the data source.
#Type a unique data source name. For example, type RangerMSPData. This name is used by applications to reference the data source.
#Specify the database or data dictionary path. Type a valid path name to a mapped drive on the server where the Advantage Database Server is installed or the path and file name of your Advantage Data Dictionary. Click Browse to select a database path or check the Data Dictionary check box and then browse to select a data dictionary file. Note Multiple Advantage data sources may need to be defined for your environment. If different settings are needed for ODBC connections in one application, separate data sources may be required.
#Specify the path to the database - point to folder: \RangerMSP\Db (do '''not''' use the data dictionary option). Click Browse to select a database path. Note Multiple Advantage data sources may need to be defined for your environment. If different settings are needed for ODBC connections in one application, separate data sources may be required.
#When using RangerMSP SQL Database select the '''Remote''' Server option and unselect the Local and Internet ones.
#Review the options and change them to your desired setup.
#Review the options and change them to your desired setup.
#Once the Options are reviewed and/or altered, click OK to exit and save the settings. The new data source is displayed.
#Once the Options are reviewed and/or altered, click OK to exit and save the settings. The new data source is displayed.
Line 280: Line 317:
|}
|}


==Samples==
===Troubleshooting===
The Commit API allows you to add/update the following entities:
In case when using applications you developed with ODBC you receive one of the following errors:
*Accounts
Error 6420:  The 'discovery' process for the Advantage Database Server failed. Unable to connect to the Advantage Database Server.  axServerConnect
*Assets
or
*Tickets
Error 5033:  No connected server was found for the given drive letter.
*Charges
*Appointments
*Tasks
*History Notes
*Opportunities
*Documents
*Knowledge Base Articles


Each API method requires a list of parameters which contain the field names and their values. The field names are the Database field names. You can see each field's name within the application (so you can verify which field you are about to update), by right-clicking the field and selecting Field Settings > Advanced Tab > view the Tech. Rec ID field.
You should perform the following:


You can view the complete list of database fields in the [[Commit API Reference Manual#API Reference Manual|API Reference Manual]] above.
1. [[SQL_Database_Troubleshooting#ADS.ini_.E2.80.93_Server_Locator|Configure the ADS.ini file]]  


Following are samples for using the API by Email and the Programming API. The samples are basic and provide an easy starting point.
2. Copy the ADS.ini file you prepared from the RangerMSP server folder into the following folder on each the affected workstation:


===Code samples===
* '''%Windir%\system32'''
The following code samples demonstrate how to add and update a record in Commit from VBA, C++ or Delphi programs.


Make sure to read [[Commit API Reference Manual#Using Commit API|Using Commit API]] before going through the samples, as it provides an overview of the Commit API work-flow and how it should be used.
This folder usually translates to: '''C:\Windows\system32'''


To test samples, it is recommended that you download a trial version of Commit from our web site and install it on a new computer that is not running Commit. Then, modify the paths in the source code to point to folder <testcomputer>\Commit\LastVer (and to the same files it already points to).  
'''Note:''' To open these folders on your PC you can paste the paths listed above into a Windows Run dialog box, or into any Windows Explorer path.


When developing your programs please make sure you work under the <testcomputer>\Commit\ThirdParty\UserDev folder (or at least call the dlls in this location). Do not copy the dll’s to any other location.  
3. Change the database path configured for ODBC connection to include the server IP address and port used by Advantage Database Server, for example:
\\192.168.0.1:6262\RangerMSP\Db


These samples create a connection to Commit's database, add a new Account record into
===Troubleshooting ODBC in 64 Bit Operating Systems===
the database, and update it. Each transaction should specify the database table to be updated by the transaction:
The RangerMSP ODBC drivers are mainly distributed for 32-bit operating systems; however, the same drivers are supported on the 64-bit operating systems (I.e. Windows 7 x64), and can be used to retrieve data from the RangerMSP database.
{| class="wikitable"
|-
! '''Application entity'''
! '''Table name'''
! '''Code'''
|-
| Accounts
| Cards
| 10
|-
| Opportunities
| Opps
| 20
|-
| Documents
| Docs
| 30
|-
| Charges
| Docs
| 30
|-
| Charges
| Slips
| 40
|-
| Appointments/Tasks
| Events
| 50
|-
| History Notes
| Notebook
| 60
|-
| Tickets
| Tickets
| 70
|-
| Items
| Items
| 80
|-
| Assets
| Assets
| 90
|-
| Knowledge Base
| KBArticles
| 100
|}
 
====API functions====
The Programming API provides the following API functions:
{| class="wikitable"
|-
! '''Method'''
! '''Return Value'''
! '''Description'''
|-
| '''CmtInitDbEngDll (app_name, path, status)'''
| status (int)
| Establishes a connection to the database. app_name - This string will be used for all functions of the package, and will appear in the application as the user who performs the changes in the records you update. You should specify a meaningful value. path - The path to the DB folder where the Commit server is installed: <server>\Commit\Db status - 1 for success. See Error Codes Description for other values.
|-
| '''CmtInsUpdRec(data_buff, map_buff, flag, tbd,<br>
rec_id_buff_size, error_codes_buff_size,<br>
err_msg_buff_size,rec_id_buff,<br>
err_codes_buff, err_msg_buff, status)'''
| status (int),<br>
rec_id (char)
| Adds/Updates records.<br>
data_buff - string containing the values to insert into the Database<br>
map_buff - mapping of the database fields corresponding with the data buff<br>
flag - stop(0)/continue(1) the input process is an invalid data value(s)<br>
tbd - Not used<br>
rec_id_buff_size - length of REC ID Buffer<br>
err_code_buff_size - length of Error Code Buffer<br>
err_msg_buff_size - length of Error Message Buffer<br>
rec_id_buff - buffer for returned REC ID<br>
err_codes_buff - buffer for returned Error Codes<br>
err_msg_buff - buffer for returned Error Messages<br>
status - returned status, 1 for success. <br>
See [[Commit API Reference Manual#Error Codes Description|Error Codes Description]] for other values.
|-
| '''CmtTerminateDbEngDll'''
|
| Close the connection to the database
|-
| '''CmtGetDescriptionByCode''' (code,<br>desc_size,<br>desc)
| message (char)
| Call this function in case of error in<br>
'''CmtInsUpdRec'''. <br>
In case of error (return code other than 1),<br>
you can use this to get error string.
|-
| '''CmtGetDescriptionByStatus'''
|
| Call this function in case of error in<br>
'''CmtInitDbEngDll'''
|}
 
'''Field buffers'''
The record to be added/updated in the database is passed to the Commit API in two buffers:
{| class="wikitable"
|-
! '''Parameter'''
! '''Description'''
! '''Example'''
|-
| data_buff
| String containing the <u>values</u> to insert into the Database. Separators between the fields are defined in the map_buff
| "17/04/2008 14:44", "CRD7C9KZPS9JN3LEZVD9", "Charge", "CRDGO0SVQ6074CMAN7DW","Closed","test note in DB Engine", "NTBL6PDPKUU6NXLRHLHP","CRDBSMJ3P72EHMU0HB LX","TKT4S81466E05IM8P23X"
|-
| map_buff
| Mapping of the data_buff: separators, field names.
 
Field names must be in the same order as the data_buff values.
 
The field names should be separated with a different separator than the values (e.g. "new line").
| " , FLDHISNOTEDATETIME<br>
FLDHISWORKERID<br>
FLDHISKIND<br>
FLDHISCONTACTID<br>
FLDHISUSER1<br>
FLDHISDESCRIPTION<br>
FLDHISRECID<br>
FLDHISCARDID<br>
FLDHISLINKRECID<br>
|}
 
====VB Sample====
To test the following VB code, create a VB program that includes this code and executes
it, or open the Visual Basic editor included with MS-Word, paste the code into it and run it.
 
In order for the VB sample to compile properly, please follow these steps:
#Go to My Computer > Right Click – Properties > Advanced Tab > Environment Variable
#At the bottom of the list, search for the Path variable (not PathText)
#Double click the Path variable > go to the end of the value/line
#Add ;
#Add the path to the folder: <server>\Commit\ThirdParty\UserDev\ and confirm.
 
Private Declare Sub CmtInitDbEngDll Lib "C:\DemoVBA\CmtDbEng.dll" (ByVal xSoftWareName As String, _
  ByVal xDbPath As String, ByRef xvStatus As Integer)
 
Private Declare Sub InitCommonControls Lib "comctl32.dll" ()
 
Private Declare Sub CmtInsUpdRec Lib "C:\DemoVBA\CmtDbEng.dll" (ByVal xSoftWareName As String, _
  ByVal xDataKind As Integer, _
  ByVal xDataBuff As String, _
  ByVal xMapBuff As String, _
  ByVal xContWhenInvalidData As Integer, _
  ByVal xFlags As Integer, _
  ByVal xRecIDBuffLen As Integer, _
  ByVal xLogErrCodesBuffLen As Integer, _
  ByVal xLogErrMsgBuffLen As Integer, _
  ByVal xvRecIDBuff As String, _
  ByVal xvErrCodesLogBuff As String, _
  ByVal xvErrMsgLogBuff As String, _
  ByRef xvStatus As Integer)
 
  Private Const C_DataBuffSize  As Integer = 1024
  Private Const C_MapBufSize  As Integer = 1024
  Private Const C_ErrMsgBuffSize  As Integer = 1024
  Private Const C_ErrCodeBuffSize  As Integer = 64
  Private Const C_RecIDBuffSize  As Integer = 20
  Private Const C_Flag  As Integer = 1
  Private Const C_Ok  As Integer = 1
  Private Const C_AccountsTable  As Integer = 10
 
  Private Const C_AppName As String = "Demo"
 
Public Sub DBEng()
 
  Dim nStatus As Integer
  Dim l As Long
  Dim S As String
  Dim pStr As Long
  Dim MapBuff As String
  Dim DataBuff As String
  Dim RecIdBuff As String * C_RecIDBuffSize
  Dim ErrCodesLogBuff As String * C_ErrCodeBuffSize
  Dim ErrMsgLogBuff As String * C_ErrMsgBuffSize
   
  Call CmtInitDbEngDll(C_AppName, "C:\DemoVBA\DB\", nStatus)
   
  If nStatus = C_Ok Then
   
Rem  ******************** Establishing connection with Commit, Should be
    called only once for the entire session ******
    MapBuff = "'" + Chr(13) + "," + Chr(13) + "FLDCRDFULLNAME" + Chr(13) + "FLDCRDDEAR" +
    Chr(13) + "FLDCRDCONTACT"
    DataBuff = "'Bart De Hantsetters','De Hantsetters','Hantsetters'"
 
    Call CmtInsUpdRec(C_AccountsTable, _
                  C_AccountsTable, _
                  DataBuff, _
                  MapBuff, _
                  C_Flag, _
                  0, _
                  C_RecIDBuffSize, _
                  C_ErrCodeBuffSize, _
                  C_ErrMsgBuffSize, _
                  RecIdBuff, _
                  ErrCodesLogBuff, _
                  ErrMsgLogBuff, _
                  nStatus)
                 
    If (ErrMsgBuff <> "") Then MsgBox ("Error Message: " + ErrMsgBuff)
   
    If nStatus = C_Ok Then
Rem      ******************** Updating the Account record we've just created *******************
   
      MapBuff = "'" + Chr(13) + "," + Chr(13) + "FLDCRDDEAR" + Chr(13) + "FLDCRDRECID"
      DataBuff = "'Doctor','" + RecIdBuff + "'"
      ErrCodesBuff = ""
      ErrMsgBuff = ""
     
     
      Call CmtInsUpdRec(C_AccountsTable, _
                  C_AccountsTable, _
                  DataBuff, _
                  MapBuff, _
                  C_Flag, _
                  0, _
                  C_RecIDBuffSize, _
                  C_ErrCodeBuffSize, _
                  C_ErrMsgBuffSize, _
                  RecIdBuff, _
                  ErrCodesLogBuff, _
                  ErrMsgLogBuff, _
                  nStatus)
      If (ErrMsgBuff <> "") Then MsgBox ("Error Message: " + ErrMsgBuff)
   
  Else
    MsgBox ("Insert new Account. Error code: " + ErrCodesLogBuff)
   
  End If
  Else
    MsgBox ("Commit Init failed. Error code: " + ErrCodesLogBuff)
  End If
   
 
End Sub
 
====C++ Sample====
// Demo.cpp : Defines the entry point for the console application.
//
#include "stdafx.h"
#include "CmtDBEng.h"
#include <string.h>
#include <stdlib.h>
int ErrCodesParsing (char* ErrCodeBuff)
{
// Demo.cpp : Defines the entry point for the console application.
//
#include "stdafx.h"
#include "CmtDBEng.h"
#include <string.h>
#include <stdlib.h>
int ErrCodesParsing (char* ErrCodeBuff)
{
  const int C_DescSize = 1024;
  char desc[C_DescSize];
  char Delimiter[] = "\n";
  int Code;
  char* pch;
  pch = strtok (ErrCodeBuff,Delimiter);
  while (pch != NULL)
  {
    Code = atoi(pch);
        CmtGetDescriptionByCode(Code, C_DescSize, desc);
    printf ("%s\n",desc);
    pch = strtok (NULL, Delimiter);
  }
  return 0;
}
int main(int argc, char* argv[])
{
  const int C_DataBuffSize = 1024;
  const int C_MapBufSize = 1024;
  const int C_ErrMsgBuffSize = 1024;
  const int C_ErrCodeBuffSize = 64;
  const int C_RecIDBuffSize = 64;
  const int C_Flag = 1;
  const int C_Ok = 1;
  const int C_AccountsTable = 10;
  const int C_ExampleCode = 54000;
  int Status;
  char DataBuff[C_DataBuffSize] = "";
  char MapBuff[C_MapBufSize] = "";
  char RecIdBuff[C_RecIDBuffSize];
  char ErrCodesBuff[C_ErrCodeBuffSize];
  char ErrMsgBuff[C_ErrMsgBuffSize];
  char* C_AppName = "Demo";
 
  //* Establishing connection with Commit, Should be called only once for the entire session **
 
  CmtInitDbEngDll(C_AppName, // Your application name. This will be used for all functions of the
                            // package.
  // Specify a meaningful value.
        "C:\\Demo\\DB\\", //Path to the database folder where Commit the server is
                          // installed <server>\Commit\Db
  &Status);          //Returned connection status
 
  if (Status == C_Ok) {
 
  //***Insert New Account into that Accounts table *******************
  strcpy (DataBuff,"'Bart De Hantsetters','De Hantsetters','Hantsetters'");
  strcat (MapBuff, "'\n,\nFLDCRDFULLNAME\nFLDCRDDEAR\nFLDCRDCONTACT");
  strcat (RecIdBuff, "");
  strcat (ErrCodesBuff, "");
  strcat (ErrMsgBuff, "");
 
  CmtInsUpdRec(C_AppName,    //String for your selection.
                C_AccountsTable,    //Desired Table Code
                DataBuff,  //This string contains the values which we want to add to the database
                MapBuff,  //List of database fields where we want to add data
                C_Flag,//Flag - stop(0) continue(1) the input process is data
                          //value(s) is invalid
                0,                        //Not used
                C_RecIDBuffSize,    //Length of REC ID Buffer
                C_ErrCodeBuffSize, //Length of Error Code Buffer
                C_ErrMsgBuffSize,  //Length of Error Message Buffer
                RecIdBuff,            //Buffer for returned REC ID
                ErrCodesBuff,        //Buffer for returned Error Codes
                ErrMsgBuff,          //Bufer for returned Error Messages
                &Status              //Returned status
        );
 
  if (ErrMsgBuff == "")
  printf("Error Message: %s", ErrMsgBuff);
 
  ErrCodesParsing(ErrCodesBuff);
 
  if (Status == C_Ok) {
 
      //***** Updating the Account record we've just created *************
      strcpy (MapBuff, "'\n,\nFLDCRDDEAR\nFLDCRDRECID"); // Map file for the  update
      // transaction - the Dear field and the record id
      strcpy (DataBuff,"'Doctor','");
      strcat (DataBuff, RecIdBuff);
      strcat (DataBuff, "'");
      strcat (ErrCodesBuff, "");
      strcat (ErrMsgBuff, "");
 
 
      CmtInsUpdRec(C_AppName,    // string for your selection.
                  C_AccountsTable,    // Desired Table Code
                  DataBuff,  // string contains the values, which we want to add into the Database
                  MapBuff,    // list of the Database Fields in which we want to add data
                  C_Flag,    // Flag - stop(0)/continue(1) the input process is some data
                              // value(s) is invalid
                  0,                        // Not used
                  C_RecIDBuffSize,    // length of RecID Buffer
                  C_ErrCodeBuffSize,  // length of Error Code Buffer
                  C_ErrMsgBuffSize,  // length of Error Message Buffer
                  RecIdBuff,            // buffer for returned RecID
                  ErrCodesBuff,        // buffer for returned Error Codes
                  ErrMsgBuff,            // bufer for returned Error Messages
                  &Status                // returned status
          );
 
      if (ErrMsgBuff == "")
        printf("Error Message: %s", ErrMsgBuff);
 
      ErrCodesParsing(ErrCodesBuff);
    }
    else
    {
      printf("Insert new Account. Error code: %d\n", Status);
  };
 
    //****Terminate connection with Commit*******************
    CmtTerminateDbEngDll();
  }
  else
  {
    printf("Commit Init failed. Error code: %d\n", Status);
  };
 
  return 0;
}
 
====Delphi Sample====
program Demo;
 
{$APPTYPE CONSOLE}
 
uses
  SysUtils, Classes;
const
  C_DataBuffSize = 1024;
  C_MapBufSize = 1024;
  C_ErrMsgBuffSize = 1024;
  C_DescSize = 1024;
  C_ErrCodeBuffSize = 64;
  C_RecIDBuffSize = 64;
 
  C_Flag = 1;
  C_Ok = 1;
  C_AccountsTable = 10;
  C_AppName = 'Demo';
 
  CmtDbEngDll = 'CmtDbEng.DLL';
 
var
  Status: integer;
  DataBuff: array [0..C_DataBuffSize] of Char;
  MapBuff: array [0..C_MapBufSize] of Char;
  RecIdBuff: array [0..C_RecIDBuffSize] of Char;
  ErrCodesBuff: array [0..C_ErrCodeBuffSize] of Char;
  ErrMsgBuff: array [0..C_ErrMsgBuffSize] of Char;
  s: string;
 
//** Establishing connection with Commit, Should be called only once for the entire session *
Procedure CmtInitDbEngDll (
              xSoftWareName  : PChar; // Your application name. Once selected this  string
                                      // will be used for all
                                      // functions of the package. Specify a meaningful value.
              xDbPath        : PChar; // Path to the DB folder under where Commit server is
                                      // installed <server>\Commit\Db
 
              var xvStatus    : integer          // Returned connection status
            ); stdcall; external CmtDbEngDll;
 
//**** Insert/Update record
Procedure CmtInsUpdRec(
              xSoftWareName  : pChar;            // See above
              xDataKind          : integer;      // Desired Table Code
              xDataBuff          : pChar;        // String containing the values, which we want
                                                  // to add into the Database
              xMapBuff            : pChar;        // List of the database fields into
                                                  //which we want to add data
              xContWhenInvalidData : Integer;    //Flag - stop(0)/continue(1) the input process
                                                  // is some data value(s) is invalid
              xFlags                : Integer;              // Not used
              xRecIDBuffLen      : Integer;          // Length of REC ID Buffer
              xLogErrCodesBuffLen  : Integer;  // Length of Error Code Buffer
              xLogErrMsgBuffLen    : Integer;    // Length of Error Message Buffer
              xvRecIDBuff          : pChar;            // Buffer for returned REC ID
              xvErrCodesLogBuff : pChar;        // Buffer for returned Error Codes
              xvErrMsgLogBuff    : pChar;        // Buffer for returned Error Messages
              var xvStatus      : Integer          // Returned status
              ); stdcall; external CmtDbEngDll;
 
//**** Terminate connection with Commit ****
procedure CmtTerminateDbEngDll; stdcall; external CmtDbEngDll;
   
procedure CmtGetDescriptionByCode(
                                  xCode    : Integer;
                                  xDescLen  : Integer;
                                  xvDesc    : pChar); stdcall; external CmtDbEngDll;
 
procedure CmtGetDescriptionByStatus(
                                    xCode    : Integer;
                                    xDescLen  : Integer;
                                    xvDesc    : pChar); stdcall; external CmtDbEngDll;
 
procedure ErrCodesParsing (ErrCodeBuff: string);
var
  lList: TStringList;
  i: integer;
  aDescErrCode : Pchar;
begin
  try
    lList := TStringList.Create;
    lList.Text := ErrCodeBuff;
    GetMem(aDescErrCode,C_DescSize);
    for i := 0 to lList.Count - 1 do
    begin
      CmtGetDescriptionByCode(StrToInt(lList[i]), C_DescSize, aDescErrCode);
      writeln('Error Code: '+lList[i]+' Desc: '+string(aDescErrCode));
    end;
  finally
    FreeMem(aDescErrCode);
    lList.Destroy;
  end;
end;
 
procedure DisplayErrStatusCode(xCode : Integer);
var
  aStatusErrCode : Pchar;
begin
  try
    GetMem(aStatusErrCode,C_DescSize);
    CmtGetDescriptionByStatus(xCode,C_DescSize, aStatusErrCode);
    writeln('Commit Init failed. Error code: '+Inttostr(xCode)+' Desc: '+string(aStatusErrCode));
  finally
    FreeMem(aStatusErrCode);
  end;
end;
 
begin
 
  //**** Establishing connection with Commit, Should be called only once for the entire session
  CmtInitDbEngDll(C_AppName, // Your application name. Once selected this string will be used
                            // for all functions of the package. Specify a meaningful value.
  'C:\DemoDelphi\db\',                    // Path to the DB folder under where Commit server is
                                          // installed <server>\Commit\Db
  Status                  // Returned connection status
    );
   
  if Status = C_Ok then
  begin
 
    //**** Insert a new Account into the Accounts table ****
 
    s := '"Bart De Hantsetters","De Hantsetters","Hantsetters"';
    StrPCopy(DataBuff, s);
    s := '"'+#13','+#13+'FLDCRDFULLNAME'+#13+'FLDCRDDEAR'+#13+'FLDCRDCONTACT'+#0;
    StrPCopy(MapBuff, s);
 
    CmtInsUpdRec(C_AppName,        // Your application name
                  C_AccountsTable,  // Desired Table Code
                  DataBuff,          // String containing the values, which we want to add into
                                    // the Database
                  MapBuff,          // List of the Database Fields in which we want to add data
                  C_Flag,            // Flag - stop(0)/continue(1) the input process is some data
                                    // value(s) is invalid
                  0,                            // Not used
                  C_RecIDBuffSize,        // Llength of REC ID Buffer
                  C_ErrCodeBuffSize,      // Length of Error Code Buffer
                  C_ErrMsgBuffSize,      // Length of Error Message Buffer
                  RecIdBuff,                // Buffer for returned REC ID
                  ErrCodesBuff,            // Buffer for returned Error Codes
                  ErrMsgBuff,                // Buffer for returned Error Messages
                  Status                      // Returned status
          );
 
 
 
    if (ErrMsgBuff[0] <> #0) then
      writeln('Error Message: '+ ErrMsgBuff);
 
    ErrCodesParsing(ErrCodesBuff);
 
    if Status = C_Ok then
    begin
  //**** Updating the Account record we've just created *****
 
      // Map file for the update transaction - the Dear field and the record id
      s := '"'+#13+','+#13+'FLDCRDDEAR'+#13'FLDCRDRECID';
      StrPCopy(MapBuff, s);
 
      s := '"Doctor","'+RecIdBuff+'"';
      StrPCopy(DataBuff, s);
 
      CmtInsUpdRec(C_AppName,          // Your application name
                  C_AccountsTable,    // Desired Table Code
                  DataBuff,            // String containing  the values, which we want
                                        // to add into the Database
                  MapBuff,            // List of the database fields into which we want to add
                                        //data
                  C_Flag,              // Flag - stop(0)/continue(1) the input process is some
                                        // data value(s) is invalid
                  0,                  // Not used
                  C_RecIDBuffSize,    // Length of REC ID Buffer
                  C_ErrCodeBuffSize,  // Length of Error Code Buffer
                  C_ErrMsgBuffSize,    // Length of Error Message Buffer
                  RecIdBuff,          // Buffer for returned RECID
                  ErrCodesBuff,        // Buffer for returned Error Codes
                  ErrMsgBuff,          // Buffer for returned Error Messages
                  Status              // Returned status
          );
   
      if ((ErrMsgBuff[0] <> #0)) then
        writeln('Error Message: '+ ErrMsgBuff);
 
      ErrCodesParsing(ErrCodesBuff);
 
      if Status = C_Ok then
        Writeln('Completed Successfully');
    end
    else
    begin
      try
        s := IntToStr(Status);
      except
        s := 'ill-defined';
      end;
      writeln('Insert new Account. Error code: '+ s);
    end;
 
  //**** Terminate connection with Commit****
   
    CmtTerminateDbEngDll();
  end
  else
  begin
    DisplayErrStatusCode(Status);
  end;
 
  writeln(#13#10+'press Enter to quit');
  readln;
end.
 
===XML samples===
 
Following are samples for adding a new Ticket and a new Charge to the Commit database using XML formatted messages.
 
Make sure to go over the [[Email Connector]] setup guide, and perform the XML API setup steps prior to testing the XML API.
 
Also please read [[Commit API Reference Manual|Using Commit API#Using Commit API]] before going through the samples, as it provides an overview of the Commit API work-flow and how it should be used.
 
Notes on API by Email Activation:
 
*Error Handling - Should the system fail to perform the XML transaction, an error message will be sent to the email address specified in the XML.
 
*Using a Password - If you wish to use a verification password for the XML transactions, define the password using the ServerConfig.exe utility. To do so, go to the XML tab, enable the API by Email option and set a password (as specified in the XML - see [[Commit API Reference Manual#General XML Tokens|General XML Tokens]]). Make sure to set the same Password in ServerConfig and in the XML email itself.
 
You can read more about the ServerConfig and how to setup the API by Email configuration in the [[Email Connector|Commit Email Connector]] Setup guide.
 
====General XML Tokens====
The sample and table demonstrates general parameters which should be used for any XML transaction:
 
<?xml version="1.0" ?>
<?commitcrmxml version = "1.0" ?>
<CommitCRMTransaction>
  <ExternalApplicationName>N-Able</ExternalApplicationName>
  <SendResponseToEmail>youremail@yourdomain.com</SendResponseToEmail>
  <Password>the-predefined-api-password</Password>
  <ReturnTransactionID>data from external application (will be returned as-is in the response)
</ReturnTransactionID>
  <DataKind>TICKET</DataKind>
  <RecordData>
    ... the transaction goes here ...
  </RecordData>
</CommitCRMTransaction>
 
{| class="wikitable"
|-
! '''Token'''
! '''Comment'''
|-
| <?xml version="1.0" ?>
| The XML version - Always 1.0
|-
| <?commitcrmxml version ="1.0" ?>
| The Commit API XML version - Always 1.0
|-
| <CommitCRMTransaction>
| Start and end transactions with this token (may have more than one in a single email)
|-
| <ExternalApplicationName>
| The sender application name, can contain any text
|-
| <SendResponseToEmail>
| When set with an email address, then a response email will be sent after processing this  transaction by the Email Connector
|-
| <Password>
| Optional Password - Only emails with a password that matches the password set in the Email Connector Settings will be processed (to prevent SPAM email from being processed and added to your CommitCRM database).
|-
| <DataKind>
| What is the Entity you wish to create/update.<br>
Possible values:<br>
ACCOUNT - for Accounts<br>
TICKET - for Tickets<br>
CHARGE - for Charges<br>
ITEM - for Items<br>
APPOINTMENT-OR-TASK - for Appointments/Task<br>
HISTORY-NOTE - For History notes<br>
ASSET - for Assets<br>
OPPORTUNITY - for Sales Opportunities<br>
DOCUMENT - for Documents<br>
KBARTICLE - for Knowledge Base articles<br>
|}
 
====Adding new Ticket====
 
In this sample, we add a new Ticket, and set some additional fields to it, such as Notes, Source, Due Date and Dispatcher flag.


<?xml version="1.0" ?>
<br>
<?commitcrmxml version = "1.0" ?>
<CommitCRMTransaction>
  <ExternalApplicationName>N-Able</ExternalApplicationName>
  <SendResponseToEmail>youremail@yourdomain.com</SendResponseToEmail>
  <Password>the-predefined-api-password</Password>
  <ReturnTransactionID>data from external application (will be returned as-is in the response)
</ReturnTransactionID>
  <DataKind>TICKET</DataKind>
  <RecordData>
        <FLDTKTCARDID> CUSTOMER-RECORD-ID-GOES-HERE-20-CHARS </FLDTKTCARDID>
        <FLDTKTPROBLEM>ticket description...</FLDTKTPROBLEM>
        <FLDTKTSTATUS>100</FLDTKTSTATUS>
  <FLDTKTKIND>General</FLDTKTKIND>
<FLDTKTNOTES>Notes</FLDTKTNOTES>
<FLDTKTSOURCE>Source</FLDTKTSOURCE>
  <FLDTKTSCHEDLENESTIM>60</FLDTKTSCHEDLENESTIM>
  <FLDTKTDUEDATETIME>02/04/08</FLDTKTDUEDATETIME>
<FLDTKTFORDISPATCH>Y</FLDTKTFORDISPATCH>
  </RecordData>
</CommitCRMTransaction>


====Adding new Charges====
===Configuring Data Source in 64-Bit Systems===
When installing the ODBC drivers on 64-bit operating systems, the ODBC drivers may not be displayed in the default ODBC manager in windows control panel. In order to view the 32-bit ODBC manager in a 64-bit operating system, you’ll need to browse to '''%windir%\SysWOW64\odbcad32.exe''' and run the 32 bit ODBC Administrator manually. Once the data source has been configured there, you should be able to select the data source in your 3rd party ODBC programs.


<?xml version="1.0" ?>
<br>
<?commitcrmxml version = "1.0" ?>
<CommitCRMTransaction>
  <ExternalApplicationName>Alert</ExternalApplicationName>
  <SendResponseToEmail>your email address for responses</SendResponseToEmail>
  <Password>12345</Password>  >> Should be the same in the ServerConfig!
  <ReturnTransactionID>data from external application (will be returned as-is in the response)
</ReturnTransactionID>
  <DataKind>CHARGE</DataKind>
  <RecordData>
        <FLDSLPWORKERID> CRDLS71RGU747TLHTFOR  </FLDSLPWORKERID>
        <FLDSLPCARDID> CRDLQXDL43BP5YCMSGM3</FLDSLPCARDID>
        <FLDSLPITEMID>  ITM1Q3GUI05ANBQGVY8D  </FLDSLPITEMID>
        <FLDSLPDESC>  Charge Description...  </FLDSLPDESC>
        <FLDSLPQUANTITY>  10  </FLDSLPQUANTITY>
        <FLDSLPSLIPDATE>  31/01/2008  </FLDSLPSLIPDATE>
        <FLDSLPSTARTTIME>  12:06  </FLDSLPSTARTTIME>
        <FLDSLPENDTIME>  14:50  </FLDSLPENDTIME>
        <FLDSLPBCRECID>  BCTMA51KBA925J7G0V67 </FLDSLPBCRECID>
        <FLDSLPPRICE> 125.3  </FLDSLPPRICE>
        <FLDSLPADJUSTAMOUNT>  10  </FLDSLPADJUSTAMOUNT>
        <FLDSLPUSER1>  Field 1...  </FLDSLPUSER1>
  </RecordData>
</CommitCRMTransaction>


=Samples=
For basic samples that provide an easy starting point see the following:
* [[VB.NET_API#Complete_Program_Sample|VB.NET Code Sample]].
* [[CSharp_API#Complete_Program_Sample|CSharp Code Sample]].
* [[C%2B%2B_API#Complete_Program_Sample|C++ Code Sample]].


====Receiving Response====
When adding or updating data via the XML API, the system can send a response (if the
XML transaction specifies this in the SendResponseToEmail token). The format of the XML
response is as follow:
<u>Response in case of success:</u>
<?xml version="1.0" ?>
<?commitcrmxml version="1.0" ?>
<CommitCRMResponse>
<Status>SUCCESS</Status>
<AffectedRecId>TKTN1NIQEYYQ8PBJMDAX</AffectedRecId>
<ReturnTransactionID>data from external application (as-is)</ReturnTransactionID>
</CommitCRMResponse>
<u>Response in case of error:</u>
<?xml version="1.0" ?>
<?commitcrmxml version="1.0" ?>
<CommitCRMResponse>
<Status>FAILURE</Status>
<AffectedRecId></AffectedRecId>
<ReturnTransactionID>data from external application (as-is)</ReturnTransactionID>
<ResultCodes>50109</ResultCodes>
<ResultMessage>
Fields with illegal values: Operation canceled. Field: Account has invalid data -
        TKTN1NIQEYYQ8PBJMDAX
</ResultMessage>
</CommitCRMResponse>
{| class="wikitable"
|-
! '''Token'''
! '''Comment'''
|-
| <?xml version="1.0" ?>
| The XML version - Always 1.0
|-
| <?commitcrmxml version ="1.0" ?>
| The Commit API XML version - Always 1.0
|-
| <CommitCRMResponse>
| The response starts and ends with this token
|-
| <Status>
| The transaction status. Possible values: FAILURE, SUCCESS
|-
| <SendResponseToEmail>
| When set with an email address, then a response email will be sent after processing this transaction by the Email Connector
|-
| <AffectedRecId>
| The REC ID of the entity which was added or updated when processing the transaction.
|-
|<ResultCodes><br><ResultMessage>
|In case of a failure, this will contain the error code and description. You can find more information about error codes [[Commit API Reference Manual#Error Codes Description|here]].
|}


==API Reference Manual==
For Low-Level API and XML samples see:
Following is a detailed listing of fields per entity which can be added/updated using the API. The API parameters are the same when using the Programming API or the XML API.  
* [[API Code Samples#Data Retrieval API Samples|Data Retrieval API Samples]].
* [[API Code Samples#Data Update API Samples|Data Update API Samples]].
* [[API Code Samples#XML samples|XML samples]].


The API allows you to add/update the following entities:
=API Reference Manual=
*Accounts
*Assets
*Tickets
*Charges (and Contract-price Charges)
*Appointments
*Tasks
*History Notes
*Opportunities
*Documents
*Knowledge Base Articles


In the following section you will find a detailed listing of the database fields. Each table includes the fields '''Display name''' (as shows in the application), the D'''atabase Field Name''' (internal database field identifier) and comments.  
Find a detailed listing of the database fields in the [[API Reference Manual]].


Note that when using XML formatted messages, the database field name refers to the name to be provided within the XML token. For example, when the field name is FLDSLPQUANTITY, the XML token should look like this:  
=See Also=
<FLDSLPQUANTITY>10</FLDSLPQUANTITY>
*[[VB.NET_API|VB.Net Programming API]]
*[[CSharp_API|CSharp Programming API]]
*[[C%2B%2B_API|C++ Programming API]]
*[[Email Connector|RangerMSP Email Connector]]
*[[API by Email and MSP Integration]]
*[[MSP Integration Setup]]
*[[API Code Samples|Low-Level API Code Samples]]
*[[API Reference Manual]]
*[http://{{SERVERNAME}}/products/sql-database/ RangerMSP SQL Database (CSD)]
*[[SQL Database Installation Guide | RangerMSP SQL Database (CSD) Installation Guide]]


You can find examples of adding and updating records in the database by using the database field in the [[Commit API Reference Manual#Samples|Samples]] section.


===Account Fields===
[[Category:User Manuals]]
The following table lists the most important parameters for adding/updating Account
[[Category:Integration]]
records.
Note slight differences when adding a main account vs. adding a secondary contact.

Latest revision as of 07:13, 8 July 2020

User Manuals > API Developers Guide

Introduction

RangerMSP provides various tools in order to read/add/update information in the RangerMSP database.

These tools include:

  • Programming API (recommended)
    The RangerMSP Programming API provides developers with a set of API functions which can be used in CSharp, VB.NET and C++. The Programming API libraries provide easy-to-use classes and objects which make the development very simple and friendly.
  • Low-level Programming API
    Provides low-level functions for programmers using other languages, such as VB, Delphi or any other programming language that lets you use a standard Windows dll file. The Programming API includes two sets of functions: Data-Retrieval API and Data-Update API.
  • API by Email
    The API by Email allows you to send XML formatted emails which contain database transactions. RangerMSP Email Connector then pulls the incoming XML emails from your POP3 mail server, processes the email and performs the transactions in the XML transaction. XML formatted email gives you access to the Data-Update API, which allows you to perform many actions such as linking to external systems, receiving forms submitted from your web site, and more.
  • Web API
    The Web API allows you to interact with your RangerMSP server and data over the Web using HTTP.
  • ODBC Link
    In addition to the Data Retrieval API, there is an option of using ODBC to read data directly from the database. RangerMSP uses an open database structure and allows other applications to access the database using ODBC (Open Data Base Connectivity) for read-only purposes. The ODBC link should not be used from programs you develop (you should use the data retrieval API for this purpose). You can use ODBC Link in order to read data from the database and integrate it with an external system (i.e. Crystal reports, or any other application).


Glossary

  • RangerMSP API (Application Programming Interface) - RangerMSP provides an API which allows you to add and update records in the RangerMSP database. The API includes a list of possible actions and parameters.
  • ODBC Link (Open Data Base Connectivity) - ODBC is a function library which provides a common API for ODBC compliant databases. RangerMSP provides an open database and allows other applications to access the database using ODBC for read-only purposes.
  • Record - Each entity in the RangerMSP database (such as Ticket, Account, Asset, Item, Appointment, Task, etc.) is considered a "record".
  • Database fields - Each record contains a list of fields, each of which holds the record's data.
  • Transaction – All additions or updates to record in the database are done by using a transaction. Each transaction includes the operation you wish to perform, the data type you wish to add/update and the additional parameters (database fields and their values).
  • Transaction Parameters - Each transaction contains the parameters for performing the transaction: record type, list of database fields and a list of the values to be updated in these fields.
  • External Application - This refers to the external program which integrates with RangerMSP in order to update the database. This name should be passed when performing updates to the database, and it will be saved in the RangerMSP record to indicate who performed the changes.
  • Record ID (REC ID) - Each record in RangerMSP has a unique Record ID which is created when adding the record to the database. Every ID is 20 characters and should be transferred with the API transaction when you need to update an existing record.


Using API

Workflow

Following is a sample work-flow, which demonstrates the process of adding a new Account, and then adding a new Ticket to this Account, using the RangerMSP API.

Note that the API can return two parameters to the External Application:

  1. The RangerMSP REC ID - when adding new records to the database, this will contains the new record ID.
  2. The ExternalTransactionID (relevant for XML transaction only) - In case the external application passes a parameter to be returned with the response, the ExternalTransactionID will be returned as-is to in the response.

The External Application should keep the REC ID which is returned from the API response. This allows you to later update the existing records with new data or add records related to it (such as opening a Ticket under the Account).


Updating existing records

In order to update an already existing record in the database (e.g. add a Ticket to an Account, update an Account's phone number, add a new Charge to an existing Ticket, etc.), you should pass the record's unique identifier to the API so it will update the existing record rather than create a new Account. The record's unique identifier is referred to as the Record ID (REC ID).

The Record ID can be obtained in the following ways:

  1. API Response (as in the flow above) - You can extract the newly created Record ID from the Email Response when adding new records with the API. This requires the developer to process the API responses, and extract the returning REC ID for each newly added record.
  2. Data Retrieval API - Use the Data Retrieval API functions to find relevant records (Accounts, Tickets, etc.), and read all information from the database, including the unique Record ID.
  3. Manually - from the Account's Notes tab, at the bottom, right-click the REC ID field and copy it.

Once you have obtained the Record ID you can pass the Record ID as a parameter for the API and update this record.


Programming API

The Programming API allows you access the RangerMSP database and perform transactions from within a program (using various programming language). This means you can embed code which accesses the RangerMSP database from within your own application, and add your own functionality which updates the database.

The RangerMSP Programming API provides developers with a set of API functions which can be used in CSharp, VB.NET and C++. The Programming API libraries provide easy-to-use classes and objects which make the development very simple and friendly.

See more details and samples about each programming environments in the following links:



Low Level Programming API

The Low-level programming API provides a way to use the API from development languages which are not supported by the main Programming API.

The Low-level programming API executes database transactions. The low-evel API provides two separate dll programs, one for adding/updating data from the database, and one for retrieving data from the database.


For the full list of API functions go to API Functions.
For samples go to API Code Samples.

Note: Retrieving data can be performed either by the API (recommended) or using ODBC when needed. You can read more about it in the Data Retrieval section.

Adding and Updating Data

RangerMSP API allows you to add and update records in the RangerMSP database, such as Accounts, Tickets, Assets, Items, Appointments, Tasks and more.

The updates are performed using transactions, where each transaction includes the operation you wish to perform, the data type you wish to add/update, and any additional parameters (i.e. record type, database fields and their values).

Every record added to the system is assigned a unique Record ID (REC ID) which is the database identifier for this record.

The RangerMSP API allows you to perform the following operations.

  • Add new records - if no Record ID is passed in the transaction, the API assumes that this is a new record, adds it to the system and creates a new Record ID for it.
  • Update existing records - if the record ID is passed in the transaction, and the record exists, the system will update the record with the information in the transaction.
  • Retrieve records - The Data Retrieval API allows you to perform an SQL Query to get a list of record IDs and retrieve the details for a single record according to a REC ID.

Note that in order to retrieve data using ODBC (e.g. when linking to Crystal Reports), use the ODBC Link, which provides a read-only link for the RangerMSP database. See more details in Retrieving Data.

Data Retrieval

RangerMSP uses an open database and the RangerMSP API provides the ability to retrieve data using standard API functions which let you define queries and retrieve data.

In addition to the Data Retrieval API, there is an option of using ODBC to read data directly from the database. The ODBC link should not be used from programs you develop (you should use the data retrieval API for this purpose). Use the ODBC link only when you need to access the database from an external program, such as external report application (i.e. Crystal reports, or any other application).


API by Email (XML transaction)

The API by Email operates under the RangerMSP Email Connector module, which automatically processes incoming emails arriving at a defined public email address. The Email may contain XML formatted messages which contain API transactions, and provides a full data update API to RangerMSP.

The Email Connector pulls incoming emails from your POP3 mail server, and processes the email. When an email containing XML formatted message is detected, the system analyzes the XML content of the email and performs the transactions written in the XML. You may set the Email Connector to send automatic replies in response to XML formatted email.

The API by Email supported only the Data Update API (Data Retrieval API is not supported via emails). The API by Email executes database the transactions using the functions provided by the API dll file which is located in the RangerMSP installation. Both the Programming API and the API by Email use the same dll for performing the transactions.

Note that the API by Mail is supported only for the Data Update API, allowing you to add and update data. In order to retrieve data, you should use the Data Retrieval Programming API (recommended) or use ODBC, both methods do not support API by Email. You can read more about it in the Data Retrieval section.

  • Learn more about using API by Email.
  • Learn more about building the XML transactions for the API by Email in XML samples.


Web API

The Web API allows you to build applications or addons which interact with your RangerMSP server remotely over the Web. You get access to all the API functions and can query the data or add/edit data (or both) giving you unparallelled control over your data. To use the Web API you need to have the RangerMSP Server service, which is also used by the Email Connector and the Alerts Server modules, installed and running and be using the Programming API.

You can find details on connecting remotely using various programming environments here:


For instructions on how to activate the Web API, go to Web API Server Configuration

Data Retrieval API

The Data Retrieval API provides API functions which help you execute database queries and receive the results. Each function uses API functions provided by the Retrieval API dll file which is located in the RangerMSP installation. The Data Retrieval API is available only via the Programming API; no API by Email is available for data retrieval.

The Data Retrieval API provides the following abilities:

  • Execute a database query and receive a result records list.
  • Read each record with its fields.
  • Find out field properties.

See low-level programming API information in Low Level API Data Retrieval.
See the Data Retrieval API Reference Manual for detailed usage description.

ODBC Data Retrieval

RangerMSP uses an open database and allows other applications to access it using ODBC (Open Data Base Connectivity) for read-only purposes. While the API provides the means for adding and updating information in the database, it is preferred to use ODBC to read information from the database.

You can use ODBC Link to read data from the database and integrate it with an external system (i.e. Crystal reports, or any other application). You may also use the ODBC Link in order to read the Database Record ID and update existing records (see Updating existing records).

Activation Notes:

  • The ODBC drivers are well tested and work well, however, please note that the RangerMSP Support team doesn't provide "pure ODBC" support.
  • NEVER use ODBC to modify or delete data; use it for READ purposes only. Modifying data should be done using the RangerMSP API.
  • The ODBC Link can be used with your installed version of RangerMSP, and only requires installing an external driver. Before working with the API we recommend that you backup the database.


Below are the database table names and their meaning:

RangerMSP Record Database Table Name
Accounts Cards
Tickets Tickets
Charges Slips
Assets Assets
Appointments Events
Tasks Events
History Notes NoteBook
Opportunities Opps
Documents Docs
Knowledge Base KBArticles

For the database fields list, please refer to API Reference Manual section. You can find detailed instructions for using the ODBC Link in the Installing ODBC Driver section.


Installing ODBC Driver

Product Overview
The Advantage ODBC Driver is an ODBC version 3 driver based on the Advantage Client Engine that provides SQL access to the Advantage Database Server. The driver provides full support for the "Minimum" ODBC SQL grammar specification, as well as many functions included in the "Core" and "Extended" grammar specifications. When used with the Advantage Database Server, ODBC users can have the application stability, performance and reduced network traffic benefits of client/server architecture.

Installation on Windows
Like other ODBC drivers, the Advantage ODBC Driver is installed and managed using the ODBC Administrator Utility. This utility works with the ODBC Driver Manager to configure ODBC data sources. At runtime, the ODBC Driver Manager works with available drivers and their configured data sources. If you have previously installed ODBC drivers, the ODBC Administrator may already be installed on your workstation. The icon for the Administrator is usually found in the Control Panel.

To install the Advantage ODBC Driver:

  1. Download the ODBC driver version that corresponds with your Advantage SQL Database Server version from the following links: (The Advantage Version number can be found in the Advantage Configuration Utility located on the RangerMSP Server)
    ODBC Driver Version 11.10 – 64 bit
    ODBC Driver Version 11.10 – 32 bit
    ODBC Driver Version 10.10 – 32 bit
    ODBC Driver Version 10.0 – 32 bit
    ODBC Driver Version 9.0 – 32 bit
    ODBC Driver Version 8.1 – 32 bit (**Also for systems that do not use the RangerMSP SQL Database)
  2. Run the Setup program.
  3. Proceed through the setup windows to complete installation.

**DISCLAIMER: Using the ODBC connector without the RangerMSP SQL Database is not recommended, since the chances for Database Corruption would be elevated.

Data Source Setup for Windows
Once the Advantage ODBC Driver is installed, a data source needs to be configured to use the Advantage ODBC Driver. The data source is an entry in the Windows Registry. When a data source is defined for the Advantage Driver, all information specific to the Advantage Driver and database files is stored under the Data Source entry in the Windows 95/98/ME/ NT/2000/2003/XP Registry.
The database files and indexes must be stored on your file server, and the Advantage Database Server must be loaded in order to access the files.

The Data Source settings may be modified at any time. Using the ODBC Administrator, you may modify the Data Source and Option settings. For specific information about the screen fields see the Data Source Setup Screen.

To Setup the Data Source:

  1. From the ODBC Administrator, click Add.
  2. Highlight the Advantage SQL ODBC line, and click OK.
  3. Type a unique data source name. For example, type RangerMSPData. This name is used by applications to reference the data source.
  4. Specify the path to the database - point to folder: \RangerMSP\Db (do not use the data dictionary option). Click Browse to select a database path. Note Multiple Advantage data sources may need to be defined for your environment. If different settings are needed for ODBC connections in one application, separate data sources may be required.
  5. When using RangerMSP SQL Database select the Remote Server option and unselect the Local and Internet ones.
  6. Review the options and change them to your desired setup.
  7. Once the Options are reviewed and/or altered, click OK to exit and save the settings. The new data source is displayed.
  8. Click Close to exit the ODBC Administrator.

ODBC Data Source Entries for Windows
In Microsoft Windows, the entries are registry settings found on HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ (your unique data source name) or HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI (your unique data source name). Unless specified otherwise, all registry entries can be setup by the Advantage ODBC Driver setup utility. See Data Source Setup for Windows and Data Source Setup Screen for more information. ODBC Data Source Keys.

ODBC Data Source Keys
The following ODBC registry is required for the driver to load:

Registry Entry Comments
DataDirectory=data path 'data path' should be a valid path name for files locations (e.g., x:\data). This path is used to automatically select all tables in the specified directory.
DefaultType=Advantage Sets a certain type of database files to use Advantageproprietary ADT/ADI/ADM files.
ServerTypes=3 Allows the Driver to use the remote or local server

The following ODBC registry keys are optional:

Registry Entry Comments
AdvantageLocking=ON \ OFF The default is ON to use the Advantage proprietary locking.
CharSet=OEM \ ANSI The default character collation setting is ANSI. If OEM is specified, Language must be indicated as well.
Language=USA Used if CharSet=OEM.
Description=String This is provided for easier administration.
Locking=RECORD \ FILE Indicates whether updates lock the entire file or the individual records that are updated. The default is RECORD.
MaxTableCloseCache=n N is the number of tables to hold in cache when cursors are opened and closed. The default is 25.
MemoBlockSize=n N is the size of the Advantage memo blocks in tables that are created by the ODBC driver. The default value is 8 for Advantage proprietary table (ADT/ADM).
Rows=TRUE \ FALSE Select whether deleted rows are displayed. If True, deleted rows are displayed. The default is False.
TrimTrailingSpaces=TRUE \ FALSE The default is False. If True is specified, trailing spaces in character fields will be removed prior to returning the values to the application.

Troubleshooting

In case when using applications you developed with ODBC you receive one of the following errors: 

Error 6420:  The 'discovery' process for the Advantage Database Server failed. Unable to connect to the Advantage Database Server.  axServerConnect

or 

Error 5033:  No connected server was found for the given drive letter.

You should perform the following:

1. Configure the ADS.ini file

2. Copy the ADS.ini file you prepared from the RangerMSP server folder into the following folder on each the affected workstation:

  • %Windir%\system32

This folder usually translates to: C:\Windows\system32

Note: To open these folders on your PC you can paste the paths listed above into a Windows Run dialog box, or into any Windows Explorer path.

3. Change the database path configured for ODBC connection to include the server IP address and port used by Advantage Database Server, for example: 

\\192.168.0.1:6262\RangerMSP\Db

Troubleshooting ODBC in 64 Bit Operating Systems

The RangerMSP ODBC drivers are mainly distributed for 32-bit operating systems; however, the same drivers are supported on the 64-bit operating systems (I.e. Windows 7 x64), and can be used to retrieve data from the RangerMSP database.


Configuring Data Source in 64-Bit Systems

When installing the ODBC drivers on 64-bit operating systems, the ODBC drivers may not be displayed in the default ODBC manager in windows control panel. In order to view the 32-bit ODBC manager in a 64-bit operating system, you’ll need to browse to %windir%\SysWOW64\odbcad32.exe and run the 32 bit ODBC Administrator manually. Once the data source has been configured there, you should be able to select the data source in your 3rd party ODBC programs.


Samples

For basic samples that provide an easy starting point see the following:


For Low-Level API and XML samples see:

API Reference Manual

Find a detailed listing of the database fields in the API Reference Manual.

See Also

Retrieved from "https://www.rangermsp.com/w/index.php?title=API_Developers_Guide&oldid=8369"