Recently in Tips and Tricks Category

Be careful when you update records by way of an import. There is no undo so make sure you have a good backup. Here's how the two features work:

Update Existing Records in Found Set

Let's say you have ten records in your database. You just performed a search so now there are six records in your current found set. You have an import file that also contains six records. Under this option, the values in the imported records will replace the values in the existing records in position order. IOW, the first record in your import file will update the first record in your found set; the second record in your import file will update the second record in your found set; etc, etc, until you reach the end.

If you have more records in your import file than you do in your found set, use the checkbox "add remaining data as new records" to instruct the database whether to add or disregard them.

I would not recommend using this option unless you can be absolutely sure the order of the records in both found set and import file are identical. This is difficult to guarantee, what if your import file is sorted in a way you can't recall or recreate in your database?

Update Matching Records in Found Set

If you have records in your import file that do not have a match in your found set, use the checkbox "add remaining data as new records" to instruct the database whether to add or disregard them.

Under Both Features

• Only the records in your found set will be effected by the import.
• Only the fields you map will be effected by the import.
• If a field in your found set contains a value while the imported field is empty, the resulting field will be empty.

$nameFind =& $fm->newFindCommand('web_NameSearch');
--one of the chunks of code below--
$nameFindresult = $nameFind->execute();

All names in table.

Simple search

$nameFind->addFindCriterion('First', 'rob');

Exact matching

$nameFind->addFindCriterion('First', '=rob');

Change the context to OR

$nameFind->addFindCriterion('First', 'james');
$nameFind->addFindCriterion('Last', 'james');
$nameFind->setLogicalOperator(FILEMAKER_FIND_OR);

Use an operator

$nameFind->addFindCriterion('Age', '>=44');

