Plugin Archives - MightyData

Exporting Cross Platform vCards From FileMaker

September 19, 2013 by Darren Burgess 6 Comments

vCard is a standard file format for distributing contact information from applications such as Mac’s Address Book and Microsoft Outlook. These applications have the ability to export contact records into a vCard file, or import an existing vCard. We see them all the time attached to emails.

Naturally, as FileMaker developers we may consider how we can leverage this data sharing tool in our FileMaker applications. If you want to create your own vCard export, then you can head over to Wikipedia and check out the vCard 3.0 specification and use that to build your calculations and scripts to format your contact data into the required format. Store that in a field and export the field contents and you are done. You can even use FileMaker to pop it in a new email. Easy.

One Step Forward

Now, any self-respecting experienced developer is also lazy, and in the process of solving this problem I first searched to see what else had been done. Gotta get it done fast, right? So a quick search found a simple vCard export solution, by Chris Pye of MacTec. There is a sample file there as well.

You would think I was just about done now, with the only steps remaining being to integrate Chris’ scripts into my customer solution. (Thanks for sharing, Chris!) Unfortunately there is one ginormous caveat, and that is the resulting vCard, when generated from a Mac, is not compatible with Windows. And my customer works entirely on Macs but requires that vCards are cross-platform. What happens when you try to import a Mac generated vCard into Windows Outlook is… nothing. Blank fields.

It so happens you can actually open the vCard file (it is just text) in WordPad, then re-save it, and it will then open in Outlook, however I can’t really ask my customer to do that. That WordPad trick did provide me a clue however.

File Encoding Is Crucial

Turns out that FileMaker’s Export Field Contents script step defaults to UTF-8 character encoding (hey – someone fact check that for me). That format is not really relevant, other than I thought this might be why it was not working in Windows. Next I thought I would check out the free Base Elements plugin (thanks, Nick!) to see if it had a function that would do what I needed. Luck was on my side, as there are two functions that will help, BE_SetTextEncoding and BE_WriteTextToFile.

After the Export vCard script deals with constructing the vCard data in the variable $vCardData, here is what the script steps look like:

# Set the FilePath
Set Variable [$FilePath; Value:Get ( DesktopPath ) & "vCard.vcf"]
# Set text encoding to ANSI
Set Variable [$var; Value:BE_SetTextEncoding ( "ANSI_X3.4-1986")
# Create a file using the data stored in the local variable.  
# This step replaces the usual Export Field Contents script step.
Set Variable [$var; Value;BE_WriteTextToFile ( $FilePath ; $vCardData )]

Just When I Thought It Was Done

But we are not. The file generated will not open in Microsoft Outlook. Bummer. Not one to give up, I tried opening the vCard in Windows NotePad and found that there were no carriage returns, just a string of data mashed together. Clearly, Windows did not like the FileMaker “¶” character that was used to build the vCard data.

Not all paragraph returns are the same – check out the table of Unicode characters documented in FileMakers Code() function. Code(10) is a LineFeed. Code(13) is a Carriage Return (remember typewriters?!? A carriage return is a linguistic artifact from this ancient invention). Next step, and the one that finally solved the issue, was to substitute all of the FileMaker “¶” characters with LineFeed characters using the Char() function. Something like this:

Set Variable [$vCardData; Value:Substitute( $vCardData; "¶"; Char (10) )]

And now the vCard, generated from a Mac resident FileMaker database, is automagically cross-platform. Maybe next time we will work on vCard importing. See you then!

Getting Started With QuickBooks Integration

March 18, 2013 by Anders Monsen 2 Comments

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

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

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

On Screen Reference Manual

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

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

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

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

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

Which Message Type?

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

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

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

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

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

Further Explorations in SQL for FileMaker

October 21, 2011 by Anders Monsen 1 Comment

Once you start down the path exploring new features or technologies, often the deeper you dig the more cool stuff you expose. In my first post on SQL and FileMaker I focused simply on pulling and comparing data between tables. There are several advantages to employing SQL in FileMaker: speed and context perhaps the primary ones. While SQL the graph remains clean, as the queries remain inside the script, no additional Table Occurences are required.

One area in FileMaker, where we tend to rely on jumping through hoops in the graph, deals with report creation. This is especially true when pulling together reports from multiple sources. Solutions require extra TOs to build queries through relationships, and significant scripting to hop between contexts and gather data.

A recent game changer is Bruce Robertson’s Virtual List (see Lisette Wilson’s recent article and her follow-up for an overview), which gathers data into a utility table for the report output. Combining SQL and the Virtual List seems like a perfect marriage. Martha Zink, Advocate of Awesomeness at MightyData, recently showed me an example of SQL combined with the Virtual List. This post borrows heavily from concepts in her solution.

SELECT From ( SELECT )

Again, for the SQL queries I used a plugin. Several plugins expose FileMaker’s internal SQL. I selected MMQuery from CNS Plug-ins. One nice feature of its Execute SQL function is the ability to set your own delimiters between fields returned in the result. The default delimiter is the comma, but when returning number you may end up with extra commas and I set my delimiter to the pipe symbol. Other plugins may not have this feature, and require you to substitute the comma for the pipe symbol before sending the data through the Virtual List process.

(I recently discovered another plugin, from the makers of Base Elements, that is not only free but lets you execute Shell and SQL, plus a host of other features.)

Although I’m not going to show long query examples in detail, I want to highlight one SQL idea used to build the queries that get sent to the Virtual List “engine”, and how to parse the output from SQL in order for the Virtual List engine to parse the data. The beauty of the SQL in Martha’s solution turns on an elegant and powerful technique in SQL: subselects.

In essence, this is a query nested within another query. The advantage lies in removing joins, which makes the query more readable. Subselects also allow you to build result sets against which you then can run other queries. For example, imagine if you want to pull a list of fields using an ID value or set of IDs. You first want to get that ID set, and then you can select information based on this list.

select <> from Table as T where T.ID in
( select O.ID from OtherTable as O where age < 45 )

To illustrate this process I added some formatting. The key lies in bracketing the select that creates the initial result, and then selecting from this above the result. This “inner” query inside the parentheses is processed first. With “in” the outer query looks at a list of records.

The inner select in the above example is a very simple statement. As with any other select in SQL you can change this to look across multiple tables, using multiple criteria, AND/OR, and other options. Using subselect is almost like creating virtual tables that exist solely within a query. Inside the <> would be all the fields required in the final result.

Dynamic Criteria by the User

In the solution that called for SQL, each report called for user-selected criteria, and searching across multiple tables. Relying solely on the graph or scripting to build the data would have required complex TOs and scripts jumping from layout to layout to build data, possibly even hard-coding criteria. By making the reporting dynamic, users could select from drop downs for different criteria, such as “single” and/or “double” graft. The query would build a pair of select statements, with the sub-select handling building a second list.

SELECT
DISTINCT P.id
FROM (( Patient as P
JOIN Admission as A ON P.id = A.idPatient )
JOIN Intervention as I ON A.id = I.idAdmission )
JOIN allvalue as AV on I.id = AV.idIntervention
WHERE lower( AV.Type ) = 'proximal'
AND lower( AV.Field1 ) = 'aortic arch'
AND lower( AV.Field2 ) = 'graft replacement'
AND lower( AV.Field4 ) LIKE '%single y-graft%'
AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'

OR P.id IN (
	SELECT DISTINCT P.id
	FROM (( Patient as P
	JOIN Admission as A on P.id = A.idPatient )
	JOIN Intervention as I ON A.id = I.idAdmission )
	JOIN allvalue as AV on I.id = AV.idIntervention
	WHERE lower( AV.Type ) = 'proximal'
	AND lower( AV.Field1 ) = 'aortic arch'
	AND lower( AV.Field2 ) = 'graft replacement'
	AND lower( AV.Field4 ) LIKE '%double y-graft%'
	AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'
	)

While both queries end up fairly complex, imagine the complexity if she had to rely on joins to perform both queries. All of this takes place inside FileMaker, using (in her case) the SyncDek plugin for the SQL. Combine the power of SQL with the Rapid Application Development (cool interfaces) of FileMaker, and you get true development synergy.

Select * From FileMaker

August 31, 2011 by Anders Monsen 11 Comments

FileMaker Pro succeeds as a database system because of its many layers. As a rapid development database you can create a robust and elegant solution fairly quickly. Dig deeper, invest some time and effort, and you expose powerful tools and options that extend your solution. One such hidden tool is FileMaker’s internal SQL engine, which intrepid developers expose via plugins.

You might ask why bother, since SQL is a complex language with specific syntax we must learn outside FileMaker. Also, almost everything that SQL does within FileMaker can be natively accomplished without adding plugins. However, I believe there are times when the power of SQL combines nicely with FileMaker to produce better, smarter solutions, and this makes learning and using SQL worthwhile.

Some of the reasons to look at SQL in FileMaker include context-free queries, a simplified the relationship graph (fewer TOs in the graph), avoiding complex finds in scripts, and flexibility in building reports. In FileMaker, context is king. When creating or finding records through scripts, for instance, failing to start on the right layout could lead to drastic consequences. SQL removes these context constraints.

Several plugins enable access to FileMaker Pro 11’s internal SQL via functions. These include:

MMQuery from CNS
DoSQL from myFMButler
2empowerFM SQL Runner from Dracoventions
ScriptMaster from 360Works

For my testing I used MMQuery, and primarily its ExecuteSQL( query ) function.

My task required comparing two tables with ca. 1 million records each and finding the records from Table A that were missing in Table B, and then make some adjustments to these records. Although I probably could have accomplished this in FileMaker, I like the elegance of SQL as it allows you to select data from various tables, and return only the results that you need. Never before having tried SQL in FileMaker, I started small, testing a basic query to discover constraints or differences in the SQL language from my experience with MySQL.

While testing the SQL queries I discovered that the tablename in each SQL statement refers to FileMaker’s table occurrence name, not the base table name. In my project database, one of the tables I queried lacked a corresponding table occurrence with the same name. The first query resulted in an error that the table did not exist, and when I switched to a table occurrence name, the query worked. Table names are not case-sensitive, so if you have a “Contacts” table occurrence name, you can use “select * from contacts” and the query works.

The Select statement returns single or multiple columns (fields) from single or multiple tables. The syntax follows a certain sequence. FileMaker, Inc’s ODBC/JDBC Developer Guide shows some SQL examples. The initially daunting syntax can be broken down into just the pieces you need from the options:

All text inside the square brackets are optional. I created a script to set two variables – a $query with “select * from contacts” and a $result with ExecuteSQL( $query ) so I could check these in the Script Debugger and use $result in subsequent steps in my script. The “*” in SQL is the same as “all” and gathers all the fields from all the records into the result. To narrow down the query you need to specify the field, or in SQL terms, the column. Querying across a single table requires just the column name, such as “select nameFirst from contacts”, but when querying multiple tables you need to include the tablename or table alias as a prefix to the column name, such as “select contacts.nameFirst from etc.”.

Now, returning to the problem at hand: how to get values from Table A that are not in Table B? Imagine two tables: author and book. In the author table we have an author ID field and the author name. In the book table we have a book ID field, an author ID field (foreign key), and the book title. I need to find authors with no titles in the book table. Starting with a simple join, I can see all the records that match (for these examples I have capitalized SQL commands, and added returns for the sake of clarity):

SELECT *
FROM author, book
WHERE author.id = book.author_id

But this query returns authors and titles that are in both tables, and I need the reverse. Using a LEFT JOIN produces the records that matches and those that do not match.

SELECT * FROM author
LEFT JOIN book
ON author.id = book.author_id

Since this returns all the data from both tables, I need to isolate the records that do not match. In SQL terms these would be any records with NULL values in the related field, ie. the book.author_id. By adding a WHERE clause to limit the found set, the result is what I need.

SELECT author.* FROM author
LEFT JOIN book
ON author.id = book.author_id
WHERE book.author_id IS NULL

Using “author.*” instead of “*” also limits my result set to just the records from the author table. No joins are required in the relationships graph, and my script can execute the query from any context. The excitement of seeing values populated into $result from the SQL query–even the most basic of queries–told me that using SQL as part of FileMaker opens up new vistas in developing solutions. In another post I’ll cover another powerful feature of SQL called sub-selects, and how this can simplify complex reports.

SyncDek: An Alternative for Audit Logging

July 14, 2011 by Kirk Bowman Leave a Comment

Auditing logging is a common request from customers—the ability to track database activity including adding, editing, and deleting records, who changed it and when. In some types of businesses, like medical and financial, the law requires it. Generally, there are two approaches to audit logging in FileMaker, either native functionality or a third-party tool.

WorldSync has two products that provide audit log functionality. FMDataGuard is a client-side (FileMaker Pro) plug-in that can log database activity using function calls in various calculation windows in FileMaker Pro (security, validation, etc.). SyncDek is a separate server application that communicates with FileMaker Server Advanced via JDBC to log database activity.

While FMDataGuard works well in typical situations, there are some performance considerations in high transaction and network environments. The reason is all the effort to log database activity happens client-side and must travel over the network in a multi-user environment. SyncDek provides a solution to performance issues because it moves a significant amount of the effort to log database activity to the server.

Case Study

Earlier this year we developed a new medical research database for one of the top cardiothoracic surgeons in the United States. Since the database contains “identifying” patient information, it must log database activity. We decided to implement FMDataGuard to meet these HIPAA requirements.

The database was deployed in a Citrix environment with FileMaker Server. During beta testing, the users reported slow performance that we determined was due to the overhead for FMDataGuard. Per the recommendation from Jason Erickson at WorldSync, we implemented SyncDek to replace FMDataGuard and the performance issues were resolved.

SyncDek Approaches

SyncDek is primarily known for database replication (syncing). However, it has additional functionality including backup recovery and audit logging. SyncDek offers a couple of different approaches to audit logging. The “PubFlag” method queries the status of the PubFlag field in each table to determine the activity to log. The advantage of the PubFlag method is that it is compatible with FileMaker Go (iPhone/iPad) and server-side scripting with FileMaker Server.

The “Live Posting” method uses the SyncDek plug-in (not the same as FMDataGuard) to push a request to log activity to the SyncDek server. The advantage of Live Posting is the auditing of activity can be near real-time. Live Posting also supports logging views, which the PubFlag method does not.

In our case we opted for the PubFlag method for logging adds, edits and deletes to move as much of the effort to the server as possible. Since logging views was also a requirement for this customer, we used Live Posting although the client-side impact to log views is light except for large found sets.

Digging Deeper

To gain a better understanding of what’s happening under the hood, I created a contact database with 5,000 records and implemented each method in turn. SyncDek uses a “temp” table in FileMaker to store requests to log database activity. It also stores the audit log in FileMaker (although it is could be a SQL table).

I opened the contact database from FileMaker Server and created three windows—one for the data entry layout, one for the temp table, and one for the audit log table. By creating, editing and deleting records in the data entry window, I was able to see SyncDek processing records in the temp table and creating records in the audit log table. If you implement SyncDek, I strongly recommend using this practice to see how SyncDek works.

To log deletes using the PubFlag method, WorldSync recommends using a custom menu to override the Delete Record and Delete All Records commands. The custom menu calls a script to change the value in the PubFlag field to “4” to mark the record for deletion. SyncDek then performs the delete and stores it in the audit log.

To log views using the Live Posting method, the Sync_LogView function is added to the limited View privilege for each table under Security. If you have three windows open, you will see SyncDek post a record to the “temp” table for the view. Then during the next cycle, SyncDek will track the view in the audit log table and delete the record from the “temp” table. Of course, this requires the SyncDek_TCP plug-in to be installed with FileMaker Pro.

Alternate Techniques

Once I had an understanding of how SyncDek works, I started experimenting with some alternate techniques. When the user performs a Find and SyncDek is logging views, it creates a View record in the “temp” table for every record in the found set. For founds sets of a few hundred records or more, this can have a negative impact on performance. My challenge was to determine a way to log a view for only the first record in the found set immediately after performing a Find.

My first idea was to use a custom menu, the same technique as logging deletes with the PubFlag method. This would override the default behavior of the Previous and Next commands in the Go To Record submenu. Unfortunately, the Previous and Next commands are not available in Custom Menus. Specifically, I wanted to use the “Based on existing command” option so logging would occur even if the user clicked the “Go to previous/next record” buttons in the FileMaker toolbar. (Request to FMI: Please provide the ability to override all commands and subcommands in the standard FileMaker menus via Custom Menus.)

For my second attempt, I tried to create a limited View calculation to check a condition and decide whether to call the Sync_LogView function. I tried several variations including setting a global variable via a script or conditional formatting, and using the Get ( WindowMode ) function. Unfortunately, the order of execution in the limited View calculation does not return a consistent result. If there were a way around this limitation, there are several possibilities to implement this technique.

For my third attempt, I setup an OnRecordLoad trigger to run a script with the Sync_LogView function. It works! The downside is the script trigger must be setup separately for each individual layout. Also, since it is implemented at the interface layer (layout) instead of the data layer (security), it may not meet the requirements in some regulatory situations.

On the upside, the script for the OnRecordLoad trigger, can be modified to work natively with FileMaker (without a plug-in). This replaces the Sync_LogView function by creating a record directly in the “temp” table. (I got this idea while watching activity in the temp and audit tables in the three-window scenario.)

Conclusion

Audit logging is one of three core features available with SyncDek. If you are already using the backup/recovery or syncing functionality, it is a no-brainer to use the audit logging, if you need it. If you are using FMDataGuard or another product/technique, SyncDek provides an alternative to address performance issues on the client-side.