This article details how to create a new export template. For more information, see the Import/Export article.
An export template can be created via the following path: Master data > Import/Export Center > Master Data Export > +NEW.
Note
Until version 21.1 it's via Administration > Import/Export Center > Master Data Export > +NEW.
To create a new export template, proceed as follows:
The following specifications can be made in general:
-
Designation: Any name for the export template is to be specified.
-
source: Selection of the type of data download is possible here. From the dropdown menu can be selected: Adresse (contact), Angebot (offer), Auftrag (sales order), Ansprechpartner (contact person), Artikel (product), Gutschrift (credit note), Rechnung (invoice), Lieferschein (delivery note), Bestellung (purchase order), Angebot Positionen (offer positions), Auftrag Positionen (sales order positions), Rechnung Positionen (invoice positions), Gutschrift Positionen (credit note positions), Lieferschein Positionen (delivery note positions), Bestellung Positionen (order positions).
-
CSV labeling: By ticking the first line of the CSV file will be used as a label
-
CSV separator: Separator used in the CSV file to separate data from each other.
-
CSV masking: Masking character used in the CSV file is optional to be selected
-
Filter date: Check it to filter the output by date.
-
Filter project: Tick the checkbox to filter the output by project.
-
API release: Check this to enable querying via API.
-
CSV fields: The export variables are to be entered here.
-
Offer filters: Filters for the download can be set here. Note: The operations are AND operations and not OR operations. Entries are searched for that meet "all conditions" and not "at least one of them".
Note
It is only possible to filter for columns in the export template that are also present in the corresponding table.
In order to be able to export invoices with a specific payment method, the following information must be provided:
It is possible to download data from a CSV file. This could look like this, for example:
In order to export the revenues generated by sales, create the following export template:
Various document data can also be exported.
How to export items from a purchase order can be read below.
The following fields can be exported from a CSV file:
-
beleg_name (document name)
-
beleg_belegnr (document number)
-
beleg_datum (document date)
-
beleg_status (document status)
-
bp.sort → sort order of the items
-
bp.bestellnummer → order number at supplier
-
bp.bezeichnunglieferant → designation at the supplier
-
bp.preis → unit price as a document item
-
bp.menge → quantity of the document item
-
bp.nummer → product number as a document item
With these fields settings can be made in the tab "Details".
Below is an example of a possible export template that is used to pull item data from the system.
 |
Note
It is possible to filter deleted items from the export files by entering "nummer not like 'DEL';" in the "Filter" field.
Various fields can be output for the export of address data. Enclosed are some examples.
Customer number and project
A list of all customers/suppliers of a project is often needed for the subsequent post-import, the change of a setting option or a field in the address record.
Export the list of all customer numbers of a project
To export all customer numbers of a project as a list, create the following export template:
The following export settings must be adhered to:
-
source: Select "Adresse" (contact) from the drop-down menu.
-
Filter project: A project can be selected for the output by ticking it.
-
CSV fields: "kundennummer" (customer number) must be entered. This will export the customer number in the first column
-
Offer filters: "kundennummer!='';" (customer number) has to be entered. This sets the condition that all customers or all addresses where the customer number is not empty will be filtered.
Note
The download of the CSV file is done via the tab "Start export: Download CSV file".
Project selection
In the Start export: Download CSV file tab, select the "STANDARD" project in the overview if you want to filter by it.
CSV list The following export list appears as a result after clicking the Download button.
This export list can be revised and extended to perform new postimports for existing customers. As long as the customer numbers are unique, they can be used normally. The system ID can simply be omitted as a column during the import.
Note
Instead of the customer number for the address record, the system id can be used for the import. If the ID is not required, it can remain in the edited file as a column and an import omits this column.
It is also possible to export all addresses with a delivery block. If a customer number exists, it will be output in the first column.
For sales, lists of customer addresses can be output over a specific postal code range:
If only one postal code range is desired as output, an additional restriction can be made in the Offer filters area. In this example the zip codes between 80000 and 90000 are desired. The input is therefore: plz > 80000 AND plz < 90000;
Additional conditions can be specified in the filter:
-
lieferantennummer!='' → all suppliers, whose supplier number is not empty, will be filtered
-
land='GB' → the filter for the country Great Britain (GB) is set
-
lagerartikel='0' → if no stock items should be displayed, set this filter
-
stueckliste='0' → if you don't want to display BOM main articles, set this filter
-
nummer!='DEL' → if you do not want to display deleted articles, set this filter
-
intern_gesperrt='0' → if no blocked articles are to be displayed, set this filter
Delivery note addresses with condition
SELECT l.belegnr,l.datum,l.name,l.abteilung,l.adresszusatz,l.unterabteilung,l.land,l.strasse,l.ort
FROM lieferschein l LEFT JOIN lieferschein_position lp ON lp.lieferschein=l.id
WHERE lp.artikel=4 AND l.datum>='2017-12-01' AND l.datum ⇐'2017-12-31'