Introduction

This is used primarily to return a description or other attribute associated with a code.  The code is usually an account code, customer code, vendor code or a dimension name. Use Main Table Reference if you do not wish to re-type the description of a code each time it is changed in the spreadsheet.

As Main Table Reference is implemented as a function, it is refreshed on demand or whenever the cell containing the code changes.

You can build the Main table reference function in one of two ways:

·         Using the wizard

·         Typing the formula directly into a cell

Note:

SP3 multi-company reporting feature does not apply to this function

 

Building the function using the wizard

Introduction

To create a Main table reference function using the wizard, follow the steps outlined below:

·         Open the wizard

·         Choose from which data source you want to get the data from

·         Enter filters against that data source

·         Choose the Main table reference field to populate the cell with

Opening the wizard

This is done from the Atlas menu or from the shortcut keys as follows:

1.    Select the cell into which you want to put the formula

2.    Click the Dynamics AX menu option. (Alt+X)

3.    Click the Main table item in the list

The wizard opens adjacent to the cell you selected and will appear as follows:

 

Figure 23 Main table reference wizard

 

Choosing a data source for your formula

Here you select from which table or view the data will come from. The list will be limited to those data sources that have been nominated as available for use with Main table reference function using the Supported tables list inside Dynamics AX.

1.    Click the drop-down list to reveal a list of data sources

2.    Use your keyboard navigation keys or your mouse to highlight and select a source

 

Entering a filter for your data source

A filter applies constraints to the data on your data source. You might apply filters to restrict the range of values that are of interest to you. You will apply filters to the list of fields available in your data source. These are shown in the Range grid.

Filters can be typed into the wizard directly or they can be sourced from a cell or range of cells in your workbook.

1.    Click the row in the grid that contains the data source field you would like to filter on

2.    Enter the filter expression in the Range entry box or navigate to the cell or range of cells that contain the filter expression

3.    Repeat for each filter you wish to apply.

Note:

Filter expressions describe the filter being applied. They conform to the conventions used by Dynamics AX and are described in more detail at the beginning of this document. An example of a filter expression applied to an account range may be 40*, 50*, !5

Choosing a Main table reference field to populate the cell with

The Output options tab is used to define what you want to show as the reference field. Normally, this field will be a name or description but can be any field on the data source.

This field is normally filled in with a default value. You can choose to use this value or you can change it suit.

1.    Click the Output options tab page

2.    Choose the field name you want returned from the function. Use the drop-down to show a list of all fields for this view or table.

In the following example, the name of the ledger account 40110 will populate the cell.

 

Figure 24 Main table reference that populates a cell with the name of a ledger account selected from the worksheet

 

Using the wizard’s buttons

Lookup

Use this button to give a list of the possible reference items you are selecting when you enter a filter expression into the range box.

1.    Click on the row that contains the data source field you want to filter

2.    Click the Lookup button

3.    A form appears showing a list of items, use your navigation keys to select the value you need.

OK

Execute the request and close the form. This will build the formula, evaluate the formula, set any registry keys and then close the form.

Cancel

Closes the formula wizard and resets the cell formula to that when the wizard opened.

Apply

This is similar in nature to the OK button but will not close the formula wizard.

 

Building the formula without the wizard

Introduction

This is an alternative to using the wizard. It is not the preferred method, as it requires you to understand the structure of the function very well. However, including it here will help you understand the function’s structure so that you can modify any that you have already created.[6]

The steps involved include:

1.    Selecting the target cell for the formula

2.    Building the function by specifying its arguments

Select the target cell for the function

The formula operates like any other Microsoft Excel formula. You can create it from within the cell you have chosen. Simply enter the ‘=’ command and then type the formula according to the correct format. (If you omit the equal sign, Microsoft Excel will treat the formula as plain text and will not compute the result)

Select the cell into which the function will be put and then press F2 to switch on edit mode.

Building the function and specifying its arguments

The Main table reference function has a pre-defined structure, like all other Microsoft Excel built-in functions. Like all formulas and functions you can use the formula bar or you can type directly into the target cell to build the associated formula.

For Main table reference, type the following:

=AxaptaTable( argument list )

Where the argument list is:[7]

 

Formula argument

What to enter as the Argument…

Comments

“Version number”,

V35

 

“Company”,

Enter the Dynamics AX company account code. E.g. “DMO”

Dynamics AX company where data is source

“View”,

View or table against which the function will be performed. E.g. “LedgerTrans”

This must be expressed as an Dynamics AX’s AOT reference.

“Output field”,

The Output field is the item that will populate the target cell with.

The output field must be expressed as an Dynamics AX AOT reference. E.g. AccountName

“Field list, ….“,

Comma separated list of fields for which a criteria has been attached.

Optional. E.g. “AccountNum, TransDate”

If no criteria is used enter as “”, 

Field list criteria, ….

Comma separated list of criteria values. These values may be references to cells in the spreadsheet

Optional. E.g. “40*”, “CY” or

$B$4, $B$5

 

Note:

The criteria can be typed directly or can de derived from a cell reference. Depending on how you specify the cell reference can have an effect when copying functions in your spreadsheet. Excel adjusts cell references relative to each new location of the formula.

To override this behaviour, you need to indicate that a cell location in a formula is absolute rather than relative. You do this by inserting a dollar ($) symbol before the column and/or row designation. So, for cell B6 to be an absolute reference you will need to describe it as $B$6. Here are some other combinations:

 

·          Column relative, row absolute:      B$6

·          Column absolute, row relative:      $B6

·          Column and row absolute:                             $B$6

·          Column and row relative:                                B6

 

You can cycle through these alternative by using the F4 command on your keyboard.

 

Here is an example of a cell with the complete Main table reference function and the correct formula description:

And in the context of the worksheet:

 

When resolved gives:

Editing formulas

Editing Main table reference functions

Microsoft Excel allows you to edit a cell containing a formula in the same way as you would any other cell. Position the cell pointer over the formula and press F2 to enter edit mode. Move the insertion point to the argument or value you want to adjust and change accordingly. Press enter to make the changes permanent or use the ESC key to cancel the edit.

If you double-click on the cell the Main table reference wizard is opened and you can adjust the settings using it.

A worked example using Main table

Example

The following worksheet shows a range of rankings 1 through 5. Main table reference will be used to show the name of 5 revenue accounts. To do this the wizard will be used to create the first formula, which will be copied downwards to resolve the value for each of the other accounts.

 

Create the formula for the first ledger account

Open the wizard and complete as follows:

1.    Choose view Ledger transactions

2.    Click ledger account row in the range grid. The row is selected

3.    Click in the Range entry box beneath the grid

4.    Using the mouse click in cell A4

5.    Click the Output options tab

6.    Choose Account name from the drop-down list of field names

7.    Click OK to return the result

The worksheet now appears as:

 

Copy the formula down for all rows in the report

Copy the formula by selecting the small AutoFill square at the bottom right of the cell containing the formula and dragging that down for the extent of the account range, thus:

 

[6] Atlas functions are not included in Microsoft Excel’s function support wizard for this release

[7] The function argument column contains separator values needed by the function. E.g. quotes (“) or commas (,). All arguments must be separated by a comma (,)