XData and Family

Today, I’d like to introduce the new XData family of functions. Although these functions will be perfected and used later in higher level tools, they can be put to profit right now by advanced user to fetch any data from the database.

XData, XDataLookup and XDataCondition were introduced in version 2.9.9 of XLGL. XData is used to fetch data from database table. For example:

=XData("tCustomr", "sName", 1)

will return the name of the first customer in the database. The field name can be replaced by expression, such as:

=XData("tCustomr", "SUBSTRING(sName, 1, 5)+'.'", 1)

will return the first 5 characters of the first customer name followed by a dot (.). Aggregate functions can also be used. For example:

=XData("tCustomr", "COUNT(sName)")

will return the number of customer in the table.

You may have notice that the third argument is the index in the table of the record we want. Most of the time, you don’t know the index of the row you want. This is where XDataLookup and XDataCondition comes in.

=XDataLookup("tCustomr", "sName", "Ashburton Reinforcing")

will return the index of the customer named Ashburton Reinforcing, which can then be used in the XData function to retrieve other information on that customer:

=XData("tCustomr", "sCity", XDataLookup("tCustomr", "sName", "Ashburton Reinforcing"))

Of course, you can put both function in difference cells and reference them.

Sometime XDataLookup will return more than one index. For example:

=XDataLookup("tCustomr", "sCity", "Richmond")

will return the index of all the customer whose reside in Richmond. You can then get a count of these by using:

=XData("tCustomr", "COUNT(sName)", XDataLookup("tCustomr", "sCity", "Richmond"))

XDataCondition returns indices in the same manner but using any custom condition. For example:

=XDataCondition("tCustomr", "sName LIKE 'A*'")

will return the indices of all all customer that have a name starting with A.

In an upcoming post, we’ll look at how to use the XData family of function to create your own powerful worksheet functions.

XLGL and VBA

A question we are receiving more and more lately is: Can XLGL functions be called from VBA (Visual Basic for Application). The answer is Yes.

This is an advanced topic and, if you’re not familiar with VBA, you may be tempted to skip it. However, knowing a little VBA can save you tons of work on repetitive tasks. Starting with this post, we’ll talk more about XLGL and VBA, particularly the ability to create your own Worksheet Functions.

Although XLGL is not fully implement in VBA, it is still possible to call any functions by using Application.Run. For example:

Amount = Application.Run("XGL", "1020")

will return in variable Amount, the current balance of Account 1020.

The first parameter to Application.Run is always the function’s name followed by any function parameter you provide.

Menu command can be called in the same fashion. For example, to recalculate the workbook using XLGL Recalculate command:

Application.Run "XLGL__Recalculate"

To Refresh:

Application.Run "XLGL__Refresh"

This should give you a good head start on using XLGL with VBA. In further posts, we’ll look at custom worksheet function and answer any question we receive on the subject.