I’ve searched the forums for a solution to what I’m trying to implement but couldn’t find anything quite like it.
Here is my sample document:
In essence:
I want to know how much each company has contributed to my business based on the deals registered
I want to create a summary table that sums all deal values and groups them by company.
I would also like to have additional columns where I can make notes per row and add a rating to each company. These are independent of the original data.
I would also be filtering these summary tables by status of the deal (won or lost)
The main challenge I’ve faced is that I’d like my summary table to always be up to date based on whatever is in my “Deals” table so that it is self maintained.
So far I’ve followed the “grouped column” approach and added a summary row. This unfortunately doesn’t allow me to add the ratings and comments column at a rolled up level. It also doesn’t allow me to sort data by total.
Any help or ideas on how to achieve what I’m looking for will be greatly appreciated
I’ve played around a little bit with your setup and here’s what I’ve got …
I must admit I’m not entirely satisfied with it (something and I can’t find what, is still bothering me actually ) and others might have better ideas !
I’ve added a Companies table which is linked to the Deals table using a Lookup field.
So, instead of doing everything on the Deals table, the maths are done in the Companies table .
Then from that table, I’ve created 2 connected views : One for the Won deals and another for the Lost ones .
Again, that’s just an idea and there might be other ways to get to that result .
Thank you so much for this!
This is very close to what I’m looking for.
The only thing that would be missing is if it’s possible for the Companies table to auto populate the Companies column. I can see in your example that this was inputted manually. If this could be populated automatically it would allow this table to be self maintained from the data in the deals table.
Concerning the auto-population of the Companies table, I did enter the names of the companies manually when creating this sample so I would be able to select them in the Deals table.
But, by the magic of lookups, if you have a new deal with a very new company (which would not be in the table Companies), you can simply create that company when populating the Deals table by writing the name of that company, in the Lookup field Companies in the Deals table .
There’s an option in that field (in item settings) that allows quick adding of new items : in this case, the name of the concerned company. So, normally, you should just need to add your deals in the Deals table (and create the Companies from there, if necessary) .
Would there be a way for the Companies column in the Companies table to pull new companies entered from the Deals table? If not, I’ll just maintain that table manually.
Sorry, it was very very late, here, when I last replied to you, so my last post might have lack some clarity !
Manually maintaining the Companies table shouldn’t be required (well, you can if you want to though ).
All can be done from the Deals table and I mean by that, adding a new/missing company in the Companies table, for example.
How does that work ?
Well, if you take a look at the Lookup field [Companies] in the Deals table, there’s a menu called “Item settings”
In that menu, you’ll find an option to “Allow quick adding of new items”, which I left on ON.
(In this case, the new items will be missing companies from the Companies table)
Meaning that if you add a deal in the Deals table and you can’t find the appropriate company in the [Companies] field you can create that missing company directly from the [Companies] field in Deals .
And just in case this is still not clear, I’ve made a screen recording to show all this “in action” .
In that screen recording, I’m adding a company (which I called Company 8) in the Companies table through the [Companies] lookup field in the Deals table .
Edit : The “long” version of the video can be found here (just in case )
!
) and others might have better ideas 
!
!
!
).
