?
Solved

Exporting data from FileMaker Pro 7.5

Posted on 2014-07-21
6
Medium Priority
?
339 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
[X]
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
6 Comments
 
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
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 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.
0
Independent Software Vendors: 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

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 25

Accepted Solution

by:
Will Loving earned 2000 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

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month14 days, 21 hours left to enroll

770 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