Fixed-Length Field Exporting

FileMaker users often have to exchange data with other database systems. Some older systems, especially governmental systems, still use fixed-length records. A fixed length record is one in which each field has been allotted a certain number of characters, and the combination of all fields in a record always results in a record that is a fixed number of characters long.

For example, suppose that a payment database has a First Name field that requires 10 characters, a Last Name field that requires 15 characters, and a Payment Amount field that requires 8 characters. Typically, number fields that hold currency values do not permit decimal characters. In this case, if a payment amount is $123.43, the properly formatted number value is 00012343. Notice that any blank spaces need to be padded with zeros, and notice also that currency symbols, commas, or decimal characters are not included. The system that receives this record will know that it needs to divide the number value by 100. Just as number values are typically padded with zeros, so text values are typically padded with spaces. The first name of "Gilbert" would use the characters of the name "Gilbert" and then follow that with three space characters. Text values are usually followed by the padding character, whereas number values are usually preceded by the padding character.

As you saw in the last section, FileMaker doesn't have an export file format specifically for fixed-length exporting. That's not a problem, though, because you can use a calculation field to construct a fixed-length record export like this. Given the fields previously mentioned, the calculation to create a fixed length export would be

 Left (First Name & "          "; 10) & Left (Last Name & "               "; 15) & Right (  "00000000" & Filter (Payment Amount; "0123456789"); 8) 

The basic concept is simple. For a text field of a given length, say 10 characters, you take the field value and append 10 spaces to the end of it. You then take the left 10 characters of that to get a string that contains the field value and the correct number of spaces. The process is reversed for numbers . Zeros are added to the left of the number, and then the rightmost characters are used. These field strings are used as building blocks to construct a full record. You can create a single calculation field (let's call it Fixed Length Export) that uses this calculation and returns a text result, as shown in Figure 20.5.

Figure 20.5. You can construct a fixed-length export record by concatenating and trimming various fields together to form one long field.

To create a fixed-length export file, you need to find the records you want to export, and then export just this one field in a tab-delimited format. Because you're exporting only one field, no tabs are inserted into the file; the carriage return delimiter that's used in the tab export format (and others) is the same delimiter typically used to set fixed length records apart from each other.

FileMaker also ships with an XSLT stylesheet that can produce fixed-length output with all columns the same length. This stylesheet is easy to modify and use for columns of differing (fixed) lengths as well. The stylesheet is part of the XML Examples.