MightyData

FileMaker and WordPress Consultants

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

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

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

Image Rotation and FileMaker

April 19, 2011 by Anders Monsen 3 Comments

Script to call AppleScript from FileMaker

Recently I was asked whether it is possible to rotate an image in a FileMaker container field. As far as I know this only is possible by exporting the image, using another application to rotate the image, and then re-importing it back into FileMaker. This solution would exist only in a Mac OS X environment, so I turned to AppleScript, and the built-in image manipulation app called Image Events.

Although I am sure refinements can be made to the process, and it currently only works on the Mac OS, the process is fairly straightforward. The file has a container field which stores an image. Another field stores the temporary path for each record’s image. I decided to capture the direction of the rotation (clockwise or counter-clockwise) as a script parameter and set up some basic buttons to handle the process for the demo. To rotate clockwise the angle is 90 degrees, while for counter-clockwise it’s 270 degrees.

Script to call AppleScript from FileMaker

Since the AppleScript uses the database name and layout name, I captured these as variables. I also set the AppleScript itself as a variable so I could step through the script and see what it generated. I then built the path for the export, and also converted the slashes to colons for the AppleScript path. Immediately after exporting the image, I ran it through the Image Events to rotate it, and then followed this up with an insert image script step, grabbing the rotated image from its Finder location. I added a platform check so this would run only on the Mac.

AppleScript to rotate an image

There are at least two plug-ins that accomplish very similar goals. 360Works’s free ScriptMaster rotates an image based on a URL, so if this image resides in a container field you first will need to export the image, apply the rotation with the ScriptMaster function, and import the modified image. CNS Image will actually rotate an image inside a container field, and has many other useful features; prices can be found at the CNS web site. Both plug-ins are cross-platform, unlike the AppleScript solution outlined here.

Sample File: ImageRotator.fp7

Filed Under: Scripting Tagged With: Container fields, Demo file, Integration

  • « Previous Page
  • 1
  • 2

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