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:
- 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
- fn_fixed_width_text ( field ; 50 ; “.” ; “” ) – this specifies: a WIDTH of 50 characters, a dot (“.”) for the PADDING and no DELIMITER
- 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:
- fn_fixed_width_number ( number ; 20 ; 0 ; “” ) – this specifies: a WIDTH of 20 characters, a zero for PADDING and no DELIMITER
- 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)
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.
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!
Once again my mind is expanded… thanks Bev and Geoff.
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
)
)
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
Note that there is a max length of 400 for pad_length when done using the exponent method. Probably sufficient in most cases!
Thanks, Bruce. You’re right, probably sufficient. If you have something that needs to be padded out past 400, you have other problems!
Beverly
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?
Bernard, what format are you using for Export?
I am just using the tab-delimited text format.
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? :)
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.
Hi Beverly
I am compiling a BACS form exporter for manual line import and your fixed number function is excellent for the sort code and account number but i cannot seem to find out how to get the decimal point which is in the original field as the function seems to strip this out?
Can you help??
Sure! there can be variations on this. A quick answer is to separate the integer from the decimal (if any). Apply the Custom Functions on the parts you need.
GIVEN: the number is 23.345
Let ([ i = Int(fixed_width_custom_fun::number)
; d = GetValue (Substitute(fixed_width_custom_fun::number; "." ; Char(13) ) ; 2 )
]
; fn_fixed_width_number ( i ; 8 ; 0 ; "" ) &
If( i ≠ fixed_width_custom_fun::number ; "." & d )
) // handles the decimal portion (any length)
00000024.345
If you need padding 0’s before the integer (i) and padding 0’s after (so the decimal is ‘fixed’):
Let ([ i = Int(fixed_width_custom_fun::number)
; d = GetValue (Substitute(fixed_width_custom_fun::number; "." ; Char(13) ) ; 2 )
; dp = 4 // decimals are 4
]
; fn_fixed_width_number ( i ; 8 ; 0 ; "" ) &
If( i ≠ fixed_width_custom_fun::number ; "." &
fn_fixed_width_text ( d ; dp ; 0 ; "" ) )
) // uses both Custom Functions to pad before and after each part of the number
00000024.3450
And there can be different ways to do this. It all gets a little complex if “decimal” is not the “.” and whether the decimal has a fixed number of ‘digits’. There may be other custom functions that work differently. The article is to demonstrate custom functions and how they can be used for Fixed-Width. Read the other comments for other suggestions!
Thanks so much, will try these today, the output is exactly right thanks for posting this really helpful