FileMaker 12 Archives

Using SQL to Duplicate a Record Set

July 26, 2013 by Anders Monsen Leave a Comment

Duplicating a single record in FileMaker is simple. However, what if you are faced with duplicating not just a record, but all related child records? I wanted to accomplish this without modifying the graph, and without looping through records to build my data sets.

For my customer’s development project, I had a table of tickets with associated time, material, and other related records where sometimes only minor changes to a big ticket were needed. Rather than create the related records from scratch each time, we needed a fast and simple method to duplicate key elements from each related table, and at the same time retaining the relation information from the newly created ticket or parent table.

I chose to use FileMaker Pro 12’s native SQL to build arrays with field/value pairs. Unlike a language like PHP, which has a built in logic for handling arrays, in FileMaker these have to be constructed using certain text-character separators, and therefore are not iron-clad arrays. However, once I constructed the, um, pseudo-arrays I could loop through the array values and set my new records.

Although it’s quite possible to set up an interface where you list all fields in related tables that need to be duplicated, I chose to set my field names within the SQL statement. Any changes to fields then need to be made inside the SQL statement, although with a little more programming my solution could conceivably adopt the field storage option. For the sake of this example, I elected to keep the syntax in the SQL statement fairly simple (no custom functions, although the GetFieldName function accounts for any Table Occurences name changes). If field names change or you want to add more fields, the SQL needs to be updated. Also, in regards to your own fields, you need to be aware of any reserved names which will cause the SQL to fail.

Download example file Tickets.fmp12.

The Related Table Query

To begin with, I set a variable with the original ticket number so I could message the user after the script finished. Then I used SQL to select the fields for each related table, and set my array. The example from the time table shows this format. I concatenated the field name and the value from the field. For number, date, and time fields I found that I needed to use the double pipe concatenation symbol instead of the plus sign; using the plus sign for these non-text field types caused some strange behaviors, usually stripping out the value.

In order to later parse out the values from my pseudo-array I added “^” as the field separator. I picked this character I didn’t anticipate it to exist in any field, unlike commas. You may notice the row separator uses a tilde or ~ symbol, instead of a pilcrow or return symbol which is the default separator. The reason for this is explained below in the script parameter section for the subscript the creates the new records.

Let([
query = "
SELECT
'" & GetFieldName ( TIME_ENTRY::NameFirst ) & "|'+namefirst,
'" & GetFieldName ( TIME_ENTRY::NameLast ) & "|'+namelast,
'" & GetFieldName ( TIME_ENTRY::TimeBegin ) & "|'||timebegin,
'" & GetFieldName ( TIME_ENTRY::TimeEnd ) & "|'||timeend
FROM time_entry
WHERE id_ticket = ?";
result = ExecuteSQL( query ; "^" ; "~" ; TICKET::ID )
];
result
)

With my result sets in hand, I duplicated the ticket with the “Duplicate Record/Request” script step. Since I used Get (UUID ) for my primary table ID I had to uncheck the “Do not replace existing value of field (if any)” check box, otherwise the UUID would also get duplicated.

Alternate Duplicate Record Option

If you do not use the “Duplicate Record/Request” you can perform the same action using New Record/Request. First, you would need another SQL statement, grabbing only select items from the record to be duplicated.

Let([
query = "
SELECT
'" & GetFieldName ( TICKET::TicketDescription ) & "|'+ticketdescription,
'" & GetFieldName ( TICKET::TicketName ) & "|'+ticketname,
'" & GetFieldName ( TICKET::ID_Customer ) & "|'+id_customer
FROM ticket
WHERE id = ?";

result = ExecuteSQL( query ; "¶" ; "" ; TICKET::ID )
];
result
)

In order to set the fields you loop through the SQL result, adding pipe separators between the fields (or another separator that later can be switched). With my field/value pair I could use the “Set Field by Name” script step.

Set Variable [$itemCount; Value: ValueCount( $ticketResult )]
Loop
  Exit Loop If [Let ( $i = $i + 1 ; If ( $i > $itemCount ; Let ( $i = "" ; True ) ) ) ]
  Set Variable [$row; Value: GetValue( $ticketResult ; $i )]
  Set Variable [$field; Value: GetValue( Substitute( $row ; "|" ; "¶" ) ; 1 ) ]
  Set Variable [$row; Value: GetValue( Substitute( $row ; "|" ; "¶" ) ; 2 )]
  Set Field By Name[ $field; $value]
