You may already know that merging FileMaker Pro database files can be a tedious and challenging process. After copying, you could spend hours fixing broken links, missing fields, remapping buttons, etc. For this reason, many people simply choose to maintain multiple files even when that may not be the ideal or preferred setup.

With the method outlined below, you can merge databases with a minimal amount of broken links. There may still be some tedious grunt work to do, but by following these steps in the order given, you can significantly reduce the time and tedious tasks involved.

The Quick Step-by-Step List

In general, when copying functionality from one FileMaker file to another, you will want to do so in the following order:

1. Rename table occurrences and fields in source file (throwaway file) to match target file.
2. Copy any necessary custom functions
3. Create any necessary value lists. These can be blank for now.
4. Copy tables and relationships.
5. Copy any necessary fields into existing tables.
6. Check Calculated Fields (check the Import.log file or use the Import Log Interpreter tool)
7. Copy the value lists
8. Create Blank Layouts
9. Copy Scripts
10. Copy Layout Objects and Recreate Layout-Based Script Triggers

This will insure that any buttons, calculations or other objects that rely on other objects will maintain their links. See more details below.

Step 1: Rename table occurrences and fields

The main idea behind this step is that you want to make sure that the two files have matching table occurrence and field names. This way when you copy scripts and other objects from one file to another, it will properly map the tables and fields. You don’t want to have to go through each layout and script and fix all of the “<field missing>” or “<table missing>” errors manually.

At this point, you are not copying any fields or tables yet, you simply want to make sure than any of the table occurrences and fields that already exist in your target database match the corresponding table occurrences and fields in the source file (the one you will be throwing away).

Step 2: Copy custom functions

If there are any custom functions in the source file that do not exist in the target file, copy or import them from the source to the the target file. We do this now in case custom functions are referenced in calculated fields or script calculations. We don’t want to have to fix “<function missing>” errors in our calculations.

There are two methods to achieve this:

Method 1:

1. Open the Manage Custom Functions dialog in the target file.
2. Click the Import button.
3. Browse to source file.
4. Check the functions you want to import.
5. Click OK.

Method 2:

1. Open the Manage Custom Functions dialog in the source file.
2. Select the custom functions you want to copy (use CMD or CNTRL click to select multiple).
3. Select Edit->Copy or CMD or CNTRL-C to copy.
4. Close the dialog and optn eht Manage Custom Functions dialog in the target file.
5. Select Edit->Paste or CMD or CNTRL-V to paste.

Step 3: Create value lists

If there are any values lists in the source file that do not exist in the target file, create them in the target file. It is not necessary at this point to define the values for the value lists; they can be empty for now. For value lists that are populated from table data, it won’t be possible to define the values. At this point, empty value lists will work fine. We want to do this now before we copy tables and fields in case any of the fields use value lists for validation.

I’ve found that the easiest way to do this is to grab a screenshot of the Define Value Lists dialog in the source file and open the Define Value Lists dialog in the target file. Create the value lists one by one while going down the list in the screen shot.

Step 4: Copy tables and create relationships

Now, we’re ready to copy any tables that exist in the source database, but not in the target database, and set up the table occurrence relationships for those tables. Copying the tables is the easy part, but unfortunately, there is no shortcut for replicating the relationships. Here’s my favorite method:

Inspect and take notes on the existing relationships:

1. Open the Define Database dialog in the source file.
2. Print the relationship graph from the source table.
3. Manually inspect the relationships in the source file relating to the tables to be moved moved.
   Hint: Select a table occurrence of your source table and press CMD or CNTRL-U on your keyboard. FileMaker will automatically select all occurrences of that source table in the relationship graph so you can quickly see all the places that table is referenced in the graph.
4. Take notes on the printed relationship graph regarding which relationships need to be copied and the properties of those relationships.

Copy the tables:

The easy part!

1. Under the tables tab, select the tables to copy.
2. Click the Copy button near the bottom of the window
3. Close that dialog and open the Define Database dialog in the target file
4. Click the Paste button.

Create relationship:

Now, set up the relationships to match those in the source file:

1. With the Manage Database dialog still open, add each of the table occurrences and relationships that you noted on the printout to the target file.

It may only be one step, but depending on the complexity of the relationships you need to replicate, it could be the most time consuming part of this whole process.

Step 5: Copy necessary fields into existing tables

If there are tables in your target database that will be used in place of tables that existed in the source table, copy them from the table in the source database to the table in your target database.

For each table that has fields that need to be copied. Do the following:

1. In the source file, open the Manage Database dialog and go to the field list of the table with fields to be copied.
2. Click to select each field you need to copy. CNTRL or CMD-Click to select multiple.
3. Click the Copy button in the lower right area of the window.
4. Close the dialog (you can click Cancel since you made no changes).
5. In the target file, open the Manage Database dialog and go to the field list of the table for the fields to be pasted into.
6. Click the Paste button.

Step 6: Check Calculated Fields

The tables and fields copied in steps 4 and 5 could have problems if any of the following occurs:

• Field name mismatch
• Table occurrence name mismatch
• Missing relationship
• Missing custom function
• Mismatch in custom function name

The easiest way to find and fix these is to check the Import.log file. You can do this manually with a text editor or with the Import Log Interpreter tool.

Alternatively (though not recommended – see the Important Tip in Step 2), you could use the brute force method and simply check every field and table that you copied. Double check any fields that use calculations or lookups for table missing or field missing warnings. Also check for calculations that are completely surrounded by block comments: i.e. “/*” at the beginning and “*/” at the end (without the quotes). This indicates that FileMaker could not resolve all of the references in the calculation. Remove the block comments and click OK to save the calculation. At this point, FileMaker will probably give you an error message highlighting the problem with the calculation. You can then do some troubleshooting.

Step 7: Copy the value lists

Now that the table occurrences and fields are in place, you can set up the value lists that depend on them. Depending on how many you have, this can be a tedious process. Here is the most efficient method I’ve come up with:

1. Open the Define Value Lists dialog in the source database.
2. Open each value list you want to copy.
3. Take notes in a simple text document about each value list. Either note the relationship used and the appropriate settings or copy the list of custom values. Alternatively, you can take a screen shot of each value list dialog and the corresponding “Specify Field…” dialog and paste it into your notes document. If you choose this route, be sure to also copy and paste custom values text so you can easily copy and paste them into the target file.
4. Open the Define Value Lists dialog in the target database.
5. Go through each of the value lists you added here in Step 3 and set them up based on your notes in the notes file.

Using this method is much more efficient than going back and forth between the files to copy each value list separately.

Step 8: Create Blank Layouts

Create a blank layout in the target file for each layout that exists in the source file if it does not already exist in the target file. Do not copy the objects from the layouts at this time. Many layout objects will depend on scripts and other layouts which have not yet been copied. The most important factors at this point are the layout names and the table occurrence.

One efficient way to accomplish this is to open the Manage Layouts window in the both files and position them side-by-side. From there, you can go down the list in the source file and create the layouts in the target file.

Be on the lookout for duplicate layout names and change them where necessary. You want them all to be unique. And, if you change any of the layout names, be sure to do it in both files. They must match.

Step 9: Copy Scripts

Now we’re ready to copy the scripts. At the surface, this process is quite simple, though you will want to spend some time double checking the Import.log file or using the Import Log Interpreter tool.

There are two methods.

• Method one: open the Manage Scripts windows in both files, select and copy the scripts from the source file and paste them in the target file.
• Method two: click the import button near the bottom of the Manage Scripts window in the target file, navigate to the source file, check the scripts to copy and click OK.

Whichever method you use, be sure to copy all of the necessary scripts at one time. Doing so will assure that any scripts that call other scripts will find their subscripts.

Once the scripts are copied, you’ll want to go through the Import.log file to make sure that everything copied correctly. Things to look out for are broken layout, field and table references.

Step 10: Copy Layout Objects and Recreate Layout-Based Script Triggers

This step is one of the more tedious steps in the process. You will need to go through each layout you added in Step 8, and modify the layout in the target file to match those in the source file.

As in Step 8, you can open the Manage Layouts window in both files and position them side-by-side. Then, go down the list and open each matching layout side-by-side.

For each layout, you will do the following:

1. Add and adjust all the layout parts in each layout to match the source file. Using the Inspector, you’ll want to make sure the header, body, footer, etc. are all the exact same size: to the pixel. And create the necessary sub-summary parts to match those in the source file.
2. Copy and paste all the objects from each layout in the source file to the corresponding layout in the target file. Simply select all in the source file. In the inspector window, notice the exact Left and Top positions of the selected objects. Copy. Paste into the layout in the target file and enter the Left and Top coordinates in the inspector window to match what was in the source file.
3. Open the Layout Setup dialog in the source file and check the Script Triggers tab. If there are any defined, add them to the target file as well.

Do this for each layout and then…

You’re Done!

You should now have a single FileMaker file with all the functionality that existed in the two separate files. However, I would strongly recommend you do some serious testing at this point to make absolute certain everything copied over properly.

The Future: Will This Get Any Easier?

This article is based on the tools included in FileMaker Pro 11 Advanced. The ability to import custom functions, tables and scripts is a recent addition to the FileMaker features list. One can only assume that it will just be a matter of time before tools are included to import layouts, value lists and possibly even relationships. But, we won’t know until that happens. So, for now, this is the process. It’s easier than it used to be, but harder than it might someday be.

And finally…

