Line Items settings

[Ugo DI LUCA]

Hi,

My current solution have Quotes, Customer Orders, Deliveries and Invoices, for the "Sales" part of it, each being a File with a Many to Many relationship to the other through a Line Items join.

With 7, each module became a TOG relating to a Line Items TO, that has 4 separate Foreign Keys (QuoteID, CustomerOrderID, DeliveryID, InvoiceID).

What could be the problem in having one ModuleID and a Module Type field instead of the 4 previous Module keys ?

I can't see any difference at the moment, and I'm even thinking I was a bit overcomplicating things with my v6 version. There must be a reason why I did that on the first place, but it now makes no sense to me.

Any idea ?

[Ender]

Ho Ugo!

So if I have this right, four files were sharing one Line Items file. A technique I have used myself (with some confusion.)

I have usually gone with the theory, 'If it's similar data, put it in the same file.' This kept the number of files down in previous versions of FMP. But it also made things more complicated when it came to the relationships, and import/export operations.

Now with unlimited number of tables, it's possible for each main table to have its own Line Items. I don't know if this is necessarily advisable. I suppose if the different Line Items data needs to be summarized within the same report, then it makes sense to keep it together.

But back to your original question; yes I think a single ModuleID with a Module Type field is better. Even in FM5/6 this is easier to deal with (think of all the variations of concatonated keys you can remove for filtered relationships.)

[Ugo DI LUCA]

Hi Mike,

Thanks for this confirmation.

The Unique Line Items file (except for PO'S) was the only way I could think to correctly track the "path" of a sale, from its starting point (a quote or a customer order) to its Invoices.

If one has a One To One relationship from each module (1 order => 1 delivery => one invoice), then quoteID, CustomerID and InvoiceID may even share the same line items.

That's how the solution I inherited was stuctured, and that couldn't work due to the Many to Many relationship from one Module to the Other in our case (One quote =>3 customer orders => 5 deliveries => 2 invoices)

My immediate idea was to split each module in a separate record in the Line Items, but curiously, while this left a few fields empty and unused, I never thought of having one key only.

Probably the Relationships Graph helped me discover this as well as a few things which makes the database even more flexible for unexpected changes.

Thanks.