To transfer your purchase order data to Tacto, export it as a CSV file directly from Sage b7. The export involves two steps: The SQL query provided by Tacto is set up once in the administration screen bv700. Afterwards, any authorized user can run it via the display screen ba700 and export the results as a CSV file.
Prerequisites
• Access to Sage b7 in the desired company (Mandant) - the query always runs in
the company you are currently logged into.
• User permissions for the screens bv700 (setup) and ba700 (execution). Admin
rights are not required; screen-level permissions are sufficient.
• The SQL query provided by Tacto (see below).
The SQL query provided by Tacto
The following SQL query reads purchase order headers, order lines, and delivery dates from
the last five years in Sage b7. When the report is run, the screen automatically prompts the company number.
SELECT
h.fi_nr AS Mandant,
h.bestnr AS Bestellnummer,
h.best_dat AS Bestelldatum,
h.konto AS Lieferant_ID,
h.sb_schl AS Einkaeufer_ID,
h.waehrung AS Waehrungs_ID,
h.wae_kurs AS Wechselkurs,
h.ansprnr AS Bestellkontakt,
h.best_art AS Bestelltyp_Code,
bt.txt AS Bestelltyp_Text,
p.bestpos AS Positionsnummer,
p.identnr AS Artikel_ID,
p.var AS Artikelvariante,
p.menge_bes AS Bestellmenge,
p.meb AS Bestellmengeneinheit,
p.ekpreis AS Nettopreis_Bestellwaehrung,
p.pwert_end AS Nettobestellwert,
p.pwert_arab AS Nettobestellwert_abzgl_Rabatt,
p.peek AS Preisfaktor,
p.mep AS Preiseinheit,
p.umrechp AS Faktor_PE_zu_BME,
p.umrechb AS Faktor_BME_zu_Basis,
p.gewicht AS Gewicht,
p.ort_incoterm AS Incoterms_Ort,
p.raaufnr AS Mengenkontrakt_ID,
p.raaufpos AS Mengenkontrakt_Pos,
t.wunsch_term AS Wunschlieferdatum,
t.lief_term AS Planlieferdatum,
t.lief_dat AS Tatsaechl_Lieferdatum,
t.menge_bes AS Bestaetigte_Menge
FROM e100 h
INNER JOIN e110 p
ON p.bestnr = h.bestnr
AND p.fi_nr = h.fi_nr
LEFT JOIN e1101 t
ON t.bestnr = p.bestnr
AND t.bestpos = p.bestpos
AND t.fi_nr = p.fi_nr
LEFT JOIN e9032 bt
ON bt.best_art = h.best_art
AND bt.fi_nr = h.fi_nr
AND bt.lang = 'de_de'
WHERE h.fi_nr = :+FI_NR
AND h.best_dat >= DATEADD(YEAR, -5, GETDATE())
ORDER BY h.best_dat DESC, h.bestnr, p.bestpos
Step 1: Set up the SQL query in bv700 (one-time)
Enter “bv700” in the search field at the top of Sage b7 and confirm with the arrow (or Enter). The screen DB-Abfragen (bv700) opens.
In the "Nummer" field, assign a free list number (e.g. 1001), enter a name in the Benennung field (e.g. “Bestelldaten”), and select Tabellarische Ausgabe (tabular output). Note down the number, you will need it later to run the report.
In the Eingabe tab, paste the SQL query provided by Tacto into the DBAbfrage field. In the Parameter section, create the parameter (KeyId “FI_NR”, description “Firmennummer”).
Save the new report via the save icon in the toolbar (Ändern/Speichern, F3) before leaving the screen.
Step 2: Run the report in ba700 and export it as CSV
Enter “ba700” in the search field at the top of Sage b7 and confirm with the arrow (or Enter).
In the "Nummer" field, enter the report number from Step 1 (e.g. 1001) and confirm
In the "Eingabe" tab, enter the parameter (e.g. company number “1”) and run the report via the arrow icon in the toolbar (Shift+F7)
The result is displayed in the "Ausgabe" tab. Click the Print icon in the toolbar.
In the following screen, select CSV as the output format. Enable headerrow print (print header row), check the delimiter (default “;”) and the column selection. Then confirm with the checkmark icon in the top left.
The CSV file is generated. Save the file and transfer it to Tacto.
Hinweise & Troubleshooting
Incorrect output destination when printing: Incorrect Output Destination: If the selection was made using the arrow and the list in the form is populated, clicking the printer icon will export the list to Excel.
If the selection was not made - that is, if the form is empty - the list will be sent to the print spooler.
Multiple clients: The query runs in the currently logged-in client. For additional clients, repeat the export using the respective company number.
Permissions: In ba700, the stored SQL query can only be executed but not modified; modifications are always made in bv700.
💡 Hinweis: The query returns purchase order data from the last five years for the company you are currently logged into.