Defining data tables

Defining a data table for the backend as a TYPO3-Extension-Developer can be done easily be using TypoScript.

Examples can be found in following extensions:

EXT:modules for Frontend- and Backend-User and groups
EXT:questions for FAQ-Questions and categories
EXT:glossaries for Glossary-Entries and categories

Attention:

Be sure this TypoScript is defined by a ext_typoscript_setup.txt file, so that this definitions are always available!

module.tx_yourextensionkey {
    settings {
        lists {

            questions {
                # Id of the backend module list - just take the parent key
                id = internalNotes
                # table defines the database table name for the records of this list.
                table = sys_note
                # Module identifier
                module = web_InternalNotesInternalNotes
                # Plugin identifier
                plugin = InternalNotes
                # sortingField defines the field, which should be used as default sorting
                sortingField = subject
                # sortingOrder defines the default sorting order for the default sorting field
                sortingOrder = asc
                # The limit defines, how many record should be displayed on each page
                limit = 20
                # Show `Column selector` widget within list,
                # you can show/hide columns for table list using this widget
                columnSelector = 1
                # Define the default fields/columns of a list
                # these fields/columns will be displayed when the list is first initialized or the user reset his UC
                columnDefault = name, label, user
                # Define the domain model class for the list
                listItemClass = CodingMs\InternalNotes\Domain\Model\InternalNote
                # Enable translation feature
                translatable = 1
                # Fields/columns of the data table
                fields {
                    # Define the fields/columns of your table
                }
                # Actions for each row of the data table
                actions {
                    # Define the actions for your table rows
                }
            }
            # Define more data tables in the same way
            aSecondTable {
                # ...
            }
        }
    }
}

Sorting via drag-and-drop

To be able to sort your records via the backend table, proceed as follows:

Make sure your model has the sorting property. You can simply use the trait use SortingTrait; from the EXT:additional_tca.
In the ctrl section of the TCA for the table, you need a 'sortby' => 'sorting', – as usual.
Make sure that in the columns section of the TCA for the table, the sorting field is defined with at least passthrough:
```
'sorting' => [
    'config' => [
        'type' => 'passthrough'
    ],
],
```

You should now see the "Start Sorting" button on the table. When clicked, the table will be sorted in ascending order by sorting, and the drag-and-drop buttons will be available.

Define table fields

The field key is written lower-camel-case and represents the getter name of a Model property!

module.tx_yourextensionkey {
    settings {
        lists {
            tableIdentifier {
                fields {
                    title {
                        format = Plain
                        sortable = 1
                    }
                    email {
                        format = Email
                        sortable = 1
                    }
                    image {
                        # Display image of a single image-relation
                        format = Image
                    }
                    images {
                        # Display image(s) of a multiple image-relation
                        format = Images
                        # Display max 2 images
                        limit = 2
                        # Display only images with flag preview enabled (don't work with limit value!)
                        previewOnly = 0
                    }
                    hex {
                        format = Color
                        sortable = 1
                    }
                    hidden {
                        format = Boolean
                        sortable = 1
                        # Requires following translations:
                        # tx_modules_label.boolean_true = yes
                        # tx_modules_label.boolean_false = no
                    }
                    description {
                        format = PlainEditable
                        sortable = 1
                        crop = 30
                    }
                    creationUser {
                        format = BackendUser/Username
                    }
                    creationDate {
                        format = DateTime
                        sortable = 1
                        sortingField = crdate
                        dateFormat = d.m.Y H:i
                    }
                }
            }
        }
    }
}

Possible native formats

Parameter/Format	Plain	PlainEditable	DateTime	Email	Boolean	Currency	Percent	Weight	Image	Custom
sortable	x	x	x	x	x	x	x	x	-	?
sortingField	x	x	x	x	x	x	x	x	-	?
hideInExport	x	x	x	x	x	x	x	x	x	?
exportGetter	x	x	x	x	x	x	x	x	x	?
dateFormat	-	-	x	-	-				-	?
crop	x	x	-	-	-				-	?

sortable - Disable the sortable of this column, boolean represented by 0/1.
sortingField - Overrides the Database column-name for sorting, in case of it is divergent to the configuration key (see example of creationDate).
hideInExport - Disable the column in the CSV export, boolean represented by 0/1.
exportGetter - Defines the domain model getter name for the CSV export cell (without the leading get).
dateFormat - DateFormat is a PHP date format pattern.
crop - Crop is an integer value and crops the cell content after the defined amount of characters.

