Database integration

SmartSoft Invoices Online can connect to databases in order to download lists such as vendors, vendor IDs, GL Accounts, etc. It can also export directly into a specific database table or a view. Database integration for Import and Export is adjusted in the Batch type settings.

Import Settings

The Import functionality allows users to connect to a database and download different lists which can be used for validation. SmartSoft Invoices supports static lists and dynamic lists.

Static lists are adjusted in the Batch type settings and Dynamic lists can be adjusted with help of ERP functions.

Static Lists

Static lists are fetched from a database. They are defined in the Import settings section of Batch types in the Admin Panel.

Active Import module:

  • Select Odbc if you are going to use the desktop Verify client as it can only access an SQL database via ODBC.

  • The other clients support both SqlServer and Odbc modes.

Connection string: The connection string to the database where the list is stored. Depending on the Import module you selected above you will have to use the appropriate connection string format:

  • ODBC connection string
    • Windows authentication (no login name and password)

      Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes

    • SQL Server Authentication (with login name and password)

      Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

      Note

      Note the use of Uid and Pwd for user name and password keywords.

  • .NET Framework Data Provider for SQL Server
    • Windows Authentication

      Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

    • SQL Authentication

      Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Click Configure and then click + to add a new list.

For each list you need to select a Name, Table and a Column.

Filter: a WHERE SQL clause to filter the list.

Tip

Do not include the WHERE keyword.

Click Validate in order to finish setting up the list and click OK.

Last step is to add the list name into the form template so that it gets bound a specific field. This is done in the Form Designer which can be accessed through the Verify client. Launch Verify, go to File ‣ Edit Form Definition, highlight the template you would like to add a list to, scroll to the right and add the list name under the List column. Make sure to add it into the proper field or column, close the Form Designer and reboot the Verify client.

../_images/static_list2.png

Dynamic Lists

SmartSoft Invoices Online can calculate and dynamically populate a field from a database. A valid import connection string must be specified in the Bytch Type Import settings as explained in the section above. There’s no need to setup the Import lists and the Import list details, only the connection string. Fields will be populated dynamically by using an SQL query. To achieve this use the SQLEXEC() ERP function in the formula column in the Form Definition Screen for the appropriate dynamic list field. Flags Dynamiclist and AutoCalc must be raised for this functinality to work properly.

Further information regarding ERP functionas can be found here.

Note

The example below explains how to dynamically populate an approver ID based on a selected vendor. SQLEXEC(STRJOIN(“SELECT [Approver] FROM [approver] WHERE [Vendor] = ‘”,FT_VENDOR_NAME,”’”))

Synchronous Static Lists

You can connect 2 static SQL lists by using the Mappings feature. The value in the first field will automatically populate the value in the second field and vice versa. Launch Verify, go to File ‣ Edit Form Definition, select the field form you would like to create mappings for and click on Mappings.

A new screen will appear that will allow you to add Sources and Destinations. In the example below you will see how synchronous mapping can be added for Vendor name and Vendor ID.

The initial requirement here is that Batch type Import Settings must be set and validated. Proper Table, Name Column and ID Column must be selected.

../_images/mappings1.png

Next you must setup the Destination and Source for the mapping.

../_images/mappings2.png

First step is to add a Destination field and then setup its Source. Click on the Add button to the right in order to add a new destination. In the example above the first destination is the Vendor Number field, the destination field’s id can be changed if needed. In the Options column make sure to check Overwrite value and MapERPNametoID.

Next, a “source” field must be set. In the example above the first source field is the Vendor Name. Click on the field’s ID if you wish to change it. In the Options column make sure to check Exact.

The first part of the synchronous mapping is complete, changing the Vendor name will automatically change the Vendor number.

../_images/mappings3.png

Next, mapping for changing the vendor name based on the selected vendor number must be created. Click on the Add button to the right in order to add a new destination.

In the example above the second destination is the Vendor Name field, the destination field’s id can be changed if needed. In the Options column make sure to check Overwrite value and MapERPIDtoName.

A Source field must be set. In the example above the second source field is the Vendor Number. Click on the field’s ID if you wish to change it. In the Options column make sure to check Exact.

Click Close to save the changes.

Export Settings

To enable export to SQL select Activate SQL Database Export in the SQL Database tab in the Export Settings tab.

Important

SmartSoft Invoices Online will automatically create the database table upon export if it does not already exist. The name of the table is generated automatically, but user can pick a custom name by using the form designer of the Verify client under SQL Name.


../_images/export-sql.png

Database connection type: Select between SQL Server and ODBC. The connection string format has to match the connection type selected.

Database connections string: Connection string for your export database. Here are some examples:
  • SQL Server connection string -

    • Windows authentication: Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

    • SQL Server authentication: Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

  • ODBC connection string -

    • Windows authentication: Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes

    • SQL Server authentication: Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Database export mode: Select between: Fixed and Dynamic
  • Fixed
    The fixed mode does not use the information from the Document Form Definition (DFD). Instead, several standard tables are created in the database specified by the connection string. These tables are the following:

    • Document - the columns are BatchType, BatchId, DocId, DocType

    • Field - the columns are SourceImageId, FieldName (that is the field ID in the DFD), FieldValue, FieldType

    • TableCell - the columns are TableName, ColumnName (that is the column ID in the DFD), ColumnTitle, LineIndex, CellValue, CellType

    • SourceImagePath - the columns are DocId, ImagePath, PageNumber

  • Dynamic
    The dynamic mode exports the information from the DFD (the values of the exportable fields). The name of the SQL table for export is determined by the DFD property “SQL Name”. By default, it is ssi_<DFD name>_<8 hex digits>. The columns in the export table are the following:

    • migration

    • file_path - this is the path and name of the source file

    • DFD_name

    • LinkID - this is a unique ID for documents, that means records with same LinkID are from the same document

    • all fields and line items of the Document Form Definition (DFD). Each table row contains information about one line item.

Click Update Batch Type in order to save the Export settings.

Warning

You can not use the SmartSoft Invoices application database (SsiServerDb) as an export database.

Tip

Make sure you provide the user account under which the Export client is running with access to the export database.