As always, if you have any questions or comments or could use some help, feel free to post a comment below or contact me directly.

With a FileMaker Pro Web Viewer and some JavaScript, we can create a FileMaker Pro native internal instant messaging system that updates every second: no plugins, no distracting refreshes, no affect on performance and no internet access required.

This article follows up on 2 previous articles discussing dynamic multi-threading with web viewers: Timers and Alarm Clocks. I recommend that you read those articles first if you have not already, especially the first one on Timers, as it covers the basics of this technique using web viewers, URIs and JavaScript.

The Basics

In the Timer article, I discuss the three main components that are the foundation of this technique: data URIs, including FileMaker Pro data in a web viewer and JavaScript. Click here to view the basics of this technique of dynamic multi-threading with web viewers.

In short, we will use a Data URI to specify the HTML and JavaScript we want to display in the web viewer, and we’ll include data from our FileMaker Pro database in that code. We will use JavaScript to build and refresh the HTML dynamically.

The Instant Messenger Project

Step 1 – Create Messenger Table

In this step, we create a simple table in our FileMaker Pro database that stores our messenger communications.

Our Messenger table will have the following fields:

__pk_MessengerID – Number – Auto enter Serial. This is the primary key of the table. Though we don’t actually use this field for any relationships or other functionality, it is always good practice to include a primary key field in every table that you create. This way, if you ever need to isolate a record it in a relationship or script, you will have a way.

_fk_Username~from – Text. This holds the user account name of the logged in user who sent the message.

_fk_Username~to – Text. This holds the user account name of the message recipient.

_fmk_Usernames – Calculation (Text). This field is used for relationships and holds the values of both of the above usernames in a list. The calculation is as follows:

_fk_Username~from & ¶ & _fk_Username~to

Message – Text. The text of the message.

Status – Text. This field will be used to flag the message as “New” or “Read”. You could simply use a number field as a flag (with values 1 or 0), but I like using the text field to allow for future expansion.

IsNew – Calculation (Number). This field will be one if Status=”New”. It is used for relationships so FileMaker knows when you have received new messages. If you choose, as mentioned above, you could simply combine this field with the Status field. The calculation is simply as follows:

Status = "New"

Timestamp – Timestamp – Auto-Enter Creation Date. This is the time that the message was sent.

Step 2 – The User Table

The basic messenger functionality could work without a user table; however, this table allows us to add a number of features such as status updates, a dynamic pop-up lists of users we can chat with and a list of user from whom we have received new messages.

The user table will hold information about each user who can log in to the database and use the chat functionality.

Our User table will have the following fields:

Username – Text. This will essentially be the primary key of the user table record. It holds the FileMaker account name of the user.

MessengerStatus – Text. This field holds the status of the user. We will set this in our open script and log in script (if one exists), as well as our close and log out scripts.

MessengerDisplay – Calculation (Text). We’ll use this one in a value list of users and their statuses. The calculation is as follows:

If ( not IsEmpty ( Username ) ;
 Username & " (" & If ( not IsEmpty ( MessengerStatus ) ; MessengerStatus ; "Status Unknown" ) & ")"
)

Step 3 -The Globals Table

You will need four global fields. Theoretically, you could put them in any table since they are global, but I strongly recommend that you put these in a table designated to hold only global variables.

_gk_Username – Text (global). This will hold the user account name of the currently logged in user. In your open script and log in script (if you have one), this field should be set with the get function: get ( AccountName ).

_gk_ChatUsername – Text (global). This is the user with whom the current user is currently chatting.

gc_1 – Calculation(Number). Simply holds the number 1 for relationship purposes. In the calculation field, simply enter “1” (without quotes). Note that this field should not be set to global.

MessengerText – Text (global). This field is used for entry of the text message before it gets sent to the recipient.

Once this table is created, you will need to create a new record in the table.

Step 4 – Relationships

In it’s simplest form, this is how your relationship graph will look.

There will be 4 occurrences of the Messenger table with three of them related to your global table (or some other table based on global fields). There will also be two occurrences of the User table related to the global table.

Create a relationship occurrence of the Messenger table called Messenger~new. This relationship will give us a list of messages sent to the current logged in user that have not yet been read. It is a multi-predicate relationship (2 to be exact) based on the following two pairs of fields:

Globals::_gk_Username = Messenger~new::_fk_Username~to
Globals::gc_1 = Messenger~new::IsNew

Create another relationship occurrence of the Messenger table called Messenger~activechat. This will give us a list of all the message interactions between the current logged in user and the user with whom the current user is chatting. It is another 2 predicate relationship based on the following two pairs of fields:

Globals::_gk_Username = Messenger~activechat::_fmk_Usernames
Globals::_gk_ChatUsername = Messenger~activechat::_fmk_Usernames

Finally, create one more relationship occurrence of the Messenger table called Messenger~activereceived. This will give us a list of all the messages to the current logged in user from the user with whom the current user is chatting. We will use this to mark messages as read. It is another multi-predicate relationship based on the following two pairs of fields:

Globals::_gk_Username = Messenger~activereceived::_fk_Username~to
Globals::_gk_ChatUsername = Messenger~activereceived::_fk_Username~from

Now we can set up the User table relationships:

Create a relationship between the globals table and an occurrence of the User table called User~loggedin.

Globals::_gk_Username = User~loggedin::Username

Create a relationship between the globals table and an occurrence of the User table called User~other.

Globals::_gk_Username ≠ User~other::Username

Note the not equal operator above.

Optionally, you can create a relationship between the Messenger~new table and an occurrence of the User table in order to produce a list of users from whom the current user has received new messages. We can call the new User table occurrence Messenger~new_User~from (or whatever your conventions suggest). That relationship will be based on the following:

Messenger~new::_fk_Username~from = Messenger~new_User~from::Username

Step 5 – Create Layouts

Chat Window Layout

We create this layout to be used as a chat window.

Our layout has three fields, four buttons, a portal (optional) and two web viewers. The context of the layout is our Globals table. We’ll give it the name “Messenger.”

The 3 fields:

User~loggedin::MessengerStatus – In the upper left under the label, “Your Status”, is the MessengerStatus field from the User table. It is a popup menu with a value list that has a few suggested status messages: Logged In, Busy, Away, etc. And selected “Allow entry of other values.”

Globals::_GK_ChatUsername – This field is placed to the right of the text, “Chatting with.” It is using a popup window using a value list (see below) defined to use values from Globals_User~other::Username, also displaying values from Globals_User~other::MessengerDisplay, include only related records from Globals and show only values from the second field.

Define a value list for the Globals::_GK_ChatUsername field as shown above.

Globals::MessengerText – This is the large text field near the bottom, to the left of the “Send” button. This is where users will type their message.

The Portal (Optional)

Under the yellow “Messenger/2 New Messages” box (which is flashing, btw), there is a portal showing related records from Messenger~new_User~from. The portal has a text object (just text, not a field) with the following text containing a merge field:

New Msg From <<Messenger~new_User~from::Username>>

New Msg From <<Messenger~new_User~from::Username>>

4 Buttons and 2 Web Viewers

These are covered in the steps 4 and 6. BTW: in case you’re wondering where the third and fourth buttons are, one is the web viewer displaying “Messenger / 2 New Messages,” the other in the text object in the portal.

Dev – Messenger Layout

Create a layout showing records from the Messenger table. The layout can be blank. It’s only purpose is for scripting. Call the layout “Dev – Messenger.”

Step 6 – Web Viewer Setup

We’ll be setting up two web viewers. One, we’ll call the messenger widget, the other is the conversation viewer.

The Messenger Widget

Create a web viewer sized 116×40 (you can do any size, but the HTML code below is optimized for this size).

The widget shows the current status or the logged in user or flashes the count of incoming messages.

We can place this widget on any layout with the appropriate context and the user will be alerted to incoming messages.

In the web viewer setup dialog, deselect all of the check-boxes near the bottom of the dialog. For the web address, use the following code:

"data:text/html,<html>
<head>
<script type='text/javascript'>

/*Variables set with FileMaker Pro data*/
var msgCount = '" & Count ( Messenger~new::__pk_MessengerID ) & "';
var recentMsgTimeTxt = '" & Max ( Messenger~new::Timestamp ) & "';
var status = '" & User~loggedin::MessengerStatus & "';
/*End FileMaker Pro Data*/

var recentMsgTime = new Date(recentMsgTimeTxt);

function setMsg()
{
	var currentTime = new Date();
	var msgobj = document.getElementById('msg');

	if(msgCount>0 && Math.floor(currentTime/1000)%2==0 )
	{
		document.body.style.backgroundColor='#FFFF00';
		msgobj.innerHTML=msgCount + ' New Message';

		if ( msgCount > 1 ) msgobj.innerHTML=msgobj.innerHTML + 's';
		msgobj.style.fontWeight='bold';

	}
	else
	{
		document.body.style.backgroundColor='#FFFFFF';
		msgobj.style.fontWeight='normal';
	}
	if(msgCount==0)
	{
		msgobj.innerHTML=status;
	}

	setTimeout('setMsg();',1000);
}

</script>
</head>
<body style='margin:4px;padding:0;font-size:11px;font-weight:normal;font-family: Arial, Helvetica, sans-serif;text-align:center;background-color:#FFFFFF;' onload='setMsg();'>
<div style='font-size:10px;font-weight:bold;padding-bottom:3px;'>Messenger</div>
<div id='msg'></div>
</body></html>"

