Config / Mappings / Read and Write Table Data

Read and Write Table Data

It is important to note that you can access the columns from that table instance only from mapping lines that are childs of the table instance. The indentation of the table rows must therefore be made in such a way that all rows that want to access the data are children of the table. For this purpose, several tables are often nested under each other to gain access to all fields.

To read or write to tables inside Microsoft Dynamics NAV 2013 you have to declare an instance of that table in the mapping first. Choose the value Loop in the mapping line Type property. This will change the available properties and add a new property called Loop Type. Select the value Table as the Loop Type.

Afterwards you can configure the other properties. The following properties are available:

Loop Type

The type of the loop. Select the value Table to access table data.

Mode

Whether to read data from a table, write data to a table (create a new record) or update existing data.

Read

Read existing data. The data can be filtered. It is possible to apply filters, so that no record is found.

Write

Create a new record in the table. Depending on the format type one or more records can be created.

Update

Update an existing record or optionally create a new one, if it does not exists. The module can only update one record at a time, so ensure to apply filters so that at max one record is found. To update multiple records, you have to create a table loop reading the data and a second instance of that table in update mode filterd to the primary key of the currently read record.

Table

Selects the source/target table from Microsoft Dynamics NAV 2013. You can either enter the name of the table or it’s ID.

Name

The name of this table instance in the mapping. The name is used to differentiate between different instances of the same table. Think of it, like a variable name.

Data View

This property is new in version 4.00 of the module.

All Records

Do not apply any special filters. You can still filter the data with the properties below.

Post Process Record

Only usable, if this mapping runs as a post-processing. Filter the table instance to the record the post-processing was defined on.

Business Transaction Data

Only usable, if this mapping runs inside a business transaction. Filters the data to the business transaction data (e.g. the job data).

Named Table View

Filters the data by a named table view. This can either be one of the build-in table view names or any table view that is defined by programming. A new property will become available to select the name.

Table View Name

This property is only available, If Data View is set to Named Table View. You can use any table view defined by code, or one of the following special ones:

TRANSMISSION

If there is an incoming transmission, you can access it with this table view.

PROCESSINGQUEUE

If the mapping is running as an error handler, you can use this filter to access the failed processing queue entry.

POST_PROCESS_REC
DEFAULT

These filter names are usually used by the module to store information about the post-process record. You should set the value of the Data View to Post Process Record instead. But if you upgrade mappings from older versions of the module, you might still find these values, which will still work.

The names can also be defined from custom code and contain any filter. We do not recommend top use these names anymore. Please use a speaking filter name or CUSTOM instead.

Limit Count

This property is new in Anveo EDI Connect 4.00.

Only available in read-mode. Specifies whether you want to limit the count of record that can be found. Can be used to select only the first or last record in combination with the sorting property.

True

Limit the records to the specified count.

False

Do not limit the returned records.

Select Top

Only available if Limit Count is set to True. The maximum number of records to be used.

Table View

Read-only. Groups other properties. The name Table View is used in programming Microsoft Dynamics NAV 2013 to describe the combination of the sorting and constant filters on a table. We’ve used this name to make it easy for C/AL programmers to use the module, think of it as constant filters and initial value.

Key

The key that should be used for sorting. Can be empty.

Order

The order in which the key is applied. The default is Ascending.

Filter

Filters that are constant or in other words do not depend on data from another table. You can filter all columns from the table.

If the Mode of the table is Write the filters are applied as initial values to the table. So you can also use the filter to specify some of the column values for new records.

Data Item Link

This property is used to define the relation to another table. This is a list of values. You select the column from the current table instance first and another table and fields afterwards. This property is used to define the foreign-key relationship.

Let’s assume you have a table instance of the table EDI Document in the mapping and as a child an instance of the table EDI Document Line. To specify that the line belongs to the header document, you define the Data Item Link as:

"Document Type"="EDI Document"."Document Type", "Document No."="EDI Document"."No.", "Document Version No."="EDI Document"."Version No."

This will filter the specified columns to the value of the header document. In read mode you will only get the lines belonging to the document. In write mode the columns will be populated with the data from the document, so that the line belongs to the header.

If the Mode of the table is Write the Data Item Link is applied as initial values to the table columns. So you can also use the Data Item Link to specify some of the column values for new records.

Temporary

This is an advanced property and normally hidden.

Specifies that you want to use a temporary table. A temporary table is newer written to the database.

Init Function

This property is new in Anveo EDI Connect 4.00.

The init function property is only available, if the property Temporary is set to True. This function is used to populate the temporary table, when it is initialized.

Object

The object name the function is defined on.

Property/Function

The function name. The function should return an object of type Codeunit ANVEDI Variant pointing to a RecordRef that contains the initial data.

Company

This is an advanced property and normally hidden.

The company the record is read from / written to. This can be used to access data from a different company.

Do not change the company on table instances in write mode, except you know what you’re doing. All triggers are executed according to the data from the current company, so that you should never write to any standard table in a different company directly.

TotalFields

This property is available only for tables in read mode. You can specify columns for which the module will automatically calculate an aggregate, such as a sum. You can read the aggregated value from the table by accessing the column after the table line, but not as a child line of the table loop.

The following aggregate functions are available:

Sum

For numeric fields only. The result is the sum of the individual values. Can be used to calculate the document amount based on the line amount, etc.

Min

For numeric fields only. The smallest number.

Max

For numeric fields only. The largest number.

First

The value from the first record found.

Last

The value of the last record that was read.

Tip: You can use this functionality for setup tables. Place them at the top of the mapping and define “First” aggregates for the fields you want to use in the mapping. You can now access the properties anywhere below this row in the mapping without having to indent everything.

Register Errors On Instance

This property is new in Anveo EDI Connect 4.00.

This is an advanced property and normally hidden. Whether you want to automatically register errors after this line to this table instance.

True

Register errors on this table instance. This makes sense for buffer tables.

False

Do not register errors on this table instance. This makes sense if the instance access, for example, secondary table information.

Linked Documents

This is a read-only property to group the child properties.

Create Linked Documents

Whether you want to create entries in the linked documents table for this table instance. Settings this to True will allow you to navigate from the EDI Processing Queue, from the EDI Transmission (if present) and from the EDI Business Transaction (if present) to this table.

Linked Tables

This property is only available, if Create Linked Documents is set to True. You can specify other table instances from this mapping that should be linked to the current table instance. This allows you, for example, on the EDI Document to navigate to other linked documents, like a created sales header.

Internal No. Display Field

You can select the column that should be used for the internal number, whenever a linked document entry is created for this table instance.

External No. Display Field

You can select the column that should be used for the external number, whenever a linked document entry is created for this table instance.

Post-Processing

Allows you to specify one or more actions that should be executed on each record of this table instance, after the mapping was successfully ended. Post-processings are not executed, if there were any errors. You can learn more in the post-processings section.

Min. Repeat

If you enter a number the mapping will throw an error, if the specified minimal repeat is not reached.

Min. Repeat Error Message

The error message that should be raised if the minimal count of records are not found.

Max. Repeat

If you enter a number the mapping will throw an error, if the specified maximal repeat is not reached.

Max. Repeat Error Message

The error message that should be raised if the maximal count of records is exceed.