Importing data into archivable templates

[Kirsten]

Good day, all.

I am trying to find a better solution for a communication archive I am building. The purpose is to archive all letter and email communications for an educational program. These documents are created using templates. The project is to save each communication so they can be tracked over time.

Currently I have three files, Contact, Communication Template, and Archive. Once the desired record or records are selected in the Contact ID (these are the people the letter will be sent to), a nav button/script jumps the user to the Communication Template where they can chose the type of Communication they wish Each Communication is a different layout in the Template file. If any changes need to be made to the template, they are done at this point. Then the contact information for either the current record or the entire group of records selected in the Contact ID file is imported. The criteria for finding the appropriate records can be complex, so I don't believe a lookup would be viable. The resulting group of new records in the Communications Template is then printed or emailed.

What I need to do is keep a record of what was sent. My clumsy solution so far is to create a calculation field that combines all the data in the body of the letter into one field. Then this field, along with each client(s)'s ID and the signator of the letter are exported into the Archive File. All the records in the Communications Template are then deleted, so the entire process can start again.

My question is, is there a cleaner way to do this? There will be thousands of communications each year, so the Archive file will increase in size quickly. Since the client contact information and the templates will both change over time, I need a way to freeze the data. I'm hoping to find something that does not take up so much space though!

Any suggestions are most welcome.

Thanks very much.

Kirsten Masse

[Vaughan]

I have built two systems that do this. It's complicated. Very complicated. I recently quoted $18,000 and 6 weeks time to do a third system. They declined but it's not worth me doing it for cheaper.

There are two ways of doing it: one is to save the text of the letter in a text field. Easy as long as your letters are simple in structure but gets difficult if the letters become complex -- say using portals or other related data that may change over time. Saving the related info can be done but then it's hard getting it into a format for reprinting that's identicle to the original letter. Takes up a modest amount of disk space so it can handle lots and lots of letters.

The second method is to save page previews of each page. This is done by changing into Preview mode, issuing a Copy command (no field specified) then pasting into a container field. The advantage is that the letters are saved independently of any templates or related records. (Tip: print the originals from the saved page images.) The disadvantage is complexity of coding and disk consumption: each "page" is about 200 KB, so printing thousands of letters a year you'll soon hit the 2 GB file size limitation. The size of this file also makes backing up a problem -- it won't fit onto Zip or Jazz disks or CD-ROM any more.

Finally: FMP 7 is much, much better for this letter template stuff. Both character and paragraph formatting can be applied in templates and are easily transferred around with calculations. In FMP 6 only character formatting is portable and only then if Copy and Paste are used to move the template text around and and Replace is used to merge the fields. The file limit is terrabytes, not gigabytes too.

[Kirsten]

Thanks, Vaughan.

After spending months reading on this forum, I had reassessed myself as an FMP novice, but I now don't feel so bad about hitting a wall with this project.

I think I will go with the text option, the first you described. Any related data I need I should be able to add into the calculation by careful use of lookups. Once the data is exported or copied into the Archive file, it will be fixed.

As for the layout in that file, I may have to develop a script that parses each sentance into 70 character lines. Ick! I have not found a way to make use of either the format commands or the size tool via script.

One question: Do you know the character limit on calculation fields?

Thanks, again.

Sincerely,

Kirsten Masse

[Fenton]

I would agree with Vaughan for the most part. I believe it could be done for less time and cost. But I suppose it partly depends on the complexity of the documents, which can only be decided after seeing what they have; and how much your time is worth.

If you can do this in version 7 you will save quite a bit of work. Because 7 preserves much text formatting when text is run through the calculation engine (6 strips all formatting from such; though there are (tricky) ways to use AppleScript on Macs to preserve it).

I've done a fair amount of this in version 6, and, yes, it's a PITA. However, if the information is handled intelligently you can save a tremendous amount of disk space in storing it.

For example, as you say, much of this communication is done from "templates." If all of the text in the letter is basically just the template, with additional "merged" data from the records, then there's no real need to save the text for each. You have the template, you have the data; all you need is to record the occurrence, and the ties between them.

There are two main types of templates. The usual kind are "layout based" (i.e., separate layouts). It is also possible to have templates that are "records-based," i.e., a table of records, each with a different template in a large text field, either plain, or with custom merge fields (user-placed, then replaced with data from records).

