2.5 Calculated Export of XML

FileMaker Pro exports with the FMPXMLRESULT and FMPDSORESULT formats. You may need to transform the exported data with a style-sheet for use with other applications. An XSL stylesheet may be used to make the transformation as you export. Stylesheets can be applied to XML after an export as well. To help you understand the logic of XSL, in this section we'll use some common FileMaker Pro functions and script steps to create calculated exports as XML.

Sometimes it may be just as easy to create a quasi-export with FileMaker Pro text functions and scripts. The structure of the XML in Listing 2.14 will be the result for this example of calculated export. The calculated export of HTML in Exercise 1.2 was used to create an ASCII table. The same principles can be used to create calculated XML export. The calculation for the HTML is as follows:

 Case(ASCII = 0, "<html><head><title>ASCII TABLE</title></head>¶<body>  <table border=0>¶<tr><th>ASCII</th><th>Character</th></tr>¶", "") &  "<tr><td>" &ASCII & "</td><td>" & Character & "</td></tr>" & Case(ASCII = 255, "¶</table></body></html>", "")

The above calculation is simple enough. The first Case statement appends "header" information before the first record. The middle part is repeated for every record, and the final Case statement appends "footer" information after the last record. The first and last record use the ASCII numbers 0 and 255 to determine the first and last records.

The FileMaker Pro function Status(CurrentRecordNumber) = 1 can also be used to determine the first record, and Status(Current-RecordNumber) = Status(CurrentFoundCount) can also be used to determine the last record of a found set. To help us see the required double quotes in the attribute calculation, a global text field named "q" will contain a double quote character. For our example, the element <items> will be the root element (first and last):

 <!-- first record --> Case(Status(CurrentRecordNumber) = 1, "<?xml version="& q & "1.0"& q &   " encoding="& q & "UTF-8"& q &" ?><items>", "") & <!-- last record --> & Case(Status(CurrentRecordNumber) = Status(CurrentFoundCount),       "</items>", "")

The database ITEMS.FP5 is used for the following example. The fields in this database are custID, invoiceID, itemID, itemQty, itemDescription, itemColor, and itemPrice. Use a calculation field or a Set Field[] script step in a loop through the item records. Each of the item elements will be calculated, taking the values from the field contents in the database:

 "<item >" & "<qty>" & itemQty & "</qty>" & "<description>" & itemDescription & "</description>" & "<color>" & itemColor & "</color>" & "<price>" & itemPrice & "</price>" & "</item>"

Put the two code snippets above together as shown in Listing 2.15. Export just the calculated field as tab-separated text to get the result.

Listing 2.15: Calculated items XML and result

 Case(Status(CurrentRecordNumber) = 1, "<?xml version="& q & "1.0"& q &   " encoding="& q & "UTF-8"& q &" ?><items>", "") & "<item >"& "<qty>" & itemQty & "</qty>" & "<description>" & itemDescription & "</description>" & "<color>" & itemColor &"</color>" & "<price>" & itemPrice & "</price>" & "</item>" & Case(Status(CurrentRecordNumber) = Status(CurrentFoundCount),   "</items>", "") <!-- the result, if the calculated field is exported "calcItems.xml" --> <?xml version="1.0" encoding="UTF-8" ?><items><item ><qty>1</qty>   <description>Cars</description><color>Blue</color><price>4</price></item> <item ><qty>3</qty><description>Trucks</description><color>Blue   </color><price>5.15</price></item> <item ><qty>2</qty><description>Trucks</description><color>Red   </color><price>5.15</price></item></items>

The above calculation is only a part of the information needed to complete the XML seen in Listing 2.14. For example, the items are all listed, but there are no elements telling us to which invoice they belong or to which customer. If you use the invoiceID as a match field back to the INVOICES from the ITEMS file, you can use the invoiceID relationship to also use the related fields in your calculation. A custID relationship can also be created back to the CUSTOMERS file to get the information for the calculated export.

Create global number fields to test the changes in customerID and invoiceID as you loop through the records _customerID and _invoiceID. Sort the records by customerID and invoiceID and create the sort script:

 Sort CustomerID InvoiceID       Sort [Restore, No dialog]

The following script will loop through the records, create the export field XMLinvoices in each record, and place parent elements around child elements. Export the field as tab-separated text and view the document in the Microsoft Internet Explorer browser.

Listing 2.16: Calculated invoices XML and result

 Loop Create Export for Invoices and Items       # "<!-- set up variables -->"       Set Field [ _invoiceID, "" ]       # "<!-- sort to get customers and invoices together -->"       Perform Script [ Sub-scripts, "Sort CustomerID InvoiceID" ]       # "<!-- begin loop for invoices -->"       View As [ View as List ]       Loop             Perform Script [ Sub-scripts, "Create Export for               Invoice Items" ]             If [ Status(CurrentRecordNumber)=1]                   Set Field [ XMLinvoices, "<?xml version=" & q                     & "1.0"&q&" encoding="&q& "UTF-8" & q                     & " ?><invoices>" ]             Else                   Set Field [ XMLinvoices, "" ]             End If             If [ _invoiceID <> invoiceID ]                   If [ _invoiceID <> "" ]                     Set Field [ XMLinvoices, XMLinvoices &                       "</items></invoice>" ]                   End If                   Set Field [ _invoiceID, invoiceID ]                   Set Field [ XMLinvoices, XMLinvoices &                     "<invoice ><date>"                     & Month(invoiceID INVOICES::invoiceDate) & "/"                     & Day(invoiceID INVOICES::invoiceDate) & "/"                     & Year(invoiceID INVOICES::invoiceDate) &                     "</date><total>" & invoiceID INVOICES::                     invoiceTotal & "</total><items>" & XMLitems ]             Else                   Set Field [ XMLinvoices, XMLinvoices & XMLitems ]             End If             If [ Status(CurrentRecordNumber) = Status(CurrentFoundCount) ]                   Set Field [ XMLinvoices, XMLinvoices &                    "</items></invoice></invoices>" ]             End If             Go to Record/Request/Page [ Next, Exit after last ]       End Loop       View As [ View as Form ] <!-- the result, if the calculated field is exported "calcInvoices.xml" --> <?xml version="1.0" encoding="UTF-8" ?><invoices><invoice   ><date>12/5/1997</date><total>4</total><items><item   ><qty>1</qty><description>Cars</description><color>Blue   </color><price>4</price></item> </items></invoice><invoice ><date>11/12/1997</date><total>25.75   </total><items><item ><qty>3</qty><description>Trucks</description>   <color>Blue</color><price>5.15</price></item> <item ><qty>2</qty><description>Trucks</description><color>Red   </color><price>5.15</price></item></items></invoice></invoices>

Challenge: You can revise the calculations and scripts to include the CUSTOMER elements <customers>, <customer >, and <name>. A single field can contain a maximum of 64,000 characters, so you may need to store each loop step result in a single field in a separate file, one record per step. There are also many fine FileMaker Pro plug-ins that can assist you with calculated XML export. You can find a listing of these at http://www.filemaker.com/.