Blog - MightyData

FileMaker Go: Field Interaction Behavior

April 18, 2013 by Darren Burgess 5 Comments

Thought I would share a quick tip that I learned while working on a mobile database application for a customer.

Naturally there are many user interface behavioral differences between the desktop and mobile versions of FileMaker. This one involves the display of a field when the field has focus. Here are two screen shots of the same layout as displayed in FileMaker desktop vs. Go.

Text Field With Focus

Note, that when the field has focus in Go, it mysteriously expands.

There may be a legitimate purpose for this behavior, however it may also break the design of the layout on the mobile device. It also represents a departure from normally expected UI behavior on a mobile device. In my work with mobile FileMaker applications, I strive to have the database behave as closely as possible to what users normally expect from iOS apps.

A Simple Fix

Fortunately there is a simple fix for this. Simply apply a vertical scroll bar to the field using the layout inspector (data tab – “Include vertical scroll bar”). Then set the line characteristics for the field to “None”, to prevent the vertical scroll bar from displaying. Now, in FileMaker Go, the field no longer expands when it has focus.

Do you have any other simple FileMaker Go tips that help in development? If so, share them in the comments!

Virtual List in 3 Easy Steps

April 12, 2013 by Anders Monsen 19 Comments

The Virtual List technique has been around for a few years (created by Bruce Robertson ca. 2009). Over time and with changes to FileMaker, implementation of this technique has evolved. Knowing how to set up and use virtual lists will help with creating certain reports, especially if you draw the values from ExecuteSQL statements in FileMaker Pro 12.

Articles (see Lisette Wilson’s Taming the Virtual List, Part I and Part II) and examples are fairly easy to find. So why write another one? Many examples and articles include use-specific cases to illustrate the virtual list. I’m going to attempt to boil this down to three simple steps. Once you draft the basics of this process into a FileMaker file, you can use this as a template for more complex reports. You can re-use the draft as a template, copying the table and script from one solution to another. This technique requires one dedicated reporting table, plus a script to populate some global variables. The fields in the reporting table are set up as calculation fields that get their values from the list within the global variable.

To illustrate the process, I added a table to the Contacts Start Solution as a sample database, which you can download here.

Step 1: The Reporting Table

When you visualize the report itself, it usually consists of rows and columns. Each row is a record in the table, and each column corresponds to a field. At minimum, you need two fields. One field is a hidden id field; it’s a crucial component of the report, but is not displayed. The second field appears on the report, and will contain data generated by the script. This simple two column example below could have been created as a SubSummary report. However, the power of the virtual list is that it allows you to combine data from many tables, something not easily within reach by a SubSummary report. In my demo file I have three fields, one for the id and two for the actual column data (state and count).

Step 2: The Fields

Of the two fields mentioned in the table, the id field governs what shows up in the data field or column. This field is set up as a number, starting at one, with each subsequent record incrementing by one. Most reports will have less than 100 rows, so a general rule of thumb is to just create 100 records (however, if your report demands more rows, just create more records). If you create a table view with the report layout, the id field should show 100 rows, with values from 1 through 100.

The second field is the first column of the report. Reports can have multiple columns, so new fields are created as needed, using the same principle. To keep it generic, in my report template I simply named the fields “column1” and “column2” and set them up as text fields. If you need to format data as numeric values, i.e. $43,000.00, this can be done with text formatting functions. The column field is set up as an unstored calculation: GetValue ( $$COLUMN1 ; id ). This will make more sense after Step 3, the script that creates the $$COLUMN1 global variable. Before the $$COLUMN1 variable exists, the “column1” field has no value. Once the variable is created, the field calculates from the content of $$COLUMN1, which is a list, and extracts the value that corresponds to the id number for that row. This is the magical part of the virtual list technique.

Assume that $$COLUMN1 contains these values:

Banana
Apple
Orange
Pear
Grape

This list has five values. For the first five records, as indicated by the values 1 through 5 in the id field, the GetValue() function will pull the corresponding fruit into the “column1” field.

Step 3: Filling In The Rows

This is the most flexible part of the process. It can range from complex loops within loops to a simple progression of steps to fill each row and column. First, to test if the reporting table works, you can create a simple looping script:

Set Variable[ $Limit ; Value: 100 ]
Loop
Exit Loop If [ Let( $i = $i + 1 ; $i < $Limit ) ]
Set Variable[ $$COLUMN1 ; Value: If( isempty($$COLUMN1) ; $i ; $$COLUMN1 & ¶ & $i ) ]
End Loop
Refresh Window [Flush cached join results]

