Linking from a spreadsheet to a database

Suppose you have a database called accounts, containing a table called invoices. Among other fields in the database table, there is a field for the total of the invoice, invtotal, one to identify the customer, custid, and two more for the date of the invoice and the date it was paid, invdate and paydate. The particular customer you are interested in has a custid entry of 1024. In a spreadsheet, you want to analyze his payment history, perhaps plotting a graph to show the payment trend. Follow these steps:

  1. Display the first invoice total: Enter the following formula:

     =DBSQLFILTER("accounts.adb", "invoices", "custid=1024", "invtotal", 1)

    Let’s explain this in some detail. The first two parameters to this function are simply the database name and the table name – note you need to include the document extension with the database name (adb). The table contains invoices to many customers, so you have to identify which customer you want to return values for. This is done with the third parameter to the function – "custid=1024". Next, we specify which field is to be returned with the third parameter – invtotal. The function actually returns all the fields matching the condition, so we need to say which one is to be displayed. The final parameter says "display record 1".

  2. Display the invoice and payment dates. In the cells to the right of the invoice total, enter the following two functions:

     =DBSQLFILTER("accounts.adb", "invoices", "custid=1024", "invdate", 1)
     =DBSQLFILTER("accounts.adb", "invoices", "custid=1024", "paydate", 1)

  3. Repeat steps 1 and 2 for the rows below, incrementing the record number (the fifth parameter) by one each time, until all the matching invoices are displayed.

  4. You can now analyze the data in several ways. Here are two examples:

    Produce an average payment period by subtracting the invdate column from the paydate column and dividing by the number of invoices.

    Plot a bar chart of the number of days it takes to pay each invoice (again by first subtracting the invdate column from the paydate column).

There are a number of ways this example can be improved and made more general (so it will work easily for other customers).

See:

Refining database links.