MightyData

FileMaker and WordPress Consultants

  • Company
    • About
    • Contact
    • Team
    • Artists
  • Services
    • Consulting
    • Development
    • Our Process
    • Coaching
    • Training
  • Results
    • Customers
    • Success Stories
    • Testimonials
  • Blog

Preparing for Your First FileMaker Class

June 23, 2011 by Martha Zink 2 Comments

This blog article isn’t about giving you homework before class starts. Well, at least not really. When you jump into any FileMaker training class, the amount of technological knowledge can be overwhelming. It helps if you step into the classroom with the right mentality. Here are a few things to keep in mind:

Picture Your Rainbow

It’s hard to learn something if you don’t know why you need it. Building a database for the sake of just building one won’t get you very far. Instead, think about a business need, or even a personal need, for a database. Some common examples include: contact management system, invoicing system, recipe database, inventory, etc. Think of a database as doing something the “easy” way. Imagine having to write all of your business’ receipts on carbon copy paper! A database could remove the paper from the process, allow you to email receipts to customers, and keep a history of all items purchased.

Know Your Business (aka Think Like a Human)

FileMaker solutions, like any other software, work as well as you build them. The key to building a successful database is to know your business process and your business needs. Before you even get into the FileMaker jargon, think about your business as a flowchart and what it takes to complete the process. It even helps to come to class with those ideas written out, as you’ll have something to reference as you learn new FileMaker skills.

Think Like a Computer

Knowing your business is all about understanding people, flow, and processes (and even a little bit of office politics). Once you have that information, you’ll want to start thinking about your process in “computer speak.” While this is something you’ll learn throughout your FileMaker training, it helps to be prepared for it. “Computer speak” simply means that you’ll have to think logically, rigidly, and in pattern-form. A good example of this is in my article about Parsing Like a Pro – in order to get FileMaker to do something, you have to TELL FileMaker what to do, how to do it, and what to look for. Being a developer is mostly about being an interpreter/translator from humans to computers.

Think In Analogies

It’s nearly impossible to build a curriculum that covers the needs of every student in the class, unless it’s a class of one. With that said, be prepared to use analogies or examples to tie the material taught in class to your example. If the trainer talks about Customers and Invoices, think about these compared to Students and Grades. Don’t get stuck in the objects or items. Take it to the next level and think about what the items represent and how they relate and/or function together. Also, don’t be afraid to ask questions! Trainers want you to walk away with material you can apply. If something just isn’t clicking, ask for another example or ask how it would apply to your scenario.

Prepare for Mental Overload

Don’t come into the class with the expectation of walking out and knowing all there is to know about FileMaker. As a trainer, my goal is to teach you what I can and expose you to things that might be out of your initial skill’s reach so that you can attempt it later. My goal is to tell you that anything can be done, whether it’s just through FileMaker or with the help of other technologies. Regardless, know that you’ll walk out of the class feeling a bit overwhelmed with knowledge and possibilities.

Set Your Expectations

My advice is that when you’re thinking up your ideal database, put things into 3 categories: Can’t function without this, Important but not required, Nice to have. By prioritizing functionality, you don’t get bogged down with the details. You’ll also find yourself building something that will be functional enough for actual use, so that you can start using your system and continue growing and building the database.

If you keep the above in mind, you’re preparing yourself for FileMaker success! Learning a new technology, or even learning more about an existing technology, is far from easy and takes a lot of work and practice. However, being organized and prepared will lead to a successful outcome as you venture onto your new FileMaker journey.

Good luck!

Filed Under: News Tagged With: Coaching

A Perfectly Scripted Collaboration

June 1, 2011 by Kirk Bowman

TECSoft

TECSoft

As the leading AppleScript training firm, TECSoft knows a thing or two about the power of FileMaker: One, this technology transformer would support an e-commerce application, allowing online registration and payment for AppleScript training. And two, when it comes to designing and implementing FileMaker applications, MightyData is the perfect choice to get the job done.

