Shop for FileMaker software

FileMaker Services

Submit your paper

About

Contact

FileMaker Papers home

FileMaker Papers home

Featured articles:

Loops for Prime Numbers
Looped Scripts
Branding, Buttons, Brain
Fun with Functions
PDF Files and FileMaker
FileMaker Security
FileMaker's Reputation
FileMaker Data Separation












Shop for more FileMaker software by clicking here...

FileMaker Papers home

FileMaker Services

Find FileMaker software!











Shop for more FileMaker software by clicking here...



Looped scripts (and the complete HTML color chart)
by Pam Rotella
April/May 2012


Note: To see the full HTML color chart (more than SIXTEEN MILLION colors and codes, generated with FileMaker software), click here. To see how FileMaker software was used to generate the chart, read on...

It's tax season, and so this month's article will be fast and simple -- an example of loops in FileMaker scripting.




A good project for loops
While trying to locate an HTML color code recently, I encountered the reason for this "fun" sample project. I noticed that "full" HTML color charts don't seem to exist online, or at least aren't easily found through a simple Google search. Then I calculated the number of possible HTML color codes, and the reason became apparent.

There are six positions in an HTML color code, and sixteen possible numbers or letters (0 through 9 and A through F -- a hexadecimal number) for each position. The total number of possible combinations would be 16 to the 6th power, multiplied this way:

16 X 16 X 16 X 16 X 16 X 16 = 16,777,216

A script could be written to create all 16+ million codes automatically, but the number of html pages to hold so many codes would be in the thousands. For example, if 5,000 codes with a sample swatch were included on a single web page (with the number 5,000 being selected to avoid making the page too large in size for people to load easily with their web browsers), then sixteen million divided by five thousand would be 3,200 pages. That's too big of a project for the number of codes that most people normally need, and so typical HTML color code charts limit themselves to a much smaller number -- usually the "safe" colors for older monitors that could only display 256 colors. Few monitors with the 256 color limitation are online today, however.

Modern monitors can display millions of colors, and so many more HTML color codes can be utilized on web sites. Some online color code charts use a widget to calculate color codes that are entered manually by the viewer, or "rolled over" by the cursor on a small chart of color continuums.

Yet charts so small fall short when a web designer needs an exact color match to a photo, artwork, company logo, or even an idea. Time spent searching page after page with roughly the same color charts could have been better spent looking through a genuinely complete HTML color code chart, or running a sampled swatch through good graphic arts software.

Realistically, it takes a computer program to generate so many codes. And so, for the sake of a sample project, that program will be FileMaker.

The first step in developing a looped database solution is to determine whether scripted loops are the best way to handle a project. Typically loops are faster than creating or changing database records manually, but for some projects they can be slower and less efficient than other database methods. Performing an import, using a calculated "Replace" command, creating custom functions, and adding calculated fields are normally faster ways to add or change data than a scripted loop.

In this case, data for such a chart must be created -- and plenty of it -- rather than imported or manipulated after the fact. Loops are better suited for the task than any of the alternative methods just mentioned, primarily due to the quantity of material.


Some basic HTML color pointers
FileMaker Pro is a relatively inexpensive yet powerful database known for rapid development. The product is popular in companies with both large and small budgets, and smaller budgets usually mean that the database developer also serves as a business analyst. Specifications must be gathered by the developer and the developer must understand the project's goals before creating a product that meets those goals.

"Understanding the project" in this case means understanding or at least learning a few web design basics. While advanced complexities of color theory are better left to the graphic arts trade, developers creating a color computation tool should at least understand a few basics of color theory.

HTML color codes are numbers based on the RGB, or red-green-blue, color mode. RGB is the color mode used by televisions and computer monitors, whereas CMYK (cyan-magenta-yellow-black) is typically used for printed materials.

Mixing component colors will generate almost every color that the artist needs, in theory. With RGB color, the first two digits represent red, the next two green, and the last two blue, sometimes notated as RRGGBB. The highest value or most available color is F, whereas the lowest value (supposedly no color) is 0. And so white is blended from the highest level of all three colors, FFFFFF. Black is the absence of all color, 000000.

This is exactly the opposite of CMYK color, where white is the absence of all color, or the blank white page, and theoretically all colors blended together yield black, although black ink or toner is usually added as its own color in a separate cartridge to ensure the "right" shade of black. One of my past employers was a garment manufacturer, and so I understand that there are different shades of black. But color printers are very capable of making black from other colors -- take the black cartridge out of your printer, and the printer will probably be willing to print black for you, from blending the colors in its color cartidge. That's when you'll probably see a brownish shade of black, which is good enough for most printed reports.