Inline-Editing

To enable inline editing, the editFormat parameter must be set. This can currently have the following values:

Plain is a single-line edit field
Textarea is a multi-line edit field
Select is a select box
MultiSelect is a multi select box (mm-relation)
Checkbox is a Boolean toggle

For example, if a field is defined with gender as the key, but the corresponding database field is called tx_modules_gender, you can map the field for editing using editFieldName = tx_modules_gender. Here are a few examples:

frontendUser {
    # …
    # Define the domain model class for re-rendering the table call
    listItemClass = CodingMs\Modules\Domain\Model\FrontendUser
    # …
    fields {
        username {
            format = Plain
            sortable = 1
            editFormat = Plain
        }
        gender {
            format = Plain
            sortable = 1
            editFormat = Select
            editFieldName = tx_modules_gender
        }
        description {
            format = Plain
            sortable = 1
            editFormat = Textarea
        }
        newsletter {
            format = Edit/Checkbox
            sortable = 1
            editFormat = Checkbox
            editFieldName = tx_modules_newsletter
        }
        realName {
            format = Plain
            sortable = 1
            editFormat = Plain
            # Example for a database-field, which is not underscored
            editFieldName = realName
        }
    }
}

Define table actions

The table actions are defined in the actions node. In this node you are able to define the actions for each table row.

This could look like:

actions {
    # The action key is the identifier.
    # This key is used additionally for the translation key for the column title.
    edit {
        action = Edit
        # Translation keys
        # tx_modules_label.list_topic_action_edit = Edit topic
    }
    inactiveActive {
        action = InactiveActive
        # Invert the value (for example for hidden field usage)
        invertState = 0
        # Translation keys
        # tx_modules_label.list_topic_action_inactive = Activate topic
        # tx_modules_label.list_topic_action_active = Deactivate topic
    }
    hideShow {
        action = HideShow
        # Translation keys
        # tx_modules_label.list_topic_action_hide = Hide topic
        # tx_modules_label.list_topic_action_show = Show topic
    }
    disableEnable {
        action = DisableEnable
        # Translation keys
        # tx_modules_label.list_frontend_user_action_enable = Enable frontend user
        # tx_modules_label.list_frontend_user_action_disable = Disable frontend user
    }
    delete {
        action = Delete
        # Data field of the record, where a label is store.
        # This label is used for the security question in the modal.
        subjectField = title
        # Translation keys
        # tx_modules_label.list_topic_action_delete_title = Delete this topic?
        # tx_modules_label.list_topic_action_delete_content = Are you sure you want to delete the topic '%1$s'?
        # tx_modules_label.list_topic_action_delete_cancel = Cancel
    }
}

Translations

Defining translations for the data tables for your project can be done like this:

module.tx_yourextensionkey {
    _LOCAL_LANG {
        default {
            #
            tx_downloadmanager_label.backend_filter = Filter
            tx_downloadmanager_label.backend_filter_submit = refresh

            # Message, if there are no records in the table
            tx_modules_label.list_download_file_collection_no_entries = No download file collections found

            # Table header title
            tx_modules_label.list_download_file_collection_header = Download file collection

            # Table header columns
            # Each identifier is build like:
            # tx_modules_label.list_ + list id underscored + _col_ + field key
            tx_modules_label.list_download_file_collection_col_title = Title
            tx_modules_label.list_download_file_collection_col_actions = Actions

            tx_modules_label.list_download_file_collection_action_edit = Edit download file collection
            tx_modules_label.list_download_file_collection_action_hide = Hide download file collection
            tx_modules_label.list_download_file_collection_action_show = Show download file collection
        }
        de {
            # German translation
            tx_modules_label.list_download_file_collection_no_entries = Es konnten keine Download-Datei-Dateisammlung gefunden werden
            tx_modules_label.list_download_file_collection_header = Download-Dateisammlung
            tx_modules_label.list_download_file_collection_col_title = Titel
            tx_modules_label.list_download_file_collection_col_actions = Aktionen
            tx_modules_label.list_download_file_collection_action_edit = Download-Dateisammlung bearbeiten
        }
    }
}

However, if you are using the table in a community extension, we strongly recommend defining it via locallang files!

Using creation user, date or modifictaion date

