Free SQL-Ledger User Guide

Today International SQL-Ledger Network Association releases free user guide for Ledger123. (the enhanced SQL-Ledger)

This user guide has been written by Sebastian Weitmann and me over the past one and half year. A lot of discussion happened on email and skype while we planned and wrote this book. Finally we met in Cologne, Germany last month (Dec, 2014) and put the finishing touches to this work.

And here you have its first edition in your hands now.

Visit ISNA website to get your free copy.

Sebastian Weitmann and Armaghan Saqib

Sebastian Weitmann and Armaghan Saqib

Visiting Zurich and Cologne

I visited Zurich (Switzerland) and Cologne (Germany) this month (Dec. 2014) to see Thomas Brandle and Sebastian Weitmann. Purpose of this visit was to discuss and plan the future development of SQL-Ledger for next few years.

SQL-Ledger is a 16 years old open source software written in Perl. Now is the time to take it to next higher level of functionality using a modern web framework. We decided to use Mojolicious for our major future development of SQL-Ledger. Details will be shared on mailing list as we make progress.

Thomas Brandle’s company, Run My Accounts, is the biggest success story of very large scale SQL-Ledger deployment. Run My Accounts experiences with SQL-Ledger and challenges faced with its use for a very large user base is helping us to shape the new functionality in SQL-Ledger.

Sebastian Weitmann and I also finished our combined project of writing a comprehensive user guide for the users of Ledger123. This user manual is available free from ISNA website.

We also met Geoffrey who is the lead developer and maintainer of kivitendo which is another SQL-Ledger fork. We discussed sharing our development efforts and knowledge with each other.

Thomas Brandle and Sebastian Weitmann have been sponsoring the development of new features as well as bug fixes for last many years. This visit was also courtesy of both these gentleman who not only took care of all traveling and staying costs but were also proved to be wonderful hosts taking care of each and every small detail for me.

Website upgraded

We have upgraded ledger123.com for easy of use and clarity. The simplicity on this website is intentional. Users who are new to SQL-Ledger can view the overview video on the home page to get an idea how SQL-Ledger looks like.

SQL-Ledger mailing list is also being revived. It went down last year (along with our website) when the hosting service provider (textdrive.com) tanked without any prior notice.

Please keep sharing suggestions for improvements.

SQL-Ledger Virtual Box Appliance

We have created a new sql-ledger virtual appliance using virtual box. Here are the details:

  1. Built using: Sun’s virtual box
  2. RAM: 256MB
  3. Hard disk: 10 GB
  4. Guest operating system: FreeBSD 8.2
  5. Packages: Enhanced SQL-Ledger, Postgresql 8.4.7, Perl 5.10.1, git 1.7.3, phpPgAdmin 5.0.2, Lighttpd 1.4.28, Postfix 2.8
  6. Root password: none
  7. Postgresql super user: pgsql, password: none
  8. SQL-Ledger admin.pl password: none

Quick Install Instructions

  1. Download virtual box for your operating system.
  2. Download SQL-Ledger virtual appliance from your order confirmation email. (filename: FreeBSD-8.2.ova)
  3. Import this virtual appliance into virtual box using menu option: File->Import Appliance.
  4. Change network settings to ‘Bridged’ using Machine->Settings menu option. Select the network interface (eth0, wlan0 etc.) to bridge this appliance with.
  5. Boot virtual appliance using ‘Start’ button on virtualbox toolbar.
  6. Watch booting process carefully and note down the virtual appliance IP address.
  7. In your browser, visit http://appliance-ip-address/ for home page.
  8. Visit http://appliance-ip-address/ledger123/ and login to pre-installed database with user ‘admin@ledger123′ and without any password.

For support, send email to support@ledger123.com.

VirtualBox SQL-Ledger Appliance

This virtual appliance comes pre-configured with our enhanced SQL-Ledger as well as LedgerSMB. Follow these instructions to get up and running:

Summary

  1. This virtual appliance has been built using FreeBSD 7.3. You do not need to know or install FreeBSD to use it.
  2. Download and uncompress this virtual appliance (url is in your order confirmation email.)
  3. Click ‘New’ to run virtual machine creation wizard and create a virtual machine with following specs:
    1. Name: SQL-Ledger (or whatever)
    2. Operating system: BSD
    3. Version: FreeBSD
    4. Memory: 256 MB
    5. Boot Hard Disk: Use existing hard disk (Browse to add the uncompressed hard disk image in above step)
    6. Change the network settings to ‘Bridged’
    7. Boot your virtual machine.
  4. Root password: ledger123
  5. IP Address: Login to appliance from console using root/ledger and issue ‘ifconfig’ to find the appliance IP address.
  6. Using your browser visit http://appliance.ip-address/. Here you will find links to sql-ledger, sql-ledger admin and phpPgAdmin.
  7. There is no password for sql-ledger admin screen. (You can set password after login)
  8. Create your datasets using ‘sql-ledger’ postgres user. This user does not require any password.

Continue reading

Generic CSV Import

