Table of Contents
Create a new export template
This article shows 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 → Button "+NEW".
Note: Until version 21.1 it's via Administration → Import/Export Center → Master Data Export → Button "+NEW".
To create a new export template, proceed as follows:
The following specifications can be made in general:
- Name → 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: Address, Offer, Order, Contact person, Item, Credit bill, Invoice, Delivery bill, Order, Offer items, Order items, Invoice items, Credit bill items, Delivery bill items, Order items.
- CSV Label → 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 Enable → Check this to enable querying via API.
- CSV fields → The export variables are to be entered here.
- Filter → 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".
- Internal remark → An internal remark can be entered here for internal company communication.
Note: It is only possible to filter for columns in the export template that are also present in the corresponding table.
Example of export template: number; name_de; name_en; description_de; description_en; short_text_de; short_text_en; internal_comment; manufacturer; manufacturer_number; manufacturer_link; ean; sales_price_net; purchase_price_net; supplier_name; supplier_number;
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:
Example of export template: date; name; contact_person; country; should; customer_number; voucher_number; ustid; contra_account; currency; order_internet; tax_rate_normal; payment_method;
Use the following filters: payment method='invoice'; status!='created';
Export revenues with distribution
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:
Example export template: date; should; customer_number; voucher_number; name; country; ustid; contra_account; currency; order_transaction_number; order_internet; tax_rate_normal; tax_rate_normal_amount; tax_rate_minor; tax_rate_minor_amount; sales;
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.
The following fields can be exported from a CSV file:
- voucher_name
- voucher_number
- document_date
- document_status
- bp.sort → sort order of the items
- bp.order_number → order_number at supplier
- bp.designation supplier → designation at the supplier
- bp.price → Here the unit price is entered as a document item
- bp.quantity → The quantity of the document item is entered here.
- bp.number → Here the item number is to be entered as a line item
With these fields settings can be made in the tab "Details".
Example of export template: document_name; document_number; document_date; document_status; bp.sort; bp.order_number; bp.description_vendor; bp.price; bp.quantity;
In the "Filter" field, the ID of the document can be specified, e.g. bp.order=52. If the "Filter" field is empty, the items of all documents are output.
Item Export
Below is an example of a possible export template that is used to pull item data from the system.
Example of export template: number; name_de; name_en; description_de; description_en; short_text_de; short_text_en; internal_comment; manufacturer; manufacturer_number; manufacturer_link; ean;
Note: It is possible to filter deleted items from the export files by entering "deleted !=1;" in the "Filter" field:
Addresses 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:
The following export settings must be adhered to:
- Source → Select "Address" from the drop-down menu.
- Filter project → A project can be selected for the output by ticking it.
- CSV fields → "customer number" must be entered. This will export the customer number in the first column
- Filter → "customernumber!='';" 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.
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.
Example of an export template: customer_number; name; delivery_block; delivery_block_reason;
The filter to be set is "delivery block=1;".
Address with postal code area and phone number
For sales, lists of customer addresses can be output over a specific postal code range:
Example of an export template: customer_number; name; phone; zip_code;
If only one postal code range is desired as output, an additional restriction can be made in the "Filter" 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:
- suppliernumber!='' → all suppliers, whose supplier number is not empty, will be filtered
- country='GB' → the filter for the country Great Britain (GB) is set
- stock items='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
- number!='DEL' → If you do not want to display deleted articles, set this filter
- internal_blocked='0' → If no blocked articles are to be displayed, set this filter
Delivery note addresses with condition
SELECT l.documentnr, l.date, l.name, l.department, l.addressaddress, l.subdepartment, l.country, l.street, l.city
FROM delivery bill l LEFT JOIN delivery note_item lp ON lp.delivery note=l.id
WHERE lp.item=4 AND l.date>='2017-12-01' AND l.date ⇐'2017-12-31'