2.2 Special Export Considerations
|
|
2.2 Special Export Considerations
This section will discuss the XML export of special field types such as number, date, time, global, summary, and container fields. XML export of fields with layout formatting is also discussed along with related fields, repeating fields, font styles, value lists, and other special considerations, including the field names.
2.21 Character Encoding
The XML element content (or value of the element) is the FileMaker Pro field content. Text is returned between the element tags and may be encoded as UTF-8. Special characters in the field names or content may be displayed strangely if you view the XML in a text editor. The characters may be encoded to represent two characters. One example of this encoding is for the o-slash character. As text, it is often displayed as "⊘." The double-byte o-slash, or ASCII 191, may be displayed in the web browser source or a text editor as two characters, "√" and "π" The character will display correctly as "⊘" in the browser window.
Exercise 2.1: Export Double-byte Characters
Use the database in Chapter 1, ASCII.FP5. Export and create the text file ascii.html and view the file in a browser. Copy all of the result and paste into any text editor. Save the file as ascii.txt. Alternately, you can save the resulting web page as plain text. The copied or saved HTML table should have converted the space between the two columns to the tab character and placed a carriage return at the end of each table row. This text format (tab-separated text) can be used to import or create a FileMaker Pro database. Create a new database with the ascii.txt file. Export with FMPXMLRESULT or FMPDSORESULT the two fields in the new database. View the exported XML in a text editor to see the double-byte representation for these characters.
Listing 2.3 shows a small portion of the ascii.xml created. The ASCII characters 195 through 200 show the double-byte export. The next listing, 2.4, shows the correct rendering of this character in the browser.
Listing 2.3: Sample double-byte XML export characters
 |
