Looping through records to get a total.

[PaulBI]

Good day,

I have two tables. The first table (a02_Opportunities_Full_Details) captures the sales opportunity and the necessary fields. Three of the fields are sales credits for consultants, so for each opportunity I can allocate sales credits to 3 different people.

The second table (a03_Opportunity_sales_credits) is populated through a triggered action, which breaks the opportunity into three separate records, one for each consultant. I did this because across the different opportunities I want to aggregate the number of sales credits per consultant, but I couldn’t find a way to do it in a single record.

Using the second table and trigger event, I create 3 separate records so getting the individual consultant’s sales credits is possible. Please see attached image.

Please can I ask for your assistance.

Do you know if there is a more efficient way to achieve the result I need for the sales credits?

Many thanks

Paul

[Volomeister]

Hi Paul!

I would suggest redoing current table structure, because it consists of multiple duplicate information, empty fields and is not scalable in the long run (e.g. imagine you have 7 agents working on one opportunity and only 1 agent working on another).

Check out database normalization concept and table relationship.

This is a classic example of a so-called many-to-many relationship between tables.
One opportunity can have multiple sales agents working on it, and at the same time, each sales agent can be involved in multiple opportunities.

Many-to-many tables relationship can be created by combination of two one-to-many relationships.
So to facilitate such a scenario, first, we 3 tables.

1. Opportunities:
https://monosnap.com/direct/gwRKXZ89cdecqn4lMHRqySSgkxvA3Z
https://monosnap.com/direct/njA5h0UADjPaapSOZvlmVK90s2WTgR

2. Consultants/agents:
https://monosnap.com/direct/GZl8e13C5rkL39WKq9Tthim9WRYZbP
https://monosnap.com/direct/P14lLrxABHpJzTQ9TTZ55Ih45DIeW3

3. Combined table Consultants-Opportunities:
https://monosnap.com/direct/Vq93PRBPEVqbNT3nVcDg9bTQ03Vo8j

Then, define relationship relationship between tables:
https://monosnap.com/direct/Cpd3NQduE5XyDyG13POw2peq6CaBQw

Opportunities to Consultants-Opportunities is a one-to-many relationship
Consultants to Consultants-Opportunities is a one-to-many relationship

After that, you can create an input form based on the Consultants-Opportunities table to enter agent's credits per each opportunity.

And finally, you can create a pivot table report based on Consultants-Opportunities:
https://monosnap.com/direct/qI9SdRhm6th6fyzDGImpVwyDKC0Viv



Hope this helps