In version 7 the latter becomes far easier, because formatting and calculations can be combined. In 6 you could get most of the formatting, from one file to another, via a lookup; but further calculations would strip it.

Layout-based has its own problems, in that a layout is not a record, hence has no record ID. It is however possible to include the "layout templates" as records in the records-based table, hence giving them the status of records, with the possibility of recording their ID, going to them when necessary to print, tying them to a certain mailing, etc.. There a ways to present layouts in portals, using LayoutNames, then choose from them; though it requires the users to follow some guidelines to create new ones. In 7 you can give them layout access without full access.

The only time you need to store the entire "letter" is when someone actually edits a template when writing a letter, adding their own words. In that case, you can flip to another table or layout, allow them to write away, then save the whole thing (with a lookup or import).

All the different types of letters can be presented as choices in a portal(s), even though they may be quite different things (layouts or records). Once a choice is made, then you go where you have to go, depending on the choice, for either editing or printing.

The "non-edited" letters are only saved as IDs and date; no text needed. The edited ones are saved with the full text. In other words, these are two completely different types of letters. If you can separate bulk letters from custom letters you have a much more efficient storage mechanism. As Vaughan says, storing the entire thing of everything is impractical.

[Fenton]

I believe the character limit on calculation fields is 30,000 (version 6 anyway). That's what I remember. There is also limit on nested calculations, which is I think 170? It will gracefully tell you (i.e., it won't crash or freeze).

I don't see why the 70-character lines? You don't want to make this any more complicated than it has to be.

[Kirsten]

Dear Fenton:

Thanks for the feedback.

I have tried both the Template-as-a-record (TAR) and the Template-as-a-layout (TAL) option. They both have challenges.

First option: TAR

The benefits:

The TAR is wonderful for making changes to templates. The user finds the template they want by a search, and either uses it as is, or uses the duplicate record command to make a copy and edits it as appropriate. Old templates can be noted with a "historic" flag and removed from the list of templates available with a search script. The templates do change frequently, so ease of use for novice users is critical.

The challenge:

Using merge fields with a record, not a layout. I have not been successful in adding merge fields or any kind of data field into a specific record. Copy As commands have not worked for me either, nor have calculation fields because they work on layouts not records. My layouts were identical in each TAR, (Contact fields, 6 large text blocks, and a signature field). I just can't seem to incorporate any data into them from another source.

Second Option: TAL

The benfits:

The TAL is great for combining data from different sources. Text and data can be combined using calculation fields, which can then be exported.

The challenge:

There is no record ID possible for a different layout, so the layout's actual text has to be exported. It seems there is no way to export just a Template ID, unless some scripting wizardry is possible to do this. It is also more complex for a user to create a new layout, as it must be done in the layout mode. I have also not been able to determine how to set which layouts are active and which are historic, so that only the active ones are shown as options.

If there are any challenges above that can be solved, please let me know! I am planning to use the TAL option, despite its limitations.

Sincerely,

Kirsten Masse

[Vaughan]

I was referring to templates as records. This system allows users complete freedom to add, delete and modify letter templates to their heart's content without having to learn any new skills other than basic word processing.

[Kirsten]

Vaughan,

How would a user be able to add merge fields in the "templates as records" model you have suggested? I have sentances that include project specific data including dates, hours, and remuneration. As a layout, I can insert the appropriate data as merge fields. If each template is a record, how could I add the looked up data?

I tried the Copy, Paste and Replace Fenton described, and it works if I have control over the naming of the titles in the replacment text placeholders, and can create a script to go through one record at a time replacing each placeholder with the appropriate field data. This won't work well for the client's users, though, because each new placeholder would require a new script to find it and replace it with the matching field.

I thought I needed the 70 character parse because copy and pasting data into a text layout changes the size of the layout...it extends to the longest sentance, and has to be resized. I double checked, and it seems to happen only for copying and pasting data into a layout, not into a record field. So, no parse necessary.

Still struggling, and thanking for info so far,

Kirsten Masse

[Kirsten]

bump....

How would a user be able to add merge fields in the "templates as records" model you have suggested?

Kirsten Masse