John Thorson, President and Founder of TECSoft in Amagansett, New York, first met MightyData’s Kirk Bowman when the latter took TECSoft’s AppleScript training classes. At the time, Kirk was seeking complementary technologies to improve his skill set and round out MightyData’s service offerings. “Kirk’s seriousness and professionalism at the time sparked a desire to work with him,” says John. “Since then, we have collaborated on numerous FileMaker Pro development projects.” It was the beginning of a perfectly scripted collaboration.

The Technology

AppleScript is a Macintosh software automation technology that allows users to write a single script that can automatically control multiple applications on a Mac. The software works especially well with FileMaker Pro to automate publishing information, as well as HTML for the Internet.

First, MightyData developed the back-end FileMaker Pro databases. Then it designed and created the CGI scripts, a go-between that communicates with the browser interface (front-end) and the FileMaker Pro database (back-end). Now TECSoft’s website, which replaced telephone registration for the AppleScript classes, allows round-the-clock registration with instant email notification.

The Transformation

Since TECSoft sells training coast to coast and in England, the site provides a 24/7, time zone independent opportunity to register—making the company virtual. What’s more, the interface enables TECSoft to administer its databases remotely over the Internet. To date, the new website reconfiguration has increased registration dramatically, and John couldn’t be more satisfied.

I was amazed that this fairly complex procedure was flawlessly executed on the very first pass, notes John.

“Kirk certainly was in command of the situation, putting in long hours to get everything working perfectly. His proficiency in three different skill sets—database development, CGI scripting and interface design—brought an advantage to the table that resulted in an exemplary job.”

At MightyData, we couldn’t script it any better than that, thanks to this powerhouse collaboration.

Filed Under: Success Stories Tagged With: Case study

Enhancing the Interface With Filtered Portals

May 25, 2011 by Martha Zink 3 Comments

Screen shot of filtered portal demo

Recently, I’ve heard some push back when it comes to using filtered portals in solutions. The reason? Filtered portals are slow for large data sets. And this is a fair point. However, filtered portals and I have a close bond for reasons beyond their Brita-like tendencies of leaving the good and filtering out the unwanted. I love using filtered portals because they can enhance the interface, while providing flexibility for the developer.

Screen shot of filtered portal demo

Recently, a customer wanted to streamline a process and I decided to implement a checklist to get this done. Of course a simple checklist isn’t enough, as each step that uses the system should be clickable so that the user is taken to the right place to complete the necessary action. In the past, how would I have accomplished this?

Two Solutions

I can think of two solutions:

  1. A slew of text boxes with scripts attached to each
  2. A portal to a table that shows me the task and description with a rank field to sort them correctly

So before the birth of filtered portals, I could have accomplished what I want with #2 above. So why the admiration for the filtered portal? The filtering capability means I can use one table to house different checklists, and when I go to apply a portal with a new filter calculation to a new layout, a little copy/paste and I’m nearly done. All that’s left to do is change the calculation for the filtered portal. In pre-FileMaker 11, I would have created a different relationship for each type of checklist. Less table occurrences makes for a happier developer!

In the example of my customer, there are two different types of checklists: the input and the output. The input is all about how they enter the data. The output is all the “printables” – invoices, project details, project summary, etc.

Setting Up a Filtered Portal

Let’s talk technical – here are the fields I setup in my checklist table:

  • Type – identifies different checklists
  • Description – what the user sees
  • Parameter – what the script will use to perform the correct action and take the user to the right place
  • Rank – what the portal uses to correctly sort the checklist

How are my portals filtered? By the field “type.” An example would be as follows:

data_checklist::Type = "Input"

Nice and simple, right? Let’s kick it up a notch, á la Emeril Lagasse.

Imagine if you had an Issues database. What if a list of suggested questions or actions were provided, based on the scenario? Instead of hard coding the actual type, you can reference a field in your table:

data_checklist::type = data::category

Conclusion

So let’s step out of the proverbial box and into the world of possibilities – I’ve used filtered portals more for interface enhancement than for true data filtering, but what’s the harm in that? In fact, using filtered portals in this form helps the developer in adding flexibility via relationships and scripts. By abstracting the interface into a relationship and filtered portals, often modifying the interface can be as simple as adding a new record in a table.

Want to see these techniques in action? Check out my sample file! I’m using the table “Checklist” for the filtered portals in both examples.

