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.

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'.

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, 201015 yr by 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.

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.