Introduction to the SQL Browser

If you are more than expert than the average user and you know how to write SQL (Structured Query Language) then you will love the SQL Browser. Make any valid SQL query and run it.

The return or result of your SQL rows can be grouped, filtered and sorted with just a few mouse clicks. This SQL browser also allows drag and drop features, which make it easy to work with.

Anything can be exported to the XML (Extensible Mark-up Language), HTML (Hypertext Mark-up Language), Microsoft Excel, or Text File formats. This powerful tool will empower you to produce your own custom view of your data in TurboCASH and osFinancials.

Even if you are not an expert in the details or file structure of osFinancials, you can also use this SQL Browser to filter and sort to see what you need to see. You may also use the SQL Browser to filter, sort and analyse your data.

This option allows you to generate queries and connect to the Database of a selected Set of Books using the Structured Query Language (SQL) standard. This Browse will list all the Firebird Database Files (*.fdb File extension) for the Set of Books.

License

This program is licensed under a commercial license. A nag screen will be displayed when this plugin is launched and will pop up with regular intervals.

This Plugin is already included in the osFinancials releases. Before you can access the SQL-Browser plug-in, you need to activate it.

Commercial: Once-off payment

- Manual - Shop - Once-off license -

A program to edit your data in the Set of Books database. SQL knowledge is necessary.

Once you have purchased the SQL Browser plug-in, a key to unlock this plug-in will be available on the Register your osFinancials screen.

Using the SQLBrowser

To use this option, you need to open a Set of Books. The SQL Browser will list all the tables in the database of the active (opened) Set of Books.

To Access and Use the SQLBrowser:

1. On the Setup ribbon, click on the Plug-ins → Tools → SQLBrowser. The SQL Browser screen will list all the tables of the active Set of Books.
2. Select a table from the list.
3. Right-click on the selected table and select the Open option from the context menu. The data for the selected table will be loaded in the bottom half of the SQLBrowser screen. In this documentation, the “TRANSACT” table is opened. The transactions will be displayed as follows:

4. You may use the context menu's on each of the sections to work with your data, if necessary:

