Showing paid invoices

[Richard Cassidy]

Hi,

First ever post so here goes...

Overview

I am trying to create a portal showing all the unpaid invoices for a client. The ultimate goal is to have a button to produce an individual client statement of amounts due.

Structure

I have a client table, an invoice table and a payments table.

The fields I'm using are 'client ref' and 'pay status'. 'Pay status' is calculated by taking the sum of payments in the payments table ('due') and subtracting it from the invoice total ('total'). The calc for 'pay status' is If(due=0,"paid", "unpaid")

The Problem

I'm trying to define a relationship between the client and invoices table based on a calculated field in each table: in clients it is ' client ref & "unpaid" ' and in invoices it is ' client ref & pay status '. The problem is that 'pay status' contains a summary field and therefore FM says I cannot use it to define a relationship.

I'm going round in circles. So is there a way to do this, am I doing everything backwards, is my database structure screwy?!

(I already have it doing all clients by using a pre defined search in a script in the invoices table and then showing it in preview mode in a layout, but then you have to page through to find the right client page and then make sure the print settings are right which is not very friendly)

Hope this makes sense. Any help much appreciated.

Cheers,

Richard

FileMaker Version: 6

Platform: Mac OS X Panther

[CyborgSam]

Richard->

Welcome to the forums!

You should be able to use calculaitons with Aggregate functions and self-join relationships. Define a relationship in the invoices file from the field client ref to the field client ref (in the invoices file). This relationship gives you access to all record for a particluar client.

Now define a calculation field with the aggregate function Sum() , fill it using this relationship, e.g. Sum(theRelationship::theField).

Does this do what you need?

Sam

[Richard Cassidy]

Hi Sam,

Thanks for the reply, but I am not sure how to do this right.

I can create the self relationship no problem but don't know how to use it to show only the unpaid invoices for a client.

I have made a layout on the invoice table showing the client ref field as a pulldown with a portal using the self ref to show only records for that client but I cannot make the portal display only unpaid invoices. Is this the right approach? Also, is a portal any good to use for a layout used for printing?

A bit more advice/hand holding would be much appreciated!

Thanks,

Richard

[stanley]

Richard:

If your ultimate goal is to print out an invoice or summary, you want to stay away from using portals, because they do not handle printing well at all in FMP6. If you're going to print, print from the file where the records reside; in that case, you may want to consider using a Find within that file (using the Client Ref and Unpaid)...

Then you can use a List View to build your printable form from...

-Stanley

[CyborgSam]

Richard->

Stanley's advise is good. I'll describe what to do it you want to use a portal.

To show just the clients that have unpaid invoices in a portal you'll need another relationship. One method I like to use for flexibility is booleans: relate true to a boolean calculation. In your case you could create a global number field and set it to 1. Then define a calculation that determines if an invoice is unpaid. Using your first post that could be "due<>0".

Gotcha to watch out for: FileMaker treats any non-zero value as true in a boolean expression. But you used just "due" in the relationship, FileMaker would see that as a number, since it is not part of a boolean expression. So it would only match clients whose due amount was 1.

Does this make more sense?