<ROW MOD RECORD><COL><DATA>195</DATA></COL><COL> <DATA>Ã</DATA></COL></ROW><ROW MOD RECORD><COL><DATA>196</DATA></COL><COL><DATA>Ä</DATA></COL> </ROW><ROW MOD RECORD><COL><DATA>197</DATA></COL><COL><DATA>Å</DATA></COL> </ROW><ROW MOD RECORD><COL><DATA>198</DATA></COL><COL><DATA>Æ</DATA></COL> </ROW><ROW MOD RECORD><COL><DATA>199</DATA></COL><COL><DATA>Ç</DATA></COL> </ROW><ROW MOD RECORD><COL><DATA>200</DATA></COL><COL><DATA>È</DATA></COL> </ROW>
|
Listing 2.4: ASCII characters 195 through 200
|
195 Ã 196 Ä 197 Å 198 Æ 199 Ç 200 È
|
Test your field names and contents with a small set of found records. Perform the exports using FMPXMLRESULT and FMPDSORESULT. Look at the XML documents in a web browser or text editor. Microsoft Internet Explorer has a built-in stylesheet to render the XML with indents. The browser window will display your field names and contents correctly, even if you use any of the double-byte characters. If you view the source of the XML document in the browser, you will see the two characters representing the double-byte characters.
There are some other characters that will get encoded upon export as XML. Because XML uses some of these characters for tags, they are encoded within the field contents. Table 2.2 shows these characters and how they are encoded. Other characters may be converted in other exports, such as returns and tabs within fields. The return-in-field character gets converted to a vertical tab with other text exports from FileMaker Pro. When you use XML export, these white space characters in a field are not converted but may be invisible until viewed in a text editor. You can read more about white space and encoding in Chapter 1, section 1.41, "White Space and End-of-Line Characters." These encoded characters are also the predefined entities seen in Table 1.1.
| ASCII | Character | Encoding |
|---|---|---|
| 34 | " | " |
| 38 | & | & |
| 39 | ' | ' |
| 60 | < | < |
| 62 | > | > |
You should be aware of character conversion and encoding when you export XML, import XML, or use XML for web publishing with FileMaker Pro. Most parsers and processors will correctly handle the conversion for you. But you may need to check for the occurrence of any of these special characters in your XML documents.
2.22 XML from FileMaker Pro Related Fields
This section discusses related fields and how FileMaker Pro 6 displays these fields in both of the XML grammars. In the exercise below, you will create two databases, set up the relationship between them, and export the data. The FMPDSORESULT and FMPXMLRESULT exports produce different results when using related fields. Test both of these grammars to see which one is better for your needs.
Exercise 2.2: Create Related Data XML Results
You do not need to have any related fields displayed on a layout to export these kinds of fields, but you must have valid relationships. Any parent record without related child records will return empty elements. If you use a calculated relationship and temporarily disable the relationship on any given record, there will be only one set of empty related data exported for that record. Whether you use FMPXML-RESULT or FMPDSORESULT grammar for XML export, the contents of each valid related field is returned in DATA elements.
The number of DATA elements returned per field per record depends upon the number of valid related records. For example, if you have a parent with three related fields and record one has two valid related records, those three fields will have two DATA elements each. If the next parent record has five valid related records, the export will create five DATA elements for each related field in the export. A small example for FMPXMLRESULT is shown in Listing 2.5.
Listing 2.5: FMPXMLRESULT export of related fields
|
<!-- not a complete export --> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="relationshipName:: fieldOne" TYPE"Text" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="relationshipName:: fieldTwo" TYPE="Text" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="relationshipName:: fieldThree" TYPE="Text" /> </METADATA> <RESULTSET FOUND="2"> <ROW MOD RECORD> <COL> <DATA>A</DATA> <DATA>B</DATA> <DATA>C</DATA> </COL> <COL> <DATA>5</DATA> <DATA>6</DATA> <DATA>7</DATA> </COL> <COL> <DATA>a</DATA> <DATA></DATA> <DATA></DATA> </COL> </ROW> <ROW MOD RECORD> <COL> <DATA></DATA> </COL> <COL> <DATA></DATA> </COL> <COL> <DATA></DATA> </COL> </ROW> </RESULTSET>
|
If the parent record has no related child records, only one set of empty elements is returned. The FMPXMLRESULT returns "<COL><DATA></DATA></COL>" for every empty related field. This empty element allows the XML export to hold a place for a related field, even if empty, so that the same number of columns is exported. The FMPDSORESULT returns just the related field name as empty elements with no DATA elements. A small example of an XML export with FMPDSORESULT is shown in Listing 2.6.
Listing 2.6: FMPDSORESULT export of related fields
|
<!-- not a complete export --> <ROW MOD RECORD> <relationship.One> <DATA>A</DATA> <DATA>B</DATA> <DATA>C</DATA> </relationship.One> <relationship.Two> <DATA>5</DATA> <DATA>6</DATA> <DATA>7</DATA> </relationship.Two> <relationship.Three> <DATA>a</DATA> <DATA></DATA> <DATA></DATA> </relationship.Three> </ROW> <ROW MOD RECORD> <relationship.One></relationship.One> <relationship.Two></relationship.Two> <relationship.Three></relationship.Three> </ROW>
|
The number of rows in a portal on any layout is not a consideration for an export of related fields. For example, if you have a portal displaying five rows and have twelve related records, those other rows may be available on the layout if you have provided a scroll bar for the portal. However, all twelve related records would be exported.
Exercise 2.3: Create Related XML Exports
This exercise will show you how related field data is displayed in the XML produced by FileMaker Pro 6.
-
Create a main database, COMPANY.FP5, with these fields: Co_ID (number, auto-enter serial, primary key field) and CompanyName (text).
-
Create a related database, EMPLOYEES.FP5, with these fields: Em_ID (number, auto-enter serial, primary key field), Co_ID (number, secondary key field), Department (text), and EmployeeName (text).
-
Create a relationship called CoID in COMPANY.FP5 to EMPLOYEES.FP5, matching the fields "Co_ID." Allow creation of related records in this relationship.
-
Create a Layout named web and place the related fields on this layout along with the two fields in this database. (Remember that a portal is not needed for web publishing or XML export of related fields.) For data entry convenience, show the portal with four or five rows on this layout or on another layout.
-
Create a new record in COMPANY.FP5 and add related data through the portal. Leave some of the related fields blank.
RECORD 1: Co_ID=1, CompanyName=Herbson's Pices
Co_ID
Em_ID
Dept
Name
1
5
Seasons
Rosemary Thyme
1
6
Pickles
Elvis Parsley
1
7
Chutney
-
Perform the XML Export with FileMaker Pro 6. The results are shown in Listing 2.7. Choose File, Export Records. Name the new file companyExport.xml and choose the Type of XML. Click the Save button. In the Specify XML and XSL Options dialog (Figure 2.1), select the Grammar FMPXMLRESULT. Ignore the Use XSL stylesheet check box for now and click the OK button. In the Specify Field Order for Export dialog, select these fields: Co_ID and CompanyName from the current file (Company.FP5). Choose the CoID relationship and select the fields: CoID::Em_ID, CoID::Department, and CoID::Employee-Name. Click the Export button and save the file.
Listing 2.7: FMPXMLRESULT export in FileMaker Pro 6
|
<?xml version="1.0" encoding="UTF-8" ?> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="08/09/2002" NAME="FileMaker Pro" VERSION="6.0v3" /> <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="COMPANY.FP5" RECORDS="1" TIMEFORMAT="h:mm:ss a" /> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Co_ID" TYPE="NUMBER" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CompanyName" TYPE="TEXT" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CoID::Em_ID" TYPE="NUMBER" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CoID:: Department" TYPE="TEXT" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CoID:: EmployeeName" TYPE="TEXT" /> </METADATA> <RESULTSET FOUND="1"> <ROW MOD RECORD> <COL> <DATA>1</DATA> </COL> <COL> <DATA>Herbson's Pices</DATA> </COL> <COL> <DATA>5</DATA> <DATA>6</DATA> <DATA>7</DATA> </COL> <COL> <DATA>Seasons</DATA> <DATA>Pickles</DATA> <DATA>Chutney</DATA> </COL> <COL> <DATA>Rosmary Thyme</DATA> <DATA>Elvis Parsley</DATA> <DATA></DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT>
|
This export is similar to the MERGE format export. The field names are returned as <FIELD> elements within the <METADATA> element. Any field names with spaces are contained in quotes with the FMPXMLRESULT. The related fields are shown with the name of the relationship, a double colon (::), and the name of the field. Finally, encoding has been performed on the data. The apostrophe in the DATA element in the first ROW and second COL has been encoded as "'".
-
Perform the same export, but this time choose FMPDSORESULT and see how the related fields are exported. Listing 2.8 shows that the field names become the element names. Related field names are converted to the name of the relationship, a single period (˙), and the name of the related field. As with unrelated fields, any spaces in the relationship name or the field are converted to the underscore character (_).
Listing 2.8: FMPDSORESULT export in FileMaker Pro 6
|
<<?xml version="1.0" encoding="UTF-8" ?> <?xml version="1.0" encoding="UTF-8" ?> <FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult"> <ERRORCODE>0</ERRORCODE> <DATABASE>COMPANY.FP5</DATABASE> <LAYOUT></LAYOUT> <ROW MOD RECORD> <Co_ID>1</Co_ID> <CompanyName>Herbson's Pices</CompanyName> <CoID.Em_ID> <DATA>5</DATA> <DATA>6</DATA> <DATA>7</DATA> </CoID.Em_ID> <CoID.Department> <DATA>Seasons</DATA> <DATA>Pickles</DATA> <DATA>Chutney</DATA> </CoID.Department> <CoID.EmployeeName> <DATA>Rosmary Thyme</DATA> <DATA>Elvis Parsley</DATA> <DATA></DATA> </CoID.EmployeeName> </ROW> </FMPDSORESULT>
|
2.23 Repeating Field Data
Defining the number of repetitions in the Field Definition dialog creates repeating fields. Click the Options button and choose the Storage tab in the dialog. Check the Repeating field with a maximum of __ repetitions option. Enter the number of repetitions and click OK to close the Options dialog. Repeating fields are displayed on a layout by placing the field on the layout, and selecting Format, Field Format. Enter the number of repetitions in the Show __ of field's ## defined repetitions option. You may show 1 to the number of defined repetitions for the field. You can select the orientation of Vertical or Horizontal for each repeating field display on the layout.
Tab-Separated Export of Repeating Fields
When you select a repeating field for export and use the tab-separated text type, the ASCII character 29 (HEX 0x1D) is placed between the repetitions. The number of repetitions exported is based upon the last repetition, not the number of repeats displayed on the layout. For example, if you define a field with ten repetitions and enter something into the sixth repetition, six items will be exported with the ASCII 29 between the items. An empty repetition will have the ASCII character 29 and no other characters, but only if one of the repetitions following it has data.
It's important to understand how FileMaker Pro handles repeating fields for export when the type is tab-separated text. FileMaker Pro uses the character between the repeats if you need to import fields with repetitions. It is also important to remember that other applications may not handle this special character if you use the exported data anywhere else. We'll now see how the export with XML is different from a tab-separated text export but similar to related fields.
Exercise 2.4: XML Export of Repeating Fields
-
Define a text field named Repeat in the Company database used in the previous exercises.
-
Click on the Options button, select the Storage tab, and enter the number of Repetitions as 10 in the Options dialog.
-
Place the field on your layout and set the field format to display five of the field's ten repetitions. Place the field on another layout and display all ten of the field's repetitions.
-
Enter data into the repeating field as shown in the table below:
Repetition
Data
1
One
2
Two
3
4
Four
5
6
Six
-
Go back to the main layout with only five repetitions displayed. You cannot see the final data entered on the other layout.
-
Export as XML using the FMPXMLRESULT. Add the Repeat field to the list of fields to export. To easily see the repeating field in XML, remove the related fields at this time. Listing 2.9 shows the result with the fields Co_ID, CompanyName, Repeat, and Constant.
Listing 2.9: FMPXMLRESULT export of a repeating field
|
<?xml version="1.0" encoding="UTF-8" ?> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="08/09/2002" NAME="FileMaker Pro" VERSION="6.0v3" /> <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="COMPANY.FP5" RECORDS="1" TIMEFORMAT="h:mm:ss a" /> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Co_ID" TYPE="NUMBER" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CompanyName" TYPE="TEXT" /> <FIELD EMPTYOK="YES" MAXREPEAT="10" NAME="Repeat" TYPE="TEXT" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Constant" TYPE="NUMBER" /> </METADATA> <RESULTSET FOUND="1"> <ROW MOD RECORD> <COL> <DATA>1</DATA> </COL> <COL> <DATA>Herbson's Pices</DATA> </COL> <COL> <DATA>One</DATA> <DATA>Two</DATA> <DATA></DATA> <DATA>Four</DATA> <DATA></DATA> <DATA>Six</DATA> <DATA></DATA> <DATA></DATA> <DATA></DATA> <DATA></DATA> </COL> <COL> <DATA>1</DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT>
|
The most noticeable difference when exporting a repeating field with XML is the MAXREPEAT attribute for that FIELD element. We defined the field to have ten repetitions and placed five of them on the layout. The XML export uses the defined maximum as the value for MAXREPEAT for that repeating field. The next difference from the tab-separated text export is how the XML export shows all of the repetitions, even if they are empty or not displayed on the layout. There are ten DATA elements created with the field repetition contents and the ASCII character 29 is not used. The FMPDSORESULT grammar also uses all ten of the repetitions for the XML export, as seen in the next listing.
Listing 2.10: FMPDSORESULT export of a repeating field
|
<?xml version="1.0" encoding="UTF-8" ?> <FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult"> <ERRORCODE>0</ERRORCODE> <DATABASE>COMPANY.FP5</DATABASE> <LAYOUT></LAYOUT> <ROW MOD RECORD> <Co_ID>1</Co_ID> <CompanyName>Herbson's Pices</CompanyName> <Repeat> <DATA>One</DATA> <DATA>Two</DATA> <DATA></DATA> <DATA>Four</DATA> <DATA></DATA> <DATA>Six</DATA> <DATA></DATA> <DATA></DATA> <DATA></DATA> <DATA></DATA> </Repeat> <Constant>1</Constant> </ROW> </FMPDSORESULT>
|
Repeating fields use the same format as related fields when you export as XML in FileMaker Pro 6. The DATA element contains the values for related and repeating fields. All of the defined repetitions are exported, but only the number of related records are used in the XML export. What about related repeating fields if they both use the DATA element? Create a repeating field in the Employee database used in the above examples. Set the number of repetitions to 2 or 3 and enter data in some of the repetitions. Now try to use the repeating field in your XML export. You will get this message: "This export type does not support related repeating field. Only the first item from each repeating field will be exported."
2.24 Number, Date, and Time Field Formats
Fields can be formatted on a layout to constrain the actual data entered. Among these fields are the number, date, and time types. Enter Layout mode and click once on a number field. You can choose Format, Number from the menus or right-click your mouse to display the dialog box shown in Figure 2.3. Control+click on the number field will also produce the contextual menu for that object (the number field). Numbers can be formatted as Boolean, and text up to seven characters can be displayed for non-zero and zero values. Numbers can also be formatted as decimal with options for currency notation. Other options are shown in the dialog and can be found in the Help topic "Specifying formats for fields containing numbers."
Figure 2.3: FileMaker Pro Number Format dialog
Numbers in fields are only displayed on a layout with these formats. The values of the numbers in the field do not change. XML results will return this value, not the formatted displayed value. You must specify the Format output using current layout option in the Specify Field Order for Export dialog when you export XML if you want to retain any of the layout formatting for numbers.
Another option for exporting a number as you want it is to create a calculation of type text. For example, the number 12.5 may be formatted as $12.50 on your layout. The calculated field (text type result) "dollars" might be defined as:
"$" & Int(number) & Case(Int(number) <> number, "." & Left(Middle(number, Position(number, ".", 1, 1) + 1, Length(number) - Position(number, ".", 1, 1)) & "00", 2), ".00")
| Note | The above calculation does not round a number as it might be formatted on the layout. For example, 1.657 might be formatted as $1.66 if two decimal places are specified. The calculation may be revised to account for this possibility. |
The format of dates on a layout is controlled just like the format of numbers. The Date Format dialog is shown in Figure 2.4, and you can read more about the date formats in the FileMaker Pro Help topic "Specifying formats for date fields." Your operating system formats for dates will also have an influence on the display and entry of these dates. If you change the date and time format on your operating system, FMP will respond. The results are still as entered.
Figure 2.4: FileMaker Pro Date Format dialog
Time format options are shown in Figure 2.5. You can read more about these in the Help topic, "Specifying formats for time fields." The time format is also a function of your operating system. You can reformat a time field by selecting the field in Layout mode. Choose Format, Number, and make your selections, or use the contextual menu for the field to open the format dialog. You can have different formats for the same field on different layouts.
Figure 2.5: FileMaker Pro Time Format dialog
All of these formatting options for numbers, dates, and times will be exported differently if you export as XML and choose the layout format option. You may also export calculation fields or post-process numbers, dates, or time with a formatting command. XSL has functions to change the display of numbers. Other applications may have options for reformatting number, date, or time data.
2.25 Formatted Text and XML Export
Text within FileMaker Pro fields may be formatted with font type, font size, font color, and several different combinations of styles. Among the styles, you can specify a field to have, for example, Plain, Bold, or Italic, or Bold and Italic styles. This formatting can be set when you select a field in Layout mode and select Format, Text from the menu or Text Format using the contextual menu. Additionally, formats for the contents of a text field or individual words or phrases can be applied by using the Format menu, contextual menu, or Text Formatting toolbar. The text format of a field in Layout mode is the default style for that field. A field may have layout text formatting and different manual text formatting.
Text formatting data will not be retained upon export from FileMaker Pro. The data in each field is converted to plain text, except for the special characters noted at the beginning of this chapter. The XML export does not use the layout text formatting or any manual text formatting, even if you select the Format output using current layout option in the Specify Field Order for Export dialog.
2.26 Container Fields and Value Lists
Container fields are not exported with XML or any other export format. You may read more about web publishing images in container fields in the "Request for Image in a Container Field" section in Chapter 5. If you store a path to an image in a field, you may use that path or image name as a reference in the XML export to retrieve the image for later presentation. An image path may be any valid URI.
The ExportFM plug-in can extract your images and place them in a directory on your computer. The name of the image saved can be a field in the database and used as a reference for XML export. Information about ExportFM can be found at http://www.nmci.com/. Other plug-ins may also assist you in saving your images if they are already stored in a database file. See the FileMaker Inc. web site for a list of current plug-ins, http://www.filemaker.com/plugins/index.html/.
Fields may be formatted on a layout using value list options of Pop-up list, Pop-up menu, Check boxes, or Radio buttons. The format for these fields does not change the value of the field contents when XML export is used. Just the value of the field contents is returned with XML. You can read about value lists and XML web publishing in Chapter 5.
The values of a value list used on a layout may be obtained by using the design functions. The function ValueListNames (Status (CurrentFileName)) will return the names of all the value lists in the current database. You can get the values by using the design function ValueListItems (databaseName, valueListName). If you create calculations or script a Set Field[] calculation for any of the values, a field can contain the value list items. The items in the field will be return-delimited upon export as XML.
2.27 Global Fields, Calculated Fields, and Summary Fields
Any one record does not own global fields, so if you select a global field for export as XML, it will be used in every ROW (record). The size of your XML document can increase greatly when you export global fields with data. If you need to use a constant value in your presentation, consider setting these one time in a stylesheet. You will learn more about XSL variables, XSL parameters, and XML entities in Chapter 7.
Calculated fields in FileMaker Pro can result in text, number, date, time, or container output. Except for calculated fields of container type, all calculated fields would be exported as plain text.
Summary fields may also be exported as XML. See the FileMaker Pro Help topic "Exporting data from FileMaker Pro" for information about using the Summarize by option. If you sort the records by a field, that field may be used in a summary export. Or you may include the summary field with every record. The type of field and whether you presort before export may determine the value of any exported summary field.
2.28 Final Thoughts on XML Export with Filemaker Pro 6
Your XML export may produce unexpected results. Always test small sets of records in your databases for special characters in relationship names, field names, and the field contents. Perform an XML export with each of the two grammars, FMPXMLRESULT and FMPDSORESULT, to see if either format is preferable for your particular export. Consider the formatting for numbers, dates, and times if you use these types of fields in your XML export.
Text loses all font formatting, such as bold, when exported or web published as XML. Container fields cannot be exported, but references to the real location of images may be used if a field with this information is exported. Value lists are not used for XML export, but the contents of fields selected by value lists are exported. Only the first related repeating field value is exported. Consider exporting from a related database rather than the parent database if you need to include a related repeating field.
An XML export can faithfully return the characters entered into any field, regardless of layout formatting. Calculated fields and summary fields are exported as plain text. These types of fields might not need to be exported if you are presenting your data with XSLT. Many processors have functions to calculate and summarize for you. XML export of data in fields truly follows the XML design goals in Chapter 1, section 1.2, "XML Advantages."
|
|
EAN: 2147483647
Pages: 100