Solved

FileMaker Pro Reporting on relational database

Posted on 2013-11-25
4
444 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 24

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

15 Experts available now in Live!

Get 1:1 Help Now