We recently upgraded (or downgraded, I don't know which) the version of PHP running on our web server. As a result, all my in_array tests broke. I use in_array on new and edit pages. They're dormant when a user first lands on the page. They're invoked when the user submits the form and fails one of the validations on the response page. A failure kicks them back to the form where targeted messages indicate what they did wrong. Read Error Trapping for the full explanation of this technique.

PHP didn't like testing against an array that was not declared. This breaks:

if(in_array("MissingFirstName", $problem))

This fixed it:

if(isset($problem) && in_array("MissingFirstName", $problem))

I used a very cool custom function to calculate due dates that avoid weekends and holidays. I added our upcoming forlough days to the list of holidays to avoid them too. In my solution, the auto-enter value for the due timestamp is calculated like this:

Let([
startDate = rd_Creation;
numDays = Case (
crt_UrgencyShort = "Urgent"; 1;
crt_UrgencyShort = "High"; 2;
crt_UrgencyShort = "Medium"; 5;
crt_UrgencyShort = "Low"; 28;
crt_UrgencyShort = "Long"; 183;
0);
loopCount = 0;
holidayList = Global::gt_HolidayList;
allowWeekends = "";
direction = ""
];

calcEndDate ( startDate ; numDays ; loopCount ; holidayList ; allowWeekends ; 
direction ) & " " & Time ( Hour(rm_Creation) ; Minute(rm_Creation) ; 
Seconds(rm_Creation) )
)

Holiday list is defined in a global field as:

9/6/2010
11/25/2010
11/26/2010
12/24/2010
12/27/2010
12/28/2010
12/29/2010
12/30/2010
12/31/2010
1/17/2011
3/18/2011
5/30/2011
7/4/2011

FileMaker played a pivotal role in a recent web clean-up project. It's more than a database, it's a powerful data scrubbing tool.

The Problem

• We have a sprawling web property with ~26,000 items, ~3,000 of which are unique html files. We don't know what all we have or who "owns" it.
• For years the site was managed in Dreamweaver and Contribute and as a result, the code is loaded with in-line styling like this:

   <p align="center" class="style1" style="margin-top: 0; margin-bottom: 0">

  In-line styles prevent us from extending content to other areas like print or mobile.
• The code is not semantic. There are no heading tags, tables are used to position elements, and blockquotes are used to indent text. As a result, our site does not meet accessibility requirements. It may look okay on the web, but if you rely on a screen reader our site is one, long, run-on sentence.

The Objective

• Audit. Make an accounting of all content. Assign owners. Determine whether content should be updated or removed.
• Scrub it. Remove all in-line styling.
• Mark it up. Apply proper tags.
• Edit it. Have content providers (which is every member of our department) edit their content according to a style guide prepared by the Communications Team.

Step 1: Learn How To Do It Right

At the University, there's no excuse for not knowing how to do things correctly. The UTTC offers several web training courses free to staff and students. I recommend the following:

• Web Accessibility Issues and Techniques
• Web Development: Essentials - XHTML
• Web Development: Essentials - Cascading Style Sheets
• Web Development: University Template Fundamentals
• Web Development: Working Effectively in the University Environment

Step 2: Get a List of Your Site Structure

Create a "site" in Dreamweaver where the local folder is a new one you create for this purpose and the remote folder is your live site. Make a connection and "GET" your entire site. For large sites, this may take a while. When you're done you have a local copy of your live site.

Use the default settings in Print Window to walk through the folder hierarchy and output a text file with every item listed line by line.

Step 3: Import It Into FileMaker

Download WebCleaner, a FileMaker database I created. Import the text file, mapping only the file name and file type into the rt_FileNameImport and rt_FileTypeImport fields. Click the button to "Backfill File Path" and "Construct Full URL."

Delete records you don't need. I deleted all folders, all image files, pdfs, movies, etc. — basically anything that was not a complete page with content.

Step 4: Scrub It In FileMaker

You'll need the 360Works Patterns plug-in to perform a search and replace using regular expression. Many thanks to this site for helping me with regular expression. Here's what the database will do for you:

• Use the complete URL to screen-scrape raw html from the live page.
• Calculate the portion that is main content, ignoring header, nav bar and footer.
• Strip inline styles. This step requires the plug-in. Edit the script to to clean as much or as little as you want.
• Perform another screen-scrape, this time pulling just text from the main content area in order to calculate a word count.
• Validate against a list of common misspellings and awkward phrases.
• Help you assign owners, audience and action for each page.
• Flag complex pages — those with tables, javascript, internal navigation, images and blockquotes.

Step 5: Publish It With FileMaker

Now we get content owners involved. I built a custom web site using FileMaker's API for PHP. It sits behind the Central Authentication Hub so I can display a list of assigned pages based on the user's log-in.

We asked content owners to edit content and apply tags. We trained them how to tag properly and gave them a limited tool bar to work with. The tool bar is courtesy of CKEditor.

Conclusion

Now we have clean content in a searchable database. Our next step is to move to a content management system, probably Drupal.

It's nice to show users record counts (found count, total number of records in the database, current record they're browsing), but if you're hiding the status bar for other reasons, it means creating separate calculated fields for each value. This can bulk up your system and be a pain to create in every single table.

Here's a trick from forum subscriber Marc Wood at the National Scholastic Press Association. Paste the following code into the Web Address field of a new Web Viewer.

Substitute (
"data:text/html,
<html>
<body style='background-color:white;margin:0;border:none'>
<div align='center'>
<span style='font-family:Lucida Grande;font-size:11px;color:black'>
### of @@@ (%%% total)
</div>
</body>
</html>" ;
["###" ; Get (RecordNumber)];
["@@@" ; Get (FoundCount)];
["%%%" ; Get (TotalRecordCount)]
)

Deselect the checkboxes at the bottom of the setup window.

Position the web viewer on your layout. You can move it, resize it and even make the surrounding box disappear by changing its line width to 0 pt.

Notice how fast it is as you click through records. Perhaps the best part is you can copy and paste this web viewer into any file and it will work! No need to alter the setup from one database to the next. Thanks Marc!