JTL Direct Query: Insert TARIC code into invoice template

JTL Direct Query: Insert TARIC code into invoice template

JTL direct queries allow direct access to values stored in the database. This is especially interesting when there is no other way to output the data.

The german edition of this article you can find by clicking here.

The following sample code illustrates a database query to display the Taric code for all invoices where the tax rate is 0.00%. The output is done together with the item name under the item items of the invoice.

The corresponding code is:

InvoicePosition.Name + Cond((("0,00")=(LocNumber$(InvoicePosition.VATRate, JTL_GetCulture(Report.CountryISO, Report.LanguageISO, Report.CurrencyISO), @DecimalVat))),(Cond(("")!=(JTL_DirectQuery ("SELECT cTaric FROM dbo.tArtikel WHERE kArtikel="+Fstr$(InvoicePosition.ProductInternalId,"?")+"")),'¶TARIC:') + JTL_DirectQuery ("SELECT cTaric FROM dbo.tArtikel WHERE kArtikel="+Fstr$(InvoicePosition.ProductInternalId,"?")+""))) + Cond(("C")=("bGF1dGVuYmFjaGVyLmNo"),'lautenbacher.ch')

Invoice.Position.Name outputs the item name while Cond is an if-query. In the code there are two if-queries per Cond – one to initiate an output only if the tax rate is 0% and another one to display “TARIC:” only if a TARIC code has actually been stored in the item in the JTL inventory. The database query itself can be found after “JTL_DirectQuery” and queries the TARIC code from the database for the corresponding article.

Please do not “play around” blindly with the JTL_DirectQueries, because in the end this is a real access to the database with the help of the SQL syntax. You could therefore damage your database by issuing incorrect commands/queries.

If you need help with customizing your invoice templates, I am also available at reasonable hourly rates.

Leave a Reply

Your email address will not be published. Required fields are marked *