Electronic invoices in FileMaker

With the MBS FileMaker Plugin we offer you a toolbox with DynaPDF, XML, CURL and Saxon functions. This allows you to implement electronic invoices in FileMaker. The whole thing runs locally without a web service, without Java, without GPL and without monthly costs.

We recently discussed electronic invoices at the Stammtisch. In addition to the technical aspects, there are also some organizational aspects to consider in the company. The question of how to process electronic invoices and how to create them offers plenty of scope for different implementations. Let's go through the individual steps:

Receive

Since January 1, 2025, your company should be able to receive electronic invoices. There are a few aspects to consider:

Postbox

You should create a separate email inbox for receiving emails.

e.g. Invoices@YourDomain.tld

Then you can automatically run actions on the server for this address, for example, place the email in an IMAP mailbox as a pure archive and in a second mailbox for automatic processing and, if you wish, place a third copy in a mailbox to which the employees have access.

We have an IMAP email example to automatically retrieve emails. You can configure an IMAP mailbox so that incoming invoices are automatically stored there. Then a server script can load the next email every 10 minutes using the MBS FileMaker Plugin with the CURL functions, place it in a data record and delete it on the server. The whole process runs in a loop until there are no more emails in the mailbox and all emails have been imported. The EmailParser functions help to parse the emails and separate the attachments.

If you use DATEV Unternehmen Online (or similar software), you could forward a copy of the email directly to it for automatic import. Alternatively, you can transfer an invoice to DATEV via web service.

ZUGFeRD PDF processing

You can use the DynaPDF functions to extract the XML part from a ZUGFeRD PDF file.

Open PDF

You open the PDF with DynaPDF.OpenPDFFromContainer or DynaPDF.OpenPDFFromFile and see if DynaPDF reports an error. This would detect if someone is sending malware that only disguises itself as a PDF.

The DynaPDF.GetJavaScriptCount function should always return zero, as JavaScript has no place in an invoice.

Check Version

Next, look for the version information with DynaPDF.GetInPDFVersionEx. It should look something like this:


{
    "Major":    1,
    "Minor":    6,
    "MainVer":  "PDF/A-3b",
    "SubVer":   "Factur-X",
    "PDFAConformance":  "B",
    "PDFAVersion":  3,
    "PDFEVersion":  "",
    "PDFVTModDate": "",
    "PDFVTVersion": "",
    "PDFXConformance":  "",
    "PDFXVersion":  "",
    "FXConfLevel":  "EN 16931",
    "FXDocName":    "factur-x.xml",
    "FXDocType":    "INVOICE",
    "FXVersion":    "1.0",
    "VersionConst": 4194325
}

Here you can see the PDF/A version, the level used and the name of the attachment. If this is missing, it is probably not a ZUGFeRD PDF file.

Read XMP metadata

You can use the DynaPDF.GetInMetadata function to read the XMP metadata. This comes as XML and you can validate this against a schema and read out the values.
Read attachments

With the DynaPDF.GetEmbeddedFileCount function you can find out how many attachments are available with DynaPDF.GetEmbeddedFile you can query metadata or the content. Here you get the XML extracted from the PDF.

Optionally, the attachment also contains an MD5 checksum, which you can check with the DynaPDF.CheckEmbeddedFileCheckSum function. This ensures that the XML has not been changed since the MD5 was calculated.

Validate

You should validate incoming invoices and reject invalid invoices. It would be unpleasant if, in 5 years' time, a tax auditor automatically goes through your archive of electronic invoices and finds incorrect invoices and rejects them for input tax deduction.

XML validates?

You can edit the XML with one of our XML functions. If a parse error occurs, the plugin will report this. XML.Parse or simply XML.Format is sufficient for this, but without the flag 1 for automatic repair.
XML structure matches the schema?
You can use XML.Validate or, better Saxon.Validate to check whether the XML matches the schema definition for the electronic invoice. The schema files are available for both Cross Industry Invoice (X-Rechnung or ZUGFeRD) and UBL formats. This check would detect if an entry in the XML is duplicated or missing.

see Validating schema for electronic invoices with ZUGFeRD
XML matches the Schematron?
There is a Schematron file with approx. 400 business rules for the ZUGFeRD standard. These rules check whether the contents of the invoice are plausible.

The Schematron file can be converted into an XSLT, which is applied to the invoice XML. With ZUGFeRD 2.3, the appropriate XSL file is included directly.

We have an example to check the XSLT transformation with the Saxon.XSLT function and display the result.

See Validate electronic invoices with Saxon

Display

If customers want to view an XML, you can refer them to the Quba Viewer or similar software. This allows anyone to view an electronic invoice free of charge.

We can also use the XML transformations in FileMaker. To do this, download the XRechnung Visualization Transformators from GitHub and use the Saxon functions in the MBS FileMaker Plugin. The XLST generates an intermediate XML from a Cross Industry Invoice (ZUGFeRD, Factur-X, X-Rechnung) or a UBL XML invoice and then transforms it into HTML, which you can display in a WebViewer.

See Visualize electronic invoices with Saxon

You can also display an invoice by reading all values, writing them to fields and displaying them via FileMaker.

Extract invoice

Our sample database “ZUGFeRD Rechnung auslesen” shows how to read ZUGeRD invoices.

We use the XML.GetPathValue function to read the data via paths. This is repeated with hundreds of fields and we need some loops for the items.