1. Tables – Open, Drop or Empty a selected table. You may also extract data (i.e. all Table names or the entire table including the properties (i.e. datatype and width) in the wiki format to the Script Editor all the Table names in a Set of Books. This can be copied and used in documentation and for development.
2. Script Editor – You may write or edit SQL script in this section. If the entire table or a selected record is exported as SQL script in the Records section, it will also be displayed in this section. You may also configure the editor font.
3. Records – This is the records in a table. For example, each line in a posted batch will create a record in the TRANSACT (Transaction) table. In this section, you may configure columns, filters, groups to view the data in a table. You may also export the entire table or a selected record as SQL script to the SQL Editor.

5. Once you are done, you may Export, Save SQL and Load SQL.
6. To exit the SQLBrowser, click on the File → Exit menu; or click on the Close button on the titlebar.

SQL Browser Menu System

File menu

 Options menu

Speedbar

The buttons on the speedbar, is as follows:

• Run SQL
• Browse older executed SQL (previous)
• Browse older executed SQL (next)
• Insert a new tab (i.e. TS2, TS3, etc.)
• Run script
• Run script set term

Tables

http://sourceforge.net/apps/mediawiki/turbocash/index.php?title=TC4:SQL_Browser_-_Extract_modal_for_wiki

http://sourceforge.net/apps/mediawiki/turbocash/index.php?title=TC4:SQL_Browser_-_List_of_Table_Names

SQL Script Editor

Writing SQL Script

1. On the top half of the SQL Browser screen, the following SQL script is displayed:

    Select * FROM followed by the name of the selected (active / opened) table. In this example,

    Select * FROM TRANSACT represents the TRANSACT (Transaction) table.

2. After the name of the table, leave a space, type where and leave a space and type the name of the column, (e.g. to list only those transactions for a specific date, type DDATE).
3. Leave a space and type an Operator symbol (e.g. to list all transactions equal to the value that needs to be entered, type =)
4. Leave a space and type a valid value (e.g. for the date, type '2009/03/16') This value must start and end with an apostrophe.
5. Click on the Run icon (or press the Enter key on your keyboard) to run the script.

The following few examples based on the TRANSACT table:

Font Editor

You may also customise the font in which the query data is to be displayed on the SQL Script Editor.

To Edit the Font:

1. Right-click on the SQL Script Editor section of this screen and select the Editor Font … option. Alternatively, click on the Options → Editor Font menu.

2. Change the font, font style, size, effects and colour as necessary.
3. Click on the OK button. The text in the SQL Script Editor will be displayed in the selected font.

Records

The data for the selected table will be loaded in the bottom half of the SQL Browser screen.

If you right-click on the records (table data) the following context menu is displayed:

Buttons to manage records

The following buttons may be used to navigate and manage the records:

- First - Go to the first record.

- Previous - Go to the previous record.

- Next - Go to the next record.

- Last - Go to the last record.

- Add - Add a new record.

- Delete - Delete a record. Once you click on this button, the following confirmation screen is displayed:

       

- Action - This will activate the Save or Delete buttons.

- Save - Save the changes to the record.

- Cancel - Cancel changes to the record.

- Undo - Undo the latest changes.

The Data records context menu

Show / hide Columns

The SQLBrowser displays all the columns for the selected table by default. You may filter (hide) or show only those columns you need to see.

To configure columns:

1. The SQL Browser displays all the columns for the selected table by default. Should you wish to hide any of these columns, you may click on the Options → Columns menu of the SQL Browser. 
2. Select the following options, if necessary:
1. Show / Hide Columns – You may de-select (remove the tick) the columns which you do not wish to view by simply removing the tick from the selected column. 

2. Sequence – Should you wish to change the sequence in which the columns are displayed, you may click on Down or Up buttons.
3. Once your selection is finished click on the OK button to apply these changes to the data in the SQL Browser.

Sequence of Data in Rows

All the data is by default displayed ascending; from the smallest to the highest value (e.g. a-z or 0-9) according to the record number or record ID (e.g. Accounts.DB table it would be the AccountID, in the TRANSACT table, it would be the TRANSACTIONID, etc.)

To change the sort order from ascending (e.g. a-z or 0-9) to descending (e.g. z-a or 9-0) select a column and click on it. If you click on it again it will change back to ascending sequence.

Filter Options in Column Headings

While viewing and analysing the data you may sort and filter the data, in each column of the active or loaded table. To do this, select the column and click on the  drop-down arrow. A list displaying the data as well as an All option and a customise option in the selected column will be displayed.

For example, the Reference column (i.e. SREFERENCE) of the Transaction table (i.e. TRANSACT) will display as follows:

The filter for this column (as per example) will list all reference numbers as entered in batches (journals) example, the reference for balancing entries (i,e, 8 asterisks ********) Reference column (i.e. SReference) of the Transaction Database table (i.e. TransAct.DB).

Custom Filter

You have the following options to sort or filter the data:

1. Select a specific entry from the list - only those entries which match the selection in the table, will be listed, e.g. with the same reference number.
2. All - Select to display all the entries in the selected table.
3. Custom - This will launch a screen on which you may set further criteria to filter the data.

1. Equal to - list or display all values which is the same as the specified value.
2. Not equal to - list or display all values which is not the same as the specified value.
3. Less than - list or display all values smaller than the specified value.
4. Less than or equal to - list or display all values smaller or equal to the specified value.
5. Greater than - list or display all values greater than the specified value.
6. Greater than or equal to - list or display all values greater or equal to the specified value.
7. Like - list all values in the table similar to the specified value.
8. Not like - list all values in the table not similar to the specified value.
9. Is null - excludes any value entered, will not be listed or displayed.
10. Is not null - is not zero - any value which is not equal to zero will be listed or displayed.

Date Filter

If you are working in a column of which the data is dates, the following date selector screen will be displayed:

You may then select a specific date to search or filter the data in the table.

Export the Data

Once you have sorted and filtered your criteria, you may:

1. Export the data in exactly the same sequence as displayed in the bottom half of the SQLBrowser screen to any of the following file formats:
1. XML - Extensible Mark-up Language
2. HTML - HyperText Mark-up Language
3. Excel - Microsoft Excel Spreadsheet
4. Text - Text file
2. Save the SQL in a (*.sql) SQL File format. You may then at any later stage open the saved *.sql file in the SQL Browser.

TABLES IN A SET OF BOOKS