Solved

FileMaker Pro Reporting on relational database

Posted on 2013-11-25
4
456 Views
Last Modified: 2014-07-21
I am new to FileMaker.
I have a customer with a database in FileMaker Pro 7.5.
There are three tables:
 - Customers (Customer number, Name, Address, Account Balance)
 - Transactions (Date, Transaction Type, Amount)
 - Notes (Date, Comment)

I need to produce a report which produces a PDF of all customers in the form:

---------------------------------------------
Customer Number
Name
Address
Account Balance

Transaction Date, Type, Amount
Transaction Date, Type, Amount
Transaction Date, Type, Amount
etc…

Notes Date, Comment
Notes Date, Comment
Notes Date, Comment
etc…

<Page Break>
---------------------------------------------


Is this possible using FileMaker Pro?


I thought about exporting the data to Excel in the form:

Customer number, Name, Address, Account Balance, Transactions Date, Type, Amount, Notes Date, Comment
Customer number, Name, Address, Account Balance, Transactions Date, Type, Amount, Notes Date, Comment
Customer number, Name, Address, Account Balance, Transactions Date, Type, Amount, Notes Date, Comment
etc…

and then doing a merge in Word but I couldn't work out how to massage this data into the required report format.

I need to be able to automate the process so the PDF can be transferred to a tablet device for use in the field.

Any suggestions would be appreciated.
0
Comment
Question by:akb
  • 2
4 Comments
 

Assisted Solution

by:dianepool
dianepool earned 200 total points
ID: 39678102
Create a global field called gNotes.
the first section is going to be a Subsummary when sorted by Customer #
Your script is going to Find the Customer's records, sort by Customer #, then Transaction Date

Your Body will have Transaction Date, Type, Amount
Create another Subsummary when sorted by Customer #, page break afterwards
Loop
SetField gNotes to gNotes & Notes Date & Comment & paragraph mark in quotes
Go to Record/Request (Next) Exit after Last Record
End Loop
Put gNotes in the Subsummary field
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 300 total points
ID: 39678244
Unless you create a special reporting table (which adds complexity), you'll need to create the report in a layout based on either Transactions or Notes. I'm assuming that you will have fewer Notes than Transactions so I would presume to create the layout in Transactions and gather the notes into a single field.

The technique Dianepool suggests will work but there is a simpler way. Rather than creating a loop in the script, just create a calculation field in Customers called NoteList and use the List() function to aggregate the notes into a single field. First create a field in Notes called NoteTextDisplay that concatenates the NoteDate, NoteText and whatever else you might want in terms of formatting. The field might look like this:

     NoteTextDisplay = NoteDate & " - " & NoteText

Then in Customers create the NoteList field

     NoteList = List( Customer_Notes::NoteText )

The Notes will sort be creation order unless you have applied a Sort to the relationship from Customers to Notes to do it some other way such as by NoteDate descending (so newest comes first).

On your report layout - which needs to be set to List View - you need to find all Transactions records for the customer and sort them as you wish using a script. In the Header or Sub-Summary part you can add the related fields from the Customer table to display the Customer's name, address and account balance.

The NoteList field, also from Customers can be displayed in a sub summary or trailing grand summary part below the Body part which contains the transaction information. Both the NoteList and the layout "Part" containing the NoteList field must be big enough to contain the maximum number of Notes that might be displayed. Finally, make sure you set the "Sliding" option for the NoteList field to "Slide Up and reduce size of enclosing part". That will ensure that the NoteList field only takes up as much room as needed rather than the maximum that might be needed.
0
 
LVL 13

Author Comment

by:akb
ID: 39682607
Thanks to both of you for your help. It will be a few days now before I can try this but I will get back to you as soon as I have.
0
 
LVL 13

Author Closing Comment

by:akb
ID: 40210760
Sorry this has taken so long to get back to.
Both solutions look viable but I am struggling with FileMaker, never having used it before.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

832 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