This loop will fill in the first column values with numbers, by virtue of the GetValue function.

To fill in your report with real values, change the “Set Variable” line to something real. In the ContactsVL database, I wanted a count of people living in each state. I created a variable with all the states, and looped through this list. I then ran an SQL query inside the loop for each state.

#Column1
Set Variable [$$COLUMN1 ; Value: If( IsEmpty( $$COLUMN1 ) ; $State ; $$COLUMN1 & ¶ & $State ) ]
#Column2
Set Variable [ $Result ; Value: ExecuteSQL( “select count(*) from contacts where \”Work State\” = ‘” & $State & “‘” ); “” ; “” ) ]
Set Variable [ $$COLUMN2 ; Value: If( IsEmpty( $$COLUMN1 ) ; $Result ; $$COLUMN1 & ¶ & $Result ) ]
#End

In another example, rather than a list you can loop through, you have an individual requirement for the report, with text for report sub-heads. Here it’s just a matter of stepping through each row and setting the variable $$COLUMNx with the result. By adding values to a growing list, you then can parse this list into the rows of the reporting table. And for the subheads you can use text formatting functions to add bold and increase the font size to make these stand out.

Conclusion

Once you have set up a reporting table as in steps one and two, this technique can be used for many different kinds of reports. With a simple starter reporting table and a script or two ready in a template, you can quickly expand this concept to specific solutions.

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.

Getting Started With QuickBooks Integration

March 18, 2013 by Anders Monsen 2 Comments

Intuit’s QuickBooks software organizes business information: contacts, vendors, orders, invoices, inventory, and more. Many small businesses use QuickBooks as their back-end accounting software, alongside FileMaker. Often these two applications perform separate functions, but sometimes there may be a need to integrate them. I’m going to briefly discuss one such method, using the FM Books Connector plugin (FMBC) from Productive Computing, Inc.

I’m not going to cover installing the plugin, as the files that are included with the plugin explain how, in detail. However, there are some items to keep in mind:

FMBC runs only on Windows.
You need to have QuickBooks, install the plugin into the directory FileMaker uses for plugins, and run a couple of installers that allow for XML communication via QuickBooks. Again, PCI has step by step instructions for this process, along with a sample database that will test each required piece.
QuickBooks needs to be up and running when you make your queries, edits, and new record requests from FileMaker.
Finally, the plugin needs to be registered, or it can run in demo mode for 30 days, though you will run into time-out issues while in demo mode.

On Screen Reference Manual

All requests from FileMaker to QuickBooks are made through function calls from FMBC. These function calls are contained in scripts, and at the very basic, contain steps to open the connection, send data, return a result, and close the connection. Exactly what takes place in the “send data” part depends on your action, and any data returned may include confirmation, content from QuickBooks, or an error message. FMBC comes with a handy developer’s guide, but this is only half the picture. The other half entails understanding Intuit’s On Screen Reference Manual and deciphering the XML tree.

Each action such as adding a customer, adding a sales order, editing a customer, etc., are grouped under Message Types.

When you select from the list of types, you can view all the requests, response, and XML tree. To initiate a request and retrieve a response you need to know the XML element names (i.e., field or column names) in QuickBooks. For example, adding a customer would require something like this:

Certain elements are required; others are optional, but they must appear in the same order in your script as in the XML tree. Otherwise, your result will contain an error message. Luckily, FMBC comes with a few sample scripts, and the developer’s guide contains some sample code. As with many plugins, sending the action can be accomplished by setting the value of a global field or a variable with the function call, such as:

Set Variable [ $Result ; Value: PCQB_RqNew( "CustomerAdd" ) ]

Which Message Type?

The key in writing any scripts to query QuickBooks via FMBC is first to outline which message type will be used, and then review the XML for fields in QuickBooks. From there, map the QuickBooks’ fields to fields in FileMaker, and use the appropriate function call. So, adding a customer, for example, requires the “CustomerAdd” message type and the Name. Salutation is an optional value that comes after the CompanyName. Additional fields and values are then added as needed.

Once you add the various fields you want in your new customer action, you need to deal with the result. You cannot assume the data will be added, so handling errors is critical. Successful calls via the plugin result in a zero, so checking for this value or !!ERROR!! text when sending the data will indicate whether something is wrong. If an error is detected, FMBC includes a handy function called PCQB_GetStatus that will show the error message. A few of the errors I’ve run into include values expected in the wrong order, the QuickBooks application being unavailable, and certain pre-conditions or values not in place.