Typically web sites use white or FFFFFF as the background color ("bgcolor") and black as the text color ("FONT COLOR") to make the page easy to read, but color can make a page more interesting.

FFE419 FFE491
Are the two digits for each color interchangeable? In some cases, when colors have a very gradual progression, they appear to be the same, but in fact the answer is no. For example, two colors from the second page of the full HTML color chart -- FFE419 and FFE491 -- are shown to the right. They're not the same color, are they?

Theoretically, the top value, or FF, of each color position (with the lowest value, or 00, in the other color positions) will display a "pure" color:

FF0000 (red) FFFF00 (yellow) 00FF00 (green) FF00FF (purple) 0000FF (blue)


Pretty ugly, huh? I doubt that many web designers would want these "pure" colors for the text or background colors on their web sites. Maybe these colors would work for flowers or neon signs, or the blue for large text on a web site, but there are easier shades of blue to read.

Notice that yellow is obtained through combining red and green -- something that you may remember from science class in grade school. Most of us are more familiar with the color system used in painting, normally learned in early childhood and used throughout our lifetimes, with its "primary colors" red, blue, and yellow. In painting, yellow and blue are mixed to make green. In the RGB color mode used by televisions and monitors, however, a screen beaming colored light will combine red and green light to make yellow light. It's a slightly different color concept.

Often web designers use lighter shades for background colors (usually whiite, FFFFFF), and darker colors for text (usually black, 000000), but some web sites have color themes with dark backgrounds and light or white text. When selecting colors for web design, the most important consideration is whether the page is easily read. Sometimes pure white on a dark background is too much contrast, and a light shade of gray, cream, or something from the background color's group may be easier on the eye as a text or font color. When using a white or pastel background, color text is sometimes hard to read, and so the font must be adjusted to a larger size or colors other than black reserved for headings only. Also, medium shades as background colors can make text difficult to read, and may be more suitable for border areas or pages where photos or graphics are the main feature.


Creating HTML codes with a database
To start the database project, a system of creating the 6-digit HTML color codes must be developed. Fields holding the full codes or their component digits should be text fields, because HTML color codes use letters A through F in addition to numbers 0 through 9. Moving each digit -- each text field -- up or down would be accomplished most efficiently with a custom function (File / Manage / Custom Functions), using a case statement that covers all 16 possible moves. The function could then be used as needed in scripts and formulas, rather than repeating its case statement when decreasing each digit of the color code.

This is the custom function I call "down" (using a single parameter named "field"):

Upper(Case ( Lower(field)="f";"e";
Lower(field)="e";"d";
Lower(field)="d";"c";
Lower(field)="c";"b";
Lower(field)="b";"a";
Lower(field)="a";"9";
field="9";"8";
field="8";"7";
field="7";"6";
field="6";"5";
field="5";"4";
field="4";"3";
field="3";"2";
field="2";"1";
field="1";"0";
field="0";"f";
"error"))

The function "Upper" will produce uppercase letters, a feature I selected because uppercase letters are easier to read in the final color chart. "Lower" ensures that the part of this formula evaluating the original digit isn't case sensitive. (I don't mean to create confusion by using both a "Case" statement -- a FileMaker function roughly equivalent to a glorified "If" statement -- and uppercase and lowercase letter functions, but the case of letters must be mentioned here.) The "error" result at the end of the case statement will appear if the original digit didn't fall within the parameters, for example if a letter other than A through F were used, or if the starting value was blank.

Before creating the first script to move the entire 6-digit code down, start and end points for code creation must be specified. Including start and end codes within the script is one possibility, but that would require adjusting a script manually for each batch of codes (and this project is large enough to utilize batch processing). I'd prefer fields on a screen where numbers are easily entered or modified for each run. Global text fields are a good choice for entering codes where calculations should start and stop. (A tip for FileMaker programming novices: "Global Fields" have the same value across all records, and in later versions of FileMaker the "Global" choice is found in field storage options. Before variables and parameters were available in FileMaker, Global Fields were frequently used in their place. Global fields are also great for holding temporary parameters on user entry screens, for example the start and end dates for a print report. There are a few things to learn about Global Fields before using them, for example the value is unique to each user in multi-user files, and the default on file opening is the previous host's last value before the file was last closed.)

Remembering that these global fields should be TEXT, I'll name them z_Position1, z_Position2, through z_Position6. (The "z_" is a convention that I normally use to indicate a global field -- more on naming conventions in future articles).

