The Table Settings page is used to set up a table of a Data Sheet.
Depending on whether the table is a child table or not, different table settings are available or possible.
In the Filter or Filters and Relations area you can specify filter criteria for the table and relations with parent tables.
Fields
Parent Table
Displays whether the table is a child table to a parent table.
Relationship
Specifies the relationship between the parent table and the table.
1:n
In a 1:n (one-to-many) relationship, a record in the parent table may be associated with multiple records in the child table.
For example, each Customer can have several Customer Ledger Entries.
1:1
In a 1:1 (one-to-one) relationship, a record in the parent table may be associated with only one record in the child table.
For example, each Customer Ledger Entry is assigned to only one Customer.
First Record
Only outputs the first record that is found.
Last Record
Only outputs the last record that is found.
Table
Specifies for which table the settings are specified.
Temporary Data Codeunit
With the help of temporary data codeunits, evaluations can be extended with functions that temporarily calculate certain data of the evaluation during execution.
This makes it possible, for example, to evaluate data that is normally not available or not available in this form.
Temporary Data Codeunits can be provided or added via custom programming.
For more information, see Information for Developers, Temporary Data Codeunits.
Name
Specifies a name for the table. If no name is specified, the default caption of the table is used.
It is possible to specify the placeholder %1 in the text, which is replaced by the default caption of the table.
Sorting
Specifies by which fields the records should be sorted.
Order
Specifies whether the records should be sorted in ascending or descending order.
Disable Company Change
Specifies whether the Company table should perform a company change or not.
Max. No. of Records
Specifies a maximum number of results for a 1:n relationship.
If the limit is reached, the search for further records is stopped.
Add Blank Record
Specifies whether an empty record should also be added in a 1:n relationship.
The setting can be useful especially in connection with child tables where the table is used for setting a FlowFilter.
Start with New Row
Specifies whether the first record of the table should also start with a new row.
Note
Regardless of the setting, a new line is always started after running through a 1:n relation or in the case of a parallel tree structure.
Example Customer 1:n Customer Ledger Entries without new row
Example Customer 1:n Customer Ledger Entries with new row
No Column Inheritance
Specifies whether the columns of the parent table should be empty after a new row starts.
Note
Individual columns can also be excluded from inheritance via the Field Settings of the parent table fields.
Example Customer 1:n Customer Ledger Entries with Column Inheritance
Example Customer 1:n Customer Ledger Entries without Column Inheritance
Parallel Tree Structure
Specifies whether the tree structure should be output parallel to the previous tree structure.
Normally tables and fields of a Data Sheet are handled serially. I.e. for each field another Excel column is added.
Parallel tree structures can be used to output parts of a Data Sheet parallel to each other.
The tree structures share the same columns and are output one below the other.
Example
This example can be downloaded in the Download Area.
The following Data Sheet was created:
The following result is displayed in Excel after executing the evaluation:
Tip
Folgende Formeln wurden für die Berechnung der Summen verwendet:
Customer Total =SUMIF( [TC:COL]:[TC:COL] , "Customer" , [BC:COL]:[BC:COL] )
Vendor Total =SUMIF([ TV:COL]:[TV:COL] , "Vendor" , [BV:COL]:[BV:COL] )
Example 2
This example can be downloaded in the Download Area.
The requirement in this example was to display all sales documents for a customer.
The documents should be displayed indented by one field under the customers.
Using a small "trick", such evaluations can also be implemented.
The following Data Sheet was created:
The trick is to subordinate the Customer table to the Customer table in a 1:1 relationship. After that, the Sales Header table can be output parallel to the subordinate customer. Both subordinated tables are linked to the superordinated customer table.
The indentation is created with an empty formula field (Spacer).
The following result is displayed in Excel after executing the evaluation:
Note
The following rules apply to parallel tree structures:
When executing the evaluation, parallel tree structures are only treated as such if they are defined directly one after the other and on the same level.
The Parallel Tree Structure field only needs to be set in the following tree structure(s), not in the first one.
The number of fields do not have to match in the parallel tree structures. The tree structure with the most fields determines how many columns are output in Excel.
The column headers are determined by the first tree structure or, if more columns are defined in subsequent tree structures, the first occurrence.
Fields from a parallel tree structure cannot be added to a PivotTable directly but only indirectly via the first tree structure.
Since there are multiple specifications for a column in the sheet due to parallel tree structures, there are also multiple Field Settings for a field.
Depending on the setting, either the setting of the first tree structure or the largest specified value is taken.
There are also settings that become active as soon as they are set for one of the columns.
Disable 'Add Filter'
Specifies whether additional filters can be added to the table in the Filter Page when the evaluation is executed.
The filter page is displayed if filters are specified for a table that are not hidden (Filter Type).
Filters and Relations
In the Filter or Filters and Relations area you can specify filter criteria for the table.
Only records that match the criteria are included in the evaluation.
If you specify criteria for multiple fields, then only records that match all criteria will be displayed.
In case of a Relation the field value of the parent record is used as filter criterion.
A operator in the filter determines how the filter should be set.
Field
Specifies which field should be filtered.
If a field is specified here that is not hidden (Filter Type), a filter page is displayed later when the evaluation is executed.
You can also simply specify a field without a filter so that additional filters can be added to the table via the filter page when the evaluation is executed.
For more information, see Execute Evaluations, Evaluation Filters.
Filter
Specifies the filter for the field.
The possible or allowed values for the filter depend on the field:
If the field is related with a parent field, the operator ('=', '<=', '<', '>=', '>', '<>') for the relation must be specified in the filter.
The Filter values and operators can be selected via a lookup.
For date fields parameters can be specified in the filter, which will be converted or calculated only when the evaluation is executed.
For more information, see Edit Evaluations, Data Sheets, Date Filter IDs.
The filters of option fields (and yes/no fields) are stored in the current display language.
When executing the evaluation in another display language (or during a lookup of the filter), the filter is automatically converted.
It is also possible to specify Filter IDs in the filter, which are then replaced by the current filter values of the Filter IDs when the evaluation is executed.
Filter IDs can be added to a filter via the Add Filter ID action. This opens a page that displays an overview of all available Filter IDs.
Note
A Filter ID cannot be added to the filter if the field is related with a parent field.
Multiple Filter IDs can be specified in the filter.
Date Filter IDs can be combined with Filter IDs.
The Filter Type must be Hidden if a Filter ID is specified in the filter.
Specifies an ID that can then be used in the filter of other lines to automatically set the same filter there.
The Filter IDs in the filters are replaced by the current filter values of the Filter IDs when the evaluation is executed.
A Filter ID must begin with [ followed by a unique ID and end with ]. The brackets are added automatically when the ID is entered.
Note
Filter IDs are not tied to a data sheet but cross data sheet.
A Filter ID cannot be specified if the field is related with a parent field.
A Filter ID cannot be specified for restricted filters.
Related with Parent Field
Specifies whether, and if so, to which field of the parent table the field of the table should be linked.
In the case of a relation, the value of the field of the parent record is used as the filter and a operator for the relation (Equal, Less, Greater, etc.) must be specified in the Filter field.
To remove a relation, click on the Related with Parent Table field.
Related with Parent Table
Specifies to which parent table the records of the table should be linked.
Tables can be linked to each other over several levels.
The tables and thus the records are linked only if a related field is also selected.
Click on the field to change or remove the relation.
Note
Theoretically, one and the same table can be superordinated to another table more than once.
In this case, the record from the lowest or most immediate parent is used for the relation.
In the following example, when linking the detailed customer ledger entries to the customer, always the subordinated customer is used:
Filter Type
Specifies whether and how the filter can be changed when executing the evaluation.
Required
Required filters must be specified by the user.
Restricted
Restricted filters can only be further restricted by the user.
Locked
Locked filters cannot be changed by the user.
Hidden
Hidden filters are not displayed to the user.
Relations are always created as hidden filters, because here the value of the field of the parent record is used as a filter.
Tip
If you want to have the possibility to add additional filters to the table when executing the evaluation, but you want certain fields to be excluded from this, you can also specify these fields as Hidden.
User Filters
Specifies whether different filters for certain users are set for the field.
Click on the field to set up the filters.
Field Filters
If FlowField fields are added to the table, FlowFilters set at the table level will affect the calculation of the fields.
Via the Field Settings FlowFilters can be set, which are only valid for the field.
Note
If a FlowFilter is set on table level and on field level, the FlowFilter on table level is ignored for the calculation of the field.
Depending on the settings, this action suggests fields of the table and related fields between table and a parent table to filter the records.
Existing filters will not be overwritten or changed.
Suggest Filters
Depending on the settings, this action suggests fields of the table to filter the records.
Existing filters will not be overwritten or changed.
Table Settings
Depending on whether the table is a child table or not, different table settings are available or possible.
In the Filter or Filters and Relations area you can specify filter criteria for the table and relations with parent tables.
Example Customer 1:n Customer Ledger Entries with new row
Example Customer 1:n Customer Ledger Entries without Column Inheritance
The following result is displayed in Excel after executing the evaluation:
The trick is to subordinate the Customer table to the Customer table in a 1:1 relationship. After that, the Sales Header table can be output parallel to the subordinate customer. Both subordinated tables are linked to the superordinated customer table.
The indentation is created with an empty formula field (Spacer).
The following result is displayed in Excel after executing the evaluation:
In case of a Relation the field value of the parent record is used as filter criterion.
A operator in the filter determines how the filter should be set.