Along with adding records, FMBC lets you add related records, query existing data, and edit data already in QuickBooks. Here are a couple of example “gotchas” to keep in mind.

Adding sales orders requires that items already exist in QuickBooks’s Item Inventory table.
Certain elements like Terms and Ship Via options will need to exist in QuickBooks when sending over sales order data, if you include these values. This isn’t always spelled out, and must be deciphered from error messages.
You will want to make sure updates only are attempted from Windows. So if you run a cross-platform user environment, prevent the scripts from running on the Mac OS using the Get( SystemPlatform ) function.

Integrating QuickBooks with your FileMaker solution is quite feasible, but requires careful planning and testing. Once you set up the first few connections and learn how to interpret error messages the process begins to flow more smoothly. An alternative to running each script in debug mode is to create an error table and log any errors there as they occur. Then you can review the table and pin point where the script faltered. Integrating FileMaker and QuickBooks should hopeful reduce data entry (no longer in both places), and allow users a friendly front end method to create and update data, and also to see the data you already have in QuickBooks.

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.

Are You Ready for Coaching?

February 14, 2013 by Anders Monsen Leave a Comment

Consider team sports. They consist of players on various surfaces and environments (fields, courts, pitches, etc.). The player performs the actions that directly affect whether the team wins or loses. Although not directly involved in the action on the field, virtually every sport includes a coach, someone who comes up with the game plan. Even in individual sports, the athlete who tends to go alone often experiences a jump in performance when consulting with a coach. Competitive runners and swimmers rely on coaches to plan and organize, even though some plans may change in the midst of an event. Even individuals whose careers are not dependent on racing discover that coaching provides unique benefits and insights, even while not making daily use of those services.

Sports analogies permeate almost every sphere of our non-sports activities, from work to politics, family to entertainment. In the business world, coaches play similar roles. Whether working alone or in a team environment, there are benefits to having coaches look over your shoulder as you encounter unfamiliar situations or to affirm ideas and defeat doubts.

MightyData provides a full-featured range of services around FileMaker Pro, from development through consulting, instructor-led training classes, and coaching. There is often a delicate balance between training and coaching. One might say in the context of FileMaker development (or any software development), that training is the first step on the road to becoming a developer. Basic training focuses on users – people relatively new to the product and from the perspective of a user. Subsequent training, especially in the FileMaker Training Series (FTS), goes far more in-depth and covers development tools and certification requirements. Not every developer seeks certification or even the broad scope of FTS training. Coaching fills this void, targeting specific areas.

Are You Ready or Not?

How do you know if you are ready for coaching? First, you must be motivated to learn. You are not simply handed a fish, but guided in the process of becoming someone who can fish, to cite another analogy. By considering coaching, you acknowledge a thirst for learning and a desire to improve. Coaching is not an admission of defeat, but a validation of your skills and your desire to take them further. A coach may suggest solutions and approaches to problems, but ultimately the implementation and understanding lies with you. Flexibility comes with any coach/developer relationship. As in sports, individuals may clash; sometimes the player refuses to budge, other times, the coach remains set in certain ways and fails to see other options. Both the developer and coach must take an open approach.

Another critical skill is communication. How do you articulate the issues or problems? MightyData’s coaching is mainly a virtual affair, conveyed via email and screen sharing using GoToMeeting. Can you state the problem so that others understand both where you are coming from and where you want to go? There is always a goal in mind, so vague generalizations tend to lead to fuzzy coaching as if the coach surveys a dusty nebula of issues, rather than sharp stars. Can you interpret what the coach tries to communicate? Sometimes the coach may assume certain things about the problem and suggest a solution that doesn’t quite fit the problem, and the developer needs to mention this to clarify any misunderstanding.

In my experience, I think a coach enjoys giving back knowledge, which is why we coach. We share in the excitement someone feels when he or she has that “aha” moment as something once murky becomes clear, the realization of learning some of the tools FileMaker contains and how they work. If you are looking to take your general FileMaker knowledge to another level, I suggest a broader training curriculum. If you are ready to hone your developer skills and have the willingness to learn and ability to communicate, then you are ready for coaching.

Get Off Script and Improvise

February 5, 2013 by David Weisiger Leave a Comment

