SmartSoft Invoices - Online Help

Online documentation

Contact Us




Overview
Quick Start
10 Tips for Best Results
Using SmartSoft Invoices
User Interface
Main Window
Image View Panel
Form View Panel
Session View Panel
Loading Documents
From a Folder
From a Scanner
From an Email Account
From a Watched Folder
From Shared Watched Folder
Training the System
Validation
Data Verification
Verification
Batch Verification
Auto Complete
Multi-page Documents
Automatic Detection
Manual Separation
Blank Pages
Selecting ERP
Software settings
General
Interface
Scanner
Macros
Exporting
Exporting to CSV
Exporting to PDF
Exporting to XML
Exporting to TIFF
Exporting to SQL Database
Exporting to QuickBooks
Exporting to SAP
Exporting to MS Dynamics
Archiving
Customizing the Form
Form Template Designer
Adding New Fields
Adding New Line Items
Mapping
Validation Formula Language
Lists
Importing Lists
Importing Custom Lists
Synchronous Lists
Selective Processing
Migrating to a new computer
Migrating document templates
Migrating custom forms
Scan Quality
Client/Server Deployment Option
Deployment
Input Station
Verify Station
Licensing
Support

Export to a SQL Database

Enabling SQL Integration

To enable SQL integration click File -> Integration and select SQL. The SQL tab will be presented in settings.

SQL Settings

Click Tools -> Settings and go to the SQL tab.
Note: If the SQL tab is not present see Enabling SQL Integration above

Connection Settings

The software uses a connection string to connect to the database.
  • In the Import connection string field type the connection string for the database from which you will fetch data for lists, such as Vendors, Products, GL accounts etc.
  • In the Export connection string field type the connection string for the database, where you will export the data from SmartSoft Invoices once it is captured from the documents.

You can use two different databases for import and export or you can use the same database.

Sample Connection Strings:

    Microsoft SQL server

  • Driver={SQL Server};Server=myServerAddress;Database=myDataBase;
    (uses Windows authentication)
  • Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
    (uses Username and Password)
  • Important for MSSQL Server! Be sure that you use UID and PWD for username and password keywords!

    MySQL server

  • Driver={MySQL ODBC 5.3 ANSI Driver};Server=localhost;Database=sakila;User=root;Password=admin;
  • Driver={MySQL ODBC 5.3 UNICODE Driver};Server=localhost;Database=sakila;User=root;Password=admin;

    Oracle

  • Driver={Oracle in OraHome92};Dbq=myTNSServiceName;Uid=myUsername;Pwd=myPassword;

    Azure SQL server

  • Driver = {SQL Server Native Client 11.0}; Server=tcp:sample.database.windows.net,1433; Database=test;Uid=sample@sample.server;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

    Microsoft Access file

  • DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};FIL={MS Access}; DBQ=C://Users/user/Desktop/database.accdb

If the system connects to the database using the specified connection string a green check mark will appear next to the text box.

Note: Setting an exporting schema can be done with using the keyword "CurrentSchema=" in connection string to set constant schema for the exporting.

Importing Lists

To import some lists from a SQL database you first need to define those lists in SmartSoft Invoices.

  • Defining a list - There are some predefined lists such as Vendors, Items, etc. To define a custom list click File -> Import -> Custom List. A wizard will open that will let you select a name for the new list. Next you will bind the list to a field in the form, by specifying a field ID (e.g. FT_VENDOR_NAME) as defined in the Form Definition Screen
  • Binding a list to a from filed - Binding is the connection between a List and a Form Field. You define this relation when creating a new list by specifying a Field ID. To change a binding later click Tools -> Settings -> SQL -> Edit Bindings or click Shift + F9.
    Note: You can bind one list to multiple fields. Then the list data is shared between those fields. This can be useful if you need to show Vendor name and Vendor ID in two sepate drop down lists that are changed synchronously.
  • Associating a list with a database table column
    • In the List column select the list for which you want to import data.
    • In the Table column select the SQL table from which you want to import data.
    • In the Name column select the SQL table column from which you want to import the data.
    • In the Id column you can optionally select the ID table column so it can be paired with the Name. This column is optional and is used if you need to share a list between two drop-down fields, e.g. Vendor Name and Vendor ID.
    • In the Filter column you can optionally specify a SQL WHERE clause to filter the list.

Exporting SQL Settings

All SQL settings and bindings can be exported to a file which can be transferred to another computer or saved for later usage. To save the Settings in a file click Export Settings

Fields with SQL formulas

You can validate a field's value against an SQL table by including validation formulas containing SQL queries in your Form Definition. Thus a SQL query will be executed in run-time and the result will be shown in a field. Use the SQLEXEC() function in the Formula column in the Form Definition Screen.
Samples:

  • Normal string - if you don't need to use other fields' values in the query:
    SQLEXEC(“SELECT DISTINCT Last_Name FROM clients_table WHERE ID=123 ”)
  • Joint string - if you need to use other fields's values in the query use STRJOIN you to concatenate the parts of the querty so that you can include in the qury field names as variables.
    STRJOIN("SELECT Last_Name FROM clients_table WHERE ID=' ", FT_CLIENT_ID, " ' ")
    Note: Make sure that your SQL query returns only one column as a result.

Dynamic lists

To dynamically populate a field from a database you have to set the Dynamiclist flag to a field in the Form Definition Screen. Such fields will be populated dynamically by using a SQL query. To achieve this use SQLEXEC() in the formula column in the Form Definition Screen. Pass a SQL queriy as a parameter to the SQLEXEC() function to fetch a list from the database. If your query uses the values of other fields, the dynamic list is automatically calculated after that field is changed.

Default SQL exporting data types

We are using default types for our formats:

  • Text, InvoiceN, BGTaxNum: VARCHAR(255)
  • Integer, INTEGER or NUMBER dependens from database.
  • Money, Decimal: REAL, FLOAT OR NUMBER
  • Date, MonthYear, DayMonth: DATE
  • Bool: BIT or NUMBER(1)
  • Misc. parameters SQL settings for fields

    Currently we support two parameters, for SQL function and SQL data type. With “SQLFUNC=” you can use any function for the field, like GETDATE(), DAY(), PARSE and etc. Also you can use “SQLTYPE=” to set data type for exporting, if you set INT the field will be exported in the database as an integer, you can use any data type which your provider supports.

    Exporting to a SQL table

    When the user clicks Export to SQL the software will create a table (if it does not already exist) and export the capture data to it. If the table already exists the systrem will append the new data to that table. All the captured data in a document is exported to a single table. The name and the structure of the table is unique and depends on the fields and columns in the current Form Definition.

    The SQL query used for creating the table or appending data into an existing table is:

    CREATE TABLE <TableName> (
    	ID INTEGER PRIMARY KEY IDENTITY(1,1), 
    	migration BIT NOT NULL,
    	file_path VARCHAR(255),
    	ftf_name VARCHAR(255) NOT NULL,
    	LinkID VARCHAR(255),
    	<form_field_1 VARCHAR(255)> , <form_field_2, VARCHAR(255)>, … ,
    	<table_field_1, VARCHAR(255)>, <table_field_2, VARCHAR(255)>, … , 
    )
    

    where:

    • file_path is the path to the archived document if you have defined a folder for archiving.
    • LinkID - this is a unique ID for documents, that means records with same LinkID are from the same document.


    Copyright ©, SmartSoft. All rights reserved.