Sometimes I get obsessive about solving problems with my Mac G4, or my Windows PC. When I come up with a solution I want to share it since, in most cases, I have already Googled and could not find one. If you have found useful information here, drop me a line or post a comment and let me know.

Monday, July 05, 2004

Mac or PC: Filemaker Pro

Converting Data To Use in a Filemaker Pro Calculation Field

Used to convert data for making it easier for me to calulate the correct HTML information to embed Quicktime videos, RealOne (RealMedia, RealPlayer) Videos and/or Windows Media Player videos into my web pages.

My problem: I had no idea how to embed videos in a Web page and get them to display on another page in a controlable player. Once I found samples of web pages that did what I wanted, I examined their source to try to understand it better.

I realized that there were many variables one needs to set and change, depending on the Media (RealMedia, Quicktime, or Windows Media Player).

Knowing I would eventually want to be able to add many little clips to my site, I decided to create a Filemaker database into which I could enter a mimimum of unique data, and have Filemaker offer me a field full of data, which if I simply copied all data in that field, and then pasted that into the right place in a new HTML document, would create a properly formatted <object>, depending on the Media.

So here's what I did:

I copied the entire "object/embed" item from a working web page to paste into a Filemaker Pro calulation field, with the intention of replacing all the variables with field-names.

But Filemaker doesn't like HTML code... it gets frustrated with the quote-marks, and so on... so I had to determine a method of converting a large block of text to a format that would not confuse Filemaker's Calculation Field editor.

Here are my steps.

I assume you have a text editor that will allow you to Find and Replace, and more importantly that you can replace invisible characters such as Carriage returns and the like.

Note: whenever I refer to a "quote" or quotemark, I am referring to the double tickmark as seen "here" — not the single one seen 'here'

In a Text Editor, Paste the Code you want to convert into a new document then, in this order:

(Make sure "Smart Quotes" or "Publisher's Quotes" is turned off in your preferences)

1. replace all variable data with
for finding later. (The underscore character stands for "Space character")

2. Replace all
(That's two "Double-quotes" in a row, no spaces)

3. Put a quote mark at the beginning of line 1 and at the end of the document, too.

4. Replace
(be sure to end it with a quote, which will place a quote mark at the beginning of the next line. The [cr] represents the invisible carriage return character. The ¶ is the actual ascii character 182 that Filemaker likes to see between quote marks to force a new line in that calculation's output.

5. Replace all
(The difference is, there are now Quotes on either end.)

6. Copy all resulting text and Paste into Filemaker Calulation field.

7. Before attempting to close, replace all
with the variable you want in each instance.

You should now have a successfully formatted Filemaker Calculation, with variables where you want them.

To make this field easy to COPY and PASTE, in Layout Mode, right-click that field and choose Field Format and check the box that says something about selecting the entire conents of the field. In Browse Mode, all you have to do, now, is click it and execute a copy command.