Alright. I just had a call with a sales guy trying to sell me on HIS services. He worked for a printing company and asked if we would want his company’s services for printing a FileMaker training manual. I told him thanks, but no thanks. We are a virtual company. We don’t use paper. (You’re welcome, Hippies!) We do our training online and create electronic documents.

Being a sales guy myself, I tried to turn the call around on him and sell him on our services to no avail. Oh well. But as I was being talked to by this guy I realized something… hold on a second. Did you hear what I just said? I was being talked to by this guy! The guy knew his script very well. But he just stuck to it. There was no friendly banter or trying to talk to me like I was a regular person. Just him spieling his spiel.

The Boiler Room

It reminded me of the scene from the movie Boiler Room. A sales guy calls Seth Davis (Giovanni Ribisi), who has recently evolved into a slick salesman. When this sales guy tries to sell Seth a newspaper subscription over the phone, Seth ends up giving the guy a lesson in sales. (Warning, some profanity in the clip.)

Now I did not have this type of conversation with my sales guy call, but it illustrates my point. So here’s a post that taps back into some of my acting training. Learn the script. Learn it inside and out. Then… let it go.

Learn The Script and Let It Go

We get phone calls from all sorts of people with various positions at companies in various industries. The difference with our products and services is that it fits a very specific need for very specific people. When somebody calls me to talk about his need, it can be completely different from the person that called me 30 minutes prior. So how do you handle so many variables? You learn your script and then get off of it. What does that mean exactly? How do you do that?

Well, one of my passions happens to be acting. When doing a scripted play you need to learn the script. The play follows that script so precisely that there are sound and lighting cues that happen because of a particular line that someone says. But, if that person forgets those lines, and it does not cue your line or the lights, there is a little improvisation that needs to happen based on your intimate knowledge of the play, scene, and character. This allows you to cover and get the play back on track should the dreaded brain freeze or technology failure happen.

This works in sales as well. Because of the randomness of situations that can come up during a call, you need to be prepared to go with the flow. Get the script on the track that you want, but staying in line with the overall theme of the call.

Then Improvise

While I lived in L.A. I became friends with an actor that has done some pretty amazing things on film. One time I asked him, “What is the most important thing you can do when going to set to shoot for the day?” He said, “Learn the script, and let it go. It is the only way to be free as an actor.” That really was an important acting lesson. It is true too in sales.

If you know your role, your company’s role to a “t”, you can sell to anyone who is willing to buy. Your improvisation skills take the conversation in many different directions… directions that other salespeople may not go. And this is what will set you apart from your competitors. Your ability to connect with the customer is what really makes your customer buy from you. This is what the customer will remember.

What Does a Developer Do All Day?

January 24, 2013 by Darren Burgess Leave a Comment

Like any fine wine/cheese combination, I found that there are certain kinds of music (or silence) that go well with the various kinds of things that I do as a developer all day. We are a “virtual” company (we all work from home) and so I get complete control of my sonic environment to enhance my productivity. Since I am MightyData’s “Maestro of Metamorphsis” I thought a brief tour of some of the items in my @work iTunes playlist might serve as a vehicle for elaborating on what a FileMaker developer does all day. Here goes:

Discovery = Silence

Discovery is the process of gathering information from our customer about the current system and the system he hopes to build. During this process, we gather documents and information and meet with our customer. In this phase of development (which can occur at any point in the process of working on project), I am working to discover how he manages information currently, what the fundamental problem is that he is trying to solve, and what the value is to the customer if that problem were successfully solved.

Naturally, this is a process that involves a lot of communication, and so silence is the best choice for enhancing the process.

Analysis = Silence

When a project enters an analysis phase, I gather the information collected in discovery and work to determine the best development solution. This may involve drawing sketches showing data relationships, building spreadsheets to model reports, brainstorming solution ideas, researching prior work, and consultation with the customer and other members of the MightyData development team. More than anything, though, analysis requires deep thinking, and integrating many pieces of information toward the goal of finding the most efficient and highest value solution for your company.

The concentrated thought of the analysis phase dictates that the best musical accompaniment is again simple silence.

Planning = Philip Glass – The Hours

