Accessing data through tables

Tables are the objects that describe the raw data contained in related source data files. Tables in AX Server are the same as table layouts in Analytics.

Finding tables in the Server Explorer

In AX Server, tables are contained within folders. Like other folder items, you can access tables from the Server Explorer.

Note

The Server Explorer icon representing a table depends on the table type. For more information, see Tables icons and table types.

How to use tables

Table metadata

Tables use the following metadata to create a structured interpretation of the raw data in a related source data file:

  • field names
  • field lengths
  • field data types
  • field positions

Accessing data through the table

When you run analytics, the scripts use the table to access the underlying data stored in related source files.

Example

You have a table called Ar that has two fields you need to work with: Customer_Number and Trans_Amount. Using the OPEN command, you select the table to work with and then you use the SUMMARIZE command to process the data in the fields that you need to work with:

OPEN Ar
SUMMARIZE ON Customer_Number SUBTOTAL Trans_Amount TO "Customer_total.FIL"PRESORT

Extracting and appending to tables in the Data folder

When running an analytic script, you cannot append extracted data directly to an existing table in the Data folder. Instead, you must:

  1. Create a temporary table to extract and append to during the execution of the script.
  2. Extract the existing data to the temporary table.
  3. Extract and append the new records to the temporary table.
  4. Extract the records in the temporary table (existing records and new records) to the table in the Data folder at the end of your script.

    This will overwrite the contents of the data table with the existing and new records from the temporary table.

COMMENT create the temporary table using data from existing table
OPEN T_Existing_Data_Table
EXTRACT RECORD TO T_Temp_Table

COMMENT extract and append new data to temporary table
OPEN T_New_Data
EXTRACT RECORD TO T_Temp_Table APPEND

COMMENT Write temporary table content to data table
OPEN T_Temp_Table
EXTRACT RECORD TO T_Existing_Data_Table

Table types

AX Server supports five table types. The standalone table is the basis of the other four specialized tables:

  • Standalone tables the basic table layout that appears in Analytics
  • Master tables a standalone table linked to by one or more linked tables
  • Linked tables a shortcut table that links to one master table
  • Shared tables a standalone table that shares its data source file with one or more other tables
  • Server tables a table layout in Analytics that is linked to a source data file on AX Server

Layouts

You can disassociate a table from its underlying data to create layouts. A layout is a table layout with no underlying source data file that you can manipulate in analytic scripts.