For the end numbers (where the script to create color codes stops, e.g. "000000" for the final code), I'll name six new global text fields z_EndPosition1, z_EndPosition2, through z_EndPosition6.

Next, the field to combine six individual digits into a six-digit number, in this case a global calculated field (also text), which I'll call z_ColorCode_calc with the simple concatenated formula:

z_Position1 & z_Position2 & z_Position3 & z_Position4 & z_Position5 & z_Position6

An alternative to the above model would be the creation of one field each for current and ending color code numbers. However, the one field model would have required needlessly complex formulas to reference each digit's position within the overall numbers. The one field model could use custom functions to simplify references to each digit's position, but the separate field model handles position references without the extra steps.

Now for a field to hold the actual color codes... or not. Storing each code by itself would mean creating one database record per code, and frankly, I don't want 16 million records sitting around in a database for a one-time project. I'd rather have one database record per color code table, or one record per HTML web page. If the individual codes are needed later, they can be parsed from the tables, or generated as needed, but this is only a sample project for this month's article, and I currently have no plans to use so many HTML color codes again.


Automating hexadecimal color code changes
Before I can write the first loop, I'll need a basic script to decrease the six digit number by one with each pass. For this script, the six individual hexadecimal numbers must work together -- when one digit reaches 0, the next time the script is run, it should not only roll over to "F" but also move the digit to the left down by one.

This script will be used by a looped script later in the project. And yes, you can set a global field to a calculation based on its existing value, and so I don't need more than the original six global fields to write this script -- not even a variable.

Note that in the sample script below, "HTMLPageGeneration" is the table name for the global fields that start with "z_". Also, "down" is the name of the Custom Function that appears earlier in this article:

If [HTMLPageGeneration::z_Position6="0"]
If [HTMLPageGeneration::z_Position5="0"]
If [HTMLPageGeneration::z_Position4="0"]
If [HTMLPageGeneration::z_Position3="0"]
If [HTMLPageGeneration::z_Position2="0"]
Set Field [HTMLPageGeneration::z_Position1;down(HTMLPageGeneration::z_Position1)]
EndIf
Set Field [HTMLPageGeneration::z_Position2;down(HTMLPageGeneration::z_Position2)]
EndIf
Set Field [HTMLPageGeneration::z_Position3;down(HTMLPageGeneration::z_Position3)]
EndIf
Set Field [HTMLPageGeneration::z_Position4;down(HTMLPageGeneration::z_Position4)]
EndIf
Set Field [HTMLPageGeneration::z_Position5;down(HTMLPageGeneration::z_Position5)]
EndIf
Set Field [HTMLPageGeneration::z_Position6;down(HTMLPageGeneration::z_Position6)]

This script is easily tested by placing all six global fields that it references onto a layout, in order from left to right, and then creating a button to run this script. Aside from moving the sixth position down by one with each button click, a field should decrease by one when the field to its right is zero and rolls over to "F". Placing a "0" in the various fields to verify that this script moves the field to their left down by one is a good test, although everything depends on the sixth position and so everything to the right of the field to be tested must also be "zero". In addition, the combination "000000" should roll over to "FFFFFF".


Script notation
Note that when I post scripts here, I format them with the same notation used by FileMaker's Script Manager in its script step list display box. That's why square brackets -- [] -- and semicolons appear in the script steps above.

Sometimes this notation indicates whether script step options are selected or not, for example with "Enter Browse Mode []" in the scripts to follow, the bracket is empty. That means I didn't select the option "Pause," which would have appeared in the brackets had the Pause box been checked.

Understanding this notation is very important in the looped scripts to follow, where the option "Select entire contents" was NOT checked for the step "Insert Calculated Result," meaning the word "Select" didn't appear within the brackets. Had that box been checked, the loop generating HTML code below would have overwritten the target field with each pass rather than compiling its calculations into HTML color code tables.


Designing a model product
Before I can program a script to build a product, I need to decide what that product will be. And so I manually create, through copying and pasting, a small test table to help visualize certain layout elements, e.g. the potential width of table cells, the font size, and the maximum number of legible codes in each table row:

FFFFFF FFFFFE FFFFFD FFFFFC FFFFFB FFFFFA FFFFF9
FFFFF8 FFFFF7 FFFFF6 FFFFF5 FFFFF4 FFFFF3 FFFFF2
FFFFF1 FFFFF0 FFFFEF FFFFEE FFFFED FFFFEC FFFFEB
FFFFEA FFFFE9 FFFFE8 FFFFE7 FFFFE6 FFFFE5 FFFFE4
FFFFE3 FFFFE2 FFFFE1 FFFFE0 FFFFDF FFFFDE FFFFDD


