Many to many in a single report

[bluearrow]

Hi,

I have to make a decision regarding an issue that has been a problem in a pre-7 database.

A project is made up of line items 1 and line items 2.

The invoice is made up of line items 1.

Line items 1 are related many to many with line items 2.

Line items relate only if they belong to the same project (line items from project A are not related in any way with line items from project .

The profit for a given project is the result of:

line items 1 - line items 2

I have to show in a report line items 1 and line items 2, and calculate profits line by line.

Currently, this line items are part of 2 different files. The report is generated in a layout from Line items 1, but show the corresponding line items 2.

When moving to FM 8.5 I may:

1.- Create a single table to hold both line items and create a field to differentiate them.

2.- Create 2 separate tables and "tunnel" and calculate data from one database to the other.

What do you think it is the best approach. It might be that what was a terrible problem in versions prior to 7 is not an issue in 8.5, given the table structure.

Thanks

[Genx]

2.- Create 2 separate tables and "tunnel" and calculate data from one database to the other.

... If I understand correctly, It's called a join table and is standard practice where a many-to-many relationship exists.

[bluearrow]

So you think it is better to have separate tables for this 2 type of lines. But, are you sure this is fine even if I need to:

1.- Calculate among tables, for instance, sum field from several records in table 1 and substract the sum of several records in table 2.

2.- Use this value in a report and calculate subsummaries, etc.

I can assure you this was a nightmare in FM6 using 2 files, that being the only reason to try to create a single file in FM8.5.

Regards

[Genx]

.. Well, you didn't really mention that Line Items 1 and 2 store exactly the same information, or go into the details of how they were related... Just expand on your issue a bit -- What do you define as a line, what makes line 1 and 2 different, how are they related?

[bluearrow]

.. Well, you didn't really mention that Line Items 1 and 2 store exactly the same information,

They don't, although they share most of the fields (not all though, of course).

or go into the details of how they were related... Just expand on your issue a bit -- What do you define as a line, what makes line 1 and 2 different, how are they related?

OK. I will try to explain.

Line 1 items

They constitute the invoice line items. They store typical invoice info such as Units, Unit_price, etc. They share the same Client, same Invoice and same Project with Line 2 items (among other fields). They do not share the same Creation_date, Modification_Date, Units or Unit_Price with line 2 items (among other fields).

Line 2 items

They have nothing to do with the invoice, except for the fact that in an invoicing report they must be taking into account to estimate, among other things, the profit of a project. Thus, Invoice_ID must be present in this lines. So has to be Project_ID because Line 1 items and Line 2 items share the same project. Line 2 items are related with Freelance workers. This lines has its own Creation_date, Modification_Date, Units or Unit_Price, because a task (read bellow) in line 1 can be assigned to one or more Lines 2 (Freelancers). Each freelance has its own Unit_price (the price they charge for doing the task). The Units in Line item 2 are not the same as the Units in Line 1 (not even the total of all related Line item 1 and Line item 2 is the same).

Project

A Client generates a task, this generates one or line items in a Quote, then this line items go to a Project. It is here, in the project, when this tasks are assigned to Freelancers (Line 2 items). Therefore, Line 2 items have a Freelance_ID, but Line 1 items do not.

Costs and profits

A project cost and profit, and a Client monthly, yearly, etc profit needs to be calculated taking into account the relationship among Line 1 items and Line 2 items. For instance, Line 1 cost / Line 2 cost. The situation is much more complex than this, and field Units is very important because the marketing department needs to know (among other details)

- how much is the client charging per Unit

- how much is that Unit costing the company

- what is the profit of all Client projects in a given amount of time

etc.

Please let me know if you need more info or if anything expressed here is unclear.

Thank you and regards