Unable to perform a supposedly easy task :( A report with a recollection of Sums

[Magistrate2020]

Hi,

I have a problem, even though I reckon the solution should be easy I seem to be unable to solve that. I have this database, made of 11 different layouts, each of them sports a "Total Value" of the page that is the result of a calculation of a repeating field.

What I want to achieve is to have a 12th layouts where it would display the "Total Value" of each other layout taken from the other layouts.

I tried to make a "relationship" between the tables, by linking Field A from Layout1 to Field A from layout 12, plus I set the field in layout 12 to be a calculation field.

I have been unable to display the sum of Field A though, not only, I would need the field to be automatically recalculated in layout 12 each time the sum vary in layout 1.

Anyone can point me to the right direction and/or debug what I did so far?

Thanks in advance.

[mr_vodka]

Layouts dont really mean too much here, you can have 50 layouts based on the same table occurrences, or 10 layouts based off 10 different table occurrences.

Please describe your structure in greater detail. Are you trying to get the summary of all the records for each table into one layout of of each found set per each layout?

[Magistrate2020]

The DB is structured with 13 different tables, the first 12 are each representing of a section of the inventory, the 13th should be the total report from all sections plus the sum of them:

Layout1 (Product1 Inventory)

Field-A1 Product1 Name

Field-B1 Quantity

Field-C1 Price

Field-D1 Product Value (Quantity x Price)

Field-E1 Total Product1 Value (Sum of Product Value)

Layout2 (Product2 Inventory)

Field-A2 Product2 Name

Field-B2 Quantity

Field-C2 Price

Field-D2 Product Value (Quantity x Price)

Field-E2 Total Product Value (Sum of Product Value)

Layout3 (Product 3 Inventory)

Field-A3 Product3 Name

Field-B3 Quantity

Field-C3 Price

Field-D3 Product Value (Quantity x Price)

Field-E Total Product Value (Sum of Product Value)

Layout4 (General Report)

Field X = Field-E1 Total Product1 Value (Sum of Product Value)

Field Y = Field-E2 Total Product2 Value (Sum of Product Value)

Field Z = Field-E3 Total Product3 Value (Sum of Product Value)

Field K = Sum of Field X + Field Y + Field Z

Note: Fields A,B,C,D are fields with repetitions, Field E is a single calculation field. Also, the whole thing is based on a SINGLE record for each table.

---

What I cannot achieve is, on layout 4 to properly read the Fields E1,E2,E3 and bring them into Field X,Y,Z in order to make the calculation implied in Field K. Also, I would need Field X,Y,Z to selfupdate the numbers WHEN something changes in Field E1,E2,E3.

Edited June 20, 2008 by Guest

[mr_vodka]

Look into the cartesian join ('X') operator. You will want to join your General Report table to each of the other tables with this type of join. Then you can have one calc that adds each of those Product Values fields together.

Sum ( D1; D2; D3 ) etc.

[comment]

What does this solution do? It looks like an invoicing solution - but why 12 tables "each representing of a section of the inventory"? There should be one table for inventory (or Products), one table for Invoices, and one table for invoice's LineItems (instead of the repeating fields).

[Magistrate2020]

Look into the cartesian join ('X') operator. You will want to join your General Report table to each of the other tables with this type of join. Then you can have one calc that adds each of those Product Values fields together.

Sum ( D1; D2; D3 ) etc.

I am sorry but, could you explain a bit more in details? Is the cartesian join X operator a calculation function? Or are you referring to table linking?

What does this solution do? It looks like an invoicing solution - but why 12 tables "each representing of a section of the inventory"? There should be one table for inventory (or Products), one table for Invoices, and one table for invoice's LineItems (instead of the repeating fields).

Point is, that each section of the inventory has different evaluation criterias, it's not just description, quantity x price. Some section calculate the value based just on meters per price, others kilograms per price, others meters AND kilogram per price etc... So I thought splitting up tables would have helped up messing up things less. Also, I don't need invoices in this DB. I know it's a complicate DB and I also know that if I knew more about FMP I could have done it much easier, I am trying to learn though :)

Edited June 20, 2008 by Guest

[mr_vodka]

Its when you creating relationships between table occurrences. The default is usually, =, Change it to X.

[comment]

It's hard to be sure with so short a description, but it still sounds like the pricing is merely an attribute of a product, and does not justify creating a separate entity for each pricing scheme. Summarizing will be much easier that way - specially if you replace the repeating fields with related records.

[Magistrate2020]

Its when you creating relationships between table occurrences. The default is usually, =, Change it to X.

Thank you very much, it is exactly the step I was missing to make it work the way it was intended

It's hard to be sure with so short a description, but it still sounds like the pricing is merely an attribute of a product, and does not justify creating a separate entity for each pricing scheme. Summarizing will be much easier that way - specially if you replace the repeating fields with related records.

I know it could have been organized better from scratch, my problem is that I had no prior DB experience and I had to learn myself, the hard way, as soon as I'll have enough knowledge I will redesign the DB properly. I am open for suggestions though.

[comment]

As I said, we don't have enough information to make a suggestion. You may find some previous threads on the subject of pricing helpful:

http://www.fmforums.com/forum/showtopic.php?tid/150039/

http://www.fmforums.com/forum/showtopic.php?tid/158664/

http://www.fmforums.com/forum/showtopic.php?tid/195995/

[Magistrate2020]

As I said, we don't have enough information to make a suggestion. You may find some previous threads on the subject of pricing helpful:

http://www.fmforums.com/forum/showtopic.php?tid/150039/

http://www.fmforums.com/forum/showtopic.php?tid/158664/

http://www.fmforums.com/forum/showtopic.php?tid/195995/

Would it help if I'd post a coupla screenshots of a some inventory sections?

[comment]

I don't know. What could help is a plain-English explanation of how the value needs to be determined.