As with the other projects, don’t worry too much about understanding all of the code above. It will work even if you don’t fully understand. The main thing to note is the series of var statements near the top. These are setting JavaScript variables with FileMaker data fields which are later used in the JavaScript function.

var msgCount = '" & Count ( Messenger~new::__pk_MessengerID ) & "';
var recentMsgTimeTxt = '" & Max ( Messenger~new::Timestamp ) & "';
var status = '" & User~loggedin::MessengerStatus & "';

If your field and/or table occurrence are named differently, you will need to adjust these lines accordingly.

The Conversation Viewer

This is the larger web viewer in the messenger window which shows the messages. When new messages appear, the messages is added to the bottom, highlighted, and the viewer automatically jumps to the bottom.

You can size this web viewer to your tastes. In the screenshot above, it is 440×220 and anchored on all four sides such that if the window is re-sized, the viewer will stretch or shrink to fill the space.

In the web viewer setup, select “Allow interaction with web viewer content.” Deselect all other check-boxes. For the web address, use the following code:

"data:text/html,<html>
<head>
<script type='text/javascript'>

/*Variables set with FileMaker Data*/
var msgtxt = new Array ( '" & Substitute ( Substitute ( List ( Messenger~activechat::Message ) ; "'" ; "&#39;" ); ¶ ; "','" ) & "');
var msgtime = new Array ( '" & Substitute ( List ( Messenger~activechat::Timestamp ) ; ¶ ; "','" ) & "');
var msguser = new Array ( '" & Substitute ( List ( Messenger~activechat::_fk_Username~from ) ; ¶ ; "','" ) & "');
var msgstatus = new Array ( '" & Substitute ( List ( Messenger~activechat::Status ) ; ¶ ; "','" ) & "');
var thisuser = '" & GLobals::_GK_Username & "';
/*End FileMaker Data*/

function setMsg(newmsg)
{
	var currentTime = new Date();
	var msgobj = document.getElementById('msg');
	var labelobj = document.getElementById('label');
	var content = '';
	msgobj.innerHTML = '';

	for ( var i=0; i< msgtxt.length ; i++ )
	{
		if (msgtxt[i]!='')
		{
			content=\"<span style='font-weight:bold;'>[\" + msgtime[i] + '] ';
			if ( thisuser.toLowerCase()==msguser[i].toLowerCase() ) content +='you';
			else content +=msguser[i];
			content += \" said:</span> <span style=''>\" + msgtxt[i] + '</span>';
			var pstyle='';
			if ( msgstatus[i]=='New' && msguser[i].toLowerCase()==thisuser.toLowerCase())
			{
				pstyle='background: #ffffcc;';
			}
			else if ( msgstatus[i]=='New' && msguser[i].toLowerCase()!=thisuser.toLowerCase())
			{
				pstyle='background: #ccccff;';
				newmsg=true;
			}
			msgobj.innerHTML += \"<p style='\" + pstyle + \"'>\" + content + '</p>';
		}
	}

	dh=document.body.scrollHeight;
	ch=document.body.clientHeight;
	if(newmsg && dh>ch)
	{
		bottom=dh-ch;
		window.scrollTo(0,bottom);
	}

	setTimeout('setMsg(false);',1000);
}

</script>
</head>
<body style='margin:4px;padding:0;font-size:11px;font-weight:normal;font-family: Arial, Helvetica, sans-serif;text-align:left;background-color:#FFFFFF;' onload='setMsg(true);'>
<div id='label' style='font-size:10px;font-weight:bold;padding-bottom:3px;'></div>
<div id='msg'>Loading...</div>
</body></html>"

Again, don’t worry too much about understanding all of this. The var statements setting JavaScript variables with FileMaker data are as follows:

var msgtxt = new Array ( '" & Substitute ( Substitute ( List ( Messenger~activechat::Message ) ; "'" ; "'" ); ¶ ; "','" ) & "');
var msgtime = new Array ( '" & Substitute ( List ( Messenger~activechat::Timestamp ) ; ¶ ; "','" ) & "');
var msguser = new Array ( '" & Substitute ( List ( Messenger~activechat::_fk_Username~from ) ; ¶ ; "','" ) & "');
var msgstatus = new Array ( '" & Substitute ( List ( Messenger~activechat::Status ) ; ¶ ; "','" ) & "');
var thisuser = '" & Globals::_GK_Username & "';

Most of these are producing FileMaker lists and converting them to JavaScript arrays.

Step 7 – Scripting

We will be creating four scripts!

1. Mark As Read – Marks incoming messages in the active conversation as “Read.”
2. Send Message – Creates a new message record with the text typed in the MessengerText field, clears that field and refocuses on that field.
3. Go To Messenger – Opens the messenger layout in a new window (selects existing window if already open). If there are unread messages, selects a user from whom messages were received to display the new message(s) and marks as read.
4. Trigger – Message Keystroke – Called on each keystroke in the MessengerText field, this simple script checks the character code. If the return key was pressed, it sends the message.

Mark As Read

The Mark As Read script simply sets all the related Messenger~activereceived records to “Read.” It uses the Install OnTimer Script to add a 1 second delay so the new messages will remain highlighted for a second.

Send Message

This script opens a hidden window, creates a new messenger record, then populates the record with values from our global fields, including the messenger text. It then closes the window, clears out the messenger text, calls a script to mark incoming messages as read, then returns the focus to the MessengerText field.

Go To Messenger

This is the script that opens the messenger window, activates a chat of unread messages if one exists, and marks as read. We will be calling it from the messenger widget.

Trigger – Message Keystroke

This script every time a key is pressed on the keyboard while in the MessageText field. It will ignore every keystroke except the return key (you could also include the enter key on the keypad by adding Code ( Get(TriggerKeystroke) ) =10 )

Step 8 – Buttons and Script Triggers

Now, we’re going to set up buttons and triggers using the scripts we created in the previous step.

The Four Buttons

The Messenger Widget – On the messenger layout, you will define a button for the messenger widget web viewer (the smaller web viewer on the left). Right click on the web viewer and select “Button Setup…”.

Button definition for The Messenger Widget.

In the Button Setup window, choose Insert Calculated Result. Click the Specify button to the right of “Go to target field” and select the field Globals::_gk_ChatUsername. Click the Specify button to the right of “Calculated result:” and enter the following:

If (
	not IsEmpty ( Messenger~new::_fk_Username~from ) ;
	Messenger~new::_fk_Username~from ;
	Globals::_GK_ChatUsername
)

This will set the active chat to the sender of a new message if one exists which effectively triggers the Mark As Read script (we’ll set that trigger up in a bit).

When including the Messenger Widget on other layouts in your solution, set the button to call the Go To Messenger script.

Button definition for The Messenger Widget on other layouts within your solution.

The portal text object – This is the text object you placed in the portal under The Messenger Widget. Set the button to perform a similar Insert Calculated Result operation as The Messenger Widget. The difference is that it will specify the from user based on which portal row was clicked. So, if there is more than one sender, the user can choose the sender to read next. The result calculation is as follows:

Messenger~new_Employee~from::UserName

The Send button – This button performs the script, Send Message. It provides a second way to send a message besides pressing return.

The Close button – Simply closes the window.

Script Triggers

_gk_ChatUsername field – This is the drop down menu to the right of the text, “Chatting with.” As suggested above, we set a script trigger on this field to trigger OnObjectModify which calls the script Mark As Read.

Script trigger for the “Chatting with …” drop down menu.

MessengerText field – We set a script trigger on this field OnObjectKeystroke which calls Trigger – Message Keystroke.

Script trigger for messenger text field.

Step 9 – Final File Setup

Now that we have the instant messaging structure in place, we need to prepare our file.

Open/Login Script

Create an open script if you do not already have one. This is a script that runs when the file opens. If you already have one, simply add the following two script steps to the file. If you do not already have one, create a new script, give it any name (perhaps “On Open”), and add the following steps:

If you have a login script, add the same two steps. If you don’t have one, don’t worry about it right now.

Close/Logout Script

Create an close script if you do not already have one. This is a script that runs when the file closes. If you already have one, simply add the following script step to the file. If you do not have one, create a new script, give it any name (perhaps “On Close”), and add the following step:

Set Field [ User~loggedin::MessengerStatus; “Logged Out” ]

If you have a logout script, add the same step. If you don’t have one, don’t worry about it right now.

Users and Accounts

Using FileMaker’s Manage Security feature, create an account for each user who will be using the messenger program (if such accounts do not already exist). Each user will have to log in using a different user account.

Then create a record in the user table for each user account. Be sure the usernames match. Alternatively, you can simply set up the relationship between Globals and User to allow creation of records in the User~loggedin table via this relationship. By doing this, the User record will be created automatically by the startup/login script when the user logs in.

File Options

Set up your file options to match the following:

File Options for the messenger file. Deselect “Log in using:”, and designate the appropriate scripts for open and close.

If your file previously logged in automatically to the default admin account, be sure you know the account name and password for and admin account.

Done!

And now we’re ready to chat! (After some testing and debugging perhaps )

What Else Is Possible?

With these three projects, we’re still just scratching the surface of possibilities with this technique. With the power of HTML and JavaScript, the possibilities are practically infinite: message tickers; powerful, flexible and interactive reporting; calendar alerts; the list goes on….

Think about it for a minute. What comes to your mind? Please share your ideas below!

Debugging

Take a look at the debugging section of the Timer article where I discuss debugging web viewers with data URIs. Some additional things you can do with this project include adding fields to your Dev – Messenger layout and viewing the data that is being generated in the Messenger table. This project is also heavy on the scripting, so the Script Debugger will be quite useful.

And of course, if you are still stuck or you have any questions or comments, feel free to post a comment below or contact me directly.

This article follows up on the previous article, Countdown Timers – Dynamic Multi-Threading with Web Viewers. I recommend that you read that article first if you have not already, as it covers the basics of how this technique works.

We’ll cover another simple example of what can be done using Web Viewers, and we’re still just scratching the surface of what can be done. For instance, in the next article, we’ll discuss creation of an interactive, internal instant messaging program with nothing but FileMaker Pro and web viewers: no plugins and no external internet access required.

The Basics

In the previous article, I discuss the three main concepts that are the foundation of this technique: data URIs, including FileMaker Pro data in a web viewer and JavaScript. Click here to view the basics of this technique of dynamic multi-threading with web viewers.

In short, we will use a Data URI to specify the HTML and JavaScript we want to display in the web viewer, and we’ll include data from our FileMaker Pro database in that code. We will use JavaScript to build and refresh the HTML dynamically.

The Alarm Clock Project

Step 1 – Create Alarm Table

In this step, we create a simple table in our FileMaker Pro database that stores our alarm clock parameters. This table is very similar to the one created for the Countdown Timer. And similarly, there are a number of ways this could be set up depending on how you want to use the alarm clock. For instance, for this example, we will set it up so there is one alarm per user. In this way, if a user closes the database and opens it again (whether on the same workstation or a different workstation), their alarm clock setting will persist. Alternatively, the alarm clock could be workstation based, function based or session based. You may also choose to allow a user to set multiple alarms at one time.

For the user-based alarm clock, our table will have the following fields:

__pk_AlarmID – Number – Auto enter Serial. This is the primary key of the table. Though we don’t actually use this field for any relationships or other functionality, it is always good practice to include a primary key field in every table that you create. This way, if you ever need to isolate a record it in a relationship or script, you will have a way.

_fk_User – Text. In my database, this is the id of a record in the User table that corresponds to the currently logged in user. However, if such a table does not exist in your database, it could simply be the account name of the currently logged in user.

Label – Text. This field is optional and allows one to give the alarm a label. For instance, if you want to remember to eat lunch at 12:30PM, set the label to “Eat Lunch,” and when the alarm goes off, it will flash, “Eat Lunch.” This is especially useful if you set it up to allow multiple simultaneous alarms.

AcknowledgeTimeStamp – Timestamp. This field will be used to flag the alarm clock as “Acknowledged” so it stops flashing and beeping. We use a timestamp so that we know when the alarm was acknowledged, so for daily alarms, the alarm will go off at the next appropriate time.

Hour – Number – Restricted to 1-12 (12 hour clock). This is a data entry field where the user enters the hour the alarm clock should go off.

Minute – Number – Restricted to 0-59. Another data entry field for entering the minute.

AMPM – Text – Restricted to value list containing values AM and PM.

Date – take a guess. Another data entry field for entering the date of the alarm or left blank for a daily alarm.

Timestamp – Calculation (Timestamp). This is the time that the alarm will go off. The calculation is built from the data entry fields above. Here it is:

If ( IsEmpty ( Date ) ;
 Let (
   theTime = Get ( CurrentDate ) & " " & Hour & ":" & Minute & " " & AMPM ;
   If ( AcknowledgeTimeStamp ≥ GetAsTimestamp ( theTime ) ;
     ( Get ( CurrentDate ) + 1 ) & " " & Hour & ":" & Minute & " " & AMPM ;
     theTime
   )
 ) ;
 Date & " " & Hour & ":" & Minute & " " & AMPM
)

This calculation allows for daily alarms where no date is specified. Once the alarm for one date is acknowledged, it is ready for the next day.

Step 2 – Relationships

The relationship setup is nearly identical to that in the Timer. Only one relationship is needed and there are two possible ways to handle it:

• If you have a user table, such as I do in my database, create a relationship between the Alarm table and the table occurrence of the User table that represents the currently logged-in user. The relationship will be based on this: Alarm ::_fk_User = User::__pk_UserID (__pk_EmployeeID in my case).
• If no user table and current user occurrence exists, simply create a global field in one of your tables (preferably a Globals table) that gets set upon log in to the current account user name. Then create a relationship between that table and the Alarm table using your global account name field = Alarm::_fk_User field.

In order to keep the scripting simple, select the check box to allow creation via this relationship:

Select “allow creation of records in this table via this relationship” to simplify the scripting process.

Step 3 – Web Viewer Setup

Create a web viewer sized 230×25 (you can do any size, but the HTML code below is optimized for this size).

The Alarm Clock.

For the web address, use the following code:

"data:text/html,<html>
<head>
<script type='text/javascript'>

/*Setting JavaScript Variables to value of FileMaker fields*/
var alarmTimeTxt = '" & Alarm::Timestamp & "';
var acknowledgeTimeTxt = '" & Alarm::AcknowledgeTimeStamp & "';
var alarmLabel = '" & Alarm::Label & "';
/*End FileMaker Fields*/

if (alarmLabel=='') alarmLabel='Alarm';
var alarmTime = new Date(alarmTimeTxt);
var acknowledgeTime = new Date(acknowledgeTimeTxt);

function setClock()
{
	var currentTime = new Date();
	var clock = document.getElementById('clock');

	if (alarmTimeTxt=='' || alarmTime >= currentTime || (acknowledgeTimeTxt !='' && acknowledgeTime >= alarmTime) || Math.floor(currentTime/1000)%2==0 )
	{
		document.body.style.backgroundColor='#FFFFFF';
		clock.innerHTML=currentTime.toLocaleDateString() + ' ' + currentTime.toLocaleTimeString();
	}
	else
	{
		document.body.style.backgroundColor='#FFFF00';
		clock.innerHTML=alarmLabel + \"<embed src='file:///System/Library/Sounds/Glass.aiff' autostart='true'  id='sound1'
enablejavascript='true' width='0' height='0'>\";
	}

	setTimeout('setClock();',1000);
}

</script>
</head>
<body style='margin:4px;padding:0;font-size:14px;font-weight:bold;font-family: Arial, Helvetica, sans-serif;text-align:center;background-color:#FFFFFF;' onload='setClock();'>
<p id='clock'>
</p>
</body></html>"

It’s not necessary for you to understand all of this code in order for you to get this to work. However, near the top, there are a series of var statements. These are setting JavaScript variables which are later used in the JavaScript function. Many of these variables are being set with FileMaker Pro data. For instance,

var alarmTimeTxt = '" & Alarm::Timestamp & "';
var acknowledgeTimeTxt = '" & Alarm::AcknowledgeTimeStamp & "';
var alarmLabel = '" & Alarm::Label & "';

If your field and/or table occurrence are named differently, you will need to adjust these lines accordingly.

Also, take note of the following JavaScript code:

clock.innerHTML=alarmLabel + \"<embed src='file:///System/Library/Sounds/Glass.aiff' autostart='true'  id='sound1'
enablejavascript='true' width='0' height='0'>\";

This specifies the sound that will be used when the timer goes off. The file URL after the scr= statement is the path on a Macintosh workstation to the Glass.aiff sound effect file. If you are using Windows, this path will have to be adjusted. If you are in a mixed environment, you may want to use a sound file that is located on the web.

Step 4 – Create Settings Layout

Create a layout for use as a popup window where the alarm can be set.

Create a layout that shows records from your Alarm table. As in the the screen shot above, include the label field, hour, minute, AMPM (with a value list with values AM and PM), and date.

The Clear button runs a script that simply runs a series of Set Field steps to clear the date from the Date, Hour, Minute, AMPM, AcknowledgeTimeStamp and Label fields, followed by a Commit Records step.

The Done button simply closes the window.

Step 5 – Scripting

We will be creating one script that will fire when clicking on the timer web viewer. Depending on the state of the alarm, it will perform one of two tasks:

• When the alarm is going off, it will set the “AcknowledgeTimeStamp” field, thus stopping the alarm.
• When the alarm is idle, it will bring up the alarm settings layout in a popup window.

Here are the script steps:

Finally, define a button for the web viewer that calls the script you just created.

That’s it. The timer should now work for you. If it doesn’t (as most programming project don’t the first time through), take a look at the notes on debugging in the timer article.

What Else Is Possible?

In the next article, we’ll do something a little more fun, and challenging, and perhaps more practical: an internal instant messaging system. This project is considerably more complex, but most of the complexity lies in FileMaker Pro development, rather than HTML and JavaScript. Arguably, the project could be accomplished exclusively with native FileMaker Pro tools, but I find it to be much more responsive, robust and dynamic when using Web Viewers.

Debugging

Take a look at the debugging section of the Timer article where I discuss debugging web viewers with data URIs. And of course, if you are still stuck or you have any questions or comments, feel free to post a comment below or contact me directly.

The multitasking capabilities in FileMaker Pro are limited in this way. Web viewers, however, run in different threads, and when fed with data from your FileMaker Pro database, they allow us to create rich, up-to-the-second, content, complete with animation, sounds, popup alerts, or whatever else we want to put in there. And, the best part: it has no noticeable affect on FileMaker’s performance!

This article covers a simple example of what can be done using Web Viewers, and it just scratches the surface of what can be done. For instance, I have also created an alarm clock as well as an interactive, internal instant messaging program with nothing but FileMaker Pro and web viewers: no plugins and no external internet access required. I’ll be discussing these in another article soon.

The Basics

Data URI

When you set up a web viewer, you normally specify a URL (web address) indicating what web page the web viewer should display. With a data URI, we can specify the HTML (and JavaScript) we want to display in the web viewer directly in place of the URL itself. This is very powerful in that it allows us to easily build a web page to display using our FileMaker data. It is a great way to create custom HTML reports that are not so easily created within the structure of FileMaker Pro. I have also used it with Instant Web Publishing to achieve greater flexibility.

While the basic format of a URL is http://<address>, the basic format of the data URI is as follows:

data:[<MIME-type>][;charset=<encoding>][;base64],<data>

Don’t worry too much if that doesn’t make sense to you. You can copy and paste the code from the articles for these projects. All we will be doing is specifying a mime type (text/html), and including the HTML and JavaScript that we need. So our Data URI will look something like this:

data:text/html,<some_html_code>

If you are interested in general information about the data URI, check out the Wikipedia Data URI article.

Including FileMaker Pro Data

With the data URI, we can build dynamic HTML content within FileMaker Pro using the calculation engine. This allows us to easily include data from our FileMaker database within our HTML.

JavaScript

In these sample projects, we will be using JavaScript to create dynamic content. JavaScript is a powerful, object-oriented scripting language used extensively on nearly every website that you visit. If you are not familiar with JavaScript, don’t be too concerned. You can copy and paste the code below. Hopefully, you will get a sense of what the code is doing if you want to make some enhancements.

One of the key JavaScript functions we will be using is the setTimeout function. This function allows us to specify a function to be called after a given period of time. It is similar to FileMaker Pro’s, OnTimer Script, script step, but it does not tie up the FileMaker Pro scripting engine. We will be creating a JavaScript function to update the web viewer content, and that function will use the setTimeout function to call itself again in 1 second. So, the content will refresh continually.

The Countdown Timer Project

Step 1 – Create Timer Table

In this step, we create a simple table in our FileMaker Pro database that stores our timer parameters. There are a number of ways this could be set up depending on how you want to use the timer. For instance, for this example, we will set it up so there is one timer per user. In this way, if a user closes the database and opens it again (whether on the same workstation or a different workstation), their countdown timer will persist. Alternatively, the timer could be workstation based, function based or session based. You may also choose to allow a user to run multiple timers at one time.

For the user-based timer, our table will have the following fields:

__pk_TimerID – Number – Auto enter Serial. This is the primary key of the table. Though we don’t actually use this field for any relationships or other functionality, it is always good practice to include a primary key field in every table that you create. This way, if you ever need to isolate a record it in a relationship or script, you will have a way.

_fk_User – Text. In my database, this is the id of a record in the User table that corresponds to the currently logged in user. However, if such a table does not exist in your database, it could simply be the account name of the currently logged in user.

Label – Text. This field is optional and allows one to give the timer a label. For instance, if you want to remember to call someone in 1/2 hour, set the label to “Call Fred,” and when the timer goes off, it will flash, “Call Fred.” This is especially useful if you set it up to allow multiple simultaneous timers.

Status – Text. This field will be used to flag the timer as “Acknowledged” so it stops flashing and beeping. You could simply use a number field as a flag (with values 1 or 0), but I like using the text field to allow for future expansion.

Timestamp_End – Timestamp. This is the time that the timer will go off. This field will be set by a script that asks the user for a timer duration. It then simply adds the duration to the current time, and that will be the timestamp when the timer will go off.

Duration – Number. For data entry. This field stores the number of minutes before the timer should go off.

Step 2 – Relationships

Only one relationship is needed and there are two possible ways to handle it:

• If you have a user table, such as I do in my database, create a relationship between the Timer table and the table occurrence of the User table that represents the currently logged-in user. The relationship will be based on this: Timer::_fk_user = User::UserID
• If no user table and current user occurrence exists, simply create a global field in one of your tables (preferably a Globals table) that gets set upon log in to the current account user name. Then create a relationship between that table and the Timer table using your global account name field = Timer::_fk_User field.

In order to keep the scripting simple, select the checkbox to allow creation via this relationship:

Select “allow creation of records in this table via this relationship” to simplify the scripting process.

Step 3 – Web Viewer Setup

Create a web viewer sized 115×40 (you can do any size, but the HTML code below is optimized for this size.

The Timer.

For the web address, use the following code:

"data:text/html,<html>
<head>
<script type='text/javascript'>

/*Setting JavaScript Variables to value of FileMaker fields*/
var targetTimeTxt = '" & Timer::Timestamp_End & "';
var status = '" & Timer::Status & "';
var labelTxt = '" & Timer::Label & "';
/*End FileMaker Fields*/

var currentTime = new Date();
var targetTime = new Date(targetTimeTxt);
var remaining = Math.floor((targetTime - currentTime)/1000);

function setClock()
{
	var currentTime = new Date();
	var clock = document.getElementById('clock');
	var labelobj = document.getElementById('label1');
	var secondsRemaining=0;

	if(labelTxt=='')
	{
		labelobj.innerHTML='Timer';
	}
	else
	{
		labelobj.innerHTML=labelTxt;
	}

	if (targetTime>currentTime)
	{
		secondsRemaining=Math.floor((targetTime - currentTime)/1000);
	}

	var hours = Math.floor( secondsRemaining / 3600 );
	var minutes = Math.floor((secondsRemaining%3600) / 60 );
	if(minutes<10)minutes='0' + minutes;
	var seconds = secondsRemaining%60;
	if(seconds<10)seconds='0'+seconds;

	clock.innerHTML=hours + ':' + minutes + ':' + seconds;

	if(targetTimeTxt=='' || status=='Acknowledged' || ( secondsRemaining==0 && Math.floor(currentTime/1000)%2==0 ) )
	{
		document.body.style.backgroundColor='#FFFFFF';
		if ( targetTimeTxt=='' || status=='Acknowledged' )
		{
			clock.innerHTML='--:--:--';
		}
	}
	else if(secondsRemaining==0)
	{
		document.body.style.backgroundColor='#FFFF00';
		document.getElementById('sound1').Play();
	}

	setTimeout('setClock();',1000);
}

</script>
</head>
<body style='margin:4px;padding:0;font-size:14px;font-weight:bold;font-family: Arial, Helvetica, sans-serif;text-align:center;background-color:#FFFFFF;' onload='setClock();'>
<div id='label1' style='font-size:10px;font-weight:bold;'>
</div>
<div id='clock'>
</div>
<embed src='file:///System/Library/Sounds/Glass.aiff' autostart='false'  id='sound1'
enablejavascript='true' width='0' height='0'>
</body></html>"

It’s not necessary for you to understand all of this code in order for you to get this to work. However, near the top, there are a series of var statements. These are setting JavaScript variables which are later used in the JavaScript function. Many of these variables are being set with FileMaker Pro data. For instance,

var targetTimeTxt = '" & Timer::Timestamp_End & "';
var status = '" & Timer::Status & "';
var labelTxt = '" & Timer::Label & "';

These line set JavaScript variables to the value of these FileMaker fields. If your fields and/or table occurrence are named differently, you will need to adjust these lines accordingly.

Also, take note of the following HTML code:

<embed src='file:///System/Library/Sounds/Glass.aiff' autostart='false'  id='sound1'
enablejavascript='true' width='0' height='0'>

This specifies the sound that will be used when the timer goes off. The file URL after the scr= statement is the path on a Macintosh workstation to the Glass.aiff sound effect file. If you are using Windows, this path will have to be adjusted. If you are in a mixed environment, you may want to use a sound file that is located on the web.

Step 4 – Scripting

We will be creating one script that will fire when clicking on the timer web viewer. Depending on the state of the timer, it will perform one of three tasks:

• When the timer is idle, it will prompt the user for a timer duration and optional label, then start the timer.
• When the timer is running, it will ask the user if the timer should be stopped. If so, it stops and clears the timer.
• When the timer is going off (the countdown has reached 0), it will flag the timer as acknowledged, thus causing the JavaScript function to stop flashing and dinging.

Here are the script steps:

Finally, define a button for the web viewer that calls the script you just created.

That’s it. The timer should now work for you. If it doesn’t, take a look at the note on debugging below.

What Else Is Possible?

The possibilities are only limited by your imagination and HTML and JavaScript skills.

In the next article, I’ll be talking about a similar, but slightly more complex project to create an alarm clock.

After that, we’ll so something a little more fun, and perhaps more practical: an internal instant messaging system. This project is considerably more complex, but most of the complexity lies in FileMaker Pro development, rather than HTML and JavaScript. Arguably, the project could be accomplished exclusively with native FileMaker Pro tools, but I find it to be much more responsive, robust and dynamic when using Web Viewers.

Debugging

This technique can be challenging to debug, because if something it not working correctly, the only indication you generally receive is an empty web viewer.

1. Copy the contents of the “web address” field from the web viewer.
2. Open the data viewer, click the “Watch” tab and click the “+” button to add a new watch expression. (If not using FileMaker Pro Advanced, go to Manage Database and add a new calculated field.)
3. Paste the code you copied from the web viewer into the calculation field.
4. Click the Evaluate Now button in the lower left of the window. (If not using FileMaker Pro Advanced, add the calculated field to a layout.)
5. Inspect the contents, especially the var expressions to see that the FileMaker data is being captured properly.
6. If you were not able to see any problems, copy the result into your clipboard.
7. Open your web browser (I prefer FireFox, because I like the error log).
8. Paste the result into your browser’s location field (the place you normally type web addresses). Then press enter.
9. Open your browser’s error log. In FireFox, go to Tools->Error Log. In Safari, go to Window->Activity. In Internet Explorer, double click the little error icon in the status bar at the bottom.

Hopefully, the errors reported by the browser will give you a clear enough indication of what went wrong. BTW: you may need to scroll all the way to the bottom of a long error log to see the most recent error.

Some other ideas:

Use the document.print JavaScript function to print messages to the web viewer or the document.alert function to bring up a dialog box. For instance, if you type the following in your JavaScript code:

document.print('hello world');

It will print hello world in the web viewer.

Or

document.alert('hello world');

This will bring up a dialog box displaying hello world.

You can also put variables (the terms defined in the var statements):

document.print(targetTimeTxt);

To concatenate strings, use the ‘+’ operator the same way you use the ‘&’ in FileMaker Pro calculations. For instance:

document.alert('This is the target time: ' + targetTimeTxt);

And of course, if you are still stuck or you have any questions or comments, feel free to post a comment below or contact me directly.

Click checkboxes to manually select records from a list.

The ability to manually select individual records for filtering is useful and often necessary, but is not as straight forward as you might think.

Your first thought may be to create a field in the table to act as a flag and place it on a list layout as a checkbox allowing users to check the records they want. The problem with this is that when one user selects a checkbox, that record is flagged for all users. So, if you have more than one user wishing to select different records, their selections will interfere with one another.

With a global field, however, the value of the field is session-based: meaning what one user does will not affect other users. But, how do you use a global field, which by definition allows only one value per table, to allow you to select multiple records? With the technique explained below, we will do just that.

Quick Overview

There are four basic steps which are outlined in detail below.

1. Create a global field to hold a list of selected IDs.
2. Created layered layout objects for the checkbox and use conditional formatting to show or hide the checkbox based on whether the current record’s ID exists in the global field.
3. Define a button on the layered layout objects that toggles the checkbox by adding or removing the current record’s ID to or from the list in the global field.
4. Create a filter button, “Show Selected,” that either goes to related records based on a relationship built with the global field, or run a find script that loops through the IDs in the global field and finds each record.

See the details of each of these steps below as well as some alternative methods and enhancements.

Step 1 – Create a Global Field

Create a text field in any table. In the field options, set the new field to use global storage as shown in the figure below. The purpose of this field is to hold a list of primary keys of the selected records.

Under the storage tab in the field options, select the “Use global storage (one value for all records)” check box.

Note: This technique could also be performed with a global variable rather than a global field. In this way, no database definition changes would be necessary. However, you would not be able to use the global variable in relationships. So, in Step 4 below, you will need to choose the script option..

Step 2 – Add the Checkbox to the Layout

This is a little bit more complex than it sounds, and there are a number of ways to approach this. The approach we’ll take is to use conditional formatting to show or hide a checkbox based on whether the current record is in the list of selected records. The method I outline here layers a couple of layout objects on top of one another. For a simpler option, see the note at the bottom of this step.

Step 2a – Place a checkbox image on the layout. This could be an image with a checkmark, an X, or you could simply use a text object with an X or √ character. Use the “engraved” effect and give it a border if you wish.

Step 2b – Place a text object or button with nothing but a single space character over the image. Make the object slightly smaller vertically and horizontally than the object from 2a and center it vertically and horizontally such that it covers the whole object except the border and/or engrave effect. Format the object to have a clear background with no border.

Step 2c – Set conditional formatting for the item in 2b with the following formula:

not ValueCount ( FilterValues ( Globals::SelectedItems ; Table::RecordID ) ) > 0

Replace Globals::SelectedItems with the name of the global field you created in Step 1.

Replace Table::RecordID primary key from the table from which you will be selecting records.

For the conditional format, set the background color to be the same as the background color on your layout.

Conditional formatting for the object layered on top of the checkbox.

The result of this step is that when the primary key of the current record exists in the list of keys in the global field, the checkbox image will be hidden, making it appear “unchecked”.

Step 3 – Create the Button

Define a button for the object you created in 2b above. The button simply needs to perform a Set Field step to either add or remove the ID of the current record to or from the global field.

The target field will be the field you created in Step 1.

The calculation will be as follows:

Case (
 ValueCount ( FilterValues ( Globals::SelectedItems ; Table::RecordID ) ) > 0;
  Substitute ( Globals::SelectedItems ; Table::RecordID & ¶ ; "" ) ;
  Globals::SelectedItems & Table::RecordID & ¶
)

Replace Globals::SelectedItems with the name of the global field you created in Step 1.

Replace Table::RecordID primary key from the table from which you will be selecting records.

Step 4 – Filter Button

Now you will want to have a way to find all of that user’s checked records. We’ll look at two approaches. One uses a find script; the other uses a relationship.

Option 1 – The Script Method – Create a script that enters find mode, loops through all the IDs in the global field and creates a find request for each. The script would look something like this:

Enter Find Mode[]
Set Variable [$i;1]
Loop
  Set Field[ Table::RecordID ; GetValue ( Globals::SelectedItems ; $i ) ]
  Set Variable [ $i ; $i+1 ]
  Exit Loop If [ $i > ValueCount ( Globals::SelectedItems ) ]
  New Record/Request[]
End Loop
Perform Find[]

Option 2 – The Relationship Method – Create a relationship to the table with records being selected based on the global field = record id. Then create a button that runs the script step Go To Related Record.

… And You’re Done

Enhancements

User-based rather than session-based

The method above will cause all selections to be cleared whenever the database is closed. You may want to set this up to be persistent across a user’s sessions. In other words, if a user logs out, then logs back in later, you may want their selections to remain. There are three possible options.

Option 1 – Instead of using a global field, use a non-global text field in the table you are filtering. Instead of storing a list of IDs in the field, store a list of account names. When the checkbox is clicked, add or remove the current account name to or from the list. When searching for selected records, do a simple find on the current account name in that field. One drawback to this method is that it will trigger any modification date/time/user fields to be updated which may not be desirable for auditing purposes.

Option 2 requires that your database be set up with a user table and a way to access the current user’s records from the current context. Then, instead of using a global field as created in Step 1 above, you will use a field in the user table in a similar manner.

Option 3 – Run a script on exit that saves the contents of the global field along with the current account name to a record in a table you create to store such things. Then upon opening or logging in, a script will run to search for a record in that table with the current account name and repopulate the global field with the contents of the record you populated on exit.

Select All and Select None buttons

For Select All, create a script that loops through each record in the current found set and adds the ID and a carriage return (¶) to the global field if it didn’t already exist.

For Select None, simply clear the global field:

Set Field [ Globals::SelectedItems ; "" ]

That’s all!

As always, if you have any questions or comments feel free to post a comment below or contact me directly.

With two (or more) global fields, portal filtering, a script trigger calling a simple script to refresh the page, you have a dynamically filtered portal with multiple filter criteria that updates as you type.

This project consists of 5 basic steps which are similar to those in the Dynamic Portal Filtering While You Type article. The main differences are that the filter calculation and script become slightly more complex. The steps are as follows:

The project consists of 5 basic steps:

1. Create the global search fields.
2. Create the custom function (copy and paste).
3. Set the portal filter calculation (copy and paste with some modifications).
4. Create the script (6 or more script steps depending on number of criteria).
5. Set a script trigger.

Step One: Create the Global Search Fields

This step is almost exactly the same as Step One in Dynamic Portal Filtering While You Type. Except that you will create one global search field for each filter criteria. Alternatively, you can create a single search field with multiple repetitions.

Step Two: Create the Custom Function

This step is exactly the same as Step Two in Dynamic Portal Filtering While You Type. If you have already created this function in your solution, you can skip this step.

Step Three: Set the portal filter calculation

The calculation will be an expanded version of the one in Step Three in Dynamic Portal Filtering While You Type. You will expand this calculation by adding a conditional statement for each of the filter fields and join all of these statements with the “and” operator. So, the entire expression will only evaluate to true if all filters contain a match.

This calculation will look different depending on the number of filters and the type of filters that make up your criteria. Here are a couple of examples:

Example 1: Calculation for One Pop-up field and one open text field:

The screen shot at the beginning of this article shows a portal of tasks with dynamic filter criteria to filter results by project status (a popup menu with fixed values) and an open text field which searches multiple fields. We will assume that the pop-up menu field is named Globals::Search_Status and the text field is named Globals::Search_Text.

In this case, we will want our filter calculation to evaluate to true if the Globals::Search_Status field exactly matches our status field in the task table (Task::Status), and our Globals::Search_Text field matches one of the words in the task title (Task::Title) or the task description (Task::Description).

Here’s the calculation:

( IsEmpty ( Globals::Search_Status )
or
Globals::SearchStatus = Task::Status )
and
( IsEmpty ( Globals::Search_Text )
or
FindWordPartsInText ( Globals::Search_Text; Task::Title & ¶ & Task::Description ; 1 ) )

Notice the use of the IsEmpty function to effectively ignore the filter if it is empty. The result is that if the filters are empty, we will see all of the records. Or if, for instance, Globals::Search_Status is set to “Open” and Globals::Search_Text is empty, we will see all task records with a status of open.

Example 2: One Pop-up and Two Open Text Searches

Taking the above example one step further, lets say we want to have a popup menu for status, and separate search fields for task name and task description.

With three filter criteria, we simply add another global field, and add another expression to our portal filter calculation.

In this case, we will have three conditional statements joined with the “and” operator:

( IsEmpty ( Globals::Search_Status )
or
Globals::SearchStatus = Task::Status )
and
( IsEmpty ( Globals::Search_Text1 )
or
FindWordPartsInText ( Globals::Search_Text1; Task::Title ; 1 ) )
and
( IsEmpty ( Globals::Search_Text2 )
or
FindWordPartsInText ( Globals::Search_Text2; Task::Description ; 1 ) )

Step Four: Create the script

Starting with the script we created in Step Four in Dynamic Portal Filtering While You Type, we will add support for a script parameter that indicates which field we are typing in. Then at the end of the script, we go back to the appropriate field.

Using Example 2 above, the script would look something like this:

Notice that we don’t do anything special if we’re editing the Status filter criteria. Since it is a pop-up menu, there is no cursor position to return to.

Step Five: Set a script trigger

As with all the other steps, we will start with the trigger we created in Step Five in Dynamic Portal Filtering While You Type. We will do this with each of the search criteria fields. In addition, we will include the script parameter indicating which field we are in.

Continuing to build on the example 2 above, we will add the parameter “Text1” to the Globals::Text1 script trigger:

Right click on the search field to get the “Set Script Triggers” window. Check the OnObject Modify and/or select it and click the “Select…” button, and the “Specify Script” dialog will appear. Select the script you created in Step Four and enter your script parameter in the “Optional script parameter:” field.

… And You’re Done

Now you can search for data in your portal using multiple criteria and have results appear while you are typing.

As always, if you have any questions or comments feel free to post a comment below or contact me directly.

]]> http://filemakerinspirations.com/2010/10/dynamic-portal-filters-with-multiple-criteria/feed/ 4 http://filemakerinspirations.com/2010/10/dynamic-portal-filtering-while-you-type/ http://filemakerinspirations.com/2010/10/dynamic-portal-filtering-while-you-type/#comments Sun, 03 Oct 2010 03:04:12 +0000 http://filemakerinspirations.com/?p=189 Using FileMaker 11’s new Portal Filtering feature, you can now show filtered views of related records in portals without having to define new relationships. What’s better than that? How about dynamically filtering your portal data while you’re typing letters into your search field! With a very simple script (4 steps), a custom function and a script trigger, you can search data in your FileMaker portals the same way you search for music in iTunes!