Filed Under: Layout Design Tagged With: Demo file, Filtered portals

A Simple Debug Function for PHP

May 23, 2011 by Anders Monsen 8 Comments

Simple PHP debug function

As FileMaker developers, we rely heavily on FileMaker Pro Advanced, which lets us step through scripts and test variables on the fly. Unlike in FileMaker, there is no Data Viewer and Script Debugger to make life easy in PHP development. We can take a manual approach or just as with FileMaker solutions write a custom function to do all the heavy lifting.

The Old Way

When trying to decide the content of variables that fail, developers usually print these to the web browser, using either “echo” for strings or “print_r” for arrays.

We quickly discover that large arrays look messy with just print_r, and so the next step is to wrap some “pre” lines around this to format the array into more readable text.

echo '<pre>';
print_r( $array_name );
echo '</pre>';

Even these three lines can get end up crowding your page as you are writing code. When stepping through the various arrays returned by the PHP API for FileMaker using a newFindCommand, adding these three lines in between each variable, and then commenting these out while debugging, can become tedious. The simple query below has five possible places you might want to check the content of the variable.

$find = $fm->newFindCommand($layout);
$find->addFindCriterion('field', $value);
$result = $find->execute();
if(FileMaker::isError($result)) {
 $error = $result->getMessage();
 die($error);
}
$records = $result->getRecords();
$record = $records[0];

A Better Way

Writing a function for PHP is somewhat similar to the method in FileMaker. The function requires a name, parameters, and calculations to serve the purpose of the function. You can store your functions in a separate functions.php page that you include in your PHP page, and call the function as needed. The function that I created to check what happens to each variable takes an optional parameter in the end to decide whether to output to the browser. So, I can include this function as one line in multiple places without worrying about commenting out the code.

function display_debug($key, $value, $debug=false) {
  if($debug){
    echo ''.$key . " = ";
    switch (gettype($value)) {
      case 'string' :
        echo $value;
        break;
      case 'array' :
      case 'object' :
      default :
        echo '';
        print_r($value);
        echo ''; 
        break;
} } }

There’s nothing too fancy here. I have in fact borrowed the main part of a function that comes with the PHP Site Assistant installed by FileMaker Server. It checks whether the variable is a string or array. I modified the function to include the key or variable name in bold, and also added the “pre” around the print_r to format the array.

Using the Debug Function

When I need to call this function, I simply call one line. The default has only two parameters.

display_debug('$findall',$findall);

Without the optional third parameter, nothing appears on the page. Add a “1” and this function becomes live.

display_debug('$findall',$findall, 1);

The variable inside quotes will let this appear as text, letting you search if multiple variables appear on the page. Often you need stop points after certain sections of code, in which adding the command “die();” would let you focus on just that section.

Debugging code is part and parcel of any development effort. Even when everything seems to work great, to echo the arrays or strings stored in variables validates your code and acts as a sanity check. Testing for errors or determining the cause of errors require stepping through all possible locations this may happen. With a function handling displaying the variable contents and not using print_r() on each page, your code ends up cleaner on each page and cleaner overall.

Filed Under: Web Development Tagged With: PHP

Sneaky Portal Deletion

May 3, 2011 by Martha Zink 4 Comments

As FileMaker developers, we often use a bit of interface trickery to make things appear as just regular old fields on a regular old layout, when in fact portals, filtered or not, may be hidden on the layout. Well when we use hidden “stuff,” we sometimes confuse the user when it doesn’t behave like regular “stuff.” In this blog entry and video, I’ll walk you through a scenario where portals and script triggers can work together to make for a seamless experience.

In this example, I have an item table. On the layout there is a portal, or a related table, where the user can enter different numbers representing PMS colors. To ensure good data modeling, I used a separate table and not just 8 fields called “Color_1” through “Color_8.”

Now imagine this scenario:

The user enters a new item. She types 108 in the first color field (really a related field), then types 2100 in the second color field, and lastly 301 in the third color field. She then realizes that 2100 is not for this item and deletes the value from the field. Structurally, this item record has 3 related records, even though there are only 2 values in the PMS color field. Now the user wonders, “Why is there a gap between color 1 and color 3?” and the developer is stuck cleaning up the data or error trapping for these “empty” records.

