Help automating Filemaker Pro -> Excel -> Word

[discoforce]

Before I start describing my convoluted hopes and dreams for these apps, let me just say that any and all ideas/suggestions are welcome - I'm feeling pretty stuck

Here's my problem: I spend much of my working hours creating decent looking reports in MS Word that include a combination of text and bar graphs based on data I have entered in other apps (SPSS and Filemaker Pro). It really burns me that I essentially have to enter the data twice (once in Filemaker and then again in the individual Word reports), and it seems like there should be an easier solution (and yeah, I need the data in Filemaker for other reasons so I can't skip that step).

I think I can do a mail merge to link Filemaker and Word to update text fields (suggestions for this step are welcome!), but what really takes the most amount of time is updating many many graphs in each report.

From Filemaker Pro's website, Mac OS doesn't support Object Linking and Embedding (OLE) so it sounds like I can't connect data/fields from Filemaker Pro to graphs in Word. Can anyone confirm this, and does it mean that there is no way to link Filemaker data to graphs?

Based on this, it looked like the only way to do this was to export Filemaker data to Excel, create the graphs in Excel, then copy and paste to Word (pasting as a "linked" object). Doing this I could create a report template that would be updated whenever the Excel data was updated.

Before I spend the many hours trying setup such a system, I'm hoping to get your suggestions on whether this is sound or if you have other ideas that would work.

Thanks so much for reading this, and I appreciate your help!

 

[MisterMe]

Before I start describing my convoluted hopes and dreams for these apps, let me just say that any and all ideas/suggestions are welcome - I'm feeling pretty stuck

Here's my problem: I spend much of my working hours creating decent looking reports in MS Word that include a combination of text and bar graphs based on data I have entered in other apps (SPSS and Filemaker Pro). It really burns me that I essentially have to enter the data twice (once in Filemaker and then again in the individual Word reports), and it seems like there should be an easier solution (and yeah, I need the data in Filemaker for other reasons so I can't skip that step).

I think I can do a mail merge to link Filemaker and Word to update text fields (suggestions for this step are welcome!), but what really takes the most amount of time is updating many many graphs in each report.

From Filemaker Pro's website, Mac OS doesn't support Object Linking and Embedding (OLE) so it sounds like I can't connect data/fields from Filemaker Pro to graphs in Word. Can anyone confirm this, and does it mean that there is no way to link Filemaker data to graphs?

Based on this, it looked like the only way to do this was to export Filemaker data to Excel, create the graphs in Excel, then copy and paste to Word (pasting as a "linked" object). Doing this I could create a report template that would be updated whenever the Excel data was updated.

Before I spend the many hours trying setup such a system, I'm hoping to get your suggestions on whether this is sound or if you have other ideas that would work.

Thanks so much for reading this, and I appreciate your help!

Your thinking is limited to reproducing Windows technology on the Mac. There are many MacOS technologies that allow you to do what you want and none of them require OLE. Get to know Automator and AppleScript. Between the two, you can automat the workflow that you have described. Excel has its strengths, but producing graphs is not one of them. DeltaGraph (formerly published by SPSS) is the recommended app for producing presentation-quality graphs from Excel data. There are others. You can use Word for your final document, but I would go with a desktop publishing app that handles linked external documents. Use PDF for the format of your final documents.

 

[discoforce]

Thanks for the suggestion to try out Delta Graph - I'll give it a try for the 30 day demo.

You also mentioned rather than use Word I...

[...] I would go with a desktop publishing app that handles linked external documents.

Like what?

 

[ahunter3]

If you draw up a report skeleton in Word, formatted the way you want it but with various merge-codes for where you want field data from FileMaker, you can save the results as an RTF file (Word>Save As>RTF). Open the result in a plain-text editor such as BBEDit or TextWrangler so you can see the raw RTF code.

In FileMaker, you can then create a calculation field (result=text) consisting of the RTF code except interspersing the actual FileMaker fields by fieldname instead of the merge-codes.

You can now, at any point, export from FileMaker (just the calc field), export as text, with filename ending in ".rtf", and the result, when opened in Word, will be your report, already formated and ready to go. All you need now is a means of inserting your graphic elements.


Another option that may be feasible if your wordprocessor formatting is relatively simplistic (font & type sizes, justification, indentation), is to do the whole thing in FileMaker. FileMaker's text formatting tools are rather limited but that may be tolerable. You can generate the graphs and graphic tables in FileMaker with a little practice and/or some searching around on the internet for some templates (you can do bar graphs, pie charts, area charts, and gantt charts with a handful of calc fields).


Ultimately, automating anything is dependent on the predictability and standardization of what you're generating. The more consistent your final result is with previous versions of itself, the more you can format structures that simply display the changed data surrounded by "boilerplate" nonchanging elements.

 

[discoforce]

If you draw up a report skeleton in Word, formatted the way you want it but with various merge-codes for where you want field data from FileMaker, you can save the results as an RTF file (Word>Save As>RTF). Open the result in a plain-text editor such as BBEDit or TextWrangler so you can see the raw RTF code.

In FileMaker, you can then create a calculation field (result=text) consisting of the RTF code except interspersing the actual FileMaker fields by fieldname instead of the merge-codes.

You can now, at any point, export from FileMaker (just the calc field), export as text, with filename ending in ".rtf", and the result, when opened in Word, will be your report, already formated and ready to go. All you need now is a means of inserting your graphic elements.

This is a huge help, I'll definitely try this out!

You can generate the graphs and graphic tables in FileMaker with a little practice and/or some searching around on the internet for some templates (you can do bar graphs, pie charts, area charts, and gantt charts with a handful of calc fields).

I can't find any info on getting Filemaker Pro to create bar graphs. My web searches revealed companies that will create such templates for you ($$$), and the Filemaker folks apparently created a dashboard widget, but nothing like you suggested (Filemaker pro templates that create bar graphs, pie charts, etc.).

Any ideas on where I could find such templates?

Thanks again - I feel like I'm getting so close

 

[ahunter3]

I can't find any info on getting Filemaker Pro to create bar graphs. My web searches revealed companies that will create such templates for you ($$$), and the Filemaker folks apparently created a dashboard widget, but nothing like you suggested (Filemaker pro templates that create bar graphs, pie charts, etc.).

Any ideas on where I could find such templates?

How about here?

 

[discoforce]

Now THAT was a cool template! And Filemaker Pro says they have no chart capabilities...

Based on your template, I'm convinced I need to spend more time learning what Filemaker pro is capable of. After that: I tackle applescripts.

Thanks much!!!

 

[ahunter3]

You should see the pie charts

I'm convinced I need to spend more time learning what Filemaker pro is capable of.

Pretty much everything, even if it's not necessarily the best tool for any given job. It will make coffee in Chicago for your Illinois-office colleagues if you want it to. Makes an adequate file-backup utility if you get tired of Carbon Copy Cloner. It has been used as a videosteam display device for a 25 year old Xray interpreter. Makes quite nice Helpdesk software. Can substitute in a pinch for an operating system's entire GUI. Etc.

 

[discoforce]

You should see the pie charts

I'd love to! Did you create these templates or have you found treasure trove of Filemaker templates somewhere? I'm no programmer (and the calculation scripts for the bar graph looked way beyond my knowledge), but usually with a few templates I can cut and paste my way to customization.

Also based on your advice, I'm going to try creating the entire report in Filemaker. In it's current incarnation it looks pretty fancy in Word, but I'd give that up in a heart-beat for being able to generate complete, or nearly complete, reports by simply selecting a group field. And I do want each report to look exactly the same.

It will make coffee in Chicago for your Illinois-office colleagues if you want it to.

Now THERE'S a template I'd like to see!

Thanks a million!

 

[ahunter3]

I'd love to!

Ask and you shall receive.

Did you create these templates or have you found treasure trove of Filemaker templates somewhere? I'm no programmer (and the calculation scripts for the bar graph looked way beyond my knowledge), but usually with a few templates I can cut and paste my way to customization.

The graphic elements for the bars and the pie wedges have been floating around the FmPro dev community for a few years. Mostly people write their own calcs. I did these templates as "snippets", the calc fields are my own. I've seen other folks do the calcwork using repeating calc fields but I'm not fond of those myself.

The calcs are only intimidating until you break them down. You look at something like

GetRepetition(Bars1,
100*Round(ExampleValue 1/Case(not IsEmpty(HundredPercentValue), HundredPercentValue, Max(ExampleValue 1, ExampleValue 2, ExampleValue 3)), 2)+1)

and yeah, sure, your first reaction is

but put some hard returns so you can see how those functions are nesting:

Get Repetition(Bars1, 
<buncha stuff> 
 + 1)

Ignoring for the moment the specifics of <buncha stuff>, you're asking for a specific repetition of a repeating field (Bars1), and the repetition you're asking for is <buncha stuff> plus 1. With me so far?

Now what is <buncha stuff>?

100 * Round (<lotsa junk>, 2)

Here you're saying to round off the results of <lotsa junk> to 2 decimal points, like 2.34 or 7.30, and then multiply the results by 100, e.g., 234 or 730. I will cheat a bit for you and tell you right now that the results of <lotsa junk> are always gonna be less than 1, so after you round it and then mutiply it by 100 you're always getting a whole number between 1 and 100. Which is the digit that, after adding 1 to it, tells FmPro which repetition of the repeating field Bars1 to use, see?

Now: <lotsa junk> unpacks as:

ExampleValue 1/Case(
                                   not IsEmpty(HundredPercentValue),
 HundredPercentValue, 
Max(ExampleValue 1, ExampleValue 2, ExampleValue 3)
                                  )

This is just division. ExampleValue 1 is your input field, that's your numerator. The denominator is either the HundredPercentValue (if it's not empty, i.e., you typed something into it to tell FmPro to use it as your denominator), or, if you left HundredPercentValue empty, it's the maximum of the three input values.

See?

Originally Posted by ahunter3

It will make coffee in Chicago for your Illinois-office colleagues if you want it to.

Now THERE'S a template I'd like to see!

Everyone always thinks I'm kidding about the coffee in Chicago for some reason...

Ever see one of those "home automation" systems? You have the Chicago office's coffeemakers each set up with a specified grind / flavor and filled with water and ready to go, and the circuitry that corresponds to hitting the "brew now" button disassembled and hooked into the X-10; each coffeemaker's X-10 is hooked into a different port on the X-10 control board of a Mac running a looping script in FileMaker that looks for a record flagged with the username of that particular Mac account, and when it finds it it executes a Perform AppleScript command which tells the X-10 controller to key a signal which speaks to the coffeemaker, causing it to start brew.

To cue up the specific desired coffee flavor at the desired time, you Timbuktu into a Mac in Chicago and open FileMaker as a guest; do a find for the flavor you want, then on that record enter the time and date you want it to go, and the next time the looping script fires, once that time goes by, the coffeemaster Mac with the X-10 will do its thing. The X-10 coffeemaster Mac also fires off an email to kitchen staff indicating that the basket needs refilling, and flags that flavor as unavailable until reset. When kitchen staff refills the Mister Coffee with water and a fresh basket, they hit a toggle switch that feeds (of course) into the X-10 and FileMaker then knows that that pot is available to brew again.

Not terribly practical but lots of fun for blowing the new IT manager's mind right after your company has just been acquired by a Chicago-based entity


I must confess that I do not hold the crown in this department. One of my colleagues used FileMaker to operate a Billy Bass talking fish from a remote location.

 

[jeremy.king]

I wonder if e.Report and e.Spreadsheet are up your alley for automating your spreadsheets and reporting, that is if Filemaker has a JDBC driver - which I think it does.

http://www.reportingengines.com/home/index.jsp

 

[discoforce]

I wonder if e.Report and e.Spreadsheet are up your alley for automating your spreadsheets and reporting, that is if Filemaker has a JDBC driver - which I think it does.

http://www.reportingengines.com/home/index.jsp

Thanks for the suggestion. It does look like they can make the reports I'm looking for, but too pricey for me.

 

[discoforce]

Ask and you shall receive.

I was kinda hoping

The graphic elements for the bars and the pie wedges have been floating around the FmPro dev community for a few years.

So that's the group I needed to break into to get my charts. I humbly thank you for the introduction.

The calcs are only intimidating until you break them down. You look at something like...

<lotsa buncha scary FM Pro syntax>

See?

Yeah, actually I was able to follow the syntax when you broke it down like that. I can usually follow the logic of most syntax, it's just the language that gets me. Playing around more with FM, it looks like the script/calculation editor will help me create what I need.

Everyone always thinks I'm kidding about the coffee in Chicago for some reason...

<insert information on home automation that will spark my next series of convoluted questions on MacRumors>

Not terribly practical but lots of fun for blowing the new IT manager's mind right after your company has just been acquired by a Chicago-based entity

That's awesome!

ahunter3, thanks so much for the templates and the tutorial. I now know enough to be dangerous.

 

[jeremy.king]

Thanks for the suggestion. It does look like they can make the reports I'm looking for, but too pricey for me.

Well, I could get you a discount My company is a partner of theirs