End Loop
Commit Records/Requests []

After Parent Record duplicated

Next, I set variables with my new ID and ticket number. The new ID would be used in the subsequent related records. I called a subscript for each related table, passing in the ticket ID, the layout of the related table, and the SQL result for that related table’s values, which is spelled out in the example file. In the subscript I used a return delimited set of values for the script parameters. To address the point raised above with the tilde separator, if I had used the default return delimiter between rows in my ExecuteSQL my script parameter method would strip out everything but the first row or record. To prevent this I used the tilde, and then in the subscript added back the pilcrow/return character as the row separator. To validate the values in my Data Viewer as I tested the scripts I have this in two actions.

Set Variable [$SQL_Lines; Value: GetValue( Get( ScriptParameter ); 3)]
Set Variable [$SQL_Result; Value: Substitute( $SQL_Lines ; "~" ; "¶" )]

If you use a different method of passing multiple parameters, you just adapt the SQL and subscript to handle this. My goal with the example file is to keep it simple with no custom functions.

The subscript, “Duplicate_RelatedTable”, goes to the relevant layout, then uses a nested loop to create the record and set the values. Since the ticket’s time entry portal might contain multiple values, I needed to create a new record for each one, and set the relevant values from the previous parent record. Since I used the same Foreign key naming convention this let me use Set Field by Name for the Ticket ID in the related tables.