To solve the issue, I’ve created a simple script that uses the OnObjectExit script trigger. When the user exits any of the PMS color fields, the script checks to see if the color field for that related record is empty. If it is not empty, then nothing happens. If it is empty, the script deletes that portal row. No “empty” fields, no “empty” related records!

Check out the video and let me know what you think.


Filed Under: Layout Design Tagged With: Video

Mutual Qualification

May 3, 2011 by Kirk Bowman Leave a Comment

One of the benefits of a value approach is it helps both the customer and consultant qualify each other. I define qualifying as a dialog with the customer to determine if we (customer and consultant) should establish a business relationship. Through my study of value pricing I have learned to ask better questions in a professional manner to help the customer and myself decide. Many of these questions are part of the value quest.

Recently, I was visiting with a new customer who wanted a CRM solution for his two-person business. The customer was using a SaaS solution for $40 per month. He said this was too expensive. I could have taken expensive to mean he was paying more than he wanted. Instead, I inquired, “$40 per month does not seem like a large amount. Why do you think it is expensive?” It turns out he was barely using the SaaS solution, and therefore, thought he was wasting almost $500 per year.

Value pricing has helped me to learn to not accept the first answer at face value. It has also helped me learn to ask stronger, penetrating questions. His answer told me expensive was not a reflection of the value he wanted or the price he was willing to pay. It took further discussion to determine how he perceived the value of the solution. By not jumping to a conclusion, I discovered a truer perspective to help both the customer and myself qualify each other.

Filed Under: Sales Tagged With: Sales process

The Leapfrog Solution

May 1, 2011 by Kirk Bowman

Neiman Marcus

Neiman Marcus

Recognized as the premier luxury retailer in America for over a century, Neiman Marcus offers upscale assortments of apparel, accessories, jewelry, beauty and decorative home products to the affluent consumer. Renown for its iconic print catalog, Neiman Marcus has taken its direct-to-consumer business even further with the help of MightyData. We were fortunate to partner with this powerhouse retailer several years ago to help them update their database to efficiently manage all of their merchandise photography and copy blocks for their catalog and Web site.

Since then, Neiman Marcus has been challenged to process more Web data as well as print and online production details at a much faster rate than ever before. The internal production teams for Neiman Marcus turned once again to MightyData to help them develop a solution that would optimize the speed of the transfer of product information messages from IBM WebSphere, its master information data management system, to FileMaker for both their print catalog and online operations.

Technology

Scott Schley, Business Solutions Management, collaborated with Ross “Mac” Mackintosh, Champion of Transformation, to create a robust exchange of information between the external system and FileMaker, optimized to parse and utilize XML data company-wide across multiple internal production teams. The newly developed application became known to many as, LeapFrog.

Now, anytime updates are needed to the website or when there’s a roll out of a new catalog, LeapFrog processes Web data and asset requests faster, while efficiently managing the flow of information between internal Web and production team members. In addition, MightyData improved system notifications, alerting team members to the current system status to allow for more timely knowledge of any system disruptions.

Transformation

The transformation caused by LeapFrog was quite dramatic. Catalog rollouts that used to take 2 weeks can now be done in 2 days. Case in point: the previous system handled 140,000 messages in 2 weeks. These messages contain information on products such as copy, pictures, sizing, colors, etc. With LeapFrog, processing nearly doubled to 260,000 messages, but was handled in only 2 days.

Production rates changed so dramatically it had Scott uttering,

Wow!

What’s more, LeapFrog synched with every computer in the network enabling production to run during the day as well as beyond after hours. When Joe Condomina, Vice President of Neiman Marcus Direct and Online Systems, says,

You are a truly valued partner.

That’s a giant leapfrog forward as far as we’re concerned.

Filed Under: Success Stories Tagged With: Case study, Performance

Inspecting Inspector Pro

April 29, 2011 by Kirk Bowman 5 Comments

Searching for dormant calculations in Inspector