As a cream-to-yellowish theme starts to emerge, it appears that seven color codes per line will allow for a small color swatch and a font size of two for upper case lettering. I have the choice of specifying seven cells per row in the script, or creating another global field to allow a variable number of cells per row to be entered. Either way, the script loop should create a new row after every seventh color code, inserting HTML codes for table cells and rows with each pass. It should also stop when the end of each table is reached, which depends on the number of rows specified for the table. Perhaps the script should also insert the starting and ending HTML table codes.

HTML table codes can be specified in the FileMaker script, or fields provided where table codes are entered each time or stored. The reason I have the luxury of choice in this case is that this is a small project, for myself and my audience of database developers with script editing skills. If this were a project regularly used by people without top database skills, almost nothing would be hard-coded into the scripts. For groups of medium-skilled database users, I might set up profiles for people to select, and store HTML table design choices in regular (not global) fields in a separate data table, perhaps even set up defaults based on user accounts. But this article is a limited sample of looped scripts, and so I won't be covering every aspect of user interface development, which could easily turn this and other tasks into week- or month-long projects under certain circumstances.

Because the database scripting here is primarily for database programmers, I'll mix hard-coded HTML table codes with numeric global fields for a few table parameters, in order to demonstrate both techniques.

For my FileMaker-generated HTML color code chart, I'll include several "blocks" or tables of color codes on each page, with 100 rows per table. The number of "blocks" (tables) of color codes that I can include on a page will depend on the web page file's overall size. I don't want megabyte-sized pages because nobody would wait for them to load in a web browser, but a half-megabyte file, or even a little larger, is near the upper limit of acceptable.

Numeric table parameter fields (all global number fields), for use by scripts, were created as follows:

z_Count_CellsPerRow
z_Count_RowsPerBlock
z_Count_BlocksPerPage


The following global calculation field was created to show the total number of codes potentially included on each page, according to numeric parameters entered in the global fields above. This is for display purposes, to save time by calculating the number automatically whenever the table parameters are changed:

Field Name:
z_TotalColorCodesPerPage_calc

Calculation (must be a number):
z_Count_CellsPerRow * z_Count_RowsPerBlock * z_Count_BlocksPerPage

These numeric limits will provide information to the looped scripts on when to break to the next line, the next table, and the next database record.


Building HTML tables with a loop
The FileMaker scripting to build a page of tables could be several loops nested together, all in one script, or several scripts, with the main script calling the subscripts as needed. Because one of the purposes of this web site is teaching, I think it's best to keep the samples relatively simple. Therefore, I'll separate the scripts so that each contains only one loop, with other looped scripts called as subscripts when needed. There are benefits to separating scripts and their tasks -- the modular format is more flexible, with easier testing, data cleanup, and feature additions, omissions, or modifications.

Before creating a script to make a simple table of HTML codes, a field is needed where the code can be written. In this case, I'll use the field name "PageCode", and the field won't be global but rather a text field. That way, the tables created by this script can be stored until copied and pasted into their respective HTML files. It's probably a good idea to turn visual spell checking off for this field (an option on the Data tab of the Inspector, in Layout mode), as checking HTML codes for spelling is a waste of processing power. In a project this large, every bit of processing efficiency counts.

I also created a page number field, "WebPageNumber", to help reference both the FileMaker record and its destined HTML file, and of course an auto-generated serial numeric ID field to identify each record uniquely.

There are other options for where to write the tables -- probably the best choice for a project like this would be to use a FileMaker plugin like TROI, and allow the plugin to create the text (HTML) file, writing the code directly to each file, which can then be uploaded with FTP software. This would also allow FileMaker to write the entire document from start to finish, if desired. However, this is an article on looped scripting and not plugins or external file management. Subjects as large as installing and using plugins can't be included here, and I'd rather write this article so that anyone with access to FileMaker database software can create HTML tables without the purchase of an additional program. And so, for this project, the instructions describe a script that writes to a text field created by FileMaker. What to do with that field once written is a separate decision.

Using the new field "PageCode," the script (called "CreateColorCodeTableBlocks_Loop") to create a simple table of HTML color codes, with the parameters and fields/functions/subscripts mentioned above, is as follows (the "loop" commands are bolded):

