Picture 18.png

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

This is the preferred method of updating records with an import. For this to work, your import file has a field with a unique identifier that corresponds to a field in your database with the same unique identifier. When you select this option you must map the ID fields then click again until the arrow is bi-directional. Under this method, the order of your records is irrelevant. The imported data will hunt down and update its matching record.

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.

How To Archive A Database

Read, "Why Archive Databases?" to consider why you should archive obsolete databases. The following is my proposal for archiving files. Use the comment section to weigh in.

While the file Is still hosted by FileMaker Server, open it as a guest and select File > Manage > Security...

  1. Make sure there's an account assigned the [Full Access] privilege set that is authenticated via FileMaker.
    archive_03.jpg
  2. Create an account assigned the [Read-Only Access] privilege set that is authenticated via FileMaker.
    archive_05.jpg
  3. Create an account assigned the [Data Entry Only] privilege set that is authenticated via FileMaker.
    archive_06.jpg
  4. Remove all other accounts and make sure Guest is deselected.
    archive_04.jpg
  5. Turn off extended privileges for ODBC, PHP, IWP etc. Just fmapp on the three default privilege sets.
    archive_02.jpg
  6. Save your changes to Security settings and close the file.

On the computer running FileMaker Server...

  1. Launch FileMaker Server Admin Console and close the file.
    archive_07.jpg
  2. In the Finder, navigate to the folder where the file is stored in Library > FileMaker Server > Data > Databases. Move it to your archive location. Make sure the file is deleted from the Databases folder!

On your computer...

  1. Navigate to the archive location and open the file with FileMaker Pro. Use the [Full Access] credentials you created above.
  2. In File > Sharing > FileMaker Network... turn off Network Sharing, set access to "No users" and deselect "Don't display in Open Remote File dialog.
    archive_08.jpg
  3. Optional. In File > Manage > External Data Sources... clean up file references.*
    archive_11.jpg
  4. Close the file and test the [Read-Only Access] and [Data Entry Only] accounts.

Wrap Up

  1. Place a README file in the archive location documenting archive date, data owners and timeline for destruction.
  2. Communicate with data owners. Let them know the file has been archived. Review the steps for accessing it if needed. Get their approval on a timeline for destruction.

* This was an issue for us because files had been around for decades and referenced external files that no longer existed or were once at different IP addresses. Bad external file references translate into a spinning beach ball when opening the file.

Why Archive Databases?

So much of our energy goes into creating new databases, but what to do with the old? You don't want to trash it because it retains some value.
  • It may store records that were not imported into the new solution.
  • You want to keep it as a safety net in the event something catastrophic goes wrong with the new solution.
  • You're required to keep it in accordance with your organization's data retention policy.
  • You want to preserve it as an artifact of how things used to be.
  • You suspect you may need to cannibalize it for reports or techniques.
This article isn't about data retention or backup strategies. For that follow the University's policy for data retention and destruction and FileMaker's best practice guide for backups. This article proposes how to take a database offline, yet keep it secure and accessible if you need it.

Why Take It Offline?

Why take it offline if it's not doing harm and there's room on the server?

It's Good For Your Users So long as it's accessible, there is potential to become entangled with active files. It may show up in the Open Remote dialog box or appear in users' Favorites or Recent lists. Other developers may link to it not knowing it's been replaced. Perhaps it's accessible through back doors like ODBC or IWP. In any case, a user could open it and either be alarmed at seeing old data or worse, start using it again.

It's Good For Your Server Removing retired files reduces the load on your server and creates headspace for the great stuff you're about to create. Also, consider auxillary files and routines in place to support a surplus database. Are there Active Directory groups created solely to support it? Is it bloating your daily backups?

It's Good For You As a developer and administrator, you should clear the decks of inactive files so you can focus on supporting vital ones. Archive the file now while you still recall the master password and the purpose the file served.

Objectives

Here's what we want to accomplish by archiving files.

  • Remove files from FileMaker Server so they cannot be accidentally accessed by users.
  • Move them onto a volume that gets backed.
  • Retain the security of the data, adhereing to the rules around private data.
  • Make sure you can open the file if needed.
  • Schedule the file for destruction according to University policy.
  • Make sure data owners understand the change.

Next read, "How To Archive A Database."

Fun With Finds