Place a global search field above your portal. Add portal filtering that uses a custom function you will create. Add a script trigger calling a script that simply refreshes the page. And, you have a dynamically filtered portal that updates as you type.

The project consists of 5 basic steps:

1. Create a global search field.
2. Create the custom function (copy and paste).
3. Set the portal filter calculation (copy and paste with some modifications).
4. Create the script (4 script steps).
5. Set a script trigger.

Step One: Create a Global Search Field

Create a text field in any table. In the field options, set the new field to use global storage as shown in the figure below.

Under the storage tab in the field options, select the "Use global storage (one value for all records)" check box.

Step Two: Create the Custom Function

To achieve the affect we are looking for, we will set a portal filter which will use a custom function that we will write that will find records that have words that begin with the search criteria typed by the user. The reason we need a custom function is that we will need to iterate (loop) through each word in the fields we want to search and look for a match on any word entered as search criteria. For instance, if the user types “port art”, we want to find records with the text “article about portal filtering”, but not “airport art“.
Our custom function will take three parameters:

• needles: This is the search criteria (the needles we are looking for in the haystack).
• haystack: This is the text we are searching. It will most likely be the contents of one or more fields.
• start: This is a number representing the word within haystack where we will start searching. When calling this function, we would almost always pass the number 1. This will be used to iterate through all the words in the haystack.