When you want to use the creation user, date or modification date of a default TYPO3 record, you need to map the Extbase fields:

In TYPO3 9 or smaller in yourextensionkey/ext_typoscript_setup:

config.tx_extbase.persistence.classes {

    CodingMs\Newsletters\Domain\Model\Topic.mapping {
        tableName = tx_newsletters_domain_model_topic
        recordType =
        columns {
            crdate.mapOnProperty = creationDate
            tstamp.mapOnProperty = modificationDate
            cruser_id.mapOnProperty = creationUser
        }
    }

}

In TYPO3 10 or higher in yourextensionkey/Configuration/Extbase/Persistence/Classes.php:

<?php

declare(strict_types=1);

return [
    \CodingMs\Questions\Domain\Model\Question::class => [
        'tableName' => 'tx_questions_domain_model_question',
        'properties' => [
            'creationDate' => [
                'fieldName' => 'crdate'
            ],
            'modificationDate' => [
                'fieldName' => 'tstamp'
            ],
            'creationUser' => [
                'fieldName' => 'cruser'
            ],
        ],
    ],
];

Additionally you must define the fields at least with passthrough:

'crdate' => [
    'config' => [
        'type' => 'passthrough'
    ],
],
'tstamp' => [
    'config' => [
        'type' => 'passthrough'
    ],
],

And the domain-model must have the getter/setter, for these you can use the traits like:

use CodingMs\AdditionalTca\Domain\Model\Traits\CreationDateTrait;
use CodingMs\AdditionalTca\Domain\Model\Traits\HiddenTrait;
use CodingMs\AdditionalTca\Domain\Model\Traits\ModificationDateTrait;
use CodingMs\Modules\Domain\Model\Traits\CheckMethodTrait;
use CodingMs\Modules\Domain\Model\Traits\ToCsvArrayTrait;
use TYPO3\CMS\Extbase\DomainObject\AbstractEntity;

class QuestionCategory extends AbstractEntity
{
    use CheckMethodTrait;
    use ToCsvArrayTrait;
    use CreationDateTrait;
    use ModificationDateTrait;
    use HiddenTrait;
    public const TABLE = 'tx_questions_domain_model_questioncategory';
}

Tables with translation view

To get a translation line for each data record column, which is even inline-editable, this feature must first be activated.

module.tx_yourextensionkey {
    settings {
        lists {
            questions {
                translatable = 1
            }
        }
    }
}

The language selection must be added to the filter panel above the list. This will be used for enabling the target translation.

<html xmlns:f="http://typo3.org/ns/TYPO3/CMS/Fluid/ViewHelpers"
      xmlns:modules="http://typo3.org/ns/CodingMs/Modules/ViewHelpers"
      data-namespace-typo3-fluid="true" lang="en">
<f:layout name="Backend" />
<f:section name="Content">
    <modules:be.header header="{f:translate(key: 'tx_questions_label.headline_categories_overview')}" />
    <f:flashMessages />
    <div class="panel panel-default">
        <div class="panel-heading">
            <f:translate key="tx_questions_label.backend_filter"/>
        </div>
        <div class="panel-body">
            <f:form class="row" action="{list.action}" controller="Backend" pageUid="{list.pid}">
                <f:form.hidden name="offset" value="0" />
                <f:render partial="Table/Filter/SearchWord" arguments="{_all}"/>
                <f:render partial="Table/Filter/Translation" arguments="{_all}"/>
                <f:render partial="Table/Filter/Submit" arguments="{_all}"/>
                <f:render partial="Table/Filter/Reset" arguments="{_all}"/>
            </f:form>
        </div>
    </div>
    <f:render partial="Table/Table" arguments="{list: list, data: categories}" />
    <modules:be.footer />
</f:section>
</html>

In the domain model of the list object, the database table name must be defined as a constant:

class Question extends AbstractEntity
{
    public const TABLE = 'tx_questions_domain_model_question';
}

The translation trait must be used in the controller action:

class BackendController extends BackendBaseController
{
    use SearchWordTrait;
    use TranslationTrait;

    public function overviewQuestionsAction(): ResponseInterface
    {
        $list = $this->backendListUtility->initList(
            $this->settings['lists']['questions'],
            $this->request,
            ['searchWord', 'category', 'translation']
        );
        // …
        $list = $this->processSearchWordFilterTrait($list);
        $list = $this->processTranslationFilterTrait($list);
        // …
    }
}