Greetings,

I have two tables:

 Home Products (several types of products some of which are also sold as commercial products), and commercial Products (again several types of products and again some of which are also sold as home products).

Each table has a summary field that totals the amounts of sales.

I’ve created a layout based on a dashboard table, and I want a portal on this layout to display combined totals by types of products, whether they are home or commercial.

So what setup is required to display each type of products (like blinds) only once, and the total amount of sales for that type, so that the end result looks like this:

Blinds: 500

Paint: 300

 I've tried to do this with just a calculation field, like Sum of the two summary fields, but I'm obviously missing something.

 Thanks.

1 hour ago, Tom Harvey said:

what setup is required to display each type of products (like blinds) only once, and the total amount of sales for that type

I believe the simplest setup would be to have a table of ProductTypes, where each type has a unique record. Then use a calculation field in this table to sum the related sales. Then show a portal to this table.

---
Not sure if this is related to your question, but it sounds like your two tables should be one, with a field to distinguish between home and commercial sales.

 

Thanks. I guess my real question was what can I do with my current two tables (+ dashboard) to achieve what I want, if I want to keep the structure I have now. Is it possible to add up the two summary fields and display the result in a portal along with the specific product to which the total relates? 

Edited February 28, 20214 yr by Tom Harvey

A summary field defined in a related table returns the summary value of all related records. If you want to show a sub-summary value for each group of related records, you will need to put in some work - see: https://fmforums.com/topic/71906-getting-more-out-of-filtered-portals-3unique-values/

If you want to combine the results from two related tables, so that each pair of sub-summary values is added up to a single value, then you are looking at an awful amount of work. I am not sure it's even possible. It's certainly not possible to show them in a portal, because if the portal is to one of the tables, and there is a type of products that only appears in the other table, there is no way you will have a portal row showing that type.

 

Edited February 28, 20214 yr by comment

Thanks for your explanations.