Exporting data from FileMaker Pro 7.5

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?
LVL 13
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresidentCommented:
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
akbAuthor Commented:
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
Will LovingPresidentCommented:
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.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

akbAuthor Commented:
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.
Will LovingPresidentCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
akbAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.