MightyData

FileMaker and WordPress Consultants

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

Using a Web Service to Generate Barcodes

September 4, 2013 by Anders Monsen 4 Comments

Recently I came across a web service over at ProgammableWeb that generates barcodes. This might be useful if you scan barcodes, and want an alternate method to create barcodes in your FileMaker solution.

The web service, Barcodes4.me, is a RESTful service and requires no API or fee. It supports a handful of barcodes types, as well as QR codes, and requires a simple URL call. Using FileMaker 12’s Insert from URL you can send a query to this web services and insert a barcode image into a container field.

Given that this is a free service, there is no guarantee how long it will remain available or a free service. The types of barcodes available also are limited. This is still a good option to keep in mind when developing and testing a mobile solution with barcodes. I have included a small demo file to show this in action.

WebServicesDEMO.fmp12

Filed Under: Barcodes Tagged With: Demo file, FileMaker Go, Insert From URL, Web service

Using SQL to Duplicate a Record Set

July 26, 2013 by Anders Monsen Leave a Comment

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

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

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

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

Download example file Tickets.fmp12.

The Related Table Query

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

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

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

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

Alternate Duplicate Record Option

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

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

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

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

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

After Parent Record duplicated

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

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

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

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

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

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

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

Filed Under: Calculations Tagged With: Data conversion, Demo file, ExecuteSQL, FileMaker 12

Propa-Gator: Modular Record Creation

May 21, 2013 by Darren Burgess 4 Comments

Creating records from global fields

A few weeks back I posted my first contribution to Todd Geist’s new collaborative effort to create a library of shared FileMaker modules. This script was originally inspired by the need to create reusable code for my customer projects at MightyData. My goal with the Propa-Gator module was to increase my coding efficiency by creating a portable script that would allow the creation of records and setting of any number of fields in the new record with any desired value.

In addition, I wanted the script to be able to use global field inputs as the field values in the new record and to nullify those globals on completion. Finally, I wanted to ensure that the script was completely context free and immune to field renaming.

The result was a script that I found to be a very useful in a variety of contexts:

  1. Related records can be created such as log records, phone numbers and attributes.
  2. Flat records can be normalized.  For example, if you have a table with two fields representing phone number 1 and phone number 2, you can loop through the found set of records and call the CreateRecord script twice, creating related phone number records in a phoneNumber table. I found this to be extremely useful when importing excel spreadsheets supplied by customers.
  3. Create record from global fields. Below is a screen shot from the examples provided with the Propa-Gator file.  In this example, the user can enter values in two global fields. The second field uses an OnObjectExit trigger to launch the CreateRecord script with the globals used as data inputs. The script has the smarts to identify that the inputs were from global fields and will nullify those globals.

Creating records from global fields

Scripted Find Requests

A common FileMaker technique is to build find requests using something like the following sequence of script steps:

Enter Find Mode []
Set Field [someTable::DateField1 ; ">=01/01/2010"]
New Records/Request
Set Field [someTable::StatusField1 ; "Complete"]

The CreateRecord script can be used in this case to create the find requests, as in example 3 that is included with Propa-Gator.

Enter Find Mode []
# Creates and sets values for first find request
Perform Script [CreateRecord] 
# Creates and set values for second find request
Perform Script [CreateRecord] 
# Delete the first request (it is blank)
Go to Record/Request/Page [First]
Delete Record/Request [No dialog]

Implementing CreateRecord Script

Implementing the CreateRecord script in your solutions is as easy as copying four scripts, three of which are for script trigger suppression and are not required. After you copy the scripts, take a look at the examples to explore ways of implementing the CreateRecord script in your solution. The most difficult part of this process will be properly implementing the script parameter that is passed. While the parameter syntax is clearly documented in the comments of the CreateRecord script, here is the example parameter with a bit of explanation. The parameter, when parsed in the script, creates three local variables: $CR_LayoutName, $CR_TargetFields, and $CR_Data.

"$CR_LayoutName = " & Quote ("DEV_Child")  & ";¶" & 
"$CR_TargetFields= " & 
Quote (
  List ( 
    GetFieldName ( Child::id_Parent ) ;
    GetFieldName ( Child::ChildData1 ) ;
    GetFieldName ( Child::ChildData2 )
  )
) & ";¶" & 

"$CR_Data =  " & 
Quote (
  List ( 
    Parent::id ;
    "SomeData" ;
    Get (CurrentDate) 
  )
)

The first line of the parameter establishes the layout name. When the script parses the script parameter, it will create a local variable $CR_LayoutName and set it to the value specified. The script will navigate to this layout and create the new record in the context of that layout.

"$CR_LayoutName = " & Quote("DEV_Child")  & ";¶" &

The next variable in the parameter, $CR_TargetFields, is a list of the fields in the new record that will be set by the script. Note that the list is wrapped in quotes by the Quote() function and each field name is wrapped by the GetFieldName () function. Use of GetFieldName () is critical to ensuring that changes to field names do not break the script parameter. In this example, the script will create the record and set the three fields in $CR_TargetFields list to the values specified in $CR_Data.

"$CR_TargetFields= " & 
Quote ( 
  List ( 
    GetFieldName ( Child::id_Parent ) ;
    GetFieldName ( Child::ChildData1 ) ;
    GetFieldName ( Child::ChildData2 )
  )
)

The final variable in the parameter, $CR_Data, is a list of data values. In this case, the three fields will each be set to the data value that occupies the same place in the list. Child::id_Parent will be set to the value of Parent::id, Child::ChildDate1 will be set to “SomeData” and Child::ChildData2 will be set to today’s date:

"$CR_Data =  " & 
Quote ( 
  List ( 
    Parent::id ;
    "SomeData" ;
    Get (CurrentDate)
  )
)

There are a few tricks to keep in mind regarding the $CR_Data variable. First, the data values can be any field accessible from the original context of the script (in this case the Parent record), or any literal or calculated value. If the source of the data value is a global input field as in the screen shot above, then the global field name should be wrapped with GetFieldName (). Doing so tells the script that source data came from a global field, and that the global field should be nullified as the script executes. Here is what that should look like in the parameter:

"$CR_Data =  " & 
Quote ( 
  List ( 
    Parent::id ;
    GetFieldName ( Globals::GlobalInput1 ) ;
    GetFieldName ( Globals::GlobalInput2 )
  )
)

The script can also handle data field values that have paragraph returns. For example, you may want to create a record and insert a list of values in a field. In this case, you need to substitute the sequence of characters “~CR~” to represent the paragraph return. The script then substitutes a real paragraph return when it encounters the string in a data value. In this example the third field will be set to a return-delimited list containing the values “FirstLine” and “SecondLine”:

"$CR_Data =  " & 
Quote ( 
  List ( 
    Parent::id ;
    "SomeData in single line" ;
    "FirstLine" & "~CR~" & "SecondLine"  )
  )
)

I should also mention that the script traps for a few errors. While most potential errors can be addressed by correctly passing the parameter of the script, I thought it would be prudent to trap for certain errors and return the error in the Exit Script[] step. In case of any trapped error, the script will exit and the new record will not be created. Errors from the following script steps are trapped:

  • Go to Layout[]: Generally caused by passing an invalid layout name, or perhaps security privileges that don’t allow access to the specified layout. (This is the only part of the CreateRecord script that is not immune to renaming).
  • New Record step:  An error may occur here when security privileges prevent new record creation.
  • Set Field By Name: Again, an error here could be caused by security privileges or perhaps a field name that does not exist in the context of the created record.

I hope you enjoy using the CreateRecord script in your solutions.  Since the work over at www.modularfilemaker.org is open source by nature, I invite you to pick apart and make suggestions in the comments below for improving the script. Or submit a revised file to darren_burgess@mightydata.com.

The latest version of Propa-Gator.fmp12 is available at: www.modularfilemaker.org

Filed Under: Rapid Development Tagged With: Demo file, Development standards, FileMaker module

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).

Data from contacts table pulled into the virtual reporting table

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.

Filed Under: Scripting Tagged With: Demo file, ExecuteSQL, Reporting, Virtual list

SQL Reserved Words in FileMaker

December 20, 2012 by Anders Monsen Leave a Comment

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

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

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

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

"Select TableName from FileMaker_Tables"

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

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

"Select Distinct BaseTableName from FileMaker_Tables"

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

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

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

SQL_Reserved.fmp12

 

Filed Under: Calculations Tagged With: Demo file, Development standards, ExecuteSQL, FileMaker 12

Scanning Barcodes with FileMaker Go – Part 2

November 8, 2012 by Anders Monsen 35 Comments

Mobile app to scan or swipe data with iPhone

This is part 2 in a 3-part series.

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

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

The Interface

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

Mobile app to scan or swipe data with iPhone

Pic2Shop

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

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

This url breaks down as follows:

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

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

pic2shop://scan?callback=fmp%3A//%24/"&Get(FileName)&"%3Fscript%3DScan%26param%3DEAN
Characters Encoded
Dollar $ %24
Space %20
Equals = %3D
Ampersand & %26
Colon : %3A
Question Mark ? %3F

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

CNS Barcode

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

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

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

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

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

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

Scanning barcode with an iPhone

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

Data from multi-scan mode in CNS Barcode app

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

barcode-data-in-cns-app

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

Data from barcode in FileMaker Go

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

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

GoInput.fmp12

Filed Under: Barcodes Tagged With: CNS Barcode, Demo file, FileMaker 12, FileMaker Go

Getting Faster: Installing Multiple Instances of FileMaker

August 15, 2012 by Darren Burgess 16 Comments

Multiple FileMaker installs on Mac OS

As you may have guessed by now, I love working in an optimized environment, designed for getting the crud out of the way so that I can focus on development. Just like a carpenter needs to not be thinking about the hammer, or the painter about the mechanics of the canvas, my computer and its operating system need to fade into the background, almost as if they were not really there at all.

Have you ever, in the course of working with a database, needed to do a long import, export or other time-intensive task?  For example, I have worked on a very large solution recently that required 4 hours to import the DDR into BaseElements. Not wanting to lose an entire 1/2 day to the task, I needed a solution where I could both perform the import and continue working on other projects.

More Than One Install

The best solution to this conundrum is to simply have more tools available. In this case, since I have two legal licenses of FileMaker 11 available to me, I have installed the application twice on my development machine.

Multiple FileMaker installs on Mac OS

Here are the steps to do so on Mac OS X:

  1. Rename the current installed version folder of FileMaker Advanced (or FileMaker). I used FMA11-A.
  2. Rename the installed FileMaker Pro Advanced application. Again I used FMA11-A.app.
  3. Install FileMaker/FileMaker Advanced again, using the second license key.
  4. Rename the folder and install application for this new installation. (FMA11-B in my case.)

That’s it! Now both versions can be run simultaneously. Note that when performing an update of the FileMaker software, the updater will ask you which installation you want to update. Select an installation, complete the first update, then run the updater again and it will update the other installation.

Two Tasks At Once

I can run that ginormous DDR import on one instance and be happily developing a solution on the other. It also means you can have two Manage Database windows, two data viewers or two script debuggers open at the same time. Or even two separate logins (Admin and User for example) into the same solution.

Here is a peek at what this all looks like in Finder.  Just for fun, I created new icons (download) for each of the installations. In OSX, use the finder info pane to change the assigned icon for an application:

Well, the icons are more than just for fun, as they provide a visual cue in the OSX dock and application switcher.  I hope you enjoyed this quick tip for setting up a more efficient FileMaker development environment.  I’d love to hear about your super-secret tips and tricks in the comments.

Filed Under: Rapid Development Tagged With: Demo file, FileMaker 11, Productivity, Software tools

Real-World FileMaker Web Service

August 10, 2012 by Anders Monsen 14 Comments

Bookcase shelves full of books

In a previous post, I enthused about FileMaker 12’s new “Insert from URL” script step. The ability to pull in data from web pages through RESTful web services is akin to the science fiction idea of wormholes into other dimensions. Programmable Web currently lists over 6,000 web services in its directory, and the trajectory only points upward. Though some web services come with commercial restrictions, and others may change without notice, getting this data into FileMaker has never been easier.

In this article I’m going to move from general enthusiasm to showing a FileMaker Pro database that I built to import book information based on ISBN values (a link to my demo file appears below). The power of “Insert from URL” grows exponentially when combined with the FMP URL protocol and FileMaker Go. In a fairly short amount of time I was able to integrate my iPhone to scan a book’s ISBN bar code from within FileMaker Go–reading the data using an app called Pic2Shop, then using “Insert from URL” to load the rest of the book information, virtually eliminating any manual data entry.

The reason I chose books for my example is that it combines two passions of mine: books and FileMaker.

Bookcase shelves full of books

Years ago in FileMaker 5.0 days, I built a database to track my book collection. Each book has certain data I need to track, such as author, title, publisher, value. etc. Entering this manually took a great deal of time. Looking for books that I didn’t have also presented problems.

With hundreds of books (paperbacks included), keeping track of books I bought years ago became a tedious effort. I would write down on index cards ones that I had or wanted, and if I happened to be in a used bookstore, I’d pull out that card from my wallet and try to find the book. With the iPod, it was possible to export some text files to the iPod, but I didn’t always carry my iPod everywhere. I carry around my iPhone, and so text notes replaced the old paper notes. However, this method is far from efficient.

When FileMaker 12 came out I rebuilt my old library database using one of the starter solutions that came with iPhone and iPad layouts. After importing my records I now had access to my library on my iPhone, and could scroll through the list.

But what if wanted to add a book? Previously I entered most of my books by hand, or scanned them using Bookpedia or some other tool where I held up the book to my computer’s camera. But I like FileMaker. It lets me control the design of my library. I don’t want to scan books on my desktop using an app, then export that to FileMaker.

Using FMP URL and Pic2shop, I can set up an interface on my FileMaker Go version to scan the book’s ISBN and use Insert from URL to load up the data, all in about one second. So, to the details.

Here is a copy of the demo file (zipped): DemoLibrary

ISBN Lookup

There are several ways to get book details from the ISBN number. You can enter a scaled down Amazon.com URL, such as <http://www.amazon.com/dp/0743273567>. This will give you a web page with all the book information that you can scrape, if you want to spend hours figuring out what to pull from the huge HTML page. You could use Amazon’s own web service API, which would return XML. However, I found that Amazon some years ago changed its method, so that instead submitting a straightforward URL, you now had to encode that URL into a key using SHA encryption. After several fruitless attempts, I gave up.

As a commercial enterprise, Amazon probably wants API users to drive traffic to buy books from Amazon, not to pull book information for a personal project. Instead, I turned to Open Library and isbndb.com, services that seem friendlier and more open. Isbndb.com requires a developer API key, but the amount of information they have is staggering. My example files show both methods, though you need to provide your own API key for the ISBNDB API portion to work. Prior to testing the isbndb actions in my demo file you need to request and input your own API key in FileMaker Pro.

Scanning Books using iPhone

In my scaled down sample database, the scan process uses four scripts. For this process I relied on a sample file that Greg Lane from Skeleton Key showed me at DevCon 2012. Greg had demoed this process on his blog almost two years ago, but his process to import the data uses a web viewer and PHP. I wanted native FileMaker 12 stuff, which I already had with Insert from URL.

The first script, triggered from a button on the iPhone, is an Open URL script step:

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

This script opens Pic2shop, and once the book is scanned, calls a script in the database called “Scan”. I modified this script from Greg’s demo file to use “Insert from URL” and then it just parses the returned JSON from Open Library with some basic text parsing. (This is why we really need native JSON and XML parsing functions in FileMaker.) The ISBNDB result is in XML, and I found an existing function that parsed XML, except for certain attributes, and so it was back to text parsing for those pieces.

FileMaker script to scan book with iPhone

This script then “Inserts the URL” from Open Library into a global field and parses the result with a semi-accurate custom function. I say semi-accurate because the data from Open Library doesn’t always behave as expected. In the instance of The Great Gatsby, the title picked up “Wiki” as the JSON came over with multiple “title” nodes. Also, ideally the function would handle names like F. Scott Fitzgerald as well as L. Sprague de Camp and Clark Aston Smith, not just First Last named authors.

Book database in FileMaker Pro

This sample desktop includes a couple of fields where I pulled additional information from isbndb.com; again this requires an API key, and in this sample database that key would go in the System table. The key loads into a global variable on startup. Again, this is a demo file, so some functionality is spelled out to illustrate how things work.

FileMaker Go and iPhone

Once I moved the database to my iPhone, I could scan any new books very quickly, search for books, and even highlight any books on a wish list using conditional formatting — all very handy when far from home and trying to remember if I have a copy of this or that. Sometimes I have multiple editions, and I can tap on the title to view more details.

Book database in FileMaker Go (iPhone)

Conclusion

Combining Insert from URL and FMP URL, or just Insert from URL and FileMaker Go, truly gives you total control to create your own app in a pocket. With FileMaker Go and the iPhone, and all those thousands of APIs out there, just image what you could create! Not to mention turning your own FileMaker Server into a RESTful web service with Goya’s RESTfm, and consuming this as needed on your iPhone.

What are the next steps? The logical one is syncing. After all, once the book is scanned into FileMaker Go, you need your FileMaker Pro database to know this, so you don’t overwrite the books in your iPhone the next time you change your database and bring it over. I’ve heard good things about a certain SeedCode/Todd Geist product.

Filed Under: Barcodes Tagged With: Demo file, FileMaker Go, Insert From URL, Integration, Web service

Getting Faster: Create a Development Reference

June 21, 2012 by Darren Burgess 1 Comment

Calculation functions in Notation Velocity

As a developer looking to deliver projects to my customers with the highest value, I am constantly looking for ways to increase my development speed. To that end I have created a developers’ reference library using a handy (and free) little application called Notational Velocity.  NV is an OS X application that allows for rapid creation and search of notes. While I have not tested such, apparently ResophNotes is an equivalent Windows application.  Both applications sync with SimpleNote, so you can get your notes on the web and on an iOS device.

Notation Velocity Library

NV uses a search-while-you-type field to allow you to search your database of notes. If the application does not find a match, you can simply hit enter and it creates a new note with the title you typed in the search field. Notes are stored as text files in a directory of your choosing and the app provides the necessary keyboard shortcuts for the power user.

SQL reserved words in Notation Velocity

My NV library currently has about 300 entries that include:

  • All of the FileMaker error codes
  • About half of the calculation functions
  • Various tested SQL statements
  • SQL reserved words
  • Commonly used calculations

Naming Conventions

Using naming conventions helps to organize the library. For example,  the FileMaker calculation function notes are named “fmfk” + FunctionType + Function Name, so that I simply can type “fmfkdes val” if I want to find the note regarding the ValueListIDs function:

Calculation functions in Notation Velocity

Hint: NV stores notes in a database in the application support folder on OSX. In preferences, you can change the location and choose to store the notes as plain text files.

My Development Library

So, who wants to collaborate on building a more comprehensive reference library?  I would be happy to share what I have created so far.  We could expand the library to include:

  • FileMaker Server error codes
  • The balance of the calculation functions
  • Windows and Mac keyboard shortcuts
  • Port numbers
  • Script steps
  • And more

You can download the current version of the library below.  If you have a suggestion to add to the library, please post a comment below.

20120626 Developers Reference

Filed Under: Rapid Development Tagged With: Demo file, Development standards, Notational Velocity, Productivity, Software tools

3 FileMaker Demo Files

October 5, 2011 by Martha Zink Leave a Comment

Golden Gate Bridge

I presented these demo files during a series of user group meetings in the Bay Area. My goal for each demo file was to incorporate the technique into an existing solution and think of it outside of the box.

Snapshot Link

In this sample file, there are two applications of Snapshot Link:

  1. Launch file
  2. Bookmark when closing

For the launch file, open up the solution. Once you’re at the opening screen with the “About” and “Demo” buttons, create a Snapshot Link and place it on your desktop. As long as the file doesn’t move, the Snapshot Link on the desktop can serve as your launch file.

For the bookmark, there is a script called “On Close” that runs when the file closes (via File > File Options). It shows a dialog and asks the user if his place should be saved. If the user agrees, the script puts a Snapshot Link to the desktop, that the user can click to start off where the database was last left.

Demo File: Snapshot-Link

Filtered Portal

In these sample files, there are two treatments of Filtered Portal:

  1. New approach to tab control
  2. Report management

It’s common to create a solution with tab controls, but what if you wanted to change the look? The layout “Sample Menu | Tab no Hierarchy” shows you how a filtered portal can enhance the user experience and control the tabs. This technique takes a combination of the following:

  • Hidden Tab Control (Fixed Width of tab = 0) where each tab has an object name
  • The table “Tab” that stores the tab display name, the menu group, the sort order, and the object name for each menu.
  • The script “SwitchTabs”  takes the user to the correct object based on the object name stored in the Tab table.

The layout “Sample Menu | Tab” takes the technique to the next level, allowing a hierarchy of parent and child. The added tools for this technique work include:

  • A field in the table “Tab” called “Belongs To” that ties each record to its parent record
  • A global variable that contains the “Belongs To” value for the filtered portal

The other sample file for filtered portals shows how to use a filtered portal with global fields. Not only is the user interface clean, but the user has control over which reports he can see. You’ll notice the “Not isEmpty( )” in the Filtered Portal calculation. One of the things I’ve learned over time is that if the user selects nothing in a filter, he or she wants to see everything. It is counterintuitive but the expected behavior. Filtered Portals require quite a bit of trial and error.

Demo File: Filtered-Portal

Script Triggers

The latest version of FileMaker Pro introduces a few new triggers. In these sample files, there are several purposes for Script Triggers:

  1. OnViewChange – Changing views (meaing layouts) with the Toolbar
  2. OnObjectValidate – Providing feedback for erroneous data
  3. OnObjectKeystroke – Limiting keystrokes and characters

In the first sample file, the script “Change in Views” is being triggered by OnView Change. If the user clicks on the “list view” from the toolbar, the script is executed, taking the user to a layout with the same data in a list view. The same applies in table and form view. The important part of the technique is consistency in the layout naming. Each one ends with the word that defines the layout view.

In the second sample file, there are three examples:

  1. OnObjectValidate – Allowing a range based on the script parameter (Example: 1 to 10)
  2. OnObjectKeystroke – Not allowing the user to type anything but positive numbers (including the decimal point)
  3. OnObjectKeystroke – Not allowing the user to type anything but numbers (including the dash for negative numbers)

There is also an example involving the drop-down. One of the things my user discovered was the ability to type into the field if the drop-down wasn’t showing. By using OnObjectKeystroke, the user is limited to what keystrokes will occur.

However, it is important to note that individual keystrokes are allowed, even though they don’t produce a character. These keys include backspace, return, escape, and arrow keys. Removing these from the user’s capabilities would leave the user feeling handicapped instead of improving the interface.

Demo File: Script-Trigger

Filed Under: Scripting Tagged With: Demo file, Filtered portals, Script triggers

  • 1
  • 2
  • 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 © 2023 · Parallax Pro Theme on Genesis Framework · WordPress · Log in