R – How does one import XML files with repeating fields into a FileMaker database

filemakerxml

I'm using FileMaker Pro 9 and I want to take a database with repeating records (e.g. one field is "Lines" and it can have up to 9 strings) and import data into it from an XML file.

Right now, with the following XML file, I only get the first entry imported ("Room"):

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
    <ERRORCODE>0</ERRORCODE>
    <PRODUCT NAME="" VERSION="" BUILD=""/>
    <DATABASE NAME="New Invoice" RECORDS="1" DATEFORMAT="M/d/yyyy" TIMEFORMAT="" LAYOUT="hh/mm/a"/>
    <METADATA>
        <FIELD NAME="Description Index" TYPE="TEXT" EMPTYOK="NO" MAXREPEAT="9"/>
    </METADATA>
    <RESULTSET FOUND="1">
        <ROW RECORDID="" MODID="">
            <COL>
                <DATA>Room</DATA>
                <DATA>Equipment</DATA>
                <DATA>Labor</DATA>
            </COL>
         </ROW>
    </RESULTSET>
</FMPXMLRESULT>

How can I get the rest of the entries ("Equipment" and "Labor") imported?

Best Answer

You can import and export repeating fields (arrays) in Filemaker, but not from an XML format.

I just figured it out. You can export and import repeating fields from Filemaker as long as you use their merge (.mer) format, or comma separated values (CSV), or another plain text delimited format. Otherwise, it will only give you the first of the repetitions in the field. XML isn't an option for this. For this example, I'll use a csv file.

First, in order to import repeating fields correctly, you have to understand a few things...

  1. When Filemaker exports or imports repeating field data, it uses a special hidden character to separate the repeating items. The character is ASCII-29, but you can't just type that in like you can other characters. On a Mac or PC, the key is ctr+] (hold down control and the right square bracket).

  2. This is best handled in a text editor that can show invisible characters, such as Coda or Text Wrangler. In Coda, I turned on "Show Invisible Characters" because unless you are in a text editor with that turned on, you won't see anything happen, as it inserts the character behind the text and there aren't any spaces between the repeating items.

  3. In a CSV, the fields are contained in quotes and separated by a comma. Records are separated by a carriage return (the enter or return key). Field headers are separated by a comma, but are not contained in quotes.

Here is an example in CSV format. I'll designate the hidden character with the number 29 in brackets ([29]):

Box Number,Contents

"1","apples[29]oranges[29]bananas"

"2","cod[29]tuna fish[29]salmon[29]trout"

"3","My Little Pony[29]He-Man"

Box 1 had apples, oranges and bananas.

Box 2 had cod, tuna fish, salmon and trout.

Box 3 had My Little Pony and He-Man.

And there you have it. Filemaker recognizes it as repeating fields. It would be worth exporting some repeating fields from Filemaker first and taking a look at the file from within a text editor (with hidden characters showing), just to get a visual of what you will be trying to accomplish.

Related Topic