It is easy to import CSV data into sql-ledger which is not support by ‘Import’ menu.

  1. Export your data from spreadsheet or software in a suitable CSV format. Assume it is named “/tmp/cities.csv”.
  2. Create a suitable table if it does not already exist.

    CREATE TABLE customertmp (id INTEGER, city TEXT);

  3. Add following lines at the top of your CSV file (adjust for your needs).

    COPY customertmp(id, city) FROM STDIN CSV HEADER;

    Your final csv file should look like this:

    COPY customertmp(id,city) FROM STDIN CSV HEADER;
    id,city
    "11733","Newyork"
    "11748","London"
    "11752","Paris"

  4. Import data using following command:

    psql -U postgres your-db-name < /tmp/cities.csv

Notes:

  1. You can directly import into standard sql-ledger tables but I find it easier and less error prone to import data into a temporary stagging table and then use INSERT/UPDATE statements to update standard tables.
  2. This method works with LedgerSMB databases as well.
  3. Gnumeric has more flexiable CSV export facility than openoffice calc. (On my Ubuntu 10.04 it took just 'apt-get install gnumeric' to get it installed.)

LedgerSMB Virtual Appliance

LedgerSMB is particularly difficult to install and configure for newbies due to its dependence on few CPAN Perl modules, fixes required and creation/configuration of a central database to store users.

Our sql-ledger vmware virtual appliance now comes with LedgerSMB 1.2.21. (with our enhancements.) It is fully configured with a blank dataset and one user. You just download it and start using it immediately. (You can create more datasets and users yourself through web interface.)

You can easily upgrade to the newer version (as well as to our new enhancements) by running ‘git pull’ in the ‘/usr/local/www/data/ledgersmb/’ folder.

Order this appliance from our order page. Your order confirmation email will include the download link.

Instructions on using vmware appliance are here.

Vmware Virtual Appliance

This virtual appliance comes pre-configured with our enhanced SQL-Ledger. Follow these instructions to get up and running:

Summary

  1. This virtual appliance has been built using FreeBSD 6.4. You do not need to know or install FreeBSD to use it.
  2. Root password: none
  3. IP Address: Login to appliance from console using root (without password) and issue ‘ifconfig’ to find the appliance IP address.
  4. Using your browser visit http://appliance.ip-address/. Here you will find links to sql-ledger, sql-ledger admin and phpPgAdmin.
  5. There is no password for sql-ledger admin screen. (You can set password after login)
  6. Create your datasets using ‘sql-ledger’ postgres user. This user does not require any password.

Continue reading

CSV Export

You can export data from your sql-ledger tables using following command in ‘psql’.

COPY (SELECT id, partnumber, description FROM parts ORDER BY partnumber) TO '/tmp/parts.csv' CSV HEADER;

Usage Guidelines

SQL-Ledger is a browser-based application. Anybody familiar with internet browsing should feel at home using sql-ledger.

All Sql-Ledger user-interface elements (menu, forms, reports) are displayed using standard HTML. You can use any modern browser to run the application. Firefox is recommended.

Main menu

Sql-ledger menu is displayed in left frame. Menu items can be collapsed into sub menus or individual menu items by clicking them.

Update button

Lookup information (customer/vendor address) and calculations (invoices subtotals etc) are not automatically updated on a form after a change. You need to click the Update button to do these.

Update button does not store anything into database. It just looks up appropriate missing information, does any pending calculations, and adds one more line on multi-line forms like invoices and orders.

It is safe to click Update button as many times as you like. You can also trigger update action by pressing ‘Enter’ key in any text field.

Adding, changing and deleting records

You can add a new transaction (order, invoice etc.) or a master record (part, customer, vendor etc.) directly through the module menu.

To change or delete a transaction or master record, you use the Reports menu option in the sub menu. Once you display the report by clicking the Continue button, you can use the hyper links on report to change or delete the record.

Example

For example to add a new sale invoice, you click AR–Sale Invoice. To change or delete an existing invoice, you click AR–Reports–Transactions. A selection form is displayed where you can specify date range and include/exclude columns.

When you click Continue, all existing invoices are displayed. Now you can open any existing invoice by clicking invoice number hyper link.

Customers and vendors lookup

There are two ways customer / vendor can be selected on an invoice (or order, quotation).

Method 1

By default customer and vendors are displayed as drop down menus on invoices, orders and quotations.External Link

Method 2

If you have a large number of customers and/or vendors, it is more convenient to select customer by typing its number or name. To do so, goto Preferences and change Dropdown Limit to 0. Now invoice form will allow you to enter customer number or part of customer name to select it.

Parts Lookup

There are three possible ways you can lookup a part on an invoice, order or quotation.

Method 1: Specify Part Number

You can lookup a part by its number.

If you enter partial number, all parts matching this number will be displayed.

You can select the required part and click Continue to return back to invoice screen.

Method 2: Specify Part Description (Name)

You can enter complete or partial part description. If there are more than one part descriptions which match, these will be displayed.

Method 3: Specify Group to List Items in the Group

Departments, warehouses and projects

You can classify your transactions by specifying department, warehouse or project during transaction entry. More details on using these features can be found else where on this wiki.

The important thing to note is that you need to define at lease one department, warehouse or project before the respective drop down list will appear on transaction forms.

Item notes, serial numbers, delivery date, packaging information on invoice lines

By defaults line items on invoices show most commonly used columns.

If you want to enter more information against each line item, check the box next to Description heading on line items. Now when you enter an item code and update, you will see additional columns for delivery date, item notes, serial number, project, packaging, net weight, gross weight and volume.