Enter Browse Mode []
Set Variable [$$CellNum; Value:1]
Set Variable [$$RowNum; Value:1]
Insert Text [HTMLPageGeneration::PageCode;"<TABLE width="600" align="center" BORDER=1 FRAME=BOX>" ]

Loop
If [$$CellNum=1]
Insert Text [HTMLPageGeneration::PageCode;"<TR>"]
End If

Insert Calculated Result [HTMLPageGeneration::PageCode; "<TD WIDTH="& Quote (45)&" bgcolor=#" &
HTMLPageGeneration::z_ColorCode_calc &
"></TD>¶
<TD WIDTH="&Quote(40)&"><FONT SIZE=2>" &
HTMLPageGeneration::z_ColorCode_calc &
"</TD>¶"]

Set Variable [$$CellNum; Value:$$CellNum + 1]
If [$$CellNum > HTMLPageGeneration::z_Count_CellsPerRow]
Insert Text [HTMLPageGeneration::PageCode;"</TR>"]
Set Variable [$$RowNum; Value:$$RowNum + 1]
Set Variable [$$CellNum; Value:1]
End If

Exit Loop If [($$RowNum > HTMLPageGeneration::z_Count_RowsPerBlock)
or
(HTMLPageGeneration::z_Position1 = HTMLPageGeneration::z_EndPosition1 and
HTMLPageGeneration::z_Position2 = HTMLPageGeneration::z_EndPosition2 and
HTMLPageGeneration::z_Position3 = HTMLPageGeneration::z_EndPosition3 and
HTMLPageGeneration::z_Position4 = HTMLPageGeneration::z_EndPosition4 and
HTMLPageGeneration::z_Position5 = HTMLPageGeneration::z_EndPosition5 and
HTMLPageGeneration::z_Position6 = HTMLPageGeneration::z_EndPosition6)]

Perform Script ["DecreaseNumberByOne"]

End Loop

If [$$CellNum > 1
and
(HTMLPageGeneration::z_Position1 = HTMLPageGeneration::z_EndPosition1 and
HTMLPageGeneration::z_Position2 = HTMLPageGeneration::z_EndPosition2 and
HTMLPageGeneration::z_Position3 = HTMLPageGeneration::z_EndPosition3 and
HTMLPageGeneration::z_Position4 = HTMLPageGeneration::z_EndPosition4 and
HTMLPageGeneration::z_Position5 = HTMLPageGeneration::z_EndPosition5 and
HTMLPageGeneration::z_Position6 = HTMLPageGeneration::z_EndPosition6)]

Insert Text [HTMLPageGeneration::PageCode;"</TR>"]
End If

