Database Lookup

Content

Basic Scenario

Creating and Updating a Database in Straatos

Advanced Scenario

Basic Scenario

Goal

To lookup data from a database using a Script Task in the Workflow

Example: Lookup the supplier name based on the Suppliers Tax ID

In the example code below, we do the lookup as a Script Task in the Workflow.

Code Sample:

// DB LOOKUP
try {
    var query = {
        ConnectionString: 'dbConnectionstring'
        User: 'dbUser',
        Password: 'dbPassword',
        SqlQuery: 'select * from invoice.Supplier where TaxID = @TaxID',
        Parameters: {
            '@TaxID': SupplierTaxID
        }    
    };

    var data = straatos.data.query(query);

    if (data.ErrorMessage) {
        console.log('Lookup Supplier Tax ID Query Error: ' + data.ErrorMessage);
        straatos.setError('Lookup Supplier Tax ID Query Error: ' + data.ErrorMessage);
    } else if (data.Records && data.Records.length == 1) {
        SupplierCompanyName = data.Records[0].Name;
    } 

} catch(err) {
    console.log('Lookup Error: ' + err);
    straatos.setError('Lookup Error: ' + err);
}

In the above example, we have defined two index fields within the workflow, they are 'SupplierTaxID' and 'SupplierCompanyName'. The 'SupplierTaxID' is a value we extract from the invoice based on OCR. The 'SupplierCompanyName' is the Name of the Company stored in a Database which we want to retrieve and assign to an index field.

In the first part of the code, we define the SqlQuery and we use the 'SupplierTaxID' index field value as a parameter '@TaxID' for the lookup

var query = {
    ConnectionString: 'dbConnectionString',
    User: 'dbUser',
    Password: 'dbPassword',
    SqlQuery: 'Select * from invoice.Supplier where TaxID = @TaxID',
    Parameters: {
        '@TaxID': SupplierTaxID
    }    
};

Note: Please define the dbConnectionString, dbUser and dbPassword as a local variables in the Script Task and assign the values in order to connect to the database.

In the next section of the code, we do the actual lookup via a straatos query wrapper.

In the code example we use the CumulusPro 'straatos.data.query' to do the lookup.

var data = straatos.data.query(query);

The above code takes the 'query' which we have provided above and calls the query wrapper.

In the following code snippet, we parse the response from the query wrapper. In case there is an error, we write the error message into the System Field straatos.setError and prevent the document from going to the next workflow step. This means in case of an error, the document will stay in this workflow step and the error message can be seen in the Process Monitor.

    if (data.ErrorMessage) {
        console.log('Lookup Supplier Tax ID Query Error: ' + data.ErrorMessage);
        straatos.setError('Lookup Supplier Tax ID Query Error: ' + data.ErrorMessage);
    } else if (data.Records && data.Records.length == 1) {
        SupplierCompanyName = data.Records[0].Name;
    } 
} catch(err) {
    console.log('Lookup Error: ' + err);
    straatos.setError('Lookup Error: ' + err);
}

Creating and Updating the Database

In case you do not have your own Database or have your own Database available as a webservice, CumulusPro can host the Database for you.

In order to do this

Provide us the database in CSV format with the relevant data/columns which are filtered.
Our team will create the database with the column names provided, but will remove any spaces in the column names. For example: VAT Number will become VATNumber.
Once the database is ready, we will inform you about the login details, schema, table name and column names.

If you need an update to the database, send us an updated masterdata via Email and we will update the Database for you.

Advanced Scenario

Overview

Use this scenario when

executing multiple SQL statements at once
inserting into multiple tables which are linked.

This scenario should be used for performance reasons (if multiple queries need to be issued to get something like line items) or if a number of statements need to be in a single database transaction.

Overview

The input has the following levels:

Queries (array), each query has:
- SqlQuery (string, SQL statement)
- Parameters (array - e.g. multiple rows can be inserted for this SqlQuery or multiple queries can be issued)
  - object containing parameters - each set of parameters needs to define a value for each parameter defined in the SQL statement

The output has the following levels:

ErrorMessage (string, empty if no error)
Queries (array - each referring to one SQL statement / SqlQuery as specified in the request)
- Parameters (array - each referring to one entry in the Parameters array for this SqlQuery)
  - Records (array - resultset for one SqlQuery)
    - object containing one property per selected column (e.g. for select First, Last from … this would be First and Last)

For tables using IDENTITY primary keys (generated by the database on insert), amend the insert statement with ‘output inserted.<keyname>’. This will be returned in the Queries[query-index].Parameters[parameter-index].Records[0].<keyname>. To use this generated id in a later SQL statement, refer to it like shown in the example below.

If a number of insert / update statements have been issued and any of the SQL statements fails, a database transaction will make sure that none of the changes are committed.

Code Example

In the following example, two records are inserted into 'TestPerson, three records in 'TestAddress' - two addesses for contact PF and one address for contact JB:

var queryMultiple = {
    ConnectionString: 'dbConnectionString',
    User: 'dbUser',
    Password: 'dbPassword',
    Queries: [{
        SqlQuery: 'insert into TestPerson(Name) output inserted.Id values(@Name)',
        Parameters: [{
            '@Name': 'PF'
        }, {
            '@Name': 'JB'
        }]
    }, {
        SqlQuery: 'insert into TestAddress(TestPersonId, AddressType, Address) output inserted.Id values(@TestPersonId, @AddressType, @Address)',
        Parameters: [{
            '@TestPersonId': '{Queries[0].Parameters[0].Records[0].Id}',
            '@AddressType': 'Home',
            '@Address': 'PF's home address'
        }, {
            '@TestPersonId': '{Queries[0].Parameters[0].Records[0].Id}',
            '@AddressType': 'Work',
            '@Address': 'PF's work address'
        }, {
            '@TestPersonId': '{Queries[0].Parameters[1].Records[0].Id}',
            '@AddressType': 'Home',
            '@Address': 'JB's home address'
        }]
    }]
};

try {
    var data = straatos.data.queryMultiple(queryMultiple);

    if (data.ErrorMessage) {
        console.log('QueryMultiple error: ' + data.ErrorMessage);
        straatos.setError('QueryMultiple error: ' + data.ErrorMessage);
    } else {
        console.log('QueryMultiple output: ' + stringData);
    }

} catch(err) {
    console.log('QueryMultiple ajax error: ' + err);
    straatos.setError('QueryMultiple ajax error: ' + err);
}

The result of the above sample call looks like this:

QueryMultiple output: {"ErrorMessage":null,"Queries":[{"Parameters":[{"Records":[{"Id":15}]},{"Records":[{"Id":16}]}]},{"Parameters":[{"Records":[{"Id":14}]},{"Records":[{"Id":15}]},{"Records":[{"Id":16}]}]}]}

Create your own Knowledge Base

Database Lookup

Content

Basic Scenario

Goal

Code Sample:

Details

Creating and Updating the Database

Advanced Scenario

Overview

Overview

Code Example