Here are several search strategies you might need. For the following examples, the basic search is:
$nameFind =& $fm->newFindCommand('web_NameSearch');
--one of the chunks of code below--
$nameFindresult = $nameFind->execute();

All names in table.

Jack Robb 36
Jacob Schmidt 44
James Lee 37
Jim Nelson 36
Rob Sanchez 44
Robert James 50
Robbin Williams 49

Simple search

$nameFind->addFindCriterion('First', 'rob');
Rob Sanchez
Robert James
Robbin Williams

Exact matching

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

Change the context to OR

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

Use an operator

$nameFind->addFindCriterion('Age', '>=44');
Jacob Schmidt 44
Rob Sanchez 44
Robert James 50
Robbin Williams 49

PHP Problem and Solution

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))

Send Email Using PHP

Here's how you can construct an email and send it from your PHP page rather than write a script to run in FileMaker. Some things to note:

  • Your web server must be configured to allow sending emails. Talk to your server admin about that.
  • $emailAddress can be a single email address or several email addresses separated by commas.
  • From and Reply-To are not parameters but rather optional values tossed in the $headers parameter. CC and BCC go here as well. Here's a better example.
  • Create line breaks with \n.
$to = $emailAddress;
$subject = "Ticket ".$record->getField('_kprt_Ticket').": ".$record->
getField('rt_Title');
$message = "Do not reply to this e-mail. Track your ticket online: \n";
$message = $message."https://gpssecure.umn.edu/support/ticket.php?id=".$record->
getField('_kprt_Ticket')."\n\n";
$message = $message."We received your support ticket.";
$headers = "From: gpstech@umn.edu\n";
$fifth = "gpstech@umn.edu";

mail( $to, $subject, $message, $headers, $fifth );

In our environment, the mail command will not work without a fifth parameter which is an email address. Unfortunately, this address gets a copy of every email sent using this routine. Plan accordingly.

Display Value Lists in Neat Columns

Do you have a long list of radio buttons or checkboxes to present to your users? If you separate items with a paragraph, the list maybe too long and narrow. If you leave them as an unbroken list, it's difficult to tell which label is associated with which checkbox. Sometimes the label slips onto the next line. It's messy. Neither meet standards for good usability.

Below is the code to turn this:

cluttered.jpg

Into this:

neat.jpg

<p>
<label>Type</label>
	<div style="float: left; width:250px; padding-right: 20px;">
<?php 
$typeRep = 0;
$item = 0;
$itemsInColumn = intval((count($typeValues) / 2));
foreach($typeValues as $value){ 
$item = $item + 1; $typeRep = $typeRep + 1;
if($item > $itemsInColumn) { $item = 1; 
?>
	</div>
	<div style="float: left; width:250px; padding-right: 20px;">
<?php } ?>
<input name="type" type="checkbox" value="<?php echo $value; ?>" 
id="<?php echo $value; ?>">
<label for="<?php echo $value; ?>" class="radio-checkbox-label">
 <?php echo $value; ?></label><br />
<?php } ?>
	</div>
</p>
<input name="typeRep" type="hidden" value="<?php echo $typeRep; ?>"/>
<br clear="all">

How It Works

You need a little knowledge of CSS and HTML to pull this off. My labels run long so I determined that two columns best fits my overall design. Also, the value list is dynamic. Users may add or remove values so I want it to grow and shrink in proportion.

Open the section with a div that floats to the left with a little padding on the right. This will contain the first chunk of values.

Before opening the foreach loop, set the variable $item to zero. In this two-column solution, calculate how many $itemsInColumn by taking the integer of the total number of items divided by two.

Open the foreach loop and increment the $item variable. Test whether it's time to start a new column: if($item > $itemsInColumn). If it is, reset the $item counter. Close the div and open a new one.

When the foreach loop is exhausted, close the div and clear the floats.

The code above includes techniques covered in other articles. Read, Custom Web Publishing: Add Elements to Search Form to learn how to turn a FileMaker value list into an array. Read, Working with Checkboxes, to understand the $typeRep variable. Read, Custom Web Publishing: Accessible Forms to understand how <label for... allows users with imprecise motor skills to click anywhere on the label text, not just the tiny radio or check box.

Odd list of values to use as an example? This is part of an actual solution that reports incidents in accordance with the Clery Act.