Insert Calculated Result [HTMLPageGeneration::PageCode;"</TABLE><br><br>¶¶"



Using the script above, setting the global fields for cells per table at 7, and rows per table at 25, a single table is created as follows:

FFF9FD FFF9FC FFF9FB FFF9FA FFF9F9 FFF9F8 FFF9F7
FFF9F6 FFF9F5 FFF9F4 FFF9F3 FFF9F2 FFF9F1 FFF9F0
FFF9EF FFF9EE FFF9ED FFF9EC FFF9EB FFF9EA FFF9E9
FFF9E8 FFF9E7 FFF9E6 FFF9E5 FFF9E4 FFF9E3 FFF9E2
FFF9E1 FFF9E0 FFF9DF FFF9DE FFF9DD FFF9DC FFF9DB
FFF9DA FFF9D9 FFF9D8 FFF9D7 FFF9D6 FFF9D5 FFF9D4
FFF9D3 FFF9D2 FFF9D1 FFF9D0 FFF9CF FFF9CE FFF9CD
FFF9CC FFF9CB FFF9CA FFF9C9 FFF9C8 FFF9C7 FFF9C6
FFF9C5 FFF9C4 FFF9C3 FFF9C2 FFF9C1 FFF9C0 FFF9BF
FFF9BE FFF9BD FFF9BC FFF9BB FFF9BA FFF9B9 FFF9B8
FFF9B7 FFF9B6 FFF9B5 FFF9B4 FFF9B3 FFF9B2 FFF9B1
FFF9B0 FFF9AF FFF9AE FFF9AD FFF9AC FFF9AB FFF9AA
FFF9A9 FFF9A8 FFF9A7 FFF9A6 FFF9A5 FFF9A4 FFF9A3
FFF9A2 FFF9A1 FFF9A0 FFF99F FFF99E FFF99D FFF99C
FFF99B FFF99A FFF999 FFF998 FFF997 FFF996 FFF995
FFF994 FFF993 FFF992 FFF991 FFF990 FFF98F FFF98E
FFF98D FFF98C FFF98B FFF98A FFF989 FFF988 FFF987
FFF986 FFF985 FFF984 FFF983 FFF982 FFF981 FFF980
FFF97F FFF97E FFF97D FFF97C FFF97B FFF97A FFF979
FFF978 FFF977 FFF976 FFF975 FFF974 FFF973 FFF972
FFF971 FFF970 FFF96F FFF96E FFF96D FFF96C FFF96B
FFF96A FFF969 FFF968 FFF967 FFF966 FFF965 FFF964
FFF963 FFF962 FFF961 FFF960 FFF95F FFF95E FFF95D
FFF95C FFF95B FFF95A FFF959 FFF958 FFF957 FFF956
FFF955 FFF954 FFF953 FFF952 FFF951 FFF950 FFF94F


Note that in the above sample, the code includes bgcolor=#FFFFFF and <FONT COLOR=#000000> for the white cells with black code number lettering that people can copy and paste. I discovered that I needed to reduce the number of characters in each table, in order to fit as many codes as possible onto an HTML page of 500 Kb or more. (No matter how large each file, they can't be big enough to take a bite out of 16+ million codes.) And so I tested the code to see what could be omitted, and the white blocks' font color and background color were fine with typical page settings; however I did need to keep the code that forced the font size to two. Because of the size limitations in FileMaker text fields, this also allowed me to increase the number of tables (7 columns X 100 rows with the code generated by the sample script above) to five instead of four. And so I can generate up to five tables in each field, and then copy and paste all five into an HTML document at once. (For the first twenty pages of the "full HTML color chart" linked at the beginning and conclusion of this article, I used this method, copying and pasting two blocks of five 7x100 tables into each HTML file, for an overall file size of over 500 Kb.) To bring a project this large to completion, another method like plugin-generated files would be a better option, but 7,000 codes generated with a few minutes of FileMaker compilation and a couple of copy and pastes -- the economy of scale is still pretty wonderful.


Creating multiple tables automatically
Of course I didn't generate several tables in the same field manually -- I used another looped script to do that. I named the new script "CreateMultiBlockPages_Loop" and it uses a global number field "z_Count_BlocksPerPage" to determine how many tables to put into the field (or document, if writing externally). It's probably a good idea to make a note on the layout where the field's value is entered of the limitations, e.g. 5 tables per field for the 7x100 table, or 10 tables when using a plugin to write to an external file whose size should be in the 500-600 Kb range.

The script's steps are as follows:

Set Variable [$$BlockNum; Value: 1]
Enter Browse Mode []
Loop
Perform Script ["CreateColorCodeTableBlocks_Loop"]
Set Variable [$$BlockNum; Value: $$BlockNum + 1]

Exit Loop If [($$BlockNum>HTMLPageGeneration::z_Count_BlocksPerPage)
or
(HTMLPageGeneration::z_Position1 = HTMLPageGeneration::z_EndPosition1 and
HTMLPageGeneration::z_Position2 = HTMLPageGeneration::z_EndPosition2 and
HTMLPageGeneration::z_Position3 = HTMLPageGeneration::z_EndPosition3 and
HTMLPageGeneration::z_Position4 = HTMLPageGeneration::z_EndPosition4 and
HTMLPageGeneration::z_Position5 = HTMLPageGeneration::z_EndPosition5 and
HTMLPageGeneration::z_Position6 = HTMLPageGeneration::z_EndPosition6)

End Loop



Creating multiple records or web pages
So far, the loops here have stayed on the same database records. Most people would think of loops as routines that are run on a record, and then moving to another record and performing the same routines, and so on, until some parameter is reached and the loop is stopped. While that isn't true of all looped scripting, the method can be of use here. For a project this large, the programmer doesn't want to initiate a looped script, wait a few minutes, and then manually start the next one. It's more efficient to specify an end point and let FileMaker create the next record, and the next, until the number of records specified is reached. In fact, most of the color code tables to follow were made by FileMaker while I slept, using that method.

There are additional variables to consider for automatically-created records. How many records will have the same page number on the final HTML documents? When should FileMaker stop creating records? In the script below, I created a field to inform FileMaker of the last page number to create, but I didn't include the multiple records per page number feature, simply because that code may make the overall script too hard for less advanced users to understand, when it should be easy to create basic record creation script.

This script does have rules -- it numbers the first page created with a numeric value of one greater than the record that was active when the script was run. It will also stop after it creates the page number specified in the new global number field "z_StopPageNumbersAuto" (or after it creates the first record, if the number in that field is actually lower than the current page numbers).

I called this script "CreateAllPages_Loop":

Enter Browse Mode []
Set Variable [$$PageNum;Value:If(Get(FoundCount)=0;0;HTMLPageGeneration::WebPageNumber)]

Loop
New Record/Request
Set Field [HTMLPageGeneration::WebPageNumber;$$PageNum + 1]
Perform Script ["CreateMultiBlockPages_Loop"
Set Variable [$$PageNum;Value:HTMLPageGeneration::WebPageNumber]

Exit Loop If [HTMLPageGeneration::WebPageNumber ≥ HTMLPageGeneration::z_StopPageNumbersAuto]

End Loop



Loops for Other Details: Page Numbering Choices
Scripting options for creating HTML tables within FileMaker can be refined in many ways. Automation is usually a matter of choice and budgeting. The final example of automation deals with page numbering. The smaller script to allow multiple records per page number is launched after the fact of record creation. (Even if numbering is managed by more steps within the main page creation script, it's good to have a smaller script like this to correct any page numbering errors after the fact.) Notice that the first line is a comment. When time and budgets are limited, and programming is meant for the programmer or advanced users only, often there isn't time for a nice screen to guide users through the process. Even so, it's good to remind yourself of the rules for running each script. In this case, the script should be launched on the first record to be renumbered, and that record should have the correct page number. Also notice that a global field has been created to allow the user to specify how many records per page number:

#Start on the first record of page number that needs next record numbered the same.
Set Variable [$$PageNum; Value:HTMLPageGeneration::WebPageNumber]
Set Variable [$$CountRecords; Value:1]

Loop
Go to Record/Request/Page [Next; Exit after last]
Set Variable [$$CountRecords; Value:$$CountRecords + 1]
If [HTMLPageGeneration::z_NumberDBRecordsPerPage < $$CountRecords]
Set Variable [$$CountRecords; Value:1]
Set Variable [$$PageNum; Value:$$PageNum + 1]
End If
Set Field [HTMLPageGeneration::WebPageNumber; $$PageNum]

End Loop


Of course, including page numbering steps in the main script is an option as well, but it comes with a warning from me. Don't make your main script too complex to be versatile. Once you add various details and options to your main script, you're locked in. It's often good to have scaled back scripts apart from an all-in-one script for purposes of testing, and varying or omitting different options later as needed. With that said, this is the actual script I used to create the Full HTML color chart. (I also used other formulas to create the page navigation and other details that varied by page, but that's another topic.):

#Start with last good page number -- the one preceding the next page number to create -- or a found set of no records to start page number with a "1."
Enter Browse Mode []
Set Variable [$$CountRecords; Value:1]
Set Variable [$$PageNum; Value:If(Get ( FoundCount )=0;1;HTMLPageGeneration::WebPageNumber + 1)]

Loop
New Record/Request
Set Field [HTMLPageGeneration::WebPageNumber; $$PageNum]
Perform Script [CreateMultiBlockPages_Loop]

Exit Loop If [HTMLPageGeneration::WebPageNumber ≥ HTMLPageGeneration::z_StopPageNumbersAuto and
(IsEmpty(HTMLPageGeneration::z_NumberDBRecordsPerPage) or
HTMLPageGeneration::z_NumberDBRecordsPerPage=1 or
HTMLPageGeneration::z_NumberDBRecordsPerPage ≤ $$CountRecords)]
or
(HTMLPageGeneration::z_Position1 = HTMLPageGeneration::z_EndPosition1 and
HTMLPageGeneration::z_Position2 = HTMLPageGeneration::z_EndPosition2 and
HTMLPageGeneration::z_Position3 = HTMLPageGeneration::z_EndPosition3 and
HTMLPageGeneration::z_Position4 = HTMLPageGeneration::z_EndPosition4 and
HTMLPageGeneration::z_Position5 = HTMLPageGeneration::z_EndPosition5 and
HTMLPageGeneration::z_Position6 = HTMLPageGeneration::z_EndPosition6)

If [IsEmpty(HTMLPageGeneration::z_NumberDBRecordsPerPage) or HTMLPageGeneration::z_NumberDBRecordsPerPage=1]

Set Variable [$$PageNum; Value:$$PageNum + 1]
Else If [HTMLPageGeneration::z_NumberDBRecordsPerPage ≤ $$CountRecords]
Set Variable [$$CountRecords; Value:1]
Set Variable [$$PageNum; Value:$$PageNum + 1]
Else If [HTMLPageGeneration::z_NumberDBRecordsPerPage > $$CountRecords]
Set Variable [$$CountRecords; Value:$$CountRecords + 1]
End If

End Looop



Practical Applications and Limitations
Modifying this programming to generate other HTML tables with FileMaker is easy. In fact, creating an HTML table of multiple data fields within FileMaker is usually less complex than the sample project above. Rather than special considerations such decreasing a hexadecimal (and therefore text) number, or generating the background color of table cells, the main formula could be as easy as a one-line calculation concatenating several fields with simple HTML table tags, for example (with sample fields named FIELD1, FIELD2, etc.):

"<TR><TD>"&FIELD1&"</TD><TD>"&FIELD2&"</TD><TD>"&FIELD3&"</TD><TD>"&FIELD4&"</TD></TR>"

Of course, every project has its own special needs. For example, HTML doesn't recognize text paragraph returns, and so hard returns within fields that need to be retained may require a Substitute command to swap text returns with HTML break or paragraph symbols. There could be other field formatting considerations, but usually the increased efficiency of automation is worth the effort.



Troubleshooting
When I first started the complete HTML color chart, I intended to compile a few pages as a sample project and move on. But it was easy -- and fun -- to start the script again as I had time, and produce a few more pages, and then a few more, until the project was complete. I especially liked to run the script all night long and wake up with an abundance of pages to add to the chart.

There was one vulnerability to the method of stopping and starting frequently, however -- user error. The stop and end points were usually perfect, but once I caught myself making a mistake with the starting code for the next block of pages, and wondered if I'd done that before without catching it. But then I thought I wouldn't worry about it, because if I made a few errors it would give me an opportunity to cover troubleshooting.

As it turns out, my troubleshooting formulas tell me there were four errors, and here's how I found them...

After the last HTML color code was generated (000000 for this chart), I calculated the number of total codes vs. pages, tables, rows, and cells as an error check. There were 7,000 codes on each page, and 7 X 100 or 700 codes per table for the partial page at the end of the chart.

This math will theoretically tell us if the target number was reached:

Complete pages: 2,396
Multiplied by 7,000: 16,772,000

Number of full tables on remaining page: 7
Multiplied by 700: 4,900

In remaining table, number of complete rows: 44
Multiplied by 7: 308

Plus six additional codes on remaining line, for a total of:

16,772,000 + 4,900 + 308 + 6 = 16,777,214

The target number is 16,777,216, and so this would seem to indicate that I'd made two errors. However, further investigation showed that there were in fact four errors, two offsetting each other. Namely, there was a gap of one code between pages 18, 385, and 400 and the pages immediately following them, and then in the middle of page 153, a code was repeated twice.

The errors bring up a number of design issues, even with a looped project as simple as HTML table code compilation. A calculated comparison feature could have been a part of the original design to catch errors and stop the loop if an error was found, although in real working environments sometimes budgets and time are short, and many desirable features are excluded from the final design.

The errors also call into question a design allowing human input while restarting the script after an interruption. Despite extensive spot-checking seeming to prove that the scripts compiled perfect table code, human input at any point meant the possibility of operator error. At first, errors seemed unlikely if the operator was careful, and the "careful employee" model may have worked if the project had remained a few pages for a FileMaker loop sample. But test a product in real production, and every possible problem becomes reality. In this case, production involved hundreds of stops and starts, often in rushed circumstances. Also, each page took minutes to create on my old laptop, and so deleting hundreds or thousands of pages and starting the project again wasn't an option. The article had to be finished, and the decision to complete the chart had already extended the article into the following month of May.

If the project had been more than a programming sample, however, corrections at the end of web pages may have been unacceptable.

*** NOTE: I will complete the troubleshooting section of this article soon. I was interrupted by a very busy project and other commitments, but will finish the last few details shortly, probably within the next few days. - PR, 12/26/2012***


To start the full HTML color code chart -- more than 16 million HTML codes -- click here.








- Pam Rotella is a Senior FileMaker Developer with over 20 years of I.T./PC database programming experience, over 15 of those years working with FileMaker. She frequently consults with major US corporations and medium-sized companies, and can be reached via e-mail at filemakertech@gmail.com.

All original content © 2012 by Pam Rotella or other authors as cited.

DISCLAIMER: This site is not affiliated with FileMaker software company, but rather seeks to provide a forum for credible discussion on software use. Tips provided here are not intended as individual consultation or advice. All papers published here are the opinions of their respective authors, and are not necessarily endorsed by FileMakerPapers.com or Pam Rotella.