Exact Globe+

Excel Add-in: Data from different tables in one report using Excel Add-in functions

Introduction

This document describes how to get data from different tables using Excel Add-in. Different functions can be used. You can find the available functions at Exact Globe 2003, Function.

Description

For example, you want one report with all the general ledger accounts with descriptions plus the tax codes that are linked to the general ledger accounts, also with description.

1. Using the functions FILE_FIRST and FILE_NEXT, generate a column with all the general ledger accounts:
• In the first cell (where you want the first general ledger account number, for example, A2) choose the function FILE_FIRST at Function. The table where you can find the general ledger accounts is "grtbk". The index name is equal to the table name, and select the right column.
• In the second cell (where you want the second general ledger account number), choose the function FILE_NEXT. In the field Cell, fill in the link to the previous general ledger account. If you want this second general ledger account number in cell A3 and the previous general ledger account number is in cell A2 (using function FILE_FIRST), then fill in "=A2" in the field Cell. Fill in the same table and index like you did in FILE_FIRST and click OK.
• Select the cell with the second general ledger account number (A3 with the function FILE_NEXT) and copy this cell.
• Select a range of cells where you want to paste the general ledger account numbers. Be sure you select as many cells as you need to have all general ledger account numbers in the report.
2. With the function ACC_DATA or FILE_FIELD, you can add the descriptions that belong to the general ledger accounts. Go to the cell next to the first general ledger account number (B2) and go to [Exact Globe 2003, Function]. Choose ACC_DATA or FILE_FIELD.
• FILE_FIELD:
  Table is "grtbk". The Key is the general ledger account number, so link the cell in the first column, e.g. = A2. Do not fill in every general ledger account number directly, as you have to do this for every cell in the second column; you can easily copy the cell. The Index is "grtbk" and you can select the data you want at  Column.
• ACC_DATA:
  ACC_DATA is mainly the same function as FILE_FIELD. The difference is that ACC_DATA only gets data from the table grtbk and not from any other table. Therefore. you do not have to point to the table grtbk or its index.
  At GL account, you link to the cell in the first column, for example =A2. You can copy the function to a selected range. Select the column you want using F5, select for example "Description".
3. With the function ACC_DATA or FILE_FIELD, you can add the tax code linked to the general ledger account. Go to cell C2 and go to [Exact Globe 2003, Function]. Choose ACC_DATA or FILE_FIELD.
   • FILE_FIELD:
     Table is "grtbk". The Key is the general ledger account number, so link the cell in the first column, for example, =A2. The Index is "grtbk" and you can select the data you want at Column. In this case, select Tax code.
   • ACC_DATA:
     At GL account, you link to the cell in the first column, for example =A2. Select the column you want using F5, select for example "Tax code".
4. Use the function FILE_FIELD to add the description of the tax code. Go to the cell next to the tax code of the first general ledger account (e.g. D2), go to Exact Globe 2003, Function and choose FILE_FIELD.
   • Table is "btwtrs". The Key is the "Tax code", so in this case the key is cell C2. The index is equal to the table, so it is "btwtrs". Select Description at Column.
5. Copy the cells with the description of the general ledger account (B2), the tax code (C2) and the description of the tax code (D2) downwards.