Tab to Leaders
By Stewart Lynch (kazar@datatude.net)
BONUS FILE: TABLEAD.FP3
PLATFORM: Macintosh & Windows
Have you ever had to include table data in an e-mail message only to realize that your formatting goes awry when your nicely formatted tab separated text does not line up? The best solution is to reformat the data into a monospaced font like Monaco or Courier and replace all of your tabs with spaces or some other kind of leader so your columns will all line up nicely. This is both, time consuming and difficult as you first have to determine the length of the longest data field so that you can ensure that your columns will be wide enough for the longest piece of data in the field.
For example, consider the tab-separated data below. It's not clear how this data lines up:
First Name Last Name Position
Jenny Smith President
Frank Jones Secretary
Frank Adams Treasurer
Aidan Lynch Membership Chairperson
Reformatting it in a monospaced font with spaces filling in for the tabs presents the data in a nice table format:
First Name Last Name Position
Jenny Smith President
Frank Jones Secretary
Frank Adams Treasurer
Aidan Lynch Membership Chairperson
Another example might be a price list:
Item Price
99 Escort ZX2 $18,983
99 GR Marquis $39,949
99 EXPL Sport $27,992
98 Ford F150 4X4 $22,829
This can be reformatted to look like this:
Item................Price
99 Escort ZX2.......$18,983
99 GR Marquis.......$39,949
99 EXPL Sport.......$27,992
98 Ford F150 4X4....$22,829
So What Will It Do For Me?
Before we delve into this solution, let's be sure we understand the problem we are facing. We've got a products database with hundreds of records in it. Or, in our example, just four but it will give you the idea. You've done the "right thing" as far as managing your business and separated data on each product into fields for easy reporting. The product name is in one field, the color of car in the next and the price in the last. You want to distribute this information to your clients via your e-mail address list but you know they will only read the message if the information comes in the body of the message and not as a file attachment. The data has to be displayed in a table format with all information being properly aligned.
With some work, we can write a calculation to convert all fields to fixed length fields and concatenate this data into a single field that is formatted in a mono spaced font.
So How's It Done?
Let me be the first to say that this is not an elegant solution. Brute force doesn't even begin to describe it. But, if you find yourself backed into a corner like the one described above, this technique just may be the quickest way to bail yourself out.
The key to this solution is using FileMaker Pro's text functions that concatenate the fields together with the correct number of spaces or other type of leader to make up the desired width of the fixed length field. You use a series of set field script steps to convert the data and assemble the converted data into a global field. And, finally, you copy the formatted data from the global field and paste it into your destination application. It ain't pretty but it works surprisingly well.
First, let's examine how we get the data converted. The Tab2Leader Converter is setup to process up to 9 fields of data. Once complete, the formatted data can be copied from the globalfinal field and pasted into another application.
When you import your data into the accompanying Bonus file, we use the Length(text) calculation function to determine the length of each field, then we use the summary Function Maximum to determine the maximum width required for each field. This will give us a starting point to determine the width that we wish to choose for each of the fields.
In our solution we set the field width for each field and the leader type (spaces, periods, dashes or underlines) to fill in the space replacing the tabs. Working backwards from the last data field, each record is then scanned using a "Convert Line" script that uses an If....then....else conditional step that checks to see if there is any data in the field. If there is, then the data is concatenated into a temporary global text field (gDataLine). Here is a portion of the "Convert Line" script (see the Bonus file titled "TABLEAD.FP3" for more details):
If ["Data9Length > 0"]
Set Field ["gDataLine", "*see below for calculation"]
Else
If ["Data8Length > 0"]
Set Field ["gDataLine", "*see below for calculation"]
Else
If ["Data7Length>0"]
etc...
Here is the calculation that is referenced in the first Set Field above (see the Bonus file titled "TABLEAD.FP3" for more details):
data1 & Left(gLeader1, FieldWidth1 - Length(data1)) & data2 & Left(gLeader2, FieldWidth2 - Length(data2)) & data3 & Left(gLeader3, FieldWidth3 - Length(data3)) & data4 & Left(gLeader4, FieldWidth4 - Length(data4)) & data5 & Left(gLeader5, FieldWidth5 - Length(data5)) & data6 & Left(gLeader6, FieldWidth6 - Length(data6)) & data7 & Left(gLeader7, FieldWidth7 - Length(data7)) & data8 & Left(gLeader8, FieldWidth8 - Length(data8)) & data9
By working backwards from the last field, you will ensure that all data is captured and if there is missing data within one of the middle fields, the fields will be replaced entirely with the chosen leader type.
But wait, there's more!
We use a looping sequence that steps through each record, Converting each line and placing it into the final global field.
Go to Record/Request/Page [First]
Loop
Perform Script [Sub-scripts] Convert Line
Exit Loop If Calculation
Length(globalfinal) + Length(gDataLine)> globalfinalsize
Set Field globalfinal
Calculation
globalfinal & gDataLine & "¶"
Go to Record/Request/Page [Next: Exit after last]
End Loop
The Devil's in the Details
If you take the time to explore the bonus file, you will see a little more to the setup than is described above. A number of features were added to make the Tab2Leader Converter easy to use so you don't have to build the monstrosity of a script and global field network into your own database. You can simply export the data and import it into the bonus file. There are some details you might want to be aware of if you use this solution often or intend to build it's elements into your own solution.
The final global field where the data finally ends up is formatted as Courier 10 point. This is a monospaced font that will display the data correctly.
A field in FileMaker Pro can hold at most 64,000 characters. For this reason, there is a fail safe built into the converter script which calculates whether processing the next record will put the globalfinal field over the 64,000 character limit. If so, the script exits without processing the next record.
Exit Loop If Calculation
Length(globalfinal) + Length(gDataLine)> globalfinalsize
When using the Tab2Leader Converter with large amounts of data, simply wait for the script to exit, copy the data from the globalfinal field, paste it into the destination application, and run the formatter script again. When asked if you want to start from the beginning or from the current record, choose the current record and it will clear the globalfinal field and pick up right where it left off. When experimenting with different formatting options and large amounts of data, it is convenient to have the script exit before it formats 64,000 characters. For this reason, a global field has been added where you can set the maximum number of characters. I often set this to the equivalent of 4-5 records while experimenting with different formats.
Not all programs accept formatted data from the Clipboard so you may have to select all of your pasted data in your target application and choose a mono spaced font.
A number of other features have been added to the converter script such as the ability to record how long it takes to convert a set of data and to speed up the converting by freezing the window. The converter script is significantly faster if the Freeze Window script step is used because the screen does not need to redraw in order for the solution to work. Even though each redraw does not take a long time, it is the accumulation of each redraw that quickly adds up. I prefer to watch the redraw when I am setting up the solution on a limited number of records and then freeze the window and turn it loose on all of the records I need. Enjoy the bonus file and may it save you many hours of tedious work.
## END ##