Define and read tables

You might need extra information to process your input data and generate the outputs. Most of the time, this additional information is defined only one time and read while generating the outputs.

Examples of such additional information are the partner rates in your FoodBroker project. You can imagine many more examples: the list of currency exchange rates, a table for tax rates, a list of customer identifiers… In all these cases, the additional information that you need is similar to a dictionary, you:

  • Know the partner name and you are searching for the related rate
  • Are dealing with an amount in Swiss Francs (CHF) and want to know the rate to obtain Dollars (USD)
  • Know the tax code (1,2,3) and you need the corresponding rate

Have the customer's name and you need its internal id

In all these situations, the input provides a key that you use to search for additional information that relates to that key.

In the integration engine, you can define and use such dictionaries. Each integration engine Table Object is a storage area in which data is organized in tabular form. When defining a table, you create as many columns as you want. For each column, you select whether it is a key column or a value column:

  • Key columns are used to select only a significant row among all existing; in the previous examples, the partner name, the currency name or code, the tax code or the customer name are key columns.
  • Value columns contain the data bound to the keys that you want to retrieve; in the previous examples, the partner rate, the currency rate, the tax rate or the customer id are value columns.

In addition to keys and values, the integration engine automatically defines two columns that state the period during which each row is valid; these are Start Date and End Date.

To define a table:

  1. In the Project Explorer right click Extended Objects - Tables in your project and select New -> Table.
  2. Defining a table using the Project Explorer
  3. When you select the Table command, a wizard for creating a new table object opens. Type in the name for the new table. Click Finish to create the table. The Table editor opens:
  4. Wizard for creating a new table object
  5. In the Table Details section you can define a Start Date, an End Date and you can select or deselect the option Send table contents to the server.
  6. In the Table Content section you can add columns using the context menu on the list header:
  7.  Table Content section
  8. When you click Insert a column before this column, the Column properties window opens. Here you define the new column.
  9. Column properties window
  10. Use the Name field to set the name of the column and the Label field to define an optional detailed description of the column contents. You must select the class for the value of the column. If you want to define to column as a key, just select the Key Column check-box.
  11. To add lines (or rows) in the new table, click the first button (green plus) of the toolbar at the right side of the list view.
  12. Adding lines or rows in a new table
  13. A new line in the main frame appears. In this new line you can enter the values of each column.
  14. The table defined above is a substitute to the XML file for partner rates in this FoodBroker example. Each row relates the rate (Rate) for a partner with its name (Name).
  15. Check and save the object.

The columns and lines that you defined in Axway Mapping Services are immutable during data generation. If you need to alter a table you have to use Axway Mapping Services. DML is reserved for table reading. In DML, you have functions to check whether at least one line exists in a given table that matches a set of keys. Here is an example:

 if isKeyPresent(entity.folder1.TBL_PartnerRates, "Jaleo") then
    addTrace("INFO", "Jaleo partner exists in the table")

The few lines above check whether a line exists for the Jaleo partner. The function IsKeyPresent returns true if such a row exists and false otherwise. The very first argument of IsKeyPresent is the name of the table and can be fully qualified or abbreviated. The remaining arguments are the values for the keys following the order defined in Axway Mapping Services. Consider the following table defined in Axway Mapping Services:

Table defined in Mapping Services

To check whether a row exists in this table, your calls to IsKeyPresent must contain real arguments for each of the three defined keys: 

if isKeyPresent(TBL_example, "K1", "K2", "K3") then
    addTrace("INFO", "this is line 2")

Calls to isKeyPresent ignore Start Date and End Date columns. To select rows that match a certain date, you must use isKeyPresentInPeriod instead. The following expression:

%has_rate := isKeyPresentInPeriod(TBL_PartnerRates,
                                  "Bombay Club Restaurant",

sets the variable has_rate to true because the period between Start Date and End Date of the 1st line of TBL_PartnerRates contains Oct 9th, 2007.

You can also extract values from a table using calls to returnColumn as follows:

%rate := returnColumn(TBL_PartnerRates, "Old Ebbit Grill",

As for isKeyPresent, arguments are the name of the table followed by the key values. Then, when your table contains more than one value column, you must append the name of the one that you want.

When a call to returnColumn is evaluated, the integration engine goes through the specified table to find the first row which matches the specified keys. If no match is found, the call is evaluated to absent.

Selection with Start Date and End Date can be done with returnColumnInPeriod as checking with dates was done with isKeyPresent.

Related Links