Clients and invoicing relationships

[Guest]

Hello all,

I'm a newbie to filemaker and have run into some problems. I'm trying to create a project database that will allow me to track projects, clients and send invoices.

I have 3 tables; projects, invoices and clients. The problem I have is that I want to link a client to a project but I also want to link a client (I don't always invoice the same client that I do the project for) to the invoice. Curently I have a many->one between projects and invoices and a many->one between projects and clients. Without creating a new table for clients I invoice, is there a way to have different clients on the invoice i.e. One that the projects are linked to and one who I invoice. 

Thanks.

[Matthew F]

A single table can have any number of representations in the relationship graph. You should create a second representation of the 'Clients' table to avoid creating a circular relationship with 'Projects' and 'Invoices'.

For example, you could change the name of the 'Clients' reference to 'Project Clients' in the relationship graph. Then create a second representation of the 'Clients' table, call it 'Invoice Clients' and link it to 'Invoices'.

[bcooney]

Sounds as if an Invoice simply needs a _kF_ProjectID and a _kF_ClientID. You can lookup the _kF_ClientID from the Project's _kF_ClientID, but allow the user to select a different ClientID for the Invoice.

So, each invoice will know its Project (and therefore that Client) and the Client that you Invoice.

It's a bit odd that you'd invoice a client for a project that isn't theirs, but there's no rule that the Invoice's ClientID must be the Project's ClientID.

btw, Welcome to the forums!

Edited September 5, 2010 by Guest

[Guest]

Thanks mfero and bcooney,

Creating a second representation of the Clients table seems to have done the trick. I might look into using a lookup in the future though as I'd quite like the invoice name and address to be set on the invoice.

[Matthew F]

I agree with your idea about the lookup. You don't want an old invoice to change, for example, when a clients address changes in the future. It should be invariant from the time is was issued.