Fixed Width for EDI and Other Reporting

Editor’s note: Today it’s my pleasure to present a guest article written by Beverly Voth. Like many other developers, I have enjoyed and benefitted from her ongoing contributions to the FileMaker community.

I do a lot of text manipulation for EDI (Electronic data interchange – http://en.wikipedia.org/wiki/Electronic_data_interchange) and plain text exports with fixed-width field data. Some varieties of EDI use XML, but this article is about plain text. EDI may or may not use the fixed-width format. Fixed-width reports may or may not use delimiters and various “padding” characters.

I created two FileMaker custom functions to help me calculate fixed-width and EDI text for export, and if you wish, you can follow along in today’s demo file, Fixed Width EDI.

The first custom function is for text with optional width (string length), optional padding character(s) and optional delimiter character(s). Text padding is placed to the right of the original string and the string truncates if it is longer than the optional width. The other custom function is for numbers with optional width (string length), optional padding character(s) and optional delimiter character(s). Number padding is to the left of the original number and returns the padding character only if number length is more than the width. These custom functions may be used “as is” or revised to include other options you may need. Or perhaps you can use them and include other calculations to achieve that you need.

fn_fixed_width_text (str; width; pad_char; delimiter )

Example text calculations:

  1. fn_fixed_width_text ( field ; 20 ; ” ” ; “|” ) – this specifies: a WIDTH of 20 characters, a space for the PADDING and a pipe character (“|”) for the DELIMITER
  2. fn_fixed_width_text ( field ; 50 ; “.” ; “” ) – this specifies: a WIDTH of 50 characters, a dot (“.”) for the PADDING and no DELIMITER
  3. fn_fixed_width_text ( field ; “” ; ” ” ; “|” ) – this specifies: no WIDTH (leave length as is), a space for PADDING (with no width, there will be no padding) and a pipe DELIMITER

fn_fixed_width_number ( num; width; pad_char; delimiter )

Example number calculations:

  1. fn_fixed_width_number ( number ; 20 ; 0 ; “” ) – this specifies: a WIDTH of 20 characters, a zero for PADDING and no DELIMITER
  2. fn_fixed_width_number ( number ; 0 ; 0 ; “|” ) – this specifies; no WIDTH (leave as is), a zero for PADDING (with no width, there will be no padding) and a pipe DELIMITER

Examples

Note that I often find it necessary to clean up text before passing it to these functions. This only removes leading and trailing spaces and removes return characters. Since those two “data-entry errors” tend to break the most for fixed-width or EDI reports, I’ve concentrated on them. If you need to add other cleaning, do so before passing to the custom function.

I have also chosen to filter all numbers to just the digits “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8” or “9” and no other characters. If you need to retain other characters ($, comma “,”, decimal “.”, dash “-“, for example) within a number (which really is text!), you may revise the custom function. Keep in mind that fixed-width is quite different if you retain these characters! $000300.00 is not the same as 00030000, although they are both “fixed-width”, so adjust as needed in the custom function or use additional text functions to revise after passing through the custom function.

I’ve used a fixed-width font ( Monaco, in these examples) on the calculated fields in the layout to show the relative width (if any). Upon export, these will be fixed-width, if opened in a text editor or other processing application that displays a fixed-width font.

calculation field definitions

results (click image to enlarge)

Examples2

More Examples

given these fields and values:

first_name  “Bob”
last_name   “Johnson”
middle_name “”

“EDI” calculation:

= fn_fixed_width_text(“NME”;””;””;”|”)
& fn_fixed_width_text(first_name;””;””;”|”)
& fn_fixed_width_text(middle_name;””;””;”|”)
& fn_fixed_width_text(last_name;””;””;”~”)
// note the last character of the “line” is the tilde and may or may not be followed by a CR (carriage return)

“EDI” result:

NME|Bob||Johnson~

“fixed-width” calculation:

= fn_fixed_width_text(first_name;30;” “;””)
& fn_fixed_width_text(middle_name;1;” “;””)
& fn_fixed_width_text(last_name;30;” “;”¶”)
// note all text will be in a single string followed by a CR

“fixed-width” result:

Bob                            Johnson                     
Bob............................Johnson....................... 
          (note: space changed to dot to show the "width" is maintained)
Bob                           | |Johnson                       | 
          (note Pipe added to show the "width" is correct, even with empty fields)

Follow-up

These calculated values can be exported (as tab-delimited) if one field is the calculation per line or all “lines” are in one field (one record) using a script to populate all lines (loop the found set). Name the file appropriately and you have a fixed-width and/or EDI export! You may also push these values to Virtual List reports.

11 thoughts on “Fixed Width for EDI and Other Reporting

  1. Geoffrey Gerhard

    This is clever…

    Substitute ( 10^pad_len – 1 ; 9 ; pad_char )

    …for creating the pad-string without recursion and without hard coding endless character repetitions in Substitute’s text parameter. I wondered why you did this…

    10^pad_len – 1 ; 9

    …instead of this…

    10^pad_len ; 0

    …and discovered that FMP returns the result in scientific notation once the pad_len value exceeds 8, which makes the additional arithmetic operation unavoidable.

    Thanks for sharing this!

    Reply
  2. Rewolfer

    Hi,
    I had written a similiar function about 2 years ago. It works with “_direction” instead of “delimiter” to expand to the left as well as to the right but doesn’t truncate.
    Just wanted to hint to its limitation: those solutions have an upper limit of about 400 (depending on the input). To make the custom function bullet proof it should check the limit first and switch to recursion if above.

    Thanks for your enlightening blogs,
    Rewolfer.

    /*=============================================================

    Text_Format_PadWithChar ( _text ; _charToPadWith ; _padToLengthOf ; _direction )

    PURPOSE: pads a text with the supplied char to a certain length to the left or right

    PARAMETER: _text text input text (or number)
    _charToPadWith text single char to be used for padding
    _padToLengthOf number length of desired final text
    _direction text 2/r/right or 1/l/left (default)

    RESULT: padded text / number

    DEPENDENCY: –

    EXAMPLE: Text_Format_PadWithChar( 0,993; “0”; 7; “r”) –> 0,99300

    =============================================================*/

    Let (
    [
    _textLength = Length( _text );
    _padLength = _padToLengthOf – _textLength ;
    _pad = (10 ^ _padToLengthOf) – 1;
    _pad = Substitute ( _pad ; [ “9” ; _charToPadWith ])
    ];

    Case (
    _textLength >= _padToLengthOf ; _text ;
    Lower( _direction ) = “r” or Lower( _direction ) = “right” or _direction = 2; _text & Left( _pad ; _padLength );
    Left( _pad ; _padLength ) & _text
    )
    )

    Reply
    1. Beverly Voth

      Absolutely, Rewolfer, direction IS one of those parameters that could be added. And the “total length” of a concatenated string (many padded fields) is also one of those requirements that can be in fixed-width and/or EDI text output – I have another function that tests that.

      I get specs (usually in spreadsheet) that are very precise with the start and length of each element in these kinds of documents. I can see importing the spreadsheet and using scripts and calculations to do most of the work. You simply need to “map” your fields to theirs!
      Thanks for sharing,
      Beverly

      Reply
  3. Bruce Robertson

    Note that there is a max length of 400 for pad_length when done using the exponent method. Probably sufficient in most cases!

    Reply
    1. Beverly Voth

      Thanks, Bruce. You’re right, probably sufficient. If you have something that needs to be padded out past 400, you have other problems!
      Beverly

      Reply
  4. Bernard Cheang

    One of the problems I am constantly facing is that when the file is saved as a text file, there always seem to be an extra carriage return at the end of the file that must be removed manually. Does anyone have any suggestions about how that can be done automatically?

    Reply
    1. Beverly Voth

      I’ve replied to Bernard privately, asking for more details. We’ll get back to you with answers, if any. Using the Tab-delimited export gives you this:
      —–
      Field TAB field TAB field CR
      Field TAB field TAB field CR
      Field TAB field TAB field CR
      —–
      If you are trying to remove the final CR, then there are options, but not natively to FMP. I’d adore a more robust Export (and Import) dialog for setting delimiters, wouldn’t you? Along with that, how about specifying the UTF? :)

      Reply
      1. Beverly Voth

        After followup with Bernard directly, it was determined that:
        * the tab-delimited export of one record, one field was producing this and the extra CR was the problem
        data CR
        data CR
        CR
        * the Character Set for the export was ASCII (DOS) which may or may not have been a problem
        * the final solution was to use Export Field Contents.

        Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s