Recently I presented Inspector Pro by Beezwax to our development team during our weekly Innovation Meeting. Most of our team is familiar with BaseElements by Goya and I wanted to expose them to another tool. Personally, I have been using Inspector since it was called Analyzer in the FileMaker 6 days. As a result, I am very comfortable with the user interface of Inspector. (For the record, I use both tools equally although each has different strengths.)

Before I go further, I need to point out two things. First, “Inspector” is also the name of the palette to change element properties in FileMaker Pro 11. This article is not about that. Second, to use Inspector (or BaseElements), you need to have FileMaker Pro Advanced (FMPA) to generate a Database Design Report (DDR). The XML version of the DDR is imported into Inspector to create an analysis of the database.

Key Features

Inspector Pro 3.0v2 was released in March, and as a long time user, I was excited to see what changed in this new version. Of course, Inspector provides standard features like list and details views, a search interface, and reports for the elements in your database. Rather than a comprehensive review, my purpose is to highlight 10 features that caught my attention.

  1. Native FileMaker Finds – Inspector uses a custom interface with Quick Find and portals to display elements in your database. Now they have added “Raw Data” views so you can search the elements using native FileMaker finds.
  2. Find Dormant Calculations – A dormant calculation is one that has been disabled by turning off a field option (like Auto-enter by calculation) or changing the field type from Calculation to something else. On the Calculations screen in Inspector, you can enter “Dormant” in the Quick Find to view all the dormant calculations.
    Searching for dormant calculations in Inspector
  3. Apply Security Perspective – Inspector imports the settings for the Privilege Sets as part of the DDR. On the various screens, you can see what the effect of a specific privilege set is by selecting one from the Security Perspective drop-down.
  4. Display Changes Inline – If you have analyses of two versions of the same database, you can display the changes for a specific element “inline”. My only concern with this feature is it seems to require significant processing as it takes a few seconds to display (although it could be my computer).
  5. Script Step and Function Reports – These reports display an inventory of the script steps and calculation functions including how many times each is used. This is helpful to see, for example, how much the database uses If vs. Case.
    Calculation functions report in Inspector
  6. Index Percentage Report – This unique report shows the percentage of fields that can be indexed and the percentage of fields that are indexed by table. It also includes a bar chart for visual reference if you run Inspector using FileMaker (not the runtime).
  7. Script Trace Report – This report shows the entire code for a parent script, including subscripts with indentation, in one document. This saves having to jump in and out of multiple script windows in FileMaker.
  8. Open with FileMaker Go – If you host the database with FileMaker Server, you can open Inspector using FileMaker Go on the iPad. This is helpful if you need to review an analysis and you do not have enough screen real estate.
  9. Customize FileMaker Go Compatibility – As I write this article, FileMaker Inc. (FMI) has released FileMaker Go 1.2. Not only does Inspector show FileMaker Go compatibility when viewing scripts, it includes preferences so you can update the status of compatibility when FMI release a new version of FileMaker Go.
  10. Customize Detection and Comparison – Inspector also has preferences to customize how issues are included in a Detection Report and which elements are included in a Comparison Report. This is very useful if you only need to review certain elements types.

Also, Beezwax has setup a Wiki for the documentation including tutorial videos for some of the key features. Both the help text and the videos are useful for learning how to get more out of Inspector.

I continue to use Inspector each time I start to work with a customer who has an existing solution. Having this tool in my toolbox helps me get the best perspective on the database and ultimately, the customer.

Filed Under: Rapid Development Tagged With: Software tools

PHP Development for FileMaker Developers

April 29, 2011 by Anders Monsen Leave a Comment

PHP is a fairly flexible language, where developers can create sites using linear or procedural code, or complex object-oriented code. The fact that you need to type most of your code and understand a new syntax is daunting to anyone, especially FileMaker developers who are used to the built-in GUI, native script steps, and pre-existing functions. The ease of setting up a database in FileMaker is one of the primary draws of this software. Despite the more manual effort required when writing PHP, there are many analogies FileMaker developers can use to become more comfortable with the code and process of PHP.

FileMaker solutions generally contain three elements: the data, the logic, and the user interface. In web-based solutions PHP acts as the logic layer. The data exists elsewhere, either in MySQL tables, FileMaker databases, or some other external database. The user interface relies on HTML, CSS, Javascript, Flash, and other interface tools. By finding common ground between certain FileMaker features and PHP scripts we can ease the transition from one to the other.