Go to Layout [$Layout]
Set Variable [$itemCount; Value: ValueCount( $SQL_Result )]
Loop
  Exit Loop If [Let ( $i = $i + 1 ; If ( $i > $itemCount ; Let ( $i = "" ; True ) ) ) ]
  Set Variable [$row; Value: GetValue( $ SQL_Result; $i )]
  New Record/Request
  Set Variable [$ForeignID; Get ( LayoutTableName ) & "::ID_Ticket" ]
  Set Field By Name[$ForeignID; $TicketID // from script parameter ]
  Set Variable[ $record; Value: Substitute( $row ; "^" ; "¶" ) ]
  Loop
    Exit Loop If [Let ( $j = $j + 1 ; If ( $j > ValueCount( $record ) ; Let ( $j = "" ; True ) ) )]
    Set Variable[ $data; GetValue( $record ; $j ) ]
    Set Variable [$field; Value: GetValue( Substitute( $data ; "|" ; "¶" ) ; 1 ) ]
    Set Variable [$row; Value: GetValue( Substitute( $data ; "|" ; "¶" ) ; 2 )]
    Set Field By Name[$field; $value]
  End Loop
Commit Records/Requests []
End Loop

The counter is handled within the Exit Loop condition. As there are multiple loops I wanted to make sure I cleared out all the values of the counter variable after each section within the loop completed.

With all this in place, the user only needs to click on the “duplicate record” button, and the script runs. Once done they see a message as to which ticket number was duplicated and the new ticket number. Success!

GTRR and the Modal Dialog Window Style

March 20, 2013 by Darren Burgess 16 Comments

Here is small development gotcha regarding FileMaker 12 advanced window styles. If you create modal dialog window, and then execute a Go To Related Record (GTRR), creating a new window from the modal dialog, the window style of the new window must also have an advanced style set to modal dialog.

You might want to do something like this if you present a modal dialog to the user and then need perform some off-screen processing in a new window after the user performs an action in the dialog. If the new window is not set properly as a modal dialog, the GTRR script step returns an error 1 (User Canceled Action) and the GTRR does not occur. Seems to me this error message does not clearly lead to an understanding of the actual cause of the error, hence the gotcha.

Step-by-Step

Here are a few screen shots to explain. This first image demonstrates the error that occurs when the GTRR new window is not specified as a dialog:

Now, let’s modify the script and specify a modal dialog for the new window in the GTRR script step:

And when we run the script again the GTRR step can successfully execute:

Documentation

Although the error code is perhaps misleading, this scripting behavior is not undocumented. In the FileMaker’s online documentation of the advanced window style feature does offer an explanation (see second to last bullet point):

Any operations that attempt to open a non-dialog window will fail when a dialog window is open (for example, using a script trigger to run a script from another file). OnTimer scripts do not execute when a dialog window is open unless the script launches from the dialog window itself.

Using Tab Panels for Interface Popups

February 22, 2013 by Darren Burgess 6 Comments

FileMaker 12 includes significant changes to tab panel objects that make using pop-up overlays far easier to implement. Now, tab panels are “less sticky” and can be moved freely around a layout without the risk of inadvertently picking up objects that were not intended to be contained in the tab panel. Our colleague Daniel Wood over at Digital Fusion offered an excellent detailed description of this new behavior in his blog post Cool things to do with Tab Controls in FileMaker 12.

In particular, I was intrigued with the idea of using invisible tab panels to provide pop-up data entry overlays on FileMaker user screens. Here is an example from a customer database that uses an overlay to display a portal that allows users to select a Contact record and add it to a list of related contacts in a Company record:

Step 1: User clicks the ‘+’ button to expose the data entry UI in a invisible tab panel

Step 2: User selects a contact records from a filtered portal.

Using this design pattern offers some really excellent advantages both for the developer and end user:

No new layout is required to provide a pop-up window
Space is saved on the current layout
User is provided a data entry UI, visually aligned to its purpose
Opacity features of the object give the interface a clean, modern look
The interface is intuitive and easy to understand

Of course, no pro/con article would be complete without the ‘con’ side. And in particular, this technique can have the effect of increasing the maintenance burden on the layouts where it is used. As stated previously, FileMaker 12’s improved tab panels can be freely moved around a layout and they will not ‘grab’ objects from the layout. The reverse, however, is not the case. That is, if an object that is within the borders of a tab panel, but not part of the tab panel, is moved – even one pixel – it will become part of the tab panel and from then on move with it. These screen shots illustrate the point:

The Title field in this screen shot is behind the tab panel, but is not a part of the panel. The panel can be moved and the Title field will stay put.

The Title field has lost its independence from the tab panel by moving the Title field 1 pixel.

Further complicating layout maintenance is the fact that objects behind the tab panel are difficult to access without first moving the tab panel. Any modification of the Title field, for example, would require the developer to first move the tab panel, make changes to the field and then move the tab panel back. This adds an additional layer of inconvenience to layout maintenance. And it requires the developer to remember to put the tab panel back in place before shipping an upgrade to the solution!

It is of course possible to access the field under the tab panel without moving the tab panel. This is accomplished by holding down the command key (OS X) or control key (Windows) while in layout mode and using the mouse to drag a rectangle around the object until it is fully enclosed. This method of layout object selection selects only objects fully enclosed within the rectangle and can be used to precisely select single objects on complex layouts.

Given this additional maintenance burden, I would conclude that hidden tab panels used as interface overlays should be used carefully and sparingly. Developers should consider the complexity of the layout, knowing that multiple objects around and behind the tab panel will increase difficulty in maintaining the layout. In these cases, a dialog window may be the best choice for providing this type of data entry interface to the user. That said, simple layouts may be a perfect match for this technique and effective use can enhance the appearance and usability of a layout.

Special thanks to MightyData customer Younger Partners for granting permission to use images from its database.

Scanning Barcodes with FileMaker Go – Part 3

January 17, 2013 by Anders Monsen 7 Comments

In this third and last post (see Part 1 and Part 2) we look at pulling data into FileMaker Go using magnetic stripe readers. In principle this is very similar to the barcode method. A button in the FileMaker database calls an Open URL script step. This Open URL step calls the iOS app – in this case CardSwipe – and includes a callback function to the FileMaker database to run a script. The script then receives the input from the card.

"ccqfm://?" & GetAsURLEncoded ( "fmp://$/" & Get ( FileName ) & "?script=swipe¶m=" )

With the swipe action, instead of URL encoding each character, we tried using FileMaker’s native function – GetAsURLEncoded(), and this seemed to have no ill effects. Once the button is pressed, the iPhone (or iPad) switches over to CardSwipe, which is then ready for you to swipe the card.

Once the card is read by the iMag device, it immediately switches back to FileMaker and runs the “swipe” script, which reads the data. Each card that is read encodes its data differently, so extracting information is a matter of trial and error, and the text parsing process must be coded specifically to each card. Characters like “^” often separate key pieces of information. Credit Card numbers get picked up, so security becomes an issue once you read these, as storing the numbers is never recommended. Once again we split the scripts into three separate pieces to assist with any troubleshooting.

A button calls the first script, which has one purpose: Perform script #2
This script opens the iOS app using the Open URL script step, which has a callback to script #3
The script that receives the data, using a script parameter in step #2

Issues that arose in this process centered around getting the proper URL. Luckily CardSwipe has great developer documentation. However, in the example, the one that referred to a local file (vs. one on the server) used the tilde sign instead of the $ sign, which looks for a file on the device, rather than an already open file. This sometimes caused issues with locating the right file, but when switched to the $ sign the callback worked seamlessly.

The iMag Pro hardware is attached to the iPhone through the USB connectors at the bottom. I found that this connection doesn’t remain firmly seated in the iPhone 4, but works well with an iPad. The iMag came with an adapter that is supposed to allow it to stick more firmly on the iPhone, but this didn’t work with the 4 model. If the card reader doesn’t separate from the iPhone or iPad, this method is perfect for quickly reading data from a card with a magnetic stripe.

Conclusions

Getting data quickly into an iPhone/iPad by scanning barcodes or reading cards with magnetic stripes makes your process mobile. This method can be used to not just for credit card actions, but any card that can be swiped, such as ID cards. Once the data is in your app, the next question is, “what’s next?” Some time back I wrote about building a real world FileMaker app using barcode scanning and Insert from URL to reach out to external databases and pull book information using just the barcode. You also could use this process to scan people for admission to events, read student information during school lunches, or attendees at a conference. Card readers might offer ways to get data about employees, build a POS device, and check drivers licenses or insurance card for medical offices. With a small investment (or in the case of pic2shop – no cost whatsoever) your business can be set up to read data using iPhones without having to type in data, speeding up the process and eliminating typos.

SQL Reserved Words in FileMaker

December 20, 2012 by Anders Monsen Leave a Comment

Unless you are a fan of Steve Ditko’s the Question, debugging FileMaker 12’s ExecuteSQL function is an art in itself. Even using a Custom Function to display the exact error message in the Data Viewer, as with Andries Heylen’s debug function, you may not always get the full story. As MightyData’s Darren Burgess wrote about SQL debugging nightmares, there are many pitfalls for FileMaker developers exploring the new world of SQL from within the calculation dialog.

The first key to SQL peace of mind is a valid SQL statement. Reading Beverly Voth’s excellent “Missing Manual” on the ExecuteSQL function, or even FileMaker’s help page, provides tips and references for checking your SQL statement. However, even a simple “select * from tablename” can result in a question mark if the tablename is a reserved word. As the FileMaker ODBC Guide indicates, there are quite a few reserved words, and these need to be escaped in your query.

Memorizing these reserved words is no easy task. After running into this issue once, I built a small FileMaker solution that’s fairly easy to integrate into a database file, so I can look for any reserved words in tables and fields. This might be the geek equivalent of stupid pet tricks, though it also has the added feature of exposing how to query FileMaker’s internal tables and fields with SQL.

Checking for reserved words with this method requires just one completely portable script. Using the built-in “FileMaker_Tables” and “FileMaker_Fields” meta tables, the queries pull a list of table occurences and the check these against the list from the ODBC Guide.

"Select TableName from FileMaker_Tables"

Checking whether each table exists in the reserved word list is carried out by a PatternCount function. While I initially had this as a Custom Function, I brought it into the single script to make it more portable.

Once the tables are checked the script moves on to the fields, where it checks these in the base table names. First we get a distinct set of the base tables, and then using a sub-select method inside a loop, go through each table and list the fields. Then each field is checked to see if it’s in the reserved word list.

"Select Distinct BaseTableName from FileMaker_Tables"

"select count(FieldName) from FileMaker_Fields 
where TableName = ( SELECT Distinct BaseTableName 
FROM FileMaker_Tables WHERE BaseTableName = '" & $baseTableName & "')"

I added a visual progress bar from Tim Cimbura, as a large table or database can take a while to check. The results appear in a simple web viewer. As the solution is unlocked you can change this to a table using the virtual list technique, or any other visual method you prefer. To render the progress bar and results just copy the two web viewers to a layout of your choice, along with the button to trigger the script.

I discovered the issue when I had a table called “transaction” which is a reserved word. There are a few words that we might use in tables or fields without thinking they’ll affect development, but if you start using SQL, checking for any reserved words might help at least eliminate this issue from any queries. The sample file has a couple of deliberate reserved words in tables and fields, and one table without any fields.

SQL_Reserved.fmp12

Restoring FileMaker 12 Backups With Managed Containers

November 28, 2012 by Darren Burgess 18 Comments

In the course of my work on a customer project, I discovered that the links to externally stored container fields with a FileMaker Server 12 backup can break if the backup is not restored properly. After trying a number of different scenarios, I have come up with a list of circumstances where container fields will remain intact and when they won’t.

All examples are using a Windows based FileMaker server and apply equally to both secure and open external container storage. Understanding these situations will help you properly restore FileMaker 12 files that use external container storage.

In case you need to get some basic knowledge about FileMaker Server 12 and the new features related to backup and enhanced container fields, here are some great resources to get you started:

Using Container Fields in FileMaker 12 (log in to FileMaker TechNet to download)
FileMaker Server 12 Configuration Guide (also found at TechNet)

Circumstances where external container data will break (container fields will show files as missing):

Uploading FMP12 backup files with the FileMaker Server admin console.
Opening backup files in FileMaker Pro — ie, not hosted by a server
Opening files in FileMaker Pro that were removed by the FMS admin console

Circumstances where external container data will remain intact:

Copying FMP12 backup files, with the respective container data folder from the RC_Data_FMS folder, to the FileMaker server data directory, then opening the files with the admin console.
Copying backup files, as above to a different FileMaker server
Downloading the files with FMS admin console, then opening the files in FileMaker Pro (e.g., not hosted). In this case, the databases and remote container file directories are downloaded together, so references to externally stored container fields are preserved.
Downloading the files with FMS admin console, then uploading to another server, or the same server, with the admin console. Again, the act of downloading the files brings the container file directory along with it. Uploading these same files will restore the container data on the FileMaker server.

So to conclude, when restoring backups of FileMaker files that use external container storage, copy the files (don’t upload with the Admin Console) to the data directory of your FileMaker server. In addition to the FMP12 files, copy the respective subfolder for your backup file in RC_Data_FMS to the RC_Data_FMS folder in the server data directory. Note that on Mac OS servers, permissions for the FMP12 file and container directories need to be reset when copied to the data directory and not uploaded with the console.

Scanning Barcodes with FileMaker Go – Part 2

November 8, 2012 by Anders Monsen 35 Comments

This is part 2 in a 3-part series.

In Part 1 of this 3-part post examining bar codes, magnetic strip readers, and FileMaker Go, we looked at the tools necessary to integrate all the components into one FileMaker Go solution. We will focus on just the bar code and FileMaker portion here. The necessary resources are listed in part 1, and the card reader portion will appear in part 3.

To create the FileMaker Go app, we create the database up in FileMaker, and then place the app on the iPhone or iPad (via iTunes or web page, for example). Since the device is mobile and connection to a networked file can get iffy, there are advantages to having all the action local on the device, at least initially. Later, you can sync the data with the server though a variety of methods (GoZync, MirrorSync, etc). We created a simple test database (see demo file below). This file contains both the scanning and bar code reading capabilities.

The Interface

The database front end is fairly simple, and consists of a field that stores the data, plus three buttons. The Scan button handles a single scan, and could be set up for either pic2shop or CNS Barcode. The Swipe button opens the CardSwipe reader app, while the Multi-Scan button works only with CNS Barcode.

Pic2Shop

The first app, Pic2Shop, scans a single barcode, or EAN number. (EANs are 13-digit barcodes, but pic2shop will still scan older ISBN10 numbers). A good starting point is to create a button that launches the script, such as “Scan”. This script in turn launches a script to open the barcode app. In the sample database we have both the pic2shop and CNS Barcode available, and you can comment out one or the other while testing each method. We have a parameter in place to handle the two different buttons, the individual
Scan”, and the “Scan Multiple”. The “Open barcode app” scrip contains the key line, which is the “Open URL” script step. Inside this step you build the URL scheme to send to the iOS app – pic2shop or CSN barcode. The callback requirements must have the EAN string in the url, and for FileMaker 12 this looks something like

pic2shop://scan?callback=fmp://$/GoInput?script=Scan¶m=EAN

This url breaks down as follows:

the app: pic2shop://scan?callback=
the callback app, in this case Filemaker: “fmp://” for FileMaker Pro 12
the location of the FileMaker app: $ for an application already open on the phone (~ to open a database on the device, and hostname if hosted remotely)
the file name: GoInput
the script: Scan
the parameter: EAN (the barcode read by the app)

The url needs to be encoded to handle certain characters, and so the URL would change to this:

pic2shop://scan?callback=fmp%3A//%24/"&Get(FileName)&"%3Fscript%3DScan%26param%3DEAN

Characters	Encoded
Dollar $	%24
Space	%20
Equals =	%3D
Ampersand &	%26
Colon :	%3A
Question Mark ?	%3F

The last piece, the “Scan” script, handles the data received from the device. Since the barcode resides in the parameter, your script simply needs to insert the data into a field. Before looking at this script, we’ll switch over to see how CNS Barcode behaves.

CNS Barcode

The only variation we used was to enable the multiple scanning option on CNS Barcode, and this was set up via the script parameter and a Case statement inside the URL. If you are scanning multiple barcodes, having to click the scan button each time can slow down the process. However, one caveat is that CNS Barcode in this mode appears very sensitive and will scan very quickly and often the same barcode multiple times, and in some cases scanning a portion of the barcode that you don’t want. Instead of the full EAN 13 you might end up with a five digit number. When scanning multiple barcodes, all the barcodes appear in one return-delimited list in the clipboard. This list then is pasted via the script into one field in FileMaker. To split out the items, you could either extract the unique values in the script or using a custom function, and then loop through the unique values to create your records in FileMaker. The CNS Barcode Open URL script step then looks like this:

"cnsbarcode://scan?" &
Case ( $param = "multiple" ; "scanmultiple=yes&" ) &
"launchurl=" &
GetAsURLEncoded ( "fmp://$/" &
Get ( FileName ) &
"?script=scan¶m=::barcode::" )

A simple Case statement checks whether we chose a single or multiple scan. Next, add the launchurl to the FileMaker database (the name of the file), and the ::bardode:: parameter (just like EAN for pic2shop). The Open URL calculation is broken down into respective parts, but really is one continuous URL.

The Scan script itself places the barcode into a text field. With multiple data you would loop through and create multiple records.

Set Error Capture [ On ]
Go to Layout [ “Input from Barcode” (zSystem) ]
If [ Get( TotalRecordCount ) = 0 ]
 New Record/Request
End If
Set Variable [ $code; Value:Get(ScriptParameter) ]
If [ not IsEmpty($code) ]
 #
 If [ PatternCount ( $code ; "Multiple" ) ]
   Paste [ zSystem::gScanData ]
 Else
   [ Select; No style ]
 Set Field [ zSystem::gScanData; $code ]
End If
Commit Records/Requests
#
End If
#
Go to Layout [ original layout ]
Set Variable [ $Barcode; Value:zSystem::gScanData ]
Loop
  Exit Loop If [ Let ( $i = $i + 1 ; $i > ValueCount( $Barcode) ) ]
  New Record/Request
  Set Field [ ScanInfo::inputText; GetValue( $Barcode ; $i ) ]
  Commit Records/Requests
End Loop
#

This script creates a new record for each scanned item. In theory once you have a valid barcode you can reach out to other databases and get additional information if required, or a custom barcode would be written to contain all the information that you need.

Note the number in the red circle at the bottom. When scanning multiple items you can see how many are currently in memory. You can view the details by clicking on this icon.

Note the first item in the list, which contains ALL the numbers from the barcode, including the smaller barcode section, whereas the others are all ISBN numbers. In your database if you are scanning books, you would need to validate the numbers before trying to pull additional information.

Finally, when you click done, the app switches back to FileMaker via the callback section in the Open URL script step.

CNS Barcode’s multi-scanning action seems to quickly scan the same number multiple times, and also sometimes scans barcode numbers other than the ISBN10 or 13. Possibly there are some settings that might limit such behavior. Otherwise the multi-scan mode significantly speeds up getting that barcode data into FileMaker without switching back and forth between the apps.

The sample file: GoInput.fmp12 applies also to the magnetic strip reading process in part 3.

GoInput.fmp12

Using Barcodes and Magnetic Stripes with iOS

October 10, 2012 by Kirk Bowman 10 Comments

The other day, Anders Monsen and I were prototyping a custom iPad app to help a stage company manage work crews for large arena events. Each event has multiple shifts (7am, 7:45am, 8:30am, etc.). At the start of each shift, several employees need to clock-in quickly and receive their work assignments.

We researched two ways to identify an employee with an ID card: scanning a barcode and reading a magnetic stripe. We found three apps in the App Store that can integrate with FileMaker Go, two for scanning barcodes and one for reading a magnetic stripe using a third-party card reader.

I really enjoy exploring the usability of a solution during prototyping. In this article, I will share some of my observations for this use case. Anders will explain the technical side of integrating FileMaker Go and iOS apps with URL scripting in a separate articles.

Observations

For the prototype, we created a simple layout with two buttons, Scan and Swipe. The purpose of each button is to switch to the companion iOS app, perform the scan/swipe, and return the data to a text field in FileMaker Go.

Scanning / swiping iOS prototype

Some of my preliminary observations include:

Holding the iPhone – I am right-handed so I visualize the user holding the device in his left hand and the ID card with his right. I liked cradling the iPhone in my left palm (portrait orientation), allowing my thumb to tap the buttons.
Holding the iPad – With the iPad, weight is the determining factor. I liked balancing it with my hand halfway between the top and bottom edges (portrait orientation). Again, my thumb was available to tap the buttons.
Button Design – Larger buttons that are easier to tap. I made the button height 50 points for the iPhone and 72 points for the iPad. To tap a button with a thumb, I made the buttons extremely wide, 75% of the iPhone screen and 85% of the iPad screen.
iPhone Case – For this app, a case cannot block the camera, which scans the barcode, or the dock connector, which connects to the third-party card reader. A thin case like the Fitted for iPhone 4 from Speck works nicely.
iPad Case – Besides working with the camera and dock connector, a case will need to help the user grip the iPad. A thin case with a rubber texture like the NGP Semi-Rigid Shell from Incipio is a good match.
Camera – For scanning a barcode, the device needs a good auto-focus camera. For this solution, we need either an iPhone 4/4S/5 or Retina display iPad. Of course, reading a magnetic stripe does not have the same requirement.
Barcode App – For scanning barcodes, we tested pic2Shop (free) and CNS Barcode ($9.99). I selected CNS Barcode because it is designed for FileMaker Go, scans more accurately, and supports scanning several barcodes in rapid succession.
Mag Stripe App – Reading a magnetic strip requires an app that can communicate with an external card reader and supports URL scripting. CardSwipe ($15.99) is designed for FileMaker Go and is the only app we found that meets these requirements.
Card Reader – The iMag Pro MagStripe Reader ($60-$70) is the only card reader that CardSwipe supports. It plugs into the dock connector on the iPhone and iPad. So far, my only concern is it can become slightly unseated when aggressively swiping a card, especially with the iPhone.

In the end, our customer decided to scan barcodes with CNS Barcode. And, now we have knowledge of using magnetic stripes for future projects.

Scanning Barcodes with FileMaker Go – Part 1

October 8, 2012 by Anders Monsen 10 Comments

This is Part 1 of a 3-part series.

A smartphone today does far more than make calls. With a variety of apps available we have access to a world of information at our fingertips, or hours of distraction. Productivity is vital from the business side of mobile computing. Reading and storing data is one powerful aspect, but collecting data on the device makes our information interactive. Getting data on to the device quickly and error-free becomes imperative for any business.

FileMaker Go is a perfect tool for storing data on a iOS device like the iPhone and iPad. But, the lack of a physical keyboard makes data-entry a slow and tedious affair. Scanning bar codes or reading magnetic cards like credit cards, drivers licenses, and so forth, enables fast access to data. This three-part article first will discuss the requirements to pull data into FileMaker Go from an iOS device, and then demonstrate two different methods of adding data.

First, your iOS device will require some software and hardware. FileMaker Go is free, though you will need to develop your database using FileMaker Pro on the desktop. To read bar codes I have used two different apps. One, Pic2Shop, is free and performs its task fairly well, though it is limited to scanning one item at a time. CNS Barcode costs $9.99, but adds a variety of features such as multi-scanning, and also can create bar codes.

To read magnetic card strips the cost climbs. You will need a paid iOS app ($15.99), CardSwipe, and a small device that plugs into your iPad or iPhone (this was tested prior to iPhone 5), such as iMag Pro, around $60. With iMag Pro you can run cards through the reader from the context of the CardSwipe software.

The glue that binds the app and FileMaker Go database is called a URI scheme or protocol. When you build your FileMaker app (you’ll have to wait for part 2!), you create scripts that call the iOS app with their URI scheme. In that URI scheme you then have a callback action to FileMaker using its URI scheme, indicating the database name and the script to run.

With the exception of Pic2Shop, you must pay for the companion app. In part 2, we’ll switch over to FileMaker and see how to set up the database, and how to pull in the data without typing a single character by scanning a barcode. Part 3 will cover reading information from magnetic strips, such as drivers licenses or credit cards.

FileMaker SQL WAN Performance Trouble

September 6, 2012 by Darren Burgess 10 Comments

One of the new all-the-rage features in FileMaker 12 is the new ExecuteSQL function. When I heard about this new function and its potential to massively simplify the graph, I decided I did not want to wait to convert my customer’s School Management Solution to FMP12 and elected to use the free BaseElements plugin to access the FileMaker internal SQL API in FileMaker 11.

Some months and many hours of learning curve time later, I was able to successfully implement SQL queries in many areas of my customer’s solution. Since I am using a plugin in a FMP11 solution, I took advantage of the opportunity to use SQL Insert, Update, and Delete queries to create, modify and delete records without having to create any schema to support the actions. As a result, I created very few additional relationships and was able to write significantly shorter scripts as I implemented several new features for the customer.

After deploying the SQL-enabled solution, we encountered a nasty performance issue over the WAN. This solution has a person table with 20,000 records and a related Event table with 80,000 records. Every time the remote WAN user added a new event, FileMaker would freeze for as long as 5 minutes. This issue could not be replicated for LAN users.

Debugging revealed that after the event was added, the very next script step that performed a SQL Select on the Events table was the source of the long delay/freeze.

Here is the original offending SQL code, part of a script step whose intent was to capture the ID of an event for purposes of conditional formatting and data entry control. This query was part of a more complex calculation, however for our purposes the simplified SQL statement is sufficient:

SELECT id
FROM Events
WHERE id_Person = $personID AND status = 'No' AND method <> 'NonEvent'

In examining this code, I concluded that it could be simplified by adding a field to the Events table, Events::eventIsValid. This is a stored autoenter Boolean calculation that resolved to ‘1’ when the status = ‘No’ and method ≠ ‘NonEvent’. I then added that query to the SQL statement, thereby reducing the query by one condition:

SELECT id
FROM Events
WHERE id_Person = $personID AND eventIsValid = 1

Unfortunately, this change did not significantly affect WAN performance, as there was still a minutes-long delay when this calculation was evaluated. At this point, in the interest of getting this bug fix delivered to the client as quickly as possible, I elected to abandon SQL as a technique for this particular function in the database. To that end, I returned to a good, old-fashioned, multi-predicate FileMaker relationship that would serve the purpose of finding all of the related event records that were considered valid as per the business rules of the customer.

Lesson learned in this case is to consider all possible solutions when debugging and optimizing a solution. In this case, the new, fancy FileMaker feature would have required more time than was of value to the customer, and so I resorted to a more conventional approach rooted in the pure FileMaker techniques. Ultimately the priority was to serve the customer with a timely resolution of the bug – a simple case of the end result (delivering value to the customer) trumping a focus on the underlying technology.

Stay tuned for a followup to this blog post where I elaborate on the technical solution to the slow SQL query.