Connecting to your Company Data

The very first thing anyone must do when using XLGL is to connect to a Simply Accounting Company. From time to time, we receive calls (and emails) from people who are unable to connect. In this article, we’ll look at the reasons you may be unable to connect to your Company Data.

The single most probable reason is that your user does not have Third Party Access right. By default, Simply Accounting users do not have the right to access the data from outside of the program. This includes sysadmin.

To assign Third Party Access right:

1. Open your Company Data in Simply Accounting;
2. Go to Setup, Set Up Users & Roles;
3. Enter the password for sysadmin and click OK;
4. Select the user from the list and click Modify User;
5. Under Rights With Third-Party Products, click Read-only access or Read/write access.
6. Click OK.

XLGL only requires Read access, but if you need to use other third-party, you may need to assign Read/write.

The second most encountered problem has to do with the user name. When accessing Simply Accounting, if you have a user called John, it doesn’t make a difference if you use John or john. The user name is not case-sensitive. However, when connecting from outside of Simply Accounting, this is not true anymore. If the user was created as John, you must use John and not john. A good practice is to have all user name in lower case.

Then, of course, there is the wrong password issue. Make sure you got your password correct. Same thing goes with the file. Sometime people make copies of their database, or change the name when they start a new year. Make sure you’re accessing the correct file.

Here’s a quick check list to finish:

1. Make sure you can open the data with Simply Accounting on the same computer where you are using XLGL;
2. Make sure you are opening the correct file;
3. Make sure you have the correct user name and that it’s spelled correctly;
4. Make sure the user has Third Party Access rights;
5. Make sure you have the correct password. Changing you password to something simple (such as 1) can help you find if this is the problem or not.

Did you face a different situation? Please, let us know.

Pierre Alain

Recalculate or Refresh

Very frequently, we receive questions about the difference between Recalculate and Refresh on the XLGL menu. Recently, someone asked the difference between the Excel recalculation shortcut (F9 and such) and XLGL Recalculate.

The quick answer is:

• Recalculate will update all formulas in the workbook, while making sure XLGL worksheet functions return the latest data from Simply Accounting;
• Refresh will update XLGL lists, adding/removing rows/columns as needed, and then recalculate everything.

Looking at the basic example of the Balance Sheet, Recalculate will update the balances, while Refresh will add new accounts to the list.

XLGL builds on the Excel calculation engine to provide flexibility and interactivity while keeping your report fully dynamic. There are two risks with this approach that the architecture of XLGL makes sure to avoid.

First, it could get very slow as each function act as a single query to the database. If a function takes 0.1 seconds to update because of network speed, you wouldn’t notice when you enter just one, but when you copy that over a column a hundred times, it would take 10 seconds to update.

Second, if the database changes while creating your report, you could end up with an unbalanced report. For example, imagine you create the assets part of a balance sheet. In the meantime, a transaction in Simply Accounting is made affecting both assets and liabilities. When you finish your report, you could have a report that does not balance.

XLGL avoids these problems with an architecture that buffers some part of the database. The upside is that only data needed to operate is fetched from the database into memory, resulting in very fast access and guaranteed to balance. The downside is that very recent transaction may not be available. This is very similar to the way Google operates.

So, what do you do to make sure you have the latest data from the database? This is where Recalculate and Refresh comes in. Using Excel’s recalculation shortcuts (F9) will only return the same data because the XLGL buffers are not refreshed. However, using XLGL Recalculate will refresh those buffers and then recalculate everything in the workbook, making sure everything still balance.

Now, data like account’s balance fit very nicely in a cell and can we can use the Excel calculation engine to update those easily. However, when the need come to enumerate data, such as a list of customers or transactions, then we need something else. This is why XLGL introduce Lists, an object similar to Excel’s QueryTable, but more powerful and adapted to our needs.

XLGL Lists, Excel QueryTables and PivotTables all have something in common. They will alter the worksheet by adding/removing rows and formatting. This is why Refresh functions are always kept separated from the calculation.

So, what should you use? Most of the time, you should be using Recalculate. Refresh is necessary when you have data that could add/remove rows/columns from your worksheet.

Refresh has giving some problems to people who lost their formatting and did not understand why. We are working on making this more user friendly. For now, just remember that Refresh can alter your workbook. If you’re unsure, save a copy of your work and test it.

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.