The core of FileMaker’s logic layer is scripting and calculations. FileMaker scripts are comprised of built-in script steps with various options or parameters. FileMaker calculations combine elements such as text strings, numbers, fields, operators, and built-in functions. Developers can write custom functions, and external plug-ins create more functions. PHP pages often appear side by side with HTML, but strip down the code to just PHP and the appearance is much like a FileMaker script — a sequence of statements, such as variables, function calls, conditional branching, loops, etc.

Although there is no list of script steps to select from in PHP, PHP statements are made up of a series of steps, just like in FileMaker. Just as new FileMaker developers stumble when trying to select appropriate script steps to build specific scripts, PHP developers need to familiarize themselves with certain key functions. For example, a foreach loop in PHP could be expressed in this way:

foreach (array as $item) {  <##> }

Here the array, $item, and <##> are parameters where the developer must consider content and format, much like Set Field [table::field ; value ] contains two different parameters.

Another approach would be to separate PHP functions into two groups: those that look like scripts and those that look like calculations. In the latter group you have built-in PHP functions like substr, number_format, in_array, etc. These tend to appear as short, single-line calculations with one or more parameters. A few years ago Jonathan Stark created a reference page to map such calculations. Here we can see many parallels between FileMaker and PHP, such as Left( “FileMaker Pro Advanced” ; 4 ), which is expressed as substr( “FileMaker Pro Advanced”, 4 ) in PHP.

Another useful analogy takes place in the use of variables. FileMaker variables take the form of local (single $ followed by a text string), and global (double $$ followed by a text string) variables. Most PHP variables start with the single $ followed by a text string. Although $$ variables exist, these are called “variable variables” in PHP, and behave completely differently from FileMaker’s global variable, such that novice developers should not use these.

Just like the Set Variable [$varName ; Value:”varValue” ] script step in FileMaker, PHP takes a similar format, without the “Set Variable” declaration. Instead, the variable is declared and set with the single equal sign.

$phpVariable = substr( "FileMaker Pro Advanced", 4 );

This sets the value of $phpVariable to “File.”

Understanding variables implies their use within functions. If you declared a variable to “FileMaker Pro Advanced,” you could then use this variable in the function.$versionName = “FileMaker Pro Advanced”;$phpVariable = substr($versionName, 4 );The next logical step is to make the contents of $versionName dynamic, either as the result of a form submit action, or a query. Novice PHP developers run into issues when comparing data, as something like if( $myVar = “this”) actually sets the variable, while the double equal sign is the correct method: if( $myVar == “this”).

The essence of integrating FileMaker and PHP is expressed in four requirements: Creating records, Reading or retrieving records, Updating records, and Deleting records (or CRUD, for short). Total integration does not stop with these four elements. Getting layout information and value lists can make interfaces more dynamic. There are functions to execute FileMaker scripts and gather portal information, as well as critical error-trapping methods. However, CRUD is the building block upon which everything else stands.

One of the earliest and most concise write-ups on FileMaker’s PHP API came from Six Fried Rice on PHP API and FMP 9 from May 2007. All the points made here remain valid. Jonathan Stark and Chris Hansen’s comparative essay on FX.php and PHP API – is an excellent overview of the two PHP classes.

Some integration examples of FileMaker and PHP include Joomla, a content management system (CMS), detailed by the folks from myFMButler on integrating FileMaker and Joomla. Another popular PHP framework is CodeIgniter. With minor modifications it is possible to integrate this framework with FileMaker using FX.php.

Further, Google maps, which combine PHP and Javascript, can be integrated into FileMaker through the Web Viewer. A PHP page resides on a server. FileMaker interacts with this page, sending data from each record into the PHP script, which then interacts with Google through their API, and displays the result in a Web Viewer on the FileMaker layout.

Recently Matthew Leering’s post on Google Analytics and FileMaker showed how you can gather data from arrays and insert these into FileMaker. There are several web services that offer APIs to place arrays of data into PHP variables through queries, which developers then can turn around and insert into FileMaker records and fields.

Integration between FileMaker and PHP works both ways, making FileMaker one of these most flexible and powerful database solutions available to individual and enterprise users. From creating custom web front ends to your FileMaker database, integrating with existing CMS frameworks, and writing scripts that import data from web sources into your solutions, the marriage of PHP and FileMaker has tremendous potential. The above examples show just a few of these opportunities.

Filed Under: Web Development Tagged With: Integration, PHP

Parsing Like a Pro

April 29, 2011 by Martha Zink 7 Comments

In many cases, FileMaker databases get data from outside sources: Quickbooks, Excel, SAP, etc. Based on the way those external spreadsheets or systems are setup, the data may or may not be in the format that FileMaker wants it. Here’s where we start dealing with the issue of parsing.

Parsing entails taking something that’s all stuck together and breaking it into smaller pieces. For example, we could parse out the area code from a 10-digit phone number, or we can extract the month, day, and year from a birth date. However, what we often face is not as simple and can require more effort to break apart. But have no fear – this article is here to get your brain thinking about ways to approach parsing.

Often, we use a field with an auto-enter calculation or a calculation field to store the parsed data. For example, if I had a class code like ENG101, I may want to put ENG and 101 in different fields so that I can easily search both separately. Additionally, I can write a calculation that determines that the abbreviation “ENG” stands for “English”.

Here are the steps I go through in order to figure out what my calculation will look like:

  1. Review multiple samples of the data to be parsed. If you only use one example, you’re bound to miss the nuances of “more complex” examples.
  2. Look for patterns. Ask yourself, “If I had to do this manually, what would I be looking for?” Look for things like:
    • X always starts with Y
    • There is always an X before/after Y (where X could be a comma, semicolon, colon, etc.)
    • X will always be Y number of characters
  3. Don’t focus too much on extraneous stuff. What I mean is there are great tools in FileMaker to filter (hint, it’s the “Filter” function!) text down to the “wanted” characters.
  4. Love and respect the Let function. The beauty of the let function is that it lets you take parsing one step at a time, with minimal confusion. See the examples below for proof. Also, use “white space” or spaces/returns within your calculation so that it’s easy to re-read your calculation later, along with comments to explain what’s going on.
  5. Test, test, test. In most cases, the Data Viewer is the best way to parse out text so you can see your calculation working against live data.

Here is a list of common functions when parsing data:

  • Left or LeftWords
  • Right or RightWords
  • Middle or MiddleWords
  • Position
  • Filter
  • Substitute
  • PatternCount
  • Length

When parsing data, the calculation you write usually goes in one of two places: a calculation field or a text/number/date field with an auto-enter calculation. The latter allows the user to modify the result if there’s some nuance, while the former does not.

Example 1

You have a part number that has some logic built into it and you want to take that logic from a code to something a human understands.

PartNumber 1: TX-157566WH3
PartNumber 2: VA-156513WH2

The format for the part number is State – Part Serial Number & Warehouse Number.

Parse 1a – Let’s get the state first. (Note: The 2-letter state always has a dash after it. Always celebrate the easy victories!)

Let( [
  pn = data::PartNumber ;
  dash = “-” ;
  pos = Position( pn ; dash ; 1 ; 1 ) ] ;

  // Minus 1 so that we don’t include the dash
  Left( pn ; pos - 1 )
)

Parse 1b – Let’s get the Part Number. (Note: The part number is between the dash and the “WH” that stands for warehouse).

Let( [
  pn = Data::PartNumber ;
  dash = "-" ;
  wh = "WH" ;

  // Find the first occurrence of the dash
  pos1 = Position( pn ; dash ; 1 ; 1 ) ; 
  // Find the first occurrence of "WH"
  pos2 = Position( pn ; wh ; 1 ; 1 ) ] ;

  /* Plus 1 to start on the character after the dash; minus 1 to compensate for starting 1 character to the right than the dash */
  Middle( pn ; pos1 + 1 ; pos2 - pos1 - 1 ) 
)

Parse 1c – How do we get the warehouse number? We are looking for whatever is after “WH”.

Let ( [
  pn = Data::PartNumber ;
  len = length( pn ) ;
  wh = "WH" ;
  pos = Position( pn ; wh ; 1 ; 1 ) ] ;
 
  // Minus 1 to not get the "H" in "WH"
  Right( pn ; len - pos - 1 )
)

Example 2

You are tracking payments in QuickBooks and at some point, the data gets pulled into FileMaker. Your accountant uses the Memo field to store important data that should go into different FileMaker fields.

Memo 1: Partial PMT for Project# 19-1A3152567
Memo 2: Full PMT for Project# 19-1A3152567, Project#79-13687AB87
Memo 3: Ptl PMT, Project 19-12562567, #79-12667DB17

Parse 2a – We need to know if it’s a partial payment or a full payment, based on the memo field. (Note: I’m looking for specific words – “partial” “full” and “ptl” so I can use PatternCount to do that.)

Let ( [
  f = data::memo ; // “f” for “field”
  t1 = “full” ; // t1 for “text 1”
  t2 = “partial” ; // t2 for “txt 2”
  t3 = “ptl” ; // t3 for “text 3”

  // Find how many times “full” appears in the memo field
  pc1 = PatternCount( f ; t1 ) ; 
  // Find how many times “partial” or “ptl” appears in the memo field
  pc2 = PatternCount( f ; t2 ) + PatternCount( f ; t3 ) ; 
 
  Case(
    pc1 > 0 ; “Full” ;
    pc2 > 0 ; “Partial” ;
  “”)
)

Parse 2b – We want to get a value list (return-delimited) of all the projects referenced in a memo. (Note: The word “Project” is the starting marker for the project numbers and it looks like they go until the end of the field, each separated by commas. The word “project” could appear in front of every project, but might not. Because the # is extraneous, I can filter or substitute that out.)

Let ( [ 
  // "Substitute" is case sensitive so by making it all upper case, I don't have to worry about it.
  f = Upper (Data::memo) ; 
  // The number of characters in the memo field
  len = Length( f ) ; 
  // Our “marker”
  t1 = "PROJECT" ; 

  // On what character does the first occurrence of "project" start?
  pos = Position( f ; t1 ; 1 ; 1 ) ; 
  // Grab everything from "Project" to the end
  rtext = Right( f ; len - pos + 1 ); 
  // Substitutes “PROJECT” to nothing,  commas with a return, # to nothing.
  sub = Substitute( rtext ; [ "PROJECT" ; "" ] ; [ ", " ; ¶ ] ; [ "#" ; "" ] ) ] ;

  // The If statement checks to see if the word “Project” was found at all.
  If( pos > 0 ; Trim( sub ) ; "" ) 
)

Talk about a lot of code! The good news: once the calculation is written, your job as the developer is done. Record after record, the text will get parsed out automatically. The key lessons are learning to recognize patterns, as well as using the let function in order to “walk through” the logic, instead of it being all jumbled in one long calculation. It also makes for easier debugging. Think of it as algebra. What’s easier to debug and decipher?

Let ( [
  Y = Z + 4 – 3A
  X = Y + 3 ] ;
	
  X + 4Y – Y/(5-Y)
)

Without the “X” and “Y” meaning something, we’d get something messier like:

((Z + 4 – 3A) + 3) + 4(Z + 4 – 3A) – (Z + 4 – 3A)/(5-(Z + 4 – 3A))

And what if you have to go in and change “Y” to “Z + 3 – 4A”? In the first example, you change it one place. In the bottom example, you have to hunt for every occurrence of it.

Parsing and the Let function are all about trial and error, pattern recognition, and patience! Also, there are usually multiple ways to solve the same problem, so don’t feel like there’s only one right way.

Have you faced any parsing problems lately? How were you able to solve the issue? Comment and let me know!

Filed Under: Calculations Tagged With: Best practices, Demo file

  • « Previous Page
  • 1
  • …
  • 10
  • 11
  • 12
  • 13
  • 14
  • Next Page »

Let’s get started on your project.

It will be more fun than you think.

Get in Touch

  • Company
  • Services
  • Results
  • Blog

Copyright © 2022 · Parallax Pro Theme on Genesis Framework · WordPress · Log in