(FMP 7) Relationships

[jasonwood]

I'm having trouble wrapping my head around the TOGs in FMP7 and I think once I have a answer to this question I will understand it much better...

In the FileMaker Business Tracker it is easy to see in the relationship graph a basic path from contacts to invoices to line items to products.

But what do you do if you have, say, a quotes database that is basically the same as the invoices database. Intuitively I'd split the path before the invoices/quotes and bring it back together afterwards, but that won't work cause then you'd have a loop.

So I guess there are a couple of options (ignore the dots)...

. . . . . . . / Invoices - Line Items - Products

Contacts

. . . . . . . Quotes - Line Items - Products

-or-

Contacts - Invoices

. . . . . . . . . . . . . . . . Line Items - Products

Contacts - Quotes /

... or just have two totally separate TOGs

Could anyone discuss why you would choose one over the other?

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[Charles Delfs]

If your quotes are the same (or almost the same) as invoices, use the same table to store them as the invoices, but, add an additional field that indicates what the record is: quote, invoice, Purchase order etc.

That way when a quote becomes an order there is no scripting required to move it from one table to the next.

Charles

[jasonwood]

Is that how it's typically done? There's a couple reasons why I'm not a big fan of that idea...

I'd have to work harder to get sequential invoice numbers, a legal requirement.

Old quotes that get invoiced will be in the wrong order

When quotes become orders, sometimes unless a client requests "ship complete", I issue invoices whenever items arrive, and only those line items are added to the invoice (the line items file has several foreign keys, for invoices, quotes, and service orders, 1 or 2 of which can be active).

Invoices may differ from the quote, and I like to refer back to see what was changed when the quote was invoiced.

[Ender]

Keeping Invoices and Quotes and their Line Items separate will ensure the integrity of your quotes and invoices. Though because Invoices and Quotes have similar fields, for convenience you could put them in the same table--you would just need to be clever about generating IDs. When a quote becomes an invoice, just duplicate the record and add the Invoice#. You would need some scripting to get the line items duplicated too. They could share the same Product file.

Below is an ER diagram showing the correct relationships, though the Invoice and Quote entities could still be in the same table (and both Line Items could share the same table)--just make sure the relationships are matched to the right one.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

[jasonwood]

yeah... but this is a loop, isn't this forbidden?