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 2017 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:
The type of the loop. Select the value Table to access table data.
Whether to read data from a table, write data to a table (create a new record) or update existing data.
Read existing data. The data can be filtered. It is possible to apply filters, so that no record is found.
Create a new record in the table. Depending on the format type one or more records can be created.
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.
Selects the source/target table from Microsoft Dynamics NAV 2017. You can either enter the name of the table or it’s ID.
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.
This property is new in version 4.00 of the module.
Do not apply any special filters. You can still filter the data with the properties below.
Only usable, if this mapping runs as a post-processing. Filter the table instance to the record the post-processing was defined on.
Only usable, if this mapping runs inside a business transaction. Filters the data to the business transaction data (e.g. the job data).
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.
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:
If there is an incoming transmission, you can access it with this table view.
If the mapping is running as an error handler, you can use this filter to access the failed processing queue entry.
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.
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.
Limit the records to the specified count.
Do not limit the returned records.
Only available if Limit Count is set to True. The maximum number of records to be used.
Read-only. Groups other properties. The name Table View is used in programming Microsoft Dynamics NAV 2017 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.
The key that should be used for sorting. Can be empty.
The order in which the key is applied. The default is Ascending.
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.
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.
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.
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.
The object name the function is defined on.
The function name. The function should return an object of type Codeunit ANVEDI Variant pointing to a RecordRef that contains the initial data.
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.
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:
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.
For numeric fields only. The smallest number.
For numeric fields only. The largest number.
The value from the first record found.
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.
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.
Register errors on this table instance. This makes sense for buffer tables.
Do not register errors on this table instance. This makes sense if the instance access, for example, secondary table information.
This is a read-only property to group the child properties.
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.
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.
You can select the column that should be used for the internal number, whenever a linked document entry is created for this table instance.
You can select the column that should be used for the external number, whenever a linked document entry is created for this table instance.
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.
If you enter a number the mapping will throw an error, if the specified minimal repeat is not reached.
The error message that should be raised if the minimal count of records are not found.
If you enter a number the mapping will throw an error, if the specified maximal repeat is not reached.
The error message that should be raised if the maximal count of records is exceed.