Before a single line of code is written, before I even consider developing a database solution, I will spend some time planning. For this phase, I open Basecamp (our web-based project management solution) and create to-do lists for each discreet bit of functionality or database structure that needs to be created. For example, if your solution requires 2 new user screens and 3 scripts, I will create a list for each of these items. I then load up the lists with all the tasks that need to be completed for each item. These tasks might be “Create New Table: Interactions”, “Add field to Interactions::Type” or “Design Interactions report layout.” The goal of this planning phase is not to capture every action that will be completed for a particular bit of functionality. Rather, I strive to capture enough information so that I have a detailed-enough map of the direction I am heading. It serves as a check against my work in the analysis phase, helping to confirm that the analysis was accurate.

Since the deep thinking is already complete when I enter a planning phase for a project, I can then crank up iTunes and dig in. I find the best accompaniment for the planning process is a musical selection that is not too intrusive, but inspiring and moving nonetheless. For this purpose, I usually select The Hours by Philip Glass. Glass is a 20th century minimalist composer and this particular recording is the soundtrack for a movie by the same name. The compositions on this recording are deceptively simple and hauntingly beautiful and are a perfect background for enhancing the development planning process.

Layout Design = Kurt Rosenwinkel’s Our Secret World

A layout, in FileMaker terms, is what you would consider a screen where database users find, view and change their data. Designing layouts is a lot like working in Adobe InDesign or Microsoft Publisher. FileMaker provides an interface for the developer to add, move and resize the various elements on the layout. Colors, gradients and graphics can be added to make the user screen pleasant to look at. I know that since a you will be spending a lot of your day looking at the layouts I create, I want them to look as clean as possible, while simultaneously delivering all of the functionality that is required.

I am an old hand with InDesign, having completed many graphic design projects in my pre-MightyData career. So designing great FileMaker layouts are dead simple for me. If I have done a thorough job with analysis and planning, then I really get to enjoy my work designing layouts by cranking up Kurt Rosewinkel’s Our Secret World. Kurt is one of the finest jazz guitar players of his generation and for this recording he teamed up with a Portuguese big band – OJM (Orquestra Jazz de Matosinhos) to play some of Kurt’s most difficult original compositions. The result is a highly kinetic and richly textured jazz big-band album featuring dynamic arrangements and some of the most inspired guitar improvisations I have heard in a while. Great stuff, and a perfect complement to designing layouts in FileMaker.

Script Writing = Pat Metheny’s Orchestrion

Scripts are the work-horse of a well-designed FileMaker database. To click a button to run a report, navigate the solution or really do anything at all with data, there is likely a script (or many) running invisibly in the background to help accomplish the task. Writing scripts also requires analysis and planning. If I have done my homework in these areas, then the next step is to plan the script itself. I create a basic outline of steps of the script and what result it will create with the data or for the user. If I do this well, then the actual process of coding a script is fairly straight forward.

Well-planned scripts often require some concentrated thought, so my musical selection for script writing requires a more subdued work that does not intrude. For this purpose, Pat Metheny’s Orchestrion is the perfect choice. Pat is a multi-Grammy award-winning jazz guitar player of the generation preceding the likes of Kurt Rosenwinkel. I grew up listening to Pat in many musical configurations and most recently experienced his Orchestrion project live here in Gainesville, FL. For this project, Pat created a huge orchestra of mechanical musical instruments that included marimbas, percussion, guitars, basses guitars, pianos, and glass bottles. Pat could control this giant one-man band entirely from a computer and his guitar. He created a suite of compositions for the project and, of course, released an album to document the music. The pieces are dynamic enough to be energizing, however they have a subdued quality that allows space for the thought process of script writing to unfold.

Testing and Perfecting = Silence

Even the best laid software development plans can result in unexpected results in a database solution – otherwise known as bugs. When bugs are encountered in a solution (often by the customer), it is my job to analyze and fix the issue. Depending on solution complexity, this can often require some deep thinking and testing of the solution. Fortunately, FileMaker provides developers with some great tools for analyzing the state of state of a database step-by-step as scripts execute, making the debugging and perfecting process as simple as possible. Regardless, though, sometimes the problem is “between the keyboard and chair,” and it can take some supremely concentrated thought to determine the root cause of unexpected or undesired behavior in a database.

Silence, again, is the best musical prescription for solution testing and debugging.

Documentation = David Grisman’s Hot Dawg

Documentation is the critical process of developing a record of how I created a solution and how it is used. Generally, database solutions are documented internally as they are created. I create copious comments throughout with the mindset that if I get hit by a beer truck tomorrow, that another developer could easily jump in and understand where to pick up from where I so unfortunately left off. Even more important though, I need to document the solution for the customer’s benefit. For this purpose I will dive back into Basecamp and create to-do lists of Test Cases with instructions on how to use your system, complete with annotated screen shots and even videos when needed.

