Access offers a Software Development Kit (SDK) and Applications Programming Interface (API) enabler, allowing third-party applications to interface with your Dimensions or Dimensions Lite package.

This is a chargeable unit that provides you with the input tables for the Dimensions database.

These tables act as 'holding tanks' for transactional data from other applications. They retain this information until given the 'trigger' to activate the process that will post them to the accounts. The posting process is dealt with by using pre-defined stored procedures and business rules which are held within the dataset. It is the 'STATUS_TEMP' field in each of the tables which is used to trigger the posting process.

Please contact your Access Partner for details of the API tables that are currently available. File format spreadsheets to assist with data input are available on request

API table usage could provide the solution to a data entry problem.

Solution: You could use the 'Data Transformation Services' import function within SQL Server to copy data from one database and place it into the API tables for invoicing purposes. Scheduling this as a 'Job' within SQL Server would automate the service. The end result would be similar to that of using Transaction Broker. Alternatively, a developer could write a series of stored procedures in the third party database to do the same task and thus provide an almost seamless link to Access Accounts.

Solution: Using ODBC connectivity, design a simple third party front-end that links directly to the relevant API table (in this example it would be CST_TRN_TEMP).

When using API tables to link a third party application to the SQL database, please be aware that you will need sufficient SQL Server licences to cover the maximum number of users who will be required to access the database at any one time.

N.B. SL_STATUS_TEMP field is the trigger to whether the transaction is posted or not.

Important

The STATUS_TEMP field in all tables, as mentioned previously, is the key to triggering the posting process. It is recommended that initial data entries made to the API tables are flagged with a STATUS_TEMP of '0' (waiting) & then changed to a '2' (validate & post) via SQL or a 'Parse-through' query. The reasoning behind this is due to multiple line Invoices, Movements, Orders etc.

E.g. Enter a multiple line Invoice into the API table.

If you enter each line with a STATUS_TEMP value of '2', as soon as the first line is complete, it will try to post it.

Therefore, as you enter the second line, the Invoice number on it has now already been used (by the first line). This will result in the transaction either hanging / not responding, or you will receive stored procedure errors relating to inserting duplicate Invoice/Credit Note numbers.

Another possibility is that you could end up with multiple headers if batching transactions.

Use the following syntax in a simple stored procedure to update the STATUS_TEMP field from '0' to '2'.

UPDATE SL_TRN_TEMP
SET SL_STATUS_TEMP = 2
WHERE SL_STATUS_TEMP = 0

Of course, the above T-SQL syntax is very simple and is only an example, but it does the update effectively.

TIP - To activate a stored procedure like the one detailed above, create a macro in MS Access to execute it. Simply enter 'EXEC name-of-stored-procedure'. Then add a command button to the form you have created to run this macro.

With the above information in mind it is advisable that more technical people deal with procedures like this.

If there appears to be a problem with the data entry via the API tables always:

Do all of the above exist in the accounts? - Incorrect codes will not post.

Have you entered all compulsory data? - If not, it will not post.

Tip - be sure to check for leading spaces in the API tables if you are experiencing problems. If the data is retained in the table and will not post, it could be a single space at the start of a unique code that is the culprit. Using the mouse to navigate the API table, when doing a manual entry, can cause spaces to be left in cells - it is always best to tab between fields.

A set of specification documents is available for each of the API Tables.

These consist of a detailed specification in table format in a spreadsheet. This is available on request.

The Specification Table contains the following information:-

The field name.
The field's data type.
Where applicable, the restricted range of values to be used as data.
Where applicable, whether the field is compulsory (i.e. requires a data value)
Where applicable, whether the field is a system field (data can be hidden from user).
Where applicable, the default value for the field.
The version of Dimensions in which each API field was included.
Where applicable, the Dimensions Source field, from where data is obtained.
Where applicable, the Dimensions Target field, where data is sent.
A description of the field and special notes, where applicable.

Each record and transaction line in the API table holds a temporary transaction status value.

The various STATUS_TEMP fields hold one of the following values as follows:-

0 = Waiting.
1 = Validate transaction but do not post.
2 = Validate transaction and post it.
3 = Failed validation.

N.B. in SOP only, the Codes 1 and 2 are reversed.

Hence transactions that are entered initially hold the value 0. Changing the value to 2 triggers the validation and if this is accepted the transaction is posted to Dimensions and is deleted from the API table.

A default string field is present in all the API tables and it's purpose and use is common to all of them. Each position in the default string can hold a value 0 or 1, representing a type of switch. Every position in the string maps to a unique field in the respective API Table.

From the specification table, it can be seen that the fields are numbered from 1, 2, 3&ldots; etc. Position 1 in the string maps to field 1 in the table, etc so the number of positions in the string matches the total number of fields in the respective API table.

Some fields in the API can use default data, and for these the default string position that maps to them is available as a switch. Conversely, fields in the table, for which a default value is not applicable, have a corresponding position in the string, but this is not used. By changing the value at a valid position in the string to 1, a default value for that field can be selected. A simple example of this is to use the default string to 'pull through' the unit cost of a stock item, without needing to insert it's value directly with the transaction.

A source field is present in each API table. This shows the origin of each record or transaction detail line. This applies to records and transactions that have come from within the application itself or from another application. They are only populated when the record or transaction is inserted and not edited. The source field holds one value, according to the source of the data as follows:-

N = Dimensions.Net

T = Transaction Broker

A = API tables/procedures

D = Dimensions

H = Horizons

C = CS Plus

I = Importer

' ' (blank) = Unknown