[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

FileMaker Pro Reporting on relational database

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
akb
Asked:
akb
  • 2
2 Solutions
 
dianepoolCommented:
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
 
Will LovingPresidentCommented:
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
 
akbAuthor Commented:
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
 
akbAuthor Commented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now