At this point, the project is mainly done, and the process of documenting the solution for the user is simple and fun. For this activity, I prefer a musical selection that matches the joy of delivering a well-hewn database solution to my customer. David Grisman’s Hot Dawg is perfect choice. David is one of the top bluegrass/new grass mandolin players with decades of creative output. Hot Dawg features a variety of joyous renditions of blue grass and jazz swing tunes in a configuration featuring mandolin, guitar, bass, and the violin of the great jazz swing master Stephane Grappelli.

Final Notes

Thank you for accompanying me on my brief tour of the development process and of some of the finest selections from my iTunes library. I hope this process has improved your understanding about what the development process is like and what it takes to create quality database solutions that can solve your real-world business problems. And, I hope now, you have more of a picture of the tremendous pleasure it is to help you and your company get out from working “in your business” to working “on your business.” It is an honor to serve in this way – and I get to enjoy listening to some really excellent music. What could be better?!

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.

When You Lose a Customer’s Trust, You Lose Their Business

January 11, 2013 by David Weisiger Leave a Comment

There are a lot of things that go on during the sales process. You and the customer are trying to see if you are a good fit together. When I qualify a customer, one of the main things I look for is, “Can we build a strong partnership together?” That is what we are doing anyway, joining together to accomplish a goal. We make some money and we create value by helping you become more effective.

Another major part of that qualifying stage is trust-building…can we show you in our first couple meetings that you can trust us with your business. I can’t tell you how many times I have heard from a potential customer, “I picked you guys because you called us back first/quickly.” Just the other day I had a potential customer tell me that he had left messages with some of our competitors and they didn’t reach out right away, but he liked us because I called him back within 4 minutes. While this is not a great reason to build a partnership, the reason behind it is. He is really saying that he trusts our word because of our quick response. It is the first stages of building trust with a potential customer.

Everyday of your life you deal with many trust tests. Anything from as small as, “Do I trust the driver in the car next to me?”, to something as big as, “Do I trust the food that I am putting into my body?” Trust tests come in many variations and situations. I think one of the most common ones is money. Do I trust the bank? Do I trust the bartender with my credit card? Do I trust this online payment is secure? Does my wife trust me not to blow all our money at the casino? ; ) And finally… do I trust this business with my business (aka, my money)?

There are many factors that go into trusting a business with your money. Are they reputable? Do they have a lot of satisfied customers? Are they going to do what they say? And how do you ever find that out with a virtual company such as ours? I mean before you sign your first proposal with us, you have not met us in person. You have not seen that we are actual people. Yet in the world we live in today, this scenario is very common.

We go to websites such as eBay, Amazon, and various other retailers, and trust that they have the product that they offer on their webpage, and that they will send it to us. We never talk with anyone about it. We simply click on a picture, add it to our virtual cart, enter our financial information and trust that it will come in a timely manner and in one piece and they won’t steal your identity. Obviously, the main difference with buying from us is that the price point is a bit higher than items you would buy on one of those sites. But another big difference is that this is a purchase you are making that could change the way you do business. And when it’s your business that is involved not just a DVD or HDTV, there are some more things to be considered in the trust exchange.

Really more than an exchange of services for money, you are trusting our FileMaker expertise and our understanding of how your business works. We do this in a couple of ways. First, when you contact us, we will get back to you within one business day. You and I will have a conversation about your needs; you will notice something different about us. We are consultants first and developers second. We want to get to know all the ins and outs of your business so that we are advising you from the best position possible. We want to create a partnership and build trust with you from day one.

The second way we will build your trust is to conduct discovery. This is a paid engagement, but it is an important early step that we take with about 95% of our customers. It helps you to see how we work, how our whole team comes together to provide you with the best we have to offer. When you have more people to lean on, it makes it easier to rest. There is an old acting exercise to teach you about trust. Your ensemble stands beneath you as you stand on a chair. You must trust them to catch you as you close your eyes, cross your arms and fall back.

We ask you to do the same when you sign a Discovery proposal with us. We know you have not met us yet, or seen how we work, but if we have shown you our ability to respond, advise and direct in our first few meetings then hopefully it will give you enough of an idea to trust us more. We are confident you will see our abilities, trust that we can get the development done and make your business run more effortlessly. After all if you don’t trust someone with your best interests why would you want to work with them?