For example, you can use XML.GetPathValue to read the date:

MBS("XML.GetPathValue"; Saxon XSLT::XML; "CrossIndustryInvoice.ExchangedDocument.IssueDateTime.DateTimeString"; 1)

You may need to add some fields to your database here. You also need to import some code tables so that you can display what is meant by various IDs, e.g. 380 for an invoice, S for the standard VAT rate and 48 for payment by card. Rate and 48 for payment by card. The tables are included as XML and Excel files with the ZUGFeRD standard download.

Checks

Your workflows should check whether things fit together. Does the quote match the inquiry, does the order match the quote, does the delivery bill match the order and does the invoice match the delivery bill.

Ideally, the invoice matches the order 1:1 and when the goods are received, someone only has to confirm that all items have been delivered and then the invoice can be paid and booked automatically.

It is important to have a supplier database with the permitted suppliers, their bank details and addresses.

You should therefore check this when you receive the invoice:

Is the invoice from one of our authorized suppliers?

Unfortunately, we have been receiving unsolicited invoices or offers that look like an invoice for years. For example, for entries in some directories.

Of course, there can also simply be mix-ups or mishaps and someone sends an invoice to the wrong email.
Does the invoice match an order?

Here it is particularly interesting to check whether the quantities match, the items match and the prices have remained the same.
Are the totals correct?

It is advisable to recalculate the totals.
Are the bank details correct?

The problem here is that a fraudster can manipulate the XML and change the bank details in the XML. You should therefore check that one of the known bank details is on the invoice and possibly ask whether the change is legitimate. However, please do not call the telephone number on the invoice, but look up the number on old invoices or in the telephone directory. The telephone number on the bill may also have been changed.

Archiving

The invoice must be archived for 10 years. You can of course do this yourself. You must take technical and organizational measures to ensure that employees cannot change or delete the invoices.

Technically, you can restrict access rights and use signatures or hash chains to log that no invoice has been changed. You write a data record for each file. This contains the path to the file and a hash (e.g. SHA512). The data record is given a hash for its data. This hash is hashed with the hash of the last data record and forms a chain of data records (blockchain). The hashes can be calculated at any time to check the chain.

Alternatively, and if your tax advisor uses DATEV, you can book companies online there. You can then import your invoices via web service, email, iPhone app or browser. DATEV can then archive the invoice for 10 years.

Send

From January 1, 2027, or for small companies from January 1, 2028, you must send your invoices electronically in one of the permitted formats. Small businesses are currently exempt from this.

Preliminary considerations

When sending electronic invoices, you first need to consider what exactly you want, how you want to implement it and what is necessary for this.

If your customers want to receive invoices in UBL format via the Peppol network, you would need to generate this format.

If you have both corporate and private customers and in particular do not know the status, ZUGFeRD is the recommended format. This gives your private customers a PDF for reading, filing and printing. Corporate customers can read and process the embedded invoice as XML.

Technically, you can also send the XML and send a PDF as a multiple copy as a second file. Please note that the combination of PDF and XML must meet the requirements of Section 14 (4) of the German Value Added Tax Act (UStG) in order to represent multiple copies of the same invoice with identical content. Otherwise you will have created two invoices!

Code lists

You need to import numerous lists of values for the various invoice formats. These are included with the ZUGFeRD standard as Excel and XML files. There you will then find out which abbreviations you need to use for units, e.g. 58 for a net kilogram.

You therefore need at least 20 tables to link the abbreviations to text. Then complete the corresponding fields in your tables for invoices and items so that you can enter the data. The employees can enter the abbreviations or they can display the list of values as a dialog using ListDialog functions, for example, and let the user select them.

Building XML

Whether ZUGFeRD, Factur-X, X-Rechnung or UBL invoice, we can help with the XML functions. You can take a look at our example “Create ZUGFeRD invoice”. But perhaps you would like to start with a ZUGFeRD sample invoice. You can enter the fixed values in the XML in advance. You can then use XML.SetPathValue to enter the values for the invoice.

Alternatively, you can export your data records for the invoice as XML and transform them into the invoice format using an XSLT.

Build PDF

You can create the PDF with FileMaker using the “Export records as PDF” script step. You can then adapt our example for ZUGFeRD to insert the XML and convert the PDF to PDF/A. To do this, you will need a DynaPDF Pro license with the PDF/A extension. Alternatively, you can do the PDF conversion with other software and then DynaPDF Lite is sufficient to embed the XML.

Send invoice

With the SendMail functions you can send the emails with the invoices directly from FileMaker and attach the XML and/or PDF version.

Ready-made modules and products

We are happy to provide you with our examples and with the MBS FileMaker Plugin a powerful tool card.

If you do not yet have ERP software to write your invoices, you can access various ready-made packages. Several of our customers use the MBS FileMaker Plugin to offer e-invoices in their software. For example, gFM-Business, advanter, onexio, VEO Enterprise and many others offer e-invoices as part of their packages.

The developers Robert Herman and Armin Egginger are working on FileMaker modules. Robert presented his solution at the FMK. Armin has presented his current status at several regulars' tables. While our examples of the MBS FileMaker Plugin only implement the functionality, Armin has invested considerably more work in a general interface and has integrated the business rules for validation.

Receive

Postbox

ZUGFeRD PDF processing

Open PDF

Check Version

Read XMP metadata

Read attachments