Exporting data from FileMaker Pro 7.5

Posted on 2014-07-21
Last Modified: 2014-07-26
I am new to FileMaker.

I have three seperate databases:
 - Customers (Customer number, Name, Address, Account Balance)
 - Transactions (Date, Transaction Type, Amount)
 - Notes (Date, Comment)

I need to be able to access this data to produce a fairly complex report.

I'd like to export the data to dbase or excel files but can't work out how to write the scripts to do this. It needs to be fully automated.

My preference would be DBase but there is one field which is too long to export to DBase and I'm not sure how to handle this.

Can anyone point me in the direction of a good tutorial to step me through how to write the scripts to achieve this?
Question by:akb
  • 3
  • 3
LVL 25

Expert Comment

by:Will Loving
ID: 40211575
You should be able to easily setup a single scripted Export that will save each of these to the Desktop in Excel .XLS format, which should accommodate long field value. The script steps for each part would look somethings like this:

Go to Layout [ Contacts ]
Find [ Restore ]
Sort [ Restore ]
Set Variable [ $FilePath ; Value: Get( DesktopPath ) & "Contacts.xls" ]
Export [ No Dialog ; $FilePath ]

Go to Layout [ Transactions ]
Find [ Restore ]
Sort [ Restore ]
Set Variable [ $FilePath ; Value: Get( DesktopPath ) & "Transactions.xls" ]
Export [ No Dialog ; $FilePath ]

Go to Layout [ Notes ]
Find [ Restore ]
Sort [ Restore ]
Set Variable [ $FilePath ; Value: Get( DesktopPath ) & "Notes.xls" ]
Export [ No Dialog ; $FilePath ]

Some notes on the above:

1. If you want all records rather than a found set, you can substitute the "Show All" step for the Find [ ] step.

2. As you setup this script, keep in mind that FileMaker intelligently looks at the last Find, Sort, Export etc. that you did in FileMaker and automatically places those settings in the script step (which you can then use, modify or ignore). So before you add each section above, manually do the Find and Sort and Export that you want to use and THEN add the script steps to the script. Each step will then have the exact same Find, Sort and Export specifications that you just used unless you uncheck the "Restore" option.

3. In order to export, I'm setting a script variable called $FilePath and then placing that variable in the Export script step instead of an explicit file path. You can set an explicit file path if you like, but this gives you more flexibility. Since the export file names are not unique, each time you run the report, any files on the Desktop will get overwritten. To make unique file names, include something like the date or time in the calculation. For example, to include the current time in the file name, use the following:

Get( DesktopPath ) & "Contacts_" & Filter( Get( CurrentTime ) ; "0123456789" ) & ".xls"

That will give you something like Contacts_174613.xls for a file created at 17:46:13

To include the current date:

Get( DesktopPath ) & "Contacts_" & Filter( Get( CurrentDate ) ; "0123456789" )  & "_" & Filter( Get( CurrentTime ) ; "0123456789" ) & ".xls"

That will give you something like Contacts_07222014_174613.xls for a file created at 17:46:13
LVL 13

Author Comment

ID: 40212911
Thanks for your help Will.
I am really struggling with this - being unfamiliar with FileMaker.
When I add "Go to Layout" to the script and Click on Specify drop box, there are no options for Contacts, Transactions or Notes.
I see:
original layout
Layout Name by Calculation...
Layout Number by Calculation...
Main Menu
DOB Calculator
To Do
Complete Invoices
LVL 25

Expert Comment

by:Will Loving
ID: 40212974
Do you have more than one file? If so, you need to be in the file that contains the tables you want to export. If you go to File -> Manage Database and look at the list of tables, do you see the tables you want to export? If you do, it will also list the "Table Occurrences" (or TOC) that appear on the Relationship Graph for each of those table (a table can appear multiple times on the relationship graph with different names). Each Layout is based on a TOC from the graph.

It's possible (though I'd consider it unlikely)  that you don't have any layouts based on those tables. If that's the case, you'll need to create at least a basic layout (I'd use the default format of "Table") for each one. If the tables and layouts are in another file, then you'll need to create the script in that file.
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

LVL 13

Author Comment

ID: 40212994
There are three different files. Is it possible to have the script open each file and export them? I need this to be fully automated.
I really appreciate the help you are giving me.
LVL 25

Accepted Solution

Will Loving earned 500 total points
ID: 40213076
In each of the files you'll need to add a script similar to:

Go to Layout [ Contacts ]
Find [ Restore ]
Sort [ Restore ]
Set Variable [ $FilePath ; Value: Get( DesktopPath ) & "Contacts.xls" ]
Export [ No Dialog ; $FilePath ]
Go to Layout [ original layout ]
Adjust Window [ Hide ]

Then, in the main file that you started with you'll need to make a script that has three "Perform Script" steps in it, each one calling the script you've created in the individual files. This will look like:

Perform Script [ "Export Contacts" from file: "Contacts" ]
Perform Script [ "Export Transactions" from file: "Transactions" ]
Perform Script [ "Export Notes" from file: "Notes" ]

To select a script in another file, first add the "Perform Script…" script step. When you do it will open the "Specify Script" dialog. In the first Popup menu at the top select the file you wish to call the script from, for example the file containing the Contacts table, then select the script.

The Adjust Window [ Hide ] step above is optional but will send the file to the background after the export if you would like that to happen.
LVL 13

Author Closing Comment

ID: 40221840
Thanks again for your help. I have tested this and I'm sure I can make it work with my customer's files. You are a FileMaker Guru!

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can't open Filemaker Server 12 Start page 1 519
Filemaker xsl file 8 132
Filemaker xsl help please 3 90
open URL to email message in apple mail from filemaker 1 124
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now