Skip to main content
Academy Community Sign in

Export of master data and company data

  • Updated

Create a new export template

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:

exportmastercompanydata_1.png

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.

Export invoices with specific payment method

In order to be able to export invoices with a specific payment method, the following information must be provided:

exportmastercompanydata_2.png

Export revenues with distribution

It is possible to download data from a CSV file. This could look like this, for example:

exportmastercompanydata_3.png

In order to export the revenues generated by sales, create the following export template:

exportmastercompanydata_4.png

Document data export

Various document data can also be exported.

Export of the positions of an order

How to export items from a purchase order can be read below.

exportmastercompanydata_5.png

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".

exportmastercompanydata_6.png

Item Export

Below is an example of a possible export template that is used to pull item data from the system.

exportmastercompanydata_7.png

Note

It is possible to filter deleted items from the export files by entering "nummer not like 'DEL';" in the "Filter" field.

Address export

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:

exportmastercompanydata_8.png

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".

exportmastercompanydata_9.png

Project selection

In the Start export: Download CSV file tab, select the "STANDARD" project in the overview if you want to filter by it.

exportmastercompanydata_10.png

CSV list The following export list appears as a result after clicking the Download button.

exportmastercompanydata_11.png

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.

Address with delivery block

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.

exportmastercompanydata_12.png

Address with postal code area and phone number

For sales, lists of customer addresses can be output over a specific postal code range:

exportmastercompanydata_13.png

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'

Related articles