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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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 is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

728 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