The function will return true if, for each word in needles, there is at least one word in haystack that begins with that word. Otherwise, it will return false.

The function, which I am calling FindWordPartsInText, is as follows:

If (
	start > WordCount ( haystack ) ; 0 ;
	Let ( [
		needle = LeftWords ( needles ; 1 ) ;
		word = MiddleWords ( haystack ; start ; 1 )
		];

		Case (
			Left ( word ; Length ( needle ) ) = needle ;
			If (
				WordCount ( needles ) < 2 ; 1 ;
				FindWordPartsInText ( RightWords ( needles ; WordCount ( needles ) - 1 ) ; haystack ; 1 )
			) ;
			FindWordPartsInText ( needles ; haystack ; start + 1 )
		)
	)
)

At this point, you can copy and paste the above function and skip to step three if you wish. Don’t forget to add the three parameters listed above in the order listed and name the function FindWordPartsInText. If you give the function a different name, you will need to change the two calls to that function in the function text above.

If you are curious to know just how this function works, read on…

Function Details

In short, we start by comparing the first word in the needle with the first characters in the first word in haystack. If that match fails, we move on to the next word in haystack. Once a match is found, we move on to the next word in needle. If a match is not found and we reach the end of the haystack, we return 0 or false. If we reach the last word in needle and find a match, then we return 1 or true. This is done recursively by calling the function within itself.

Our function, FindWordPartsInText, is somewhat complex in that it has double recursion. It will loop through each word in haystack for each word in needles.

There are two base conditions:

• The first base condition is start > WordCount ( haystack ). Which tells us that we have reached the last word in haystack, and did not find a match, so we return false.
• The second base condition is when both Left ( word ; Length ( needle ) ) = needle and WordCount ( needles ) < 2 evaluate to true. This indicates that we have found a match and reached the last word in the search criteria (needle).

There are also two defining conditions:

• The first defining condition exists when we set our Let variables, needle (note needle is different than our parameter needles) and word. Then if a match is not found, we make the recursive call with the parameters needles, haystack, and start + 1. By adding 1 to the start parameter, we are telling the function to look at the next word and getting closer to the base condition of start > WordCount ( haystack ).
• The second defining condition exists when we find a match Left ( word ; Length ( needle ) ) = needle, which tells us that the first letters in the word we are searching are the same as the the search criteria. If this evaluates to true, and there are still words left in needle (WordCount ( needles ) < 2 evaluates to false), then we make the recursive call with the remaining words in needles, thus bringing us closer to our second base condition.

Step Three – Set the Portal Filter Calculation

To set up the portal filter, simply open the calculation window for your portal filter mentioned above and enter a simple calculation that calls the custom function we created in the previous step:

IsEmpty ( Globals::SearchField )
or
FindWordPartsInText ( Globals::SearchField ; RelatedTable::Field1 & ¶ & RelatedTable::Field2 ; 1 )

You will replace Globals::SearchField with your global search field that you created in Step One, and replace the RelatedTable::Fieldx text with fields from the table in your portal. You can have as many fields there as you want, just join them with & ¶ &.

Step Four – Create the Script

Our script needs to do three things: commit, refresh and put the cursor back where it was found. It will look something like this:

Of course, your global search field will take the place of Globals::SearchField.

In layout mode, right click on the search field and click “Set Script Triggers …” A dialog will appear:

In the script trigger dialog, select the action (or event) that you want to trigger the script, and select the script to be triggered.

Select the OnObjectModify action which will run the specified script any time text is entered in or deleted from the field. Click Select and choose the script that you created in Step Four.

…And You’re Done!

Now anyone can simply begin typing in the field and their portal contents will be refined as they type without even click a button.

Do you want to make it even better? . . . .

Enhancements

Delayed Search for Fast Typing

One of the drawbacks of having scripts and calculations run every time you type a character is that it can cause serious lag time while you are typing. Especially for fast typist, this can be very annoying, especially when doing searches on large data sets and/or working over a slow network connection.

This can be easily remedied using the Install OnTimer Script script step introduced in FileMaker 10. Simply create a new script that sets an OnTimer script to call the script you made in step four (we’ll call it “Task Filter Utility”). The script will simply have one step:

Install OnTimer Script [ “Task Filter Utility” ; Parameter: ; Interval: .3 ]

Now call this script from the script trigger instead of calling the Task Filter Utility script directly. Every time a user types another character, the script will be called again, thus resetting the interval. So the Task Filter Utility script will not be called until the user stops typing for 0.3 seconds. You can of course adjust that interval. I have found .3 and .4 to be good numbers.

One more thing!!! You must also add a script step to the Task Filter Utility script. Otherwise it will run over and over again every .3 seconds:

Install OnTimer Script [Interval: “” ]

Call the Install OnTimer Script step with no script or interval specified will halt the timer.

Multiple Search Criteria

What if you want to use multiple search fields? You can do that too!

To accomplish this, the filter calculation and script become slightly more complex. The calculation will need a condition for each field joined by the “and” operator, and you will want to set up the script to take a parameter indicating the field name. This way it will know which field to return to once it has committed and refreshed.

To continue reading about this, see Dynamic Portal Filters With Multiple Criteria where I walk through this in detail.

If you have any questions or comments feel free to post a comment below or contact me directly.

Place a global search field with a script trigger in the header of your list layout for live search-as-you-type functionality

Step One: Create a Global Search Field

This step is the same as Step One in my article, Google-Like Searches In FileMaker. If you have already done this for that project, you can skip this step and use the same field. Otherwise, create a text field in any table. In the field options, set the new field to use global storage as shown in the figure below.

Under the storage tab in the field options, select the “Use global storage (one value for all records)” check box.

Step Two: Create the Triggered Find Script

Create a script that will be triggered when the user enters text. The script will be similar to the one created in the Google-Like Searches In Filemaker – Non Contiguous article except that it will also capture the cursor position within the find field and return it there when complete, and it will not present an error dialog if the found set is empty. Here’s an overview:

• Set variables to store the position of the cursor in the search field and the selection size.
• Enter find mode (do not pause or specify find request).
• Loop through each search term in the global field, create a find request and use the “Set Field” script step to set the find criteria in each field to search. (For more information on understanding this looping process, see Google-Like Searches In Filemaker – Non Contiguous.)
• Perform Find in first loop iteration and Constrain Found Set in subsequent iterations.
• Use the Set Selection script step to place the cursor back in the search field at the position stored at the beginning of the script.

The script will look something like this:

In layout mode, right click on the search field and click “Set Script Triggers …” A dialog will appear:

In the script trigger dialog, select the action (or event) that you want to trigger the script, and select the script to be triggered.

Select the OnObjectModify action which will run the specified script any time text is entered in or deleted from the field. Click Select and choose the script that you created in Step Two.

…And You’re Done!

Now the user can simply begin typing in the field and their search results will be refined as they type without requiring that the user enter find mode, know which fields to search or even click a button.

If you have any questions or comments feel free to post a comment below or contact me directly.

1. Use variables instead of global fields. Variables are stored in active system memory while fields are stored on disk. Therefore, accessing a variable is much faster than accessing fields. So, use global variables instead of global fields wherever possible except under the following circumstances:
   • global data is needed to define a relationship,
   • a user must input or modify the global data.
2. Improving performance in looping calculations. The best way to improve the performance of a complex, time-consuming script is to make the loops more efficient. One way to do this is to use local variables to store data to be manipulated in a loop. When the manipulations are complete, copy the data back to a field if it is needed there.
3. Managing script parameter contents. When writing a script that accepts a parameter, set a variable with a descriptive name at the beginning of the script with the contents of the parameter (or multiple variables if input is being parsed for multiple delimited values). Use that variable in the script where the data is needed instead of using Get ( ScriptParameter ). This will make the script much easier to understand and future maintenance much easier.
4. Use variables with global fields. When writing a script that grabs data from one or more global fields, copy the value of the global fields to variables as the first step or steps in the script. This improves the script’s portability and maintainability, because if you ever have to change the data source, you will  only have to change the first few steps in the script. It improves readability of the script, because you can instantly see what fields the script is using by glancing at the first steps.
5. More flexible portal looping. When looping through a portal, instead of using Go to Portal Row [Next; Exit after last], consider using an incrementing variable with Go to Portal Row by calculation. This allows you to Commit records and/or move to different layouts or objects without losing the portal row you are on. The script would appear as follows:
   Set Variable [ $i ; 1 ]
   Loop
   Exit Loop If [ $i > Count ( /* primary key of related record */ ) ]
   Go to Object [ /* portal object name */ ] (needed if layout has multiple portals)
   Go to Portal Row [ No Dialog ; $i ]
   …
   Do loop operations.
   …
   Set Variable [ $i ; $i + 1 ]
   End Loop
6. More flexible record looping. Use the above technique for looping through records as well, thus allowing you to jump to different layouts, records and/or found sets without losing your place or having to open multiple windows.
7. Manage smarter sorting operations. Use global variables to store and manage sorting operations. In a future article, I will describe a method of producing column headers that toggle sort order when clicked that uses global variables rather than global fields.
8. Create new records more efficiently. When scripting the creation of new related records, copy the value of the primary key to a variable, go to a layout of the related table, create a new record and copy the contents of the variable to the foreign key field. Finally, return to the original layout.  With the use of the Freeze Window step, this process can be completely invisible to the user, and you will not have to use the “Allow creation of records in this table via this relationship” feature which causes a blank record at the bottom of your portals which may be awkward and confusing to some users.
9. Parsing lists or other conglomerate data. Scripts become difficult to read and maintain when field and/or parameter data is parsed within script steps, especially when the parsed data will be used more than once. Use variables to hold parsed data. Consider the readability of the following script clips which perform the exact same function:
   1. Set Variable [ $FirstName ; LeftWords ( Contacts::Name ; 1 ) ]
      Set Variable [ $LastName ; RightWords ( Users::Name ; WordCount ( Contacts::Name ) – 1 ) ]
      Show Custom Dialog [“Hello ” & $FirstName ; “Dear Mr/Ms ” & $LastName & “, Do you want to add yourself to the ” & $LastName & ” family group?”]
   2. Show Custom Dialog [“Hello ” & LeftWords ( Contacts::Name ; 1 ) ; “Dear Mr/Ms ” & RightWords ( Users::Name ; WordCount ( Contacts::Name ) – 1 ) & “, Do you want to add yourself to the ” & RightWords ( Users::Name ; WordCount ( Contacts::Name ) – 1 ) & ” family group?”]

This technique produces an interface similar to the Google-like Search described in previous articles in this series, but instead of using a script and find mode, it uses calculated fields and relationships. This can be quite useful in portal views and requires no scripting.

Place a global search field and button above a portal for Google-like search functionality through a portal.

To create the filter, we will build a relationship between a table with a global search field (we’ll call this the base table) and the table in which we want to view filtered records (we’ll call this the target table). This relationship will be based on three calculated fields: two in the base table and one in the target table.

Step One: Create a Global Search Field

Create a text field, GlobalSearch, in the base table. In the field options, set the new field to use global storage as shown in the figure below.

Under the storage tab in the field options, select the "Use global storage (one value for all records)" check box.

Step Two: Create Calculated Fields For The Relationship

Field 1: FilterStart

In the base table, we will create a calculated field, FilterStart. This field will contain a calculation that will produce the ‘lowest’ value in the possible set of desired results.

The FilterStart calculated field will produce a Text result of a single underscore(_) when GlobalSearch is empty, otherwise it will simply be the value of the GlobalSearch field. In a sort, this value would always appear at the beginning of the desired search results.

• The FilterStart calculation, as shown in the image above, will be as follows: Case ( Isempty ( GlobalSearch ) ; "_" ; GlobalSearch )
• The calculation result is Text
• The “Do not evaluate …” check box should be checked if you want to show nothing when the search field is empty, unchecked if you want to show all records when the search field is empty.

Field 2: Filter End

Also in the base table, we will create another calculated field, FilterEnd. This field will contain a calculation that will produce the ‘highest’ value in the possible set of desired results.

The FilterEnd calculated field will produce a Text result of the contents of GlobalSearch followed by the repeated letter z. In a sort, this value would always appear at the end of the desired search results.

• The FilterEnd calculation, as shown in the image above, will be as follows: GlobalSearch & "zzz"

• The calculation result is Text
• The “Do not evaluate …” check box should be checked if you want to show nothing when the search field is empty, unchecked if you want to show all records when the search field is empty.

Field 3: FilterTarget

In the target table, we will create a calculated field, FilterTarget, which will contain a value list of values from each of the fields we wish to search on. For instance, if we have the two fields, Name and Notes, and we want search on each of these, our calculation would be as follows:

• The FilterTarget calculation will be as follows: Name & ¶ & Notes

• The calculation result is Text

Step Three: Build The Relationship

Add a new table occurrence of the target table to the relationship graph.

Define a relationship between the base table and the new occurrence of the target table.

The relationship will be based on two criteria:

• FilterStart in the base table is less than or equal to FilterTarget in the target table.
• FilterEnd in the base table is greater than or equal to FilterTarget in the target table.

The result in the relationship graph should look something like this:

Step Four: Add Layout Objects

You can then place the global field and a search button which simply performs a commit.

In the Button Setup Dialog for the search button, simply select Commit Records/Requests. No script necessary.

Below the search field, add a portal to the new table occurrence you just created in the relationship graph.

That’s it! Now the user can simply enter search terms in the search field and view their results in the portal.

Limitations

One limitation of this method is that it will only match on the first word or words of the target table’s fields. For instance, typing “Doe” in the search field will not match a record with Name “John Doe”. One possible solution would be to substitute spaces in the FilterTarget calculation with carriage returns:

Substitute ( Name ; " " ; ¶ ) & ¶ & Substitute ( Notes ; " " ; ¶ )

This allows searching on any word. However, this introduces a new limitation; You may now only match on single search terms. Any muliple word searches will result in no matches. A possible solution to this would be to perform the same substitution in the FilterStart and FilterEnd fields. The problem with this solution is that our results will be based on an OR search rather than an AND search. In other words, if “John Doe” is typed as the search term, the resulting set will be all records with “John” and all records with “Doe”.

To achieve a true Google-like search showing all records that contain a search phrase within a field, we would either have to introduce some scripting, perhaps using triggers, or produce calculations with much greater complexity which could greatly increase file size (due to indexing) and goes beyond the scope of this particular article.

So, use this method when matching from the beginning of the target field or matching on a single term is sufficient.

For a much more robust and streamlined solution that eliminates all of these limitations using the Portal Filtering feature introduced in FileMaker 11, see Dynamic Portal Filtering While You Type.

If you have any questions or comments feel free to post a comment below or contact me directly.