Solved

Exporting data from FileMaker Pro 7.5

Posted on 2014-07-21
6
307 Views
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?
0
Comment
Question by:akb
  • 3
  • 3
6 Comments
 
LVL 24

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
0
 
LVL 13

Author Comment

by:akb
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
Start
About...
DOB Calculator
Scheduling
To Do
Hospital
Complete Invoices
0
 
LVL 24

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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Author Comment

by:akb
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.
0
 
LVL 24

Accepted Solution

by:
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.
0
 
LVL 13

Author Closing Comment

by:akb
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!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

20 Experts available now in Live!

Get 1:1 Help Now