Electronic invoices with FileMaker
The implementation of electronic invoices in existing FileMaker solutions is an increasingly important topic in the digital business world. In particular, the increasing legal obligation to use electronic invoices for certain business processes and the resulting benefits, such as cost efficiency and process automation, make it essential for companies to develop efficient solutions for processing such invoices. In this context, various standards for electronic invoices are available, including XRechnung and ZUGFeRD/Factur-X. These standards pose different technical challenges, particularly with regard to reading and processing the data they contain.
Challenges when importing electronic invoices
One of the biggest challenges when importing electronic invoices is the variety of formats and types of data that need to be processed. While the structure of the XRechnung is relatively clear and only one XML document needs to be analyzed, processing a ZUGFeRD or Factur-X invoice poses a greater challenge. These are hybrid documents that contain both a PDF document and embedded XML data.
Difference between XRechnung and ZUGFeRD/Factur-X
XRechnung: This standard contains all relevant invoice data in a pure XML file. Reading in this data requires the XML document to be parsed in order to extract the required information. The main challenge here lies in the complexity of the XML format and the need to ensure that all relevant data fields are read and interpreted correctly.
ZUGFeRD/Factur-X: In this format, the XML data is embedded in a PDF file. This means that the PDF must first be extracted and analyzed in order to access the XML data. This process requires special tools or plugins to reliably extract the embedded information from the PDF document.
Options for processing PDFs in FileMaker
A key challenge when importing ZUGFeRD/Factur-X invoices is extracting the XML data from the PDF. There are various approaches and tools that can be integrated into FileMaker:
MBS Plugin (MonkeyBread Software)The MBS plugin is a powerful extension for FileMaker that offers a variety of features, including extensive PDF processing capabilities. With this plugin, FileMaker can read and parse PDFs to extract embedded XML data. It also provides access to the PDF's metadata and supports parsing and processing of the contained content. The MBS plugin is particularly useful for developers who need a flexible and comprehensive solution to process PDFs in their FileMaker workflows.
DynaPDF PluginThe DynaPDF plugin is another powerful tool for PDF processing in FileMaker. It offers functions for creating, modifying and reading PDF files and is ideal for accessing embedded XML data in ZUGFeRD/Factur-X invoices. With DynaPDF, the XML data can be securely extracted and made available for further processing in FileMaker. In addition, the plugin offers functions for analyzing and extracting text and images from PDFs, making it a versatile solution for comprehensive PDF processing requirements.
Technical requirements and considerations
There are various technical aspects to consider when using such plugins and tools. On the one hand, this includes the licensing of the plugins, as many of these tools are subject to a fee and offer different license models depending on the range of functions. On the other hand, careful planning of the integration into existing FileMaker solutions is required to ensure that the plugins work seamlessly and that no compatibility problems occur.
Another important point is the performance of the solution. Reading and processing XML data from PDFs can be computationally intensive, especially if large quantities of invoices have to be processed simultaneously. It is therefore advisable to design the solution in such a way that it works efficiently with the available resources and ensures a high processing speed.
FileMaker ERP software with integrated processing of electronic invoices.
More information
Import and process XRechnung in FileMaker
In addition to the general mandatory fields of the invoice, a comprehensive implementation of the import of an XRechnung into a FileMaker solution also includes the processing of document items (line items) and validation of the data to ensure the integrity of the imported information. The process is described in detail below.
BaseElements plugin for XML parsing
The BaseElements plugin significantly simplifies the processing of XML data in FileMaker. The plugin offers a variety of functions to extend the FileMaker functionality, including the parsing of XML data. One of the most frequently used functions for this purpose is BE_XPath, which can be used to extract XML data from a file or a variable.
Overview of the BaseElements plugin
The BaseElements plugin is a free plugin designed specifically for FileMaker to provide additional functionality. It is particularly useful for working with XML, JSON, file management and other special requirements that FileMaker does not natively support. With the BE_XPath function, XML data can be extracted quickly and accurately by using XPath expressions to read specific parts of the XML document.
Step-by-step instructions for importing an XRechnung
1. preparations in FileMaker
Make sure that your FileMaker database contains the required tables and fields as described above:
Main table "Invoices":
- Invoice number
- Invoice date
- Buyer name
- Buyer address
- Seller name
- Seller address
- Total amount
- Value added tax amount
- Currency
- Terms of payment
Linked table "Invoice items":
- Article description
- Quantity
- Unit price
- Total amount of the item
- VAT rate
FileMaker Crash Course
Fast and effective FileMaker
learning in 20 chapters.
Participate free of charge
2. import XML file
Load the content of the XML file into a variable, e.g. with the Import data function or by reading the content directly from a file.
Set variable [$xmlPath; Value: "Path/to/of/XInvoice.xml"]
Set variable [$xmlContent; Value: BE_ReadTextFromFile($xmlPath)]
--- Import of mandatory fields with BE_XPath ---
Set variable [$Rechnchnchnumber; Value: BE_XPath($xmlContent; "//cbc:ID"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2")]
Set variable [$Rechnungsdatum; Value: DateTimeMySQLToFMDate ( BE_XPath($xmlContent; "//cbc:IssueDate"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2") )]
Set variable [$CustomerName; Value: BE_XPath($xmlContent; "//*[local-name()='AccountingCustomerParty']/*[local-name()='Party']/*[local-name()='PartyName']/*[local-name()='Name']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2")]
Set variable [$CustomerAddress; Value: BE_XPath($xmlContent; "//*[local-name()='AccountingCustomerParty']/*[local-name()='Party']/*[local-name()='PostalAddress']/*[local-name()='StreetName']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2") & ", " & BE_XPath($xmlContent; "//*[local-name()='AccountingCustomerParty']/*[local-name()='Party']/*[local-name()='PostalAddress']/*[local-name()='CityName']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2")]
Set variable [1TP4VendorName; Value: BE_XPath($xmlContent; "//*[local-name()='AccountingSupplierParty']/*[local-name()='Party']/*[local-name()='PartyName']/*[local-name()='Name']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2")]
Set variable [1TP4VendorAddress; Value: BE_XPath($xmlContent; "//*[local-name()='AccountingSupplierParty']/*[local-name()='Party']/*[local-name()='PostalAddress']/*[local-name()='StreetName']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2") & ", " & BE_XPath($xmlContent; "//*[local-name()='AccountingSupplierParty']/*[local-name()='Party']/*[local-name()='PostalAddress']/*[local-name()='CityName']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2")]
Set variable [$TotalAmount; Value: BE_XPath($xmlContent; "//cac:LegalMonetaryTotal/cbc:PayableAmount"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2")]
Set variable [$MVATAmount; Value: BE_XPath($xmlContent; "//cac:TaxTotal/cbc:TaxAmount"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2")]
Set variable [$Currency; Value: BE_XPath($xmlContent; "cbc:DocumentCurrencyCode")]
Set variable [$PaymentTerms; Value: BE_XPath($xmlContent; "//cac:PaymentTerms/cbc:Note"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2")]
--- Create new data record in the main table ---
New data record/query
Set field value [Invoices::Invoice number; $Rinvoice number]
Set field value [Invoices::Invoice date; $RInvoice date]
Set field value [Invoices::Buyer name; $Buyer name]
Set field value [Invoices::Buyer address; $Buyer address]
Set field value [Invoices::Vendor name; 1TP4Vendor name]
Set field value [Invoices::Vendor address; 1TP4Vendor address]
Set field value [Invoices::Total amount; $Total amount]
Set field value [Invoices::VAT amount; $MVAT amount]
Set field value [Invoices::Currency; $Currency]
Set field value [Invoices::Terms of payment; $Terms of payment]
--- Import of line items ---
Set variable [$PositionenAnzahl; Value: BE_XPath($xmlContent; "count(//cac:InvoiceLine)")]
Set variable [$Counter; value: 1]
Loop start
Set variable [$ArticleDescription; Value: BE_XPath($xmlContent; "//*[local-name()='InvoiceLine'][" & $lineIndex & "]/*[local-name()='Item']/*[local-name()='Name']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2")]
Set variable [$Menge; Value: ReadAsNumber(Exchange(BE_XPath($xmlContent; "//*[local-name()='InvoiceLine'][" & $lineIndex & "]/*[local-name()='InvoicedQuantity']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"); "."; ","))]
Set variable [$EInvoiceLine; Value: ReadAsNumber(Exchange(BE_XPath($xmlContent; "//*[local-name()='InvoiceLine'][" & $lineIndex & "]/*[local-name()='Price']/*[local-name()='PriceAmount']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"); "."; ","))]
Set variable [$TotalAmountDerPosition; Value: BE_XPath($xmlContent; "//cac:InvoiceLine[" & $Counter & "]/cbc:LineExtensionAmount")]
Set variable [$MwStStatz; Value: ReadAsNumber(Exchange(BE_XPath($xmlContent; "//*[local-name()='InvoiceLine'][" & $lineIndex & "]/*[local-name()='Item']/*[local-name()='ClassifiedTaxCategory']/*[local-name()='Percent']"; "cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2|cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"); "."; ","))]
Go to Layout "Invoice items"
New data record/query
Set field value [Invoice items::Item description; $Article description]
Set field value [Invoice items::Quantity; $Quantity]
Set field value [Invoice items::Unit price; $Unit price]
Set field value [InvoiceItems::TotalAmountOfItem; $TotalAmountOfItem]
Set field value [Invoice items::VAT rate; $Mvat rate]
Exit loop When $PositionsNumber = $Counter
Set variable [$Counter; value: $Counter + 1]
Loop end
3. validation of the imported data
Check that all mandatory fields are present and that no data is missing. This can be done using a script that ensures that all necessary variables contain values.
When [ActualVoid(Invoices::Invoice number)]
Display your own dialog box ["Error: The invoice number is missing."].
Exit current script
End when
When [ActualVoid(Invoices::Invoice date)]
Display your own dialog box ["Error: The invoice date is missing."].
Exit current script
End when
--- Perform further validation checks for all mandatory fields ---
When [Sum of invoice items::Total amount of the item ≠ Invoices::Total amount]
Display your own dialog box ["Error: The sum of the line items does not match the total amount"].
Exit current script
End when
Further fields and data of electronic invoices
In our guide, we have focused on importing the most important mandatory fields to provide a basic overview of the integration of electronic invoices in FileMaker. However, it is important to mention that electronic invoices can contain many more fields and information that are important depending on the use case.
For example, the routing ID, which is mainly used in the public sector to identify administrative units, can play a crucial role in determining the correct recipient. Additional fields such as specific reference numbers, payment information and tax details are also relevant to enhance invoice processing and meet legal requirements and individual business needs. A complete implementation should therefore be carefully checked and, if necessary, supplemented with these additional fields to ensure a comprehensive solution. Use the official documentation.
Four FileMaker ERP platforms for optimal operating processes.
Request information ZUGFeRD and more with the MBS and DynaPDF plugin
The MBS FileMaker Plugin is a highly versatile plugin for FileMaker that provides advanced functionality, including the ability to both read and generate electronic invoices. With the MBS plugin, developers can implement comprehensive automation solutions for handling XML data and embedded invoice information. A particularly valuable feature is the integration of DynaPDFa powerful PDF library that makes it possible to create and manipulate PDF documents, including those that support the ZUGFeRD/Factur-X format. This means that the plugin is not only able to read the data from PDFs and import it into FileMaker, but also to generate new electronic invoices that comply with current standards.
The MBS plugin comes with an extensive collection of sample files that cover various use cases, including the creation and import of ZUGFeRD invoices. These examples provide an excellent basis for quickly familiarizing yourself with how the plugin works. In addition, the manufacturer provides detailed instructions on its website that describe step by step how to create electronic invoices using the MBS plugin and DynaPDF. The latest instructions are available at this link which also contains download links for sample electronic invoice files. This resource is particularly helpful for developers who want to delve deeper into the automation of invoice processes in FileMaker and create legally compliant electronic invoices according to ZUGFeRD/Factur-X standards.
Forum post on the integration of electronic invoices
We have published a new post in our forum dedicated to the discussion about the integration and processing of electronic invoices in FileMaker. In this post, we invite all interested parties to ask personal questions and share their experiences. Whether it's about the use of plugins such as the MBS Plugin or the BaseElements Plugin, the implementation of scripts or the optimization of workflows - this forum post is the perfect place to exchange ideas and benefit from the expertise of the community. We want to encourage a lively discussion where solutions, challenges and best practices can be discussed. Stop by and be part of the conversation!
Summary
Integrating the reading of electronic invoices into existing FileMaker solutions poses various challenges, especially when processing ZUGFeRD/Factur-X invoices. While XRechnung is primarily an XML-based challenge, ZUGFeRD/Factur-X processing requires the use of special PDF tools and plugins such as MBS and DynaPDF. Careful planning, consideration of technical requirements and the selection of the right plugins are crucial for the successful implementation of an efficient and reliable solution.
The free BaseElements plugin and the command BE_XPath make it possible to process XML data in FileMaker and to import data reliably. The use of XPath enables precise extraction of the relevant information and flexible adaptation to different XML structures.
If ZUGFeRD or Factur-X files are also to be imported and read out, we recommend using the MBS plugin in conjunction with the DynaPDF